IQL in Forms

In forms IQL can be written to filter records, to change behaviors of components, or as code that is ran on click or on change.

Create an Advanced filter

Once the Record filter pop-up form is opened, it’s easier to get started creating a custom filter first, and from there click on Advanced. This way you can see and learn how basic conditions are typed in IQL. After that, modify it to create your own queries.

Examples with System defined fields used as keywords/functions to filter:

createdusername = 'JSMITH'

/*This will find records created by that user*/
createddate <= '2019-01-31' and createddate >= '2019-01-01'

/*Will find records created during January 2019*/
createdrealname("Requested by") contains 'John'

/*To filter records based on fields, in this case where the "Requested by" Text field was inserted by a user called John, regardless of other names he/she might have*/
updateddate("Invoice total") = today() and "Invoice total" > 5500

/*to filter records where the "Invoice total" Number field was updated today with a value higher than 5500*/
System defined fields can be used as functions at Record level or at Field level. For the latter one, the field must be provided after the function within parentheses. For example, while createddate(Field) refers to the day the Field was inserted, createddate refers to the day when the record was created.

Examples with Number fields

Price <= 5.5

/*Will display records where "Price" Number field is 5.5, 5.4, 0, -1, etc. Empty "Price" is not considered*/
Price is null or Price = 0

/*Records with some price will be filtered out*/

Examples with Date fields

"Requested on" = '2017-04-18' and Customer like 'Acme'

Be aware that for filters with constant values on dates, single quotation marks must be used.

"Item date" > Relation1."Order date"

/*Will find those records which "Item date" is later than the "Order date" of their parents records*/
trunc("Approval date-time") = '2015-09-10'

/*Will find the record with the value 10-09-2015 13:24:06 in that field*/
DeliveryDate = today()

/*Use the today() function to compare a date with today's date. Or use now() to also get the current time*/
"Delivery Date" > today() + 2

Addition or subtraction of integer numbers together with today() and now(), represents entire days later or earlier, respectively. To specify hours for Date time fields, use a fraction of the 24-hour day, like:

"Delivery Date Time" > now() + (2/24)

/*which means the current date and time, plus 2 hours*/

The trunc() function can be used to convert a Date time field to a Date field, and ease the comparison, e.g.:

trunc("Approval date-time") = '2015-09-10'

/*Will find the record with the value 10-09-2015 13:24:06 in that field*/

Examples with Relation fields

Relation1.Text1 = 'Yes'

/*Will display those records which Parent related record has the value 'Yes' in its "Text1" field*/
Customer in(’C1’, ‘C2’)

/*Will result in records having "Customer" either with 'C1' or 'C2' name*/

Examples with File fields

File1 is null

/*For empty fields*/
File1 is not null

/*For uploaded files*/

Examples with Drop-down fields

List items are case-sensitive and must be typed within quotes.

OrderStatus in(“Closed”)

List items must be separated by a comma.

OrderStatus in(“Closed”, "Cancelled")
/*For inclusion, i.e., records with OrderStatus as Closed and records with OrderStatus as Cancelled will be taken in consideration*/
Drop-down1" not in ("List item 1")

/*For exclusion, i.e., empty Drop-down fields are not considered*/

For comparison, Drop-downs must have the same list of items assigned:

"Drop-down1" = "Drop-down2"

or

Drop-down1" != "Drop-down2"

Examples with Checkbox fields

Operators is null or is not null (or = true) must be used together with Checkboxes.

Delivered is not null

/*Note that Delivered = true can also be used to display ticked checkboxes*/
"Authorized by Manager" is null

Examples with functions

createduser = currentuser() and Relation1.updateddate = today() - 1

/*Will find records created by the current user, and which parent records were updated yesterday. Alternatively, use currentUserName(), so you can use the whole name instead of the userid, as a practical way to run queries*/
"Assigned username" = currentusername()
userhasrole(‘Manager’) and Status=Approved

/*where Manager is a constant value, will display only records which Status is set as Approved, to users that are managers (i.e., their role is Manager)*/
userhasrole(Customer)

In the above example, Customer is a field containing a value. This will show only records that match whoever customer accesses the form; for instance, the record Order-0001 has the field Customer=ACME, and the physical customer, with user and role ACME, can access the form that displays this. For that, note that the role name must be the same as the item (in case the field is a drop-down) or the string (in case it’s a Text field). In case you are an administrator, try adding yourself to the previous filter, so the customer can see their own records, but you can see it all:

userhasrole(Customer) or userhasrole('ADMIN')
userhasrole(Recordname)

/*where a function is used, in case the role is the same as the record of a table, i.e., Customer is the parent table of Order, and ACME is one of the record names of Customer*/
geodist(latiude, longitude, latitude2, longitude2)

/*to get the distance in kilometers between two locations (i.e., records)*/
usergeodist(latitude, longitude)

/*to get the distance in kilometers between a user and a location*/
usergeodist(latitude, longitude) < 100

/*To display the hotels that are within 100 km to the current user*/

To be able to use geodist and usergeodist, enable the geolocation setting in Role administration , and in Application settings (server level).

Examples with strings

Text1 = upper(Text1)

/*To find text in uppercase*/
Text1 = string(Drop-down1)

/*To compare the item of a drop-down with a string in a Text or Note field*/
Text1 = substring(Text2, 0, 2)

/*Would find "ABC" in Text1 within ABCDE stored in Text2*/

Examples for interrelated sections

You may use the variable @limitByValue to customize any kind of relation between sections.

@limitByValue is very useful in combination with the Limitation field: Use custom IQL condition for fixed filters.

To show unrelated records between connected sections (in a model like Child → Parent, where Parent is the main section and Child the limited one):

RelationToParent != @limitByValue

To show records in a many-to-many relation (in a model like TableA ← LinkTable → TableB, where TableA is the main section and TableB is the limited one):

RecordName in (RelationToTableB from LinkTable where RelationToTableA = @limitByValue)

To show grandchildren records (in a model like Table3 → Table2 → Table1, where Table1 is the main section and Table3 is the limited one):

RelationToTable2 in (Recordname from Table2 where RelationToTable1 = @limitByValue)

Another example for grandchildren records:

Parent in (Child from Parent where recordid = @limitByValue)

For direct relation:

RelationToTableB.RelationToTableA = 'A1'

/*will show the records related to the Parent records which have A1 as parent record (i.e., 2 levels above the current table)*/

For Parents with no Child records:

recordname not in (ParentRelationField from Child where ParentRelationField is not null)

For reverse relation:

recordid in (RelationToCustomer from Order where ApprovedCheckbox is not null)

/*it will show the customers that have any order being approved, i.e., from Customer (parent table) the Orders (child table) are reviewed to see if the condition matches*/

Another example when limited by Child record:

RecordId in (RelationToParent from ChildTable where RecordId = @limitByValue)

For active record limitation, use:

RecordId = @limitByValue

For filters, note that @limitByValue can be compared with Relation and RecordId, but not with RecordName

RecordId = @limitByValue

Relation and Text fields can be compared with both:

Relation = RecordId, Relation = RecordName)

However the string() function must be used for RecordId for the latter type of field:

Text = string(RecordId), Text = RecordName)

More examples for interrelated sections:

For a many-to-many relation:

recordid in (Actor from Role where Movie = 'Life is beautiful')

/*Will show the actors in that movie*/
recordid in (Movie from Role where Actor = 'Woody Allen')

/*Will show the movies where that actor had a role in*/

Another example for the above, is:

recordid in (Relation1 from LinkTable where Relation2 = @limitByValue)

Records having the same parent:

Parent = (Parent from Child where recordid = @limitByValue)

Change the behavior of components

Most components in Forms have behaviors that can be adjusted with IQL. In this section we will walk through the possible changes in behavior.

If you involve fields from a parent relation in Behavior, like in the query below, you must add the field somewhere in the form (even if hidden) in order to make it work.

if Parent.Number > 0 then
  set :backgroundColor = 'red'
end

:backgroundColor

The :backgroundColor property is used to change the background color of a component. The color is represented by the Name or the Hex-code of the color in a string format.

Syntax

set :backgroundColor = 'Color'

The following example sets the background color of a component to yellow.

Example 1

set :backgroundColor = 'yellow'

Example 2

set :backgroundColor = '#FFFF00'

Example 3

Change a field’s backround:

If the amount of items received is lower than the expected, then this field will turn red. For that, select the "Expected quantity" Number field and for its Behavior, type

if "Actual quantity" < "Expected quantity" then
    set :background = 'red'
end

:disabled

The :disabled property is used to disable buttons. To disable the component, set :disabled to true or 1. To make the component visible, set :disabled to false or 0.

Syntax

set :disabled = boolean

or

set :disabled = binary

Example 1

Disable a button if the current user doesn’t have ADMIN as a role.

set :disabled = not userhasrole('ADMIN')

Example 2

Disable a button depending on the value in a Note field:

If the warehouse receives a package that is marked as dented, a button called "Ready" will be disabled until a description of the damage is provided.

if "Description of the damage" is null then
    set :disabled = 1
end

:caption

Syntax

set :caption = 'value'

Example 1

set :caption = 'Hello world'

Example 2

set :caption = TextField

Example 3

set :caption = ''

/*To set an empty string*/

Example 4

More than one condition and behavior.

if MyField < 0 or MyField > 100 then
    set :disabled = 1,
            :caption = 'MyField is out of range'
else
    set :caption = ''
end

:imageUrl

:imageUrl is the only function available for Media component. The URL must be typed within single quotations.

Syntax set :imageUrl = 'URL'

Example

Display a default image:

For Media component, display a specific picture after choosing a certain item in a Drop-down, or typing a certain value in a Text field, e.g., if you choose “Vehicle inspection”, the template picture of a car will be shown, ready to make marks on it; but if you choose “Excavator inspection”, then the template will change accordingly.

if "Type of inspection Drop-down" in ("Vehicle inspection") then
    set :imageUrl = 'https://picsum.photos/200/300'
else
     if "Type of inspection Drop-down" in ("Excavator inspection") then
         set :imageUrl = 'https://source.unsplash.com/WLUHO9A_xik/1600x900'
end

:hidden

The :hidden property is used to hide components. To hide the component, set :hidden to true or 1. To make the component visible, set :hidden to false or 0.

Syntax

set :hidden = boolean

or

set :hidden = binary

The following example hides a component if the current user doesn’t have ADMIN as a role.

Example

set :hidden = not userhasrole('ADMIN')

Use :hidden for Panels to configure Tabbed forms and other designs.

:readOnly

The :readOnly property is used to set the writing properties of a component. To make a component only readable set the :readOnly property to true or 1. To make it able to write in the component, set :readOnly to false or 0.

Syntax

set :readOnly = boolean

or

set :readOnly = binary

The following example enables writing in a component for all users with ADMIN as role and disables writing for every other user.

Example

set :readOnly = not userhasrole('ADMIN')

:textColor

The :textColor property is used to change the background color of a component. The color is represented by the Name or the Hex-code of the color in a string format.

Syntax

set :textColor = 'Color'

The following example sets the background color of a component to blue

Example

set :textColor = 'blue'

or

set :textColor = '#0000FF'

:x :y

Use the :x and :y properties to set the position of a component. Note that this position is relative to the Parent of the component; for example, a Button with a position x:0 and y:0 will appear at the top left of the form is placed on the form root, but it will appear on the top left of a section if placed inside of it.

Syntax

set :x = 10
set :y = 10

:height :width

Set the dimensions of components with these properties:

Syntax

set :height = 200
set :width = 200

:borderColor

Highlight your components adding some color to their borders:

Syntax

set :borderColor = 'blue'

or

set :borderColor = '#0000FF'

:icon

The :icon property is used to choose an image for a Button or Icon component from our collection, found in the Icon property. For that, simply type within single quotes one of the registered icons.

Syntax

set :icon = 'Floppy-disk'

:tooltip

You can add a text that pops up when users hover over a component by using this property:

Syntax

set :tooltip = 'This is the total amount authorized by Finance'

:rangeMin :rangeMax

Only for Meter component, these properties set the minimum and maximum value of the range to be displayed.

Syntax

set :rangeMin = 0
set :rangeMax = 100

As an integrated example of the above descriptions, let’s say we have a Button component that is toggled to acomplish two different actions, and upon each of them the behavior will change accordingly, making it look like two different buttons:

set :caption = 'Filter by last week'
if @param = 1 then
    set :width = 200
    set :height = 20
    set :borderColor = 'yellow'
    set :icon = 'Filter'
    set :caption = 'Show all'
    else
        if @param = 2 then
            set :width = 100
            set :borderColor = 'green'
            set :icon = 'Filter-slash'
            set :caption = 'Filter by last week'
        end
end

The usage of Behavior may impact on the performance of the application due to the constant evaluation made on the form.

Run actions with components

Run multiple actions at one click of a Button, Link or Icon; for instance, configure the actions to set a value to a field, save the form, and open another form with the selected record, all upon a single click, using the Run on click property.

Type the keyword call, then click on Call a procedure to autocomplete any of the following actions:

call save()

This action, in combination with a Button placed on the form root and a Repeater as Target section, works to save all edited records.

 
call create()
call saveAsNew()
call delete()
call open(form(FORMNAME))

Open form, same record selected if possible. If FORMNAME contains spaces, use double quotes, not single.

call openRelated(form(FORMNAME), RELATION)

Open form, use value of RELATION field to send to the new form.

call runReport(REPORTID)
call runReportRelated(REPORTID, RELATION)

Run a Report with data belonging to a related record, without having to manually type the name of that record in the Run report window.

call runReportImmediate(REPORTID, 'FORMAT')

Run a Report from a form and download it afterwards. Format may be 'PDF', 'EXCEL' or 'HTML', and the Report ID can be found in the Report manager, in the column called "id". In case the file takes a long time, it will not be downloaded but placed in the Report and import queue instead, and the user will be notified about this. For that, the user must have rights granted in the Report and import queue form.

call runReportImmediateAndStore(REPORTID, 'FORMAT', FileField)

Run a Report from a form, and save it directly on the specified File field, with the chosen format. Format may be 'PDF', 'EXCEL' or 'HTML', e.g.,

runReportImmediateAndStore(100585, 'excel', MyFile1)

Note that the Report ID can be found in the Report manager, in the column called "id".

call runReportImmediateAndStoreWithName(REPORTID, 'FORMAT', FileField, FileName)

Similar to the above function, with the additional feature of naming the report as you like, e.g.,

runReportImmediateAndStoreWithName(100585, 'excel', MyFile1, 'Report Q2 2024')

or you can also concatenate the Report name with other elements, so instead of 'Report Q2 2024' you could add 'Report ' || Quarter || ' ' || today() || '-' || RecordName to get something like Report Q2 01/06/24-ACME1234.

call openUrl(url)

This is an alternative function to open a form with a given parameter such as the Record name or other variables; for example, a form with a Selector with property "URL parameter for selected record" set to myRecord, so every time you select a record, the URL will change accordingly.

To open the form in a new tab, use a Link/Icon/Button component with Run on click like

call openUrl('https://swe02.istools.com/istools/apps/1000/forms/dynamic/123456?myRecord=' || RecordName)

To open it in the same tab, use

call openUrl('/istools/apps/1000/forms/dynamic/123456?myRecord=' || RecordName)
 
createFormUrl(recordid, form)

Nest the function createFormUrl(recordid, form) in openUrl to avoid typing the desired URL of a known form, e.g.,

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

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

If more variables are added, they must be typed with & instead, as shown in the example below.

 
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. Make sure that any variable you concatenate to the URL must be defined as a parameter with the "Write parameter to URL" property checked in the destination form.

Check another example on how to open a form that will change its looks depending on variables in our Tips and tricks topic, where we use this function.

You may also type the keyword set, then click on Set a field, property o parameter to autocomplete the functions described in the topic Change the behavior of components. For instance:

Use a File field in combination with the function

captureSignature(label, imageType)

for digital signature, like:

Example 1

Run on click on Button for digital signature

set File1 = captureSignature('Enter your signature here', 'png|jpg|svg')

A File field can also be used with the takePhoto function, like:

set File2 = takePhoto()

In case the Button/Link/Icon is placed outside the section, the property Target section must be provided.

Be aware that a Button or Icon configured to do actions using data in a Table section will only work for the selected record. For example, a Button is expected to create a new record assigning the Record group of the Parent record, with the following IQL on Run on click:

call create()
set RecordGroup = Parent.RecordGroup

If no row is selected in the Table section, no Record group will be set, because there is no data reference.

To enforce that the user selects a row before clicking on the Button/Icon, you could set a Behavior where the Button/Icon is disabled until a row is selected, like:

set :disabled = @button is null

In combination with Run on select on the Table section where you define the variable:

set @button = 1

Configuration could become more complex in case you also want to make sure that the last selected row belongs to the newly selected Parent record, where a new variable would have to be set on the Parent table as well, and included in the Button/Icon Behavior.

You can also combine conditions and functions with actions or simply use set alone:

Example 1 Save upon condition (if + call)

if (Field is not null) then
   call save()
end

Example 2 Take a screenshot and save it into a File field (set + function + call)

set "My File Field" = takescreenshot(34302)
call save()

Use the function

takescreenshot(COMPONENTID)

to export Maps and Charts as images and then save them into a File field, as described above. You can later use this File field to print these components on PDF files.

Example 3 Clear a field (set + null)

set "My Relation Field" = null

To clear the value of all types of fields, except for Checkbox, use the keyword null as described above.

Checkbox is a special case, for having boolean values:

To clear it:

set "My Checkbox Field" = false

To tick it:

set "My Checkbox Field" = true

For functions involved with File fields, you may customize the file name of photos, signatures, videos, documents, etc., uploaded either by a Button, a Media component, screenshots of Maps and Charts, or directly uploaded to File fields. For example, you may have a fixed name like “Survey signature.png” or even a complex concatenation with name and current date, like “Survey signature 30-10-2023.png”. For that, select in Form designer a Media component or a File field, and in “Run on change” type, for example:

set :filename = 'Survey signature' || today()

or like this:

Example "Run on change" on File or Media component for File name customization

set :filename = RecordName || ' ' || string("Customer drop-down") || ' ' || today() || '.xls'

to store the file as “PO-001 ACME 30-10-2023.xls>

It is possible to set form-global parameters:

set @myParam = 'Test'

so if you have an input field bound to @myParam, the update will be reflected there. Or, if you have a section that uses it in a filter, it will automatically be refreshed.

Check out the examples we have for Run on select and for Run on init.

Parameters with special characters or keywords are not supported; e.g. @gör @and

Parameters can be used as Search box, to use, for example:

MyNoteField contains @Param1

You may have a Button/Icon with Run on click:

set @search = scanQrCode()

in combination with a Search box, to filter records based on the scanned value, when using the QR-code scan feature.

The regular Action property of a component in Form designer does not work together with Run on click.

Run calculations on fields or set the value of fields

Get the result of a formula immediately after changing values in your form, without having to save or click on a button. This can be done thanks to the Run on change property. For example, select the "Quantity" field, go to its property Run on change and type:

set Total = Price * Quantity

where "Total" will be automatically updated every time "Quantity" changes. But be aware that "Price" might also change, so in this case, both factors (fields "Price" and "Quantity") would have to add the same IQL code in their Run on change IQL Editor.

Select multiple fields to apply the same code in IQL editor, i.e., you can edit all fields involved at the same time.

Another example is:

set Text = Relation

where "Relation" is the recordname of the Parent table. The syntax set Text = Relation.recordname is also supported.

Be aware that using Run on change to get data from a Parent table via Relation field, supports only its record name one level up on the direct relation. For example,

set Text = RelationToParent1

is supported, but

set Text = RelationToParent1.Text

or

set Text = RelationToParent1.RelationToParent2

is not.

To accomplish this, there are two alternatives:

  1. Use Run IQL or Set the value of a fields with Rules actions.

  2. Or define those related values with variables using a query with From syntax. For example, in Run on select of the section, type:

    let @relation = RelationToParent1
set @relatedData = Text from Parent1 where RecordName in(@relation)

    Then, in Run on change of a field, type:

    set Text = @relatedData

With Run on change, you can use most of the functions described in other sections within this topic. For example, if-else, call save(), set parameters, trunc, parsedate, substring, etc.

Example using Run on change with functions:

if "Work status" in(Started) then
     set "Worklog notes" = 'Work started on ' || now() || ' with provider ' || nvl(Provider, ' not available yet') || chr(10)
 end

In this example, Run on change is on a Drop-drown called "Work status" that once it changes to "Started", the Note field "Worklog notes" concatenates strings, today’s date and time and the Relation field "Provider", which uses an nvl function to add a string in case it is null, i.e., the result could be "Work started on 06-04-2023 08:30:00 with provider ACME", or could be "Work started on 06-04-2023 08:30:00 with provider not available yet", in case Provider was null.

Another example, simpler, with nvl would be set

"Unit price" = nvl("Unit price", 0)

to replace empty prices with a 0 instead.

Note how the function chr(10) was used into the concatenation to add a new line after the resulting string. In the given example, having it after now(), would result in:

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

Run on change may also be used to customize the name of a File.

To set the value of some particular data extracted from the metadata of a picture (known as EXIF data) stored in a File field, use the parsejson function together with the readExif function via "Run on change" of the File field (of the Media component connected to it), like this:

Example using parsejson and readExif

await @exif = readExif(FileField)
let @degrees = parsejson(@exif, 'GPSLongitude[0]')
let @min = parsejson(@exif, 'GPSLongitude[1]')
let @sec = parsejson(@exif, 'GPSLongitude[2]')
set Longitude = @degress + (@min / 60) + (@sec / 3600)

The above example is based on EXIF that stores Longitude with this format [°]["][']

Note that you could also store the whole EXIF data into a Note field:

Example using readExif to store it into a Note field

set NoteField = readExif(FileField)

You may also use the parsejson function to extract data from the Geojson Note field used in the configuration of Map layers.

To calculate days between Date and Date time fields, use the days function to return the result into a Number field:

Example 1 for calculation between Dates

set "Days for completion" = days("Start date", "Finish date")

You can even get hours:

Example 2 for calculation between Dates

set "Hours worked" = days("Start date time", "Finish date time") * 24

Run actions upon selection of a Record (Run on select)

Whenever you use the Selector, Detail section, Multi detail section, Table section, Map, or Calendar, the Run on select property will be available for you to apply an IQL action. This property is especially useful to define parameters that will later be used somewhere else as a variable.

Example 1 In your form, you have a Record Selector and a related Detail section showing Projects. But you also have a Table section showing Inventory, that has no relation with Project, but you wish to limit the Inventory where the Client matches the Project’s Client.

For that, in the Run on select of the Selector or the Detail section of Project, type:

set @client = Client

Then, in the Table section of Inventory, use that parameter in its Filter property, as follows:

Client = @client

After that, navigating through Projects will refresh the Inventory section accordingly.

Example 2

Let’s say that you have a form where you show the detail of an "Inspection" record, but you want to hide or show the Panel "Electrical" depending on if this aspect (defined in a drop-down) is applicable to the selected inspection.

For that, in the Run on select of the Selector or the Detail section type:

if "Is Electrical inspection applicable?" in(Yes) then
    set @electricalPanel = 1
else
    set @electricalPanel = 0
end

Then, in the Panel in question, you will use that parameter to modify its Behavior, as follows:

set :hidden = @electricalPanel != 1

Note that navigating through records will show or hide the Panel accordingly, but if the user is allowed to change the drop-down in the form, then the same parameter should also be defined in the Run on change of that drop-down.

Example 3

The "Inspection" is related to "Project". And the latter, related to "Client", i.e., a direct relation Inspection→Project→Client. There is a need of a form with a Table section displaying all inspections, but with a Selector on top, with the list of clients that can work as a filter for our Table section.

For that, in the Run on select of the Selector, type:

set @client = RecordName

Then, in the Table section, you will use that parameter in the Filter, as follows:

Project.Client = @client

Example 4

You wish to display data of the "Service area" based on the selection of either a "Duct", a "Cable" or a "Conduit" located in a Map.

For that, in the Run on select of each Map layer of the Map component, type:

set @serviceArea = "Service Area"

Then, in the Selector or Detail section with "Service Area" as Datasource, you will use that parameter in the Filter, as follows:

RecordName = @serviceArea

Preferably, try to avoid Run on select to define fields. For instance, even though it is possible to add an action like:

if Status is null then
    set Status = "In Progress"
end

it would result in a form constantly waiting to be saved. Instead, you could use that IQL in a Run on change of a mandatory field, or in a Run on click property of a component, or even via conventional rules.

Look for another example of how to open a form that will change its appearance depending on more that one variable, using Run on select, in our Tips and Tricks topic.

Be aware that using Run on select to get data from a Parent table via Relation field, supports only its record name one level up on the direct relation. For example,

set @client = Client

is supported, but

set @clientsContract = Client.Contract

or

set @clientsContractsReference = Client.Contract.Reference

is not.

To accomplish this, define instead those related values with variables using a query with From syntax. For example, in Run on select of the section, type:

let @client = Client
set @clientsContract = Contract from Client where RecordName in(@client)

Even when you can use Run on select in combination with almost all IQL functions, we highly recommend you to avoid using it to set values on fields; for example, a query like set Client = 'ACME' would make the form permanently waiting to be saved. Even if you add a call save() afterwards, it would be a constant resource spent to save at every selection of the user. Instead, a better practice is to have these actions in a Button or upon changing a field.

Run actions upon loading the form (Run on init)

Sometimes it is necessary to set up some parameters or variables from the moment the form is opened, which will be later modify by the user depending on actions from other components.

For example, let’s say that you have a form with a Table section showing Projects. At the top of form, you have two Buttons: "Active" and "Inactive" which, if clicked, will filter the Projects by Status Active or Status Inactive, or both of them. But regardless of what the user clicks, you wish to show them only Active Projects by default when opening the form. For that, select the canvas of the Form, click on Run on init and type the following:

set @active = 1
set @inactive = 0

After that, you can then set up the Behavior. In the "Active" Button:

if @active = 1 then
    set :backgroundColor = 'limegreen'
end

In the Behavior of "Inactive" Button:

if @inactive = 1 then
    set :backgroundColor = 'limegreen'
end

In the Run on click of the "Active" Button, you could type this query to toggle it:

if @active = 1 then
    set @active = 0
else
    set @active = 1
end

Do the same for the "Inactive" Button. Then, in the Filter of the Table section, type:

/*To display only Active*/
(Status in(Active) and @active = 1 and @inactive = 0)
or
/*To display only Inactive*/
(Status in(Inactive) and @inactive = 1 and @active = 0)
or
/*To display both Active and Inactive*/
(Status in(Active,Inactive) and @active = 1 and @inactive = 1)
or
/*To remove the filter. This might show othe Status, if available*/
(@active = 0 and @inactive = 0)

Configure charts components

Select the IQL chart component and then click on the IQL: Edit…​ button. Start typing the name of a field. At least two names of fields must be provided, separated by comma, where the first one refers to the x-axis, and the second one(s) to the y-axis.

Line chart example:

Recordname, "Number field 1" from "Table 1"

Pie chart examples:

"Checkbox field 1", "Number field 1" from "Table 1"
Status, count(RecordId) from "Table 1" group by Status

Line chart example, with Limited by other section via Active record:

Recordname, "Number field 1" from "Table 1" where recordid = @limitByValue

Bar chart example, with Limited by other section via Relation:

Recordname, "Number field 1" from "Table 1" where "Relation field 1" = @limitByValue

Table chart examples:

Officer, WorkOrderStatus, count(1) from WorkOrder group by Officer, WorkOrderStatus

For the IQL chart above, the property Split series by has been applied per Status as the items of this drop-down field will be counted separately per Officer.

For any chart where you split series, you can use the @series variable in the Behavior property of the number bar/line/slice to customize its color or caption.

For the chart above, you would have to select the "count(1)" in y-series and then click on Behavior to type as follows:

IQL chart: Example to set a bar/line to color red in case the Status is On-Hold

if @series = 'On-Hold' then
   set :backgroundColor = 'red'
   set :caption = 'On hold'
end
Status, count(RecordName) from Activity where Status is not null group by Status

Note that the statement "where Status is not null" above is useful to avoid displaying the count of records without Status. For the heading "QTY", click on the series "count(RecordName)" and type a Caption property.

For the custom colored cells, click on the Behavior button and use the variable @value as follows:

IQL chart Example: IQL Table chart colorized

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

For any chart of type Table, you can use the @value variable in the Behavior property of a field in y-axis to customize its color.

It is a good practice to use the condition is not null when configuring charts with value conditions to avoid unexpected results in case there are empty values.

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:

IQL chart Example: IQL Table chart formatted numbers as dollar with one decimal

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

For any chart where the x-axis is a Date field, you may use the Date range selector to be able to filter throught periods of time.

General example:

'Summary', count(Number1), count(Date1) from Table1

/*
Note that 'Summary' is not a field, but a label used for the x-axis. It must have apostrophe.
*/