abs()

The abs() function is used to return the absolute value of a number.

Syntax

abs(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the absolute value of -23.

Example

set @numVariable = abs(-23)

avg()

The avg() function is used to return the average value of a numeric field.

Syntax

avg(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the average value of the field "Salary".

Example

set @avgSalary = avg("Salary")

captureSignature()

The captureSignature() function is used to save an image of a signature to a File type field or variable. It’s mainly used together with a button.

The file formats that is supported are png, jpg and svg.

Syntax

capture('Label', 'File format')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code makes a window pop up where the signature will be written. The signature is then saved to the record as a png file.

For the image to be saved to the record, there must be a Media component in the form that has the right field connected to it.

Example

set Signature = captureSignature('Sign here please', 'png')
call save()

ceil()

The ceil() function returns the smallest integer value bigger than or equal to a numeric parameter.

Syntax

ceil(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the smallest integer bigger than 22.7

Example

set @numVariable = ceil(22.7)

chr()

The chr() function returns the character for the specified ASCII number code.

Syntax

chr(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the character "Line Feed".

Example

set @char = chr(10)

chr(10)

Use chr(10) as a line break for Note fields.

set "Worklog notes" = 'Work started on ' || now() || chr(10) || 'The provider is' || nvl(Provider, ' not available yet') || chr(10)

To get a result like this:

Work started on 06-04-2023 08:30:00
The provider is ACME

count()

The count() function return the number of records that matches a criterion.

Syntax

count(Field name) from Table name


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written in a IQL chart, and it makes the IQL chart display how many employees every department has from a table named Employee. The chart is both grouped and sorted by Department.

Example

Department, count(Recordname) from Employee
group by Department
order by Department

create()

The create() function creates a new record when called.

Syntax

call create()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code creates a new record in the datasource of the detail when the button is clicked.

Example

call create()

createFormUrl()

The createFormUrl() function creates a hyperlink based on the URL of an application form. It must be used nested within the openUrl() function.

Syntax

call openUrl(createFormUrl(recordid, form("Any form")))


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a Link component in a Table section from the origin form "All projects", which must open the destination form "Project information" selecting the project referred by a variable defined in the origin form. For this, be aware that the destination form has that variable defined as a parameter with the "Write parameter to URL" property checked. The createFormUrl function must be concatenated to the variable as follows:

Example 1

call openUrl(createFormUrl(RecordId, form("Project information")) || '?project=' || @project)

Note that the characters at the beginning and at the end of the first variable, i.e., ? and = respectively as shown in Example 1, must always be typed that way.

If more variables are added, they must be typed with & instead, as shown in Example 2.

Example 2

call openUrl(createFormUrl(RecordId, form("Project information")) || '?project=' || @project || '&panelToOpen=' || @panelToOpen)

In the above case, not only the Project is provided, but also another variable that will determine which Panel will be displayed when loading the destination form. As in the Example 1, make sure that any variable you concatenate to the URL is defined as a parameter with the "Write parameter to URL" property checked in the destination form.

currentuser()

The currentuser() function returns the user ID of the current user.

Syntax

currentuser()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the user ID of the current user.

Example

set :caption = currentuser()

currentusername()

The currentusername() function returns the username of the current user.

Syntax

currentusername()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the username of the current user.

Example

set :caption = currentusername()

days()

The days() function returns the difference between two Date fields, as a number.

Syntax

days(Start date, End date)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the amount of days between the field value Start date and today.

Example

set :caption = days("Start date", today())

where the result is "6" (days), if we consider today’s date to be December the 7th, and Start date as December the 1st.

delete()

The delete() function is used to delete records.

Syntax

call delete()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code deletes the selected record when the button is clicked.

Example

call delete()

find()

The find() function is used to search a text within the content of a field.

Syntax

find(string where to search from, string to be found)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

Example 1

The following IQL is an example for an advanced filter in Record selector:

find(NoteField, TextField) > 0

Having a record which NoteField’s value is 'ABCDEF' and TextField’s value is 'CDE', this filter would be successful to find it. The comparison > 0 simply refers to the number of occurrences found, so if it’s found at least once, it’s a match.

You may nest the function substring() as first parameter in case you need to specify a portion of its content.

Example 2

The following IQL is an example for Rules. let’s say that you have to import a file with historical Inspections which record names contain the associated project and, once imported, you wish they were automatically related to the matching Project. For that, this query could be written for the Inspection table upon creation:

let @inspection = RecordName
for Project do
   let @project = RecordName
   if find(substring(@inspection, 11, length(@inspection)), @project) > 0 then
      let @match = @project
   end
end
set Project = @match

Based on the above, the Inspections

Daytona01/Daytona Beach/2025-06-01 Orlando01/Speedway Daytona/2025-03-01

would be related to the Project “Daytona”, but the Inspection

Daytona01/Int Blvd/2025-06-01

would not count.

Note that the substring function uses in this case the start parameter as "11", with the length parameter to whatever long the RecordName is, to only considered that portion to search for the project name.

floor()

The floor() function returns the largest integer value smaller than or equal to a numeric parameter.

Syntax

floor(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the largest integer smaller than 22.7.

Example

set @numVariable = floor(22.7)

form()

The form() function is used to return a form from the form name. It is often used together with the open() function.

Syntax

form('Form name')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

x

✓

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code opens the form, Employee Overview.

Example

call open(form("Employee Overview"))

formatNumber()

The formatNumber() function returns a string representing a number formatted according to a specified format (look for patterns here).

Syntax

formatnumber(Number, 'format')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

x

x

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the value of the field Distance formated as ".#", i.e 2 decimals.

Example 1

set :caption = formatnumber(Distance,'#.##')

Example 2

You can also use Behavior and @value, together with the function formatnumber to set a pattern to the values of a numeric column in the Table chart. For example, let’s say that "QTY" with be displayed with the dollar sign and one decimal:

set :text = formatnumber(@value, '$ 0,0')

Example 3

set "Note" = 'The invoice number' || RecordName || 'was approved with the total amount of' || formatnumber("Invoice total", '$ 0,0.00')

formatdate()

The formatdate() function returns a text string representing a date formatted according to the specified pattern.

Syntax

formatdate(Date,'format')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the value of the field Start date formated as "Day DD, Month, YYYY".

Example 1

set :caption = formatdate("Start date",'Day DD, Month, YYYY')

Example 2

In the example below, in Rules, note that this function converts now() into a string with ISO date format, to be concatenated with some other string.

set Note = 'Paid on' || formatdate(now(), 'YYYY-MM-DDTHH:MM:SS')

Read more about this function here.

geodist()

The geodist() function returns the distance between two latitude and longitude coordinates.

Syntax

geodist(latitude-1, longitude-1, latitude-2, longitude-2)
The type of the parameters is number.


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the distance beetween the two coordinates represented by the number fields latitude1, longitude1, latitude2, longitude2. The distance is displayed in meters.

Example

set :caption = 1e3 * geodist(latitude1, longitude1, latitude2, longitude2)

getRecordGroup()

The getRecordGroup() function returns the name of the group the record belongs to, based on a string or a concatenation of string and variable.

Syntax

getRecordGroup(text)
getRecordGroup(text || Field name)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

x

✓

✓

For the following example, let’s say that the record groups in your application are named with the pattern "Contractor-Name of the contractor", like "Contractor-IS Tools". Also, you have a Task table where records have a Relation field with a list of contractors, like "IS Tools". To make it easy to automatically store this record in its corresponding Record group, you can use this function in order to match the proper Record group with a concatenation of a string and a variable.

Example

let @rg = getRecordGroup('Contractor-' || Contractor Field)
if @rg is not null then
    set recordgroup = @rg
else
    set recordgroup = STANDARD
end

Note that if the Else is not part of the query, the rendered form will warn the user in case the field has a non-matching value with the existing Record groups.

length()

The length() function returns the length of a specified string.

Syntax

length(text)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to the length of the Full name value in a record.

Example

set :caption = length("Full name")

lower()

The lower() function returns a specified string with only lower-case letters.

Syntax

lower(text)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to "lower-case".

Example

set :caption = lower('Lower-Case')

max()

The max() function returns the maximum value from a field where certain criterion are met.

Syntax

max(Field name) from Table name


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written in a IQL chart, and it makes the IQL chart display the highest salary from each department from a table named Employee. The chart is grouped by Department.

Example

Department, max(Salary) from Employee
group by Department

median()

The median() function returns the median value from a field where certain criterion are met.

Syntax

median(Field name) from Table name


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written in a IQL chart, and it makes the IQL chart display the median salary from each department from a table named Employee. The chart is grouped by Department.

Example

Department, median(Salary) from Employee
group by Department

min()

The min() function returns the smallest value from a field where certain criterion are met.

Syntax

min(Field name) from Table name


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written in a IQL chart, and it makes the IQL chart display the lowest salary from each department from a table named Employee. The chart is grouped by Department.

Example

Department, min(Salary) from Employee
group by Department

mod()

The mod() or modulo function returns the remaining quantity given a total number divided into groups of fixed quantity.

Syntax

mod(total number, fixed number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example for Rules. The warehouse must pack material in batches of 4 pieces in each box, and the total amount to be packed is 11 pieces.

Example

set Remaining = mod(“Quantity to pack”, “Batch quantity”)

This would result in 3, meaning 3 pieces will remain unpacked, assuming that a couple of boxes will be full (each with 4 pieces).

now()

The now() function is used to return the Date time type value of the moment when the function is called.

Syntax

now()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code sets the Latest clock-in value in the selected record to the Date time type value of when the button is clicked.

Example

set "Latest clock-in" = now()

nvl()

The nvl() function is used to replace a null value to a specified value.

Syntax

nvl(Field name, value)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from Run IQL the Rules manager. The code is written to set the Salary field value to 20 000 if the Salary field is empty when a record is created.

Example

set Salary = nvl(Salary, 20000)

open()

The open() function is used to open a form.

Syntax

call open(form('form name'))


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code opens the form, Employee Overview.

Example

call open(form("Employee Overview"))

openRelated()

The open() function is used to open a form and use the value of a Relation type field to send to the form.

Syntax

call openRelated(form('form name'), Relation field)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The detail section displays records from the table, Employees. The Employees table has a Relation type field that is named Latest project and has Project as parent table. The code opens the form, Project Overview with the selected record’s Latest project value as chosen record.

Example

call openRelated(form("Project Overview"), "Latest project")

openUrl()

The openUrl() function is used to open a specified url.

Syntax

call openUrl('Url')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. The code opens the Google image page for "Giraffes".

Example

call openUrl('https://www.google.com/search?sca_esv=7035ff6bfa45f4a8&sca_upv=1&sxsrf=ADLYWIK1GNR7E7FifIAx2eLeUNpkqYdZyg:1722522357572&q=Giraffes&udm=2')

Also, you can nest createFormUrl within openUrl to avoid typing the desired URL of a known form, as in the following example:

Example

call openUrl(createFormUrl(RecordId, form("Any form")) || '?variable=')

padStart()

Returns a string left padded to the specified length, with a pad character, in order to set a pattern.

Syntax

padStart(string, maxlength, padString)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

✓

✓

Given a Table with Generated series set to "1" as Record name policy (which makes the records be named as 1, 2, 3, and so on), it is found that the sorting would result like:

Record name

1

10

11

2

This is due to Record name is a Text field, sorted alphabetically. This can be solved by padding zeros to the left to make a pattern that can be sorted in an optimal way. For that, in a Rule with Trigger condition of type Record keyword: CREATED, the following query must be typed as a Run IQL action:

Example 1

set Recordname = padStart(RecordName, 3, '0')

This would result in the following created records, sorted like:

Record name

001

002

010

011

Below, a similar example, but concatenating the first letter of the customer related to the record.

Example 2

set Recordname = substring(Customer, 1, 2) || '-' || padStart(RecordName, 3, '0')

Resulting in AC-001 for the first record related to the customer ACME.

padEnd()

Returns a string right padded to the specified length, with a pad character, in order to set a pattern.

Syntax

padEnd(string, maxlength, padString)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

✓

✓

The "Invoice folio" of an invoice is needed to be a standard number of 20 digits, made up with the "Customer", "Customer reference" and a random string that makes it unique. For that, we type in a Run IQL action rule:

Example

set "Invoice folio" = padEnd(("Customer reference" || randomstring(5)), 20, '0')

This would return folios like:

ISTOOLS100012yU3mX00

ACME100013xMZ1i00000

parsedate()

The parsedate() function is used to return a date type value parsed from a specified string.

Syntax

parsedate(text, date format)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets the variable, @date1 to the same value as the Date time type field, Latest clock-in, except that the time is set to midnight.

Example

set @date1 = parsedate(formatdate("Latest clock-in", 'YYMMDDHH')|| '000000', 'YYMMDDHHMISS')

Read more about this function here.

parsejson()

The parsejson() function allows you to read values from a JSON object.

Syntax

parsejson(json, path)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The first argument is the JSON object (as text), and the second argument is the JSON path.

The following example extracts two values from a JSON object into parameters:

Example

/* Assume that @json has value:
{
  "name": {
    "first": "James",
    "last": "Smith"
  },
  "salary": 56000,
  "department": "IT"
}
*/

set "Full name" = parsejson(@json, 'name.first') || ' ' || parsejson(@json, 'name.last')
set Salary = parsejson(@json, 'salary')
Return values are assumed to be the correct type. If the "salary" field contained a text instead of a number in the above example, running the rule would result in an error.

parsejson is also useful for parsing EXIF data.

parsenumber()

The parsenumber() function returns a number parsed from a specified string.

Syntax

parsenumber(text)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets the value of a variable to 20.

Example

set @number = parsenumber('20')

power()

The parsenumber() function returns a number value raised to the power of the exponent.

Syntax

power(base number, exponent number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example would return 100.

Example

set Number = power(10, 2)

random()

The random() function returns a randomized number between two specified numbers.

Syntax

random(number, number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Rules manager. The code is written to set the value of Salary, to a random value between 10 000 and 100 000.

Example

set Salary = random(1e4, 1e5)

randomstring()

The randomstring() function returns a string with a specified number of characters.

Syntax

randomstring(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Rules manager. The code is written to assign a serial number to an equipment, with 15 randomized characters.

Example

set "Serial number" = randomstring(15)

replace()

The replace() function is a function that returns a string there some specified parts have been replaced by a specified string in an original text.

Syntax

replace('Original text', 'text to replace', 'text to be added')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to "What’s your postal code?"

Example

set @textVariable = replace('What's your PC?', 'PC', 'postal code')

round()

The round() function returns the closest integer value to a numeric parameter. If the parameter is right between 2 integers, the higher one is returned.

Syntax

round(number)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to 23

Example

set @numVariable = round(22.7)

The following example sets a variable to 32

Example

set @numVariable = round(32.2)

runReport()

The runReport() function is used to run a report.

Syntax

call runReport(Report ID)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. The code runs the report with 100224 as report id when the button is clicked.

Example

call runReport(100224)

runReportImmediate()

The runReportImmediate() function is used to run a specified report and saves it in a specified format. The format may be 'PDF', 'EXCEL' or 'HTML'.

Syntax

call runReportImmediate(Report ID, 'Format')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. The code runs the report with 100224 as report id and saves the report as an Excel file when the button is clicked.

Example

call runReportImmediate(100224, 'EXCEL')

runReportImmediateAndStore()

The runReportImmediateAndStore() function is used to run a report and save it in a specified file field in a specified format.

Syntax

call runReportImmediateAndStore(Report ID, 'Format', File field)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. When the button is clicked, the code runs the report with 100224 as report id and saves the report as an Excel file in the File field named Latest report.

Example

call runReportImmediate(100224, 'EXCEL', "Latest report"
)

runReportImmediateAndStoreWithName()

The runReportImmediateAndStoreWithName() function is used to run a report and save it in a specified file field in a specified format.

Syntax

call runReportImmediateAndStoreWithName(Report ID, 'Format', File field, 'File name')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. When the button is clicked, the code runs the report with 100224 as report id and saves the report as an Excel file with LatestFile as name in the File field named Latest report.

Example

call runReportImmediate(100224, 'EXCEL', "Latest report", 'LatestFile')

runReportImmediateWithName()

Use this function to run a report with a custom name, without having to store it.

Syntax

call runReportImmediateWithName(Report ID, 'Format', 'File name')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

Example

call runReportImmediateWithName(100224, 'EXCEL', 'LatestFile')

save()

The save() function saves the changes in the selected record when called.

Syntax

call save()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code makes the changes in the detail section get saved when the button is clicked.

Example

call save()

saveAsNew()

The saveAsNew() function saves the selected record with changes as a new record.

Syntax

call saveAsNew()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code makes the selected record in the detail section get saved as a new record.

Example

call saveAsNew()

scanQrCode()

The scanQrCode() function is used to open up the camera on the user’s device and scan a Qr code. The information in the Qr code can then be stored in a variable or a field.

Syntax

scanQrCode()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button. The code sets the variable, @qrInformation to the data that is stored in the qr code which will be scanned after the button is clicked.

Example

set @qrInformation = scanQrCode()

string()

The string() function returns a text representation of the parameter.

Syntax

string(Field name)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a Label component. The code makes the label’s caption a representation for an image.

Example

set :caption = string(Image)

The following IQL is an example from the Form designer. The code is written as Run on change for a Drop-down field. The code makes a Text field get the list item selected in the Drop-down.

Example

set Text Field = string(Drop-down Field)

substring()

The substring() function is used to extract a string from another string.

Syntax

substring('text', start index, length)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to "abc efg".

Example

let @textvar = 'abcdefg'
set :caption = substring(@textvar,1,3) || ' ' || substring(@textvar,5,3)

sum()

The sum() function returns the sum of the values from a field where certain criterion are met.

Syntax

sum(Field name) from Table name


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written in a IQL chart, and it makes the IQL chart display the sum of all salaries from each department from a table named Employee. The chart is grouped by Department.

Example

Department, sum(Salary) from Employee
group by Department

takePhoto()

The takePhoto() function is used to save a photo to a field or variable.

Syntax

takePhoto()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

x

✓

x

x

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code makes the computer’s file manager pop up. When a picture is chosen from the file manager, the field Avatar is set to the chosen picture.

Example

set Avatar = takePhoto()

today()

The today() function returns the Date type value of when it’s called.

Syntax

today()


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as a Run on click routine for a button that has a detail as parent. The code sets the Start date value in the selected record to the Date type value of when the button is clicked.

Example

set "Start date" = today()

trim()

The trim() function removes all leading and trailing whitespace from a string.

Syntax

trim(text)

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example is Run Iql. The rule ensures that there are no whitespaces in the beginning or end of the text field Name.

Example

set Name = trunc("Name")

trunc()

The trunc() function returns the largest integer smaller than the parameter if the parameter is a number. If the parameter is of the type Date or Date time, then the same date value is returned but the time is set to midnight.

Syntax

trunc(Number)

or

trunc(Date)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following example sets a variable to the largest integer smaller than 22.7

Example

set @numVariable = trunc(22.7)

The following example sets a variable to today’s date without using the floor() function.

Example

set @dateVariable = trunc(now())

upper()

The upper() function returns a specified string with only capital letters.

Syntax

upper(text)


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓

The following IQL is an example from the Form designer. The code is written as behavior for a label. The code sets the caption of the label to "UPPER-CASE".

Example

set :caption = lower('Upper-Case')

userhasrole()

The userhasrole() function takes a string as parameter and returns a boolean. If the current user has a role that matches the parameter, true is returned. In the other case false is returned.

Syntax

userhasrole(text)

The following IQL is an example from the Form designer. The code is written as behavior for a Text component. The code makes it impossible to write in the component if the user doesn’t have the role ADMIN.

Example

set :readOnly = not userhasrole('ADMIN')


The function can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

Filters & iql-charts

✓

✓

✓

✓