IQL Reference

This is a reference of all IQL functions and operators.

Functions

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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() 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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()

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

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

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')

Commands

Before using commands, it is important for you to understand how queries are evaluated and executed, especially when using Set, Insert and Trigger.

Let

The let command defines a value for use in another expression.


The command can be used as following:

Forms

Rules

Behavior

Run on: click, change, init and select

The following example sets a variable to "b".

Example

let @textvar = 'abc'
set :caption = substring(@textvar,2,1)

More examples here.

Set

The set command is used to update a value. It can be used to update Field values or variables.


The command can be used as following:

Forms

Rules

Behavior

Run on: click, change, init and select

The following IQL is an example from the Form designer. The code is written as behavior for a Text component. The code makes the text color red in the component if the user has the role ADMIN.

Example

if userhasrole('ADMIN') then
    set :textColor = 'red'
end

Below, there is another example written for a Button component which, upon clicking, enters the content of a QR-code into a parameter used as "search" field.

Example

set @search = scanQrCode()

The following IQL is an example from the Rules manager. The code is written to set the value of the field, "Full name" to a concatenation of the fields, "First name" and "Last name".

Example

set "Full name" = "First name" || ' ' || "Last name"

Even though both commands set and let can be used with parameters and variables, be aware that let is only used to define them for a later action, whilst set is an action itself.

For instance, the following example, which is meant to concatenate a suffix string, will not work as both lines are executed as independent actions, where the first one sets a value but does not define it for a later use:

set @suffix = 'suffix'
set RecordName = formatdate(now(), 'YYYYMMDDHHMISS') || '_' || @suffix

Instead, this code will work successfully:

let @suffix = 'suffix'
set RecordName = formatdate(now(), 'YYYYMMDDHHMISS') || '_' || @suffix

Another mental model you may use is that set is asynchronous, while let is synchronous.

See also an important note at the top of this Commands topic about evaluation and execution of IQL.

If Then Else

Used to express a condition. An if statement will run the statements between then and end if the condition is true.

Example

if "Budget remaining" < 0 then
    set "Attention required" = true
end

If statements may optionally have an else block. If present, the statements after the else keyword will run when the condition is false or NULL.

Example

if "Finish Date" < today() then
    set "Days remaining" = today() - "Finish Date"
else
    set "Days remaining" = 0
    set Status = Overdue
end


The command can be used as following:

Forms

Rules

Behavior

Run on: click, change, init and select

For

Used to loop over records in rules. See Run IQL.

Trigger

Used to trigger other rules. See Run IQL.

Await

Used in form rules for intermediate storage of results from asynchronous expressions. An example of an asynchronous expression is a query within a statement:

Example

set "Number of logs" = count(recordname) from Log

This works as expected in both Run IQL and in Run on click, because the Set command works with asynchronous expressions.

However, the following expression does not work when used in forms:

Example - Incorrect in Run on click, etc

let @numberOfLogs = count(recordname) from Log
if @numberOfLogs > 0 then
    ...
end

Instead, the await command must be used:

Example - Correct

await @numberOfLogs = count(recordname) from Log
if @numberOfLogs > 0 then
    ...
end


The command can be used as following:

Forms

Rules

Behavior

Run on: click, change, init and select

x

x

Note Run IQL actions can use let instead of await, and asynchronous expressions are not evaluated in Behavior at all.

See also an example of reading EXIF data using await.

Operators

Contains

The contains operator is used in conditions to search for patterns in text. The contains operator is often used combined with the where clause.

Syntax

Text or Note Field name contains 'text'

Note that it cannot be used with a variable, like:

@variable contains 'text'


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the Full name value contains the pattern "na".

Example

"Full name", Salary from Employee
where "Full name" contains 'na'

In ()

The in () operator is used to specify multiple values. The operator is a shorthand for multiple or conditions.

Syntax

Field name in (value1, value2, ...)


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the dropdown type value of Department is Sales or Finance.

Example

"Full name", Salary from Employee
where Department in (Finance,Sales)

Is

The is operator is used for detecting if a value is null or not. Null is unknown, not applicable or missing information, accordingly, it’s not possible to use comparison operators (=,<,>,etc..). Therefore the is operator is used.

Syntax

Field name is (not) null


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Department from all records in the Employee table where the field Phone number is not null.

Example

"Full name", Department from Employee
where "Phone number" is not null

Like

The like operator is used for searching fo a specified pattern in text values.

There are two wildcards that can be used with the like operator:

  • The % or * signs represent multiple characters.

  • The _ or ? signs represent one, single character.

Syntax

Field name like 'text'


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the Full name value has an "a" as second character.

Example

"Full name", Salary from Employee
where "Full name" like '?a*'

Not

The not operator is used together with like or an in to invert the result.

Syntax

not in
not like
not contains


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the dropdown type value of Department isn’t Sales.

Example

"Full name", Salary from Employee
where Department not in (Sales)

endswith

The endswith operator is used to check if a text type value ends with a specified text pattern. The operator is often used combined with the where clause.

Syntax

Field name endswith 'text'


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the Full name value ends with "son".

Example

"Full name", Salary from Employee
where "Full name" endswith 'son'

startswith

The startswith operator is used to check if a text type value starts with a specified text pattern. The operator is often used combined with the where clause.

Syntax

Field name startswith 'text'


The operator can be used in:

Forms

Rules

Behavior

Run on: click, change, init and select

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 values of the fields Full name and Salary from all records in the Employee table, where the Full name value starts with "Si".

Example

"Full name", Salary from Employee
where "Full name" startswith 'Si'

Examples of comparison operators

MyTextField = 'desks'

would match the value "desks".

MyTextField like 'desks'

would match the values "desks", "Desks", "DESKS".

MyTextField like 'd?sk*'

would match the values "desks", "Desks", "DESKS", "disks", "dusk".

MyNoteField startswith 'logged by smith'

would match the value "Logged by Smith on July the 1st, 2024".

MyNoteField endswith '2024'

would match the values "Logged by Smith on July the 1st, 2024", "Logged by Andersson on January the 20th, 2024".

RecordName contains 'invoice'

would match the records "INSTALLATION-INVOICE01", "CIVIL-INVOICE33".

RecordName not contains 'invoice'

would match the records "INSTALLATION-QUOTATION09", "CIVIL-QUOTATION91".

Note that when using != or not like, it’s implicit that the comparison is made on fields with values (i.e., not null fields); for instance, having the filter

MyNumberField != 1

on this table:

Record

MyNumberField

A

0

B

C

2

the resulting records would be A and C.

To combine conditions, there is AND, OR, NOT. Note that parentheses can be used as group separators. For example:

(OrderStatus in(“Ready”) AND PlanedDeliveryDate => Today()) OR OrderStatus in(“Escalated”)

Null

This keyword can be used as follows:

To filter values in Advanced filters. Must be used with is or is not operators, to look for blank or not blank values, respectively.

Department from Employee
where "Phone number" is not null
Price = 0 or Price is null

/*Note that specifying 0 and null together, will filter out records having some price*/

To clear values in Rules or in Forms

set "My Relation Field" = null

To be used as criteria to change behavior or to do actions in Forms

if "Description of the damage" is null then
    set :disabled = 1
end
if Field is not null then
   call save()
end

To be used as criteria to do actions in Rules

for Resource where Project is null do
    set Project = @triggerrecord
end

To be used in IQL Charts, "is not null" is necessary to avoid displaying the count of records grouped by an empty field. In the example below, it will not display the records without Status.

Status, count(RecordName) from Activity where Status is not null group by Status

Below, another example for IQL Charts, using not null for Behavior.

if @value >= 2
    and @value <= -2
    and @value is not null then
    set :backgroundColor = 'red'
else
    set :backgroundColor = 'purple'
end

Note that IQL works in the same was as SQL, where it’s implicit that comparisons are made on fields with values, i.e., excluding null values.

That is the action in the following query would be executed only for record D, given the table below.

if "Drop-down field" not in (Item1, Item2) then
   set "Text field" = 'OK'
end

Record

Drop-down field

Text field

A

Item1

B

Item2

C

D

Item3

OK

If you want to include record C too, then the query must include is null:

if "Drop-down field" not in (Item1, Item2)
   or "Drop-down field" is null then
   set "Text field" = 'OK'
end

Record

Drop-down field

Text field

A

Item1

B

Item2

C

OK

D

Item3

OK