IQL in Rules

There are two rule actions that utilize IQL, Run IQL and Set the value of fields using IQL. The latter is the predecessor to Run IQL and as such is not as powerful or user-friendly, which is why we recommend using Run IQL when possible.

Run IQL

This is an open IQL editor where you can use IQL statements to set field values, create records, or run multiple actions on one or many records at once. You can use most of the functions described in this document except for the ones used for components (:background, :disabled, call save(), call delete(), etc.).

Before using Run IQL, be aware that its resulting action will not trigger other rules automatically (like Set the value of a field, Set the value of a field with a formula and Set the value of fields using IQL do). Instead, other rules must be triggered explicitly using the trigger statement.

For example, having rule #1 with action

set Dropdown = Yes

will not trigger rule #2 with condition

Dropdown = 'Yes'

unless the query trigger "rule #2" is typed into the Run IQL action of the first rule:

set Dropdown = Yes
trigger "rule #2"

See more examples about how to trigger a rule from a rule action here.

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

Create new records

To insert new records, at least a table and RecordName must be provided, and also RecordGroup in case the table in question has no Default record group. Fields must be separated by comma.

Syntax

insert "Table name"
    values RecordName = value,
    RecordGroup = value,
    field = value

Example 1

insert "Invoice Item"
    values RecordName = string(RecordId),
    RecordGroup = "New invoices",
    "Authorized by" = CreatedRealName,
    "Valid until" = today() + 30

Note that the RecordId is set here as the name of the new record.

Example 2

insert "Work order"
    values RecordName = Customer || Index,
    RecordGroup = STANDARD

Note that the RecordName is set here with a concatenation of field values.

See another example of Insert combined with other functions here.

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

For loop over records

This query is used for iterating an action over a sequence of records that match the query within, mostly for cases where you want to affect multiple Children records in one run, but also useful to affect a single record if a proper condition is given.

Syntax

for "table query" do
    <action>
end

Example 1. Set a value for all records that fulfill the condition

for "Work order" where "WO Customer" = 'ACME' do
    set "WO Responsible" = 'John Smith'
end

In this case, all work orders having ACME as a customer, will have John Smith set as the responsible person.

Example 2. Insert a new record per record that fulfill the condition

for Invoice where Service = Installation do
    insert "Log" values RecordName = string(RecordId),
    Description = 'Installation invoiced on' || today() || 'by' || createdrealname
end

Here, all invoices created for installation services will automatically create a log record registering this activity.

Example 3. Move a record to a Record group defined with a concatenation of a string and a variable

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

With this 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. 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.

Find other examples in the description of let, trigger and at the end of this section.

Define parameters and variables

Declare a temporary element that you can later use as a reference for other queries within the same rule action.

Syntax

let @parameter = <function, query or value>

Example 1. Define a time variable that is called in a query to make a comparison

let @verifiedTime = now()
set "Historical number of overdue activities" =
    count(RecordId) from Activity
    where createdtime < @verifiedTime
    and Status = Delay

This would return how many activities are delay up to this moment.

Example 2. Define a parameter that keeps in the loop new records

let @new =
    insert "Invoice" values RecordName = string(RecordId)

for Invoice @new do
    insert "Invoice Item" values RecordName = string(RecordId),
    "Invoice related" = @new,
    Description = 'Discount 10%'
end

Each invoice newly created will automatically include an item with a discount of 10%.

Trigger a rule from a rule action

Call a rule within another rule, useful to run actions in a planned chain.

Syntax

trigger "rule name"

Example 1. A rule is called directly on the action of a first rule

In this example, it is expected that the table "Invoice" calculates its "Grand total" and then runs other query for the same record.

Note that this rule has "Invoice" as both Trigger table and Target table.

set "Grand total" = sum("Item total") from "Invoice Item" where "Invoice ID" = @targetrecord

trigger "Set other Invoice information"

Note that the rule you called will be looking for a match between the Target record in the first rule and the one referred as Target in the called rule which, in this case, is the same invoice.

(Complement to Example 1) Rule "Set other Invoice information"

This rule has "Invoice" as both Trigger table and Target table.

set "Invoice last updated on" = today()
set "Invoice responsible" = createdrealname

Example 2: A rule is called nested within a "For loop over records" query of a first rule, targeting its Parent record

In this example, it is expected that the table "Invoice Item" calculates its "Item total" and then runs a query for its Parent record so it can sum up all "Item total" to get its "Grand total".

Note that this rule has "Invoice Item" as Trigger table and Target table.

set "Item total" = Price * Qty

let @relatedInvoice = "Invoice ID"
for Invoice where RecordName = @relatedInvoice do
    /* Trigger rule Example 1 for Invoice references by "Invoice ID" relation field */
    trigger "Example 1"
end

/* Outside the loop, "trigger" will invoke the rule on the target record  */
trigger "Set other Item information"

Remember that a rule called directly on the action will affect the same record targeted in the first rule, in this case, the Item.

Example 3: A rule is called nested within a "For loop over records" query of a first rule, targeting its Child records

In this example, based on the Example 1 above, it is expected that the table "Invoice", besides calculating its "Grand total" and other calculations, also runs a query for its Child records, i.e., its Items, so each of them will get a "Percentage" of how much their "Item total" represents in proportion of their Invoice’s "Grand total", for example, an Item with a total of $500 would represent the 25% of its Invoice that has a Grand total of $2,000.

Note that this rule has "Invoice" as both Trigger table and Target table.

set "Grand total" = sum("Item total") from "Invoice Item" where "Invoice ID" = @targetrecord

trigger "Set other invoice information"
/*As mentioned before, this rule you called above, will be looking for the Record targeted in the first rule which, in this case, is the same invoice.*/

for "Invoice Item" where "Invoice ID" = @targetrecord do
    trigger "Rule to set Percentage = Item total / Grand total"
end

Note that this rule you called, will be looking for a match between the Target record in the "For loop over records" query (which, in this case, is the Item), and the once referred as Target in the called rule (i.e., Item), affecting in this case, many records.

(Complement to Example 3): "Rule to set Percentage = Item total / Grand total"

This rule has "Invoice Item" as both Trigger table and Target table.

set Percentage = "Item total" / "Invoice ID". "Grand total"

Example 4a: A rule called will fail if the target is not found

In this example, the Trigger table is "Invoice Item", and the Target table is "Invoice", and it is expected that once an Item fulfills its condition (whatever it is), its related Invoice will get a date value, and a grand total (the latter one, by calling a rule).

set "Invoice last updated on" = today()
/*The above query will be successful, as is a direct action.*/

trigger "Example 4b"
/*The above query will not be successful, as the rule "Example 4b" has a reference to the @triggerrecord, instead of to the @targetRecord.

Remember that called rules will be looking for a match between the Target in both the first rule and the second rule, which, in this case, the second refers to the @triggerRecord. Observe the rule "Example 4b" for more clarification.

Example 4b: A rule that works standalone, but will fail if called by another rule

This rule has "Invoice" as both Trigger table and Target table.

set "Grand total" = sum("Item total") from "Invoice Item" where "Invoice ID" = @triggerrecord

If this rule fulfills its Trigger condition, the action will be successfully executed because the Trigger record is found. But if it’s called by another rule, it will fail, since a rule called will always look for the Target record, bypassing the Trigger record and its conditions.

In conclusion, when calling a rule by using the trigger "rule name" query, keep in mind that:

  • The called rule’s Trigger table and conditions will be bypassed

  • If a rule is called directly on the action (i.e., not nested), the Target table of both rules must be the same.

  • If a rule is called within a for loop over records query, the called rule’s Target table must be the same as the Table referred by the for loop over records query.

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

Use record name as limitation or as value

Use @triggerRecord to refer to the name of the record which the conditions will apply to. If you are familiarized with Record filters, think of this variable as the @limitByValue we use in forms, since they both refer to whatever record is the "source" of the dependency.

Use @targetRecord to refer to the name of the record that will have the result of the rule action.

Both can be compared with RecordName, RecordId, and fields of type Relation and Text.

Be consistent in the way you use either RecordName and RecordId together with @triggerRecord and @targetRecord in your queries; e.g., if you use @triggerrecord as RecordName once, you cannot later use it as RecordId in the same Run IQL action.

Example 1: Query to limit the resulting action based on the record triggering the rule

set "Number of resources" = count(RecordName) from Resources where "Project assigned" = @triggerrecord

The purpose of this rule is to count how many resources have been assigned to a project, i.e., tables related like Resource→Project

For this example, both the Trigger conditions and the Actions in the rule configuration were set to the "Project" table, i.e., Project triggers its own count of child records, e.g., by clicking on its "Update status of the project" checkbox.

Note that without the where "Project assigned" = @triggerRecord limitation, the result would be the count of all resources among all projects, set on the one Project triggering the rule.

Now, let’s compare the previous example with the use of @targetRecord:

Example 2: Query to limit the resulting action based on the record targeted by the rule

set "Number of resources" = count(RecordName) from Resources where "Project assigned" = @targetrecord

The purpose of this rule is to count how many resources have been assigned to a project, i.e., tables related like Resource→Project

For this example, in the rule configuration, the Trigger conditions were set on the "Resource" table, while Actions were set on the "Project" table, i.e., Resource triggers the count that will reflect on Project, e.g., upon creation of a new resource related to that project.

Note that without the

where "Project assigned" = @targetrecord

the result would be the count of all resources among all projects, set on the one Project related to the triggered Resource.

As a complement to the Example 2, in case you wish to discount deleted resources, you would have to create an additional rule where the Trigger condition is "Record keyword = DELETED" on "Resource" table and the Action Run IQL on "Project" table is:

set "Number of resources" = count(RecordName) from Resources where "Project assigned" = @targetrecord and recordid != @triggerRecord

As you can see, the variables @triggerRecord and @targetRecord will consider the record affected by the conditions or by the actions, respectively.

For rules without Trigger conditions (for example, rules that will be called by another rule using the query trigger "rule name", the @triggerRecord will be empty, but if that query trigger "rule name" is nested within a query for loop over records, then the Trigger condition will be the one specified by the latter query, just as in the Example 1.

Example 3: Set the value of a field as the pointed Record name

Having the model Resource→Project, the purpose of this rule is to assign resources who lack project (action 1), to the project that is created (condition), and also set the start date on the project (action 2).

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

set "Project starts on" = today()

For this example, both the Trigger condition and Actions in the rule configuration were set to the "Project" table, even though the action 1 is aiming another table, the action 2 is for itself.

Most of the IQL functions used in filters and forms can also be used in Run IQL. For example, the replace function could be used in both Run on change (property for fields in Form designer) or Run IQL (action in Rules manager):

set "Full address" = replace("Full address", 'PC', 'Postal code')

meaning that if the text PC is found in any part of the value of the "Full address" Text field, then it will be replaced with Postal code instead. The "Full address" being "6300 Legacy Drive, PC: 75024, Plano, TX", would be replaced for "6300 Legacy Drive, Postal Code: 75024, Plano, TX" after this query is applied.

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

would concatenate string together with the Record name and a Number field converted to a string with currency format, resulting to "The invoice number 10001 was approved with the total amount of $ 130,000.99", that can be used in both Rules and Forms.

General example

/* Define a parameter to temporarily fetch the number of related orders,
   which will be called in the next parameter to define a consecutive ID
   number for the new internal order */
let @consecutiveNumber =
     count(MainOrder) from Order where MainOrder = @triggerrecord

/* Create a new Order record and assign the new record id to a parameter */
let @newInternalOrder =
     insert Order values RecordName = RecordName || '-' || (@consecutiveNumber + 1)

/* Set additional fields on the newly created record */
for Order @newInternalOrder do
     set OrderType = Internal
     set MainOrder = @triggerrecord
     set Customer = (Customer from Order where RecordName = @triggerrecord)
end

/* Finally, reset the checkbox that triggered the rule in first place */
set "Create internal order" = false

Timeout on rule chain: In order to ensure a good performance of the application, a time of 15 minutes has been set up as the maximum limit for a rule to trigger a large chain of actions. After that, the rule will be automatically stopped.

Set the value of fields using IQL

In Rules manager, create a rule with an action called Set the value of fields using IQL.

In the Fields to use (in list order) box, select the field(s) which will store the resulting value (i.e., the target field). In the IQL box, type:

  1. The name of field(s) that contain the value you want to set. This field can be accompanied by a function.

  2. The table of the above field(s). Can be any table (the same target table, a related one, or even a table with no relation with the target). The keyword from must be used, like from table.

  3. The criteria to find the value.

For instance, in Fields to use (in list order), select Responsible, and in IQL, type

Name from Employee where Project = 'Campaign' and Approved is not null

If you select multiple fields (to obtain more values at once), like Responsible, Years of service and Current manager, make sure you type the necessary fields in the right order, separated by commas, like

Name, Seniority, Manager from Employee where Project = 'Campaign' and Approved is not null

There are other elements that can be included, such as variables like

@triggerrecord , and @targetrecord

Examples:

Name from Employee where Project = 'Campaign' and Approved is not null
Name, Seniority, Manager from Employee where Project = 'Campaign' and Approved is not null

If you select multiple fields (to obtain more values at once), make sure you type the necessary fields in the right order, separated by commas.

createdusername(Name) from Employee where Project = 'Campaign' and Approved is not null

Note that you can use system defined fields as functions at field level.

recordid, Name, createddate(Name), createdrealname(Name), updateddate(Name), updatedrealname(Name) from Employee where updateddate is not null
Customer from Order where recordname in(Order from OrderItem where Total > 10000)

Note how a query can be nested within a main one.

min(DeliveryDate) from ProductTable where Product = ‘A’ and RelationToShipmentTable = @targetrecord

Would return the earliest date not only from all products that are ‘A’ in the whole table, but from a limited bunch of rows that are children of the parent being targeted in the form.

Field from Table2 where RelToTableA= @triggerrecord

Makes reference of the record that triggers the rule.

Name from Customer where recordname in (Customer from Work where recordname = @targetrecord)

A value is obtained from the parent table, i.e., reverse direction, one level down.

Name from Customer where recordname in (Customer from Work where recordname in (Work from Employee where recordid = @targetrecord))

A value is obtained from the parent table of the parent table, i.e., reverse direction, two levels down.

round(avg(NumberField1)) from Table1

To remove decimals from the number resulting from average of all values in NumberField1.

upper(CustomerName) from CustomerTable

Would return the Customer name string in uppercase.

substring(TextField, 1, 3) from Table1

Would return ABC for a TextField with value ABCDE.

Concatenation is not a function but the || operator can be used for that purpose; e.g.:

Text1||'-'||Text2 from Table1

Would result in Value1-Value2

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.

To be able to use geodist and usergeodist, enable the geolocation setting in Role administration, and in Application settings (server level). Note that there are also useful functions for geolocation via rule action formulas and validations.

Set field values using IQL should only have one result row. If the IQL query returns more than one row, the value from the first row will be set.