Record filter

Application forms > Record filter

The best way to find the records you need is by using the Record filter represented by the ShowFilterIcon icon in application forms (specifically in sections, charts and maps), which displays a pop-up form, where a custom filter can be configured and applied and, optionally, even saved as private or public for later use.

For IS Tools to function properly, you may need to enable pop-ups for the IS Tools site in the settings of the web browser. The most sensible choice is to permanently allow pop-ups for IS Tools servers on which you will be working. The procedure for doing so varies with the type and version of your web browser.

The following actions can be performed with the Record filter:

Create a custom filter

Open the Record filter pop-up form and note that a line into the section called Show records matching all of these is blank waiting for a condition. The condition usually consist of a field, a logical expression (e.g. Equal to, Greater than, etc.) and a value (a constant, an option, another field’s value, e.g., 42, "Option A", MyField2).

Select then the desired field, the logical expression and the value you want to compare the record with. For instance, a condition like Record name Equal to A*, will try to find all those records which names start with the letter "A". If you are looking for an exact match, the condition would be like Record name Equal to (no wildcards) A.

Note that:

Some system defined fields are included by default in the list of fields to choose from, besides the fields that belong to the current table’s field group.
The logical expression’s list will depend on the type of field previously selected.
Constant values are usually case sensitive (depending on the server settings for the current application). The characters ? and * are allowed, where ? is translated as any one character, and* as any number of any characters when typing and absolute value.
Conditions that involve date and time (e.g., Record created time, Record updated time, any Date or Date time fields using the keyword TODAY) will be regulated by server time.

If necessary, add more conditions by clicking the Add OR criteria or Add AND criteria buttons.

Note that the first condition line added by default when opening the Record filter with no previous filter applied, implies the use of the "AND" operator in respect of the next condition added to the filter, e.g., condition1 AND condition2, where records must match all conditions to be able to be displayed as result in the form.

In contrast with the latter operator, the "OR" operator is added in pairs, i.e., two conditions (or more, by clicking the plus icon at the corner) in a delimited area, e.g., [condition1 OR condition2], where records must match any of the conditions, i.e., at least one of the conditions must be true. Observe that two pairs added one after the other implies an "AND" operator between them, e.g., [condition1 OR condition2] AND [condition3 OR condition4].

A mixture of conditions with both operators is also possible, e.g., [condition1 OR condition2] AND condition3 AND condition4, where records complying either condition1 or condition2, together with both condition3 and condition4 will be displayed as result in the form. In this example, note that the first condition line added by default must be deleted or left as blank.

Optionally, click the Count matches button to preview the number of records (displayed on the header of the current filter window) matching the criteria. Then, click Run button (or press Enter) to finally apply the filter on the form.

If the tools described above are not enough to make the query you need, use Advanced filters instead.

After applying the filter, note that the Record filter icon becomes dark gray, indicating to the user that the list of records has been filtered. The filter and the indication will be only available for the current form, and will remain so until the filter is cleared.

After this, you can either close the Record filter pop-up form to come back to the application form, or you can save the filter (to reuse it at a later stage) before closing the pop-up.

To save the filter, click the Manage filters button, type a Filter name, tick the Public checkbox (all users will be able to use it) or leave unticked to save it as private (only the user who create the filter will be able to use it) and then click the Save as new button.

Click the Hide button if you do not wish to display saved filters of the Manage filters section.

To apply a saved filter, click the arrow next to the Record filter icon of the section, and then click on the desired saved filter.

To apply a saved filter, click the arrow next to the Record filter icon RecordFilterQuick of the section, and then click on the desired saved filter.

If you apply a filter, the filter expression will be remembered and applied the next time you re-open that form, however not in other forms that use the same table. The filter will also be remembered in that form after logging off and on again.

After applying a filter, if the filter matches no record, the form still displays the record that was being displayed before applying the filter (except for table sections and charts, shown as empty).

If your intention is to filter only by Record name in a Detail section, you can instead dismiss this Record filter and use the quick filter available in the Record selector.

Note that the list of records can by filtered by both the quick filter and the Record filter in a combined way, i.e., all criteria will be taken in consideration.

Edit a custom filter

In the Record filter pop-up form (with the desired filter currently displayed), edit the necessary criteria and click Run (or press Enter). If you want to remove a condition, click on the Delete3gIcon icon next to it, or click the Remove all button to remove them all. Click Run after that.

In case this is a saved filter, click the Manage filters button in the Record filter, edit the filter by modifying, adding or removing criteria, and then click on the Save button.

Delete a custom filter

Several actions can be understood as "deleting" a filter:

To clear a custom filter on a section or chart, click on the ClearRecordFilter icon next to the ShowFilterIcon icon. Note that the form now displays all available records.

To remove only one condition, open the Record filter pop-up and click on the Delete3gIcon icon next to it and click Run.

To delete a saved custom filter (private or public), open the Record filter pop-up, click on Manage filters and select it in the Filters list, then click on the Delete button.

Delete a custom filter

If filter conditions described above are not enough to make the query you wish, click on the Advanced button.

Advanced filters

If filter conditions described above are not enough to make the query you wish, click on the Advanced button.

To get started with Advanced filters, you can create a custom filter first, and from there click on Advanced. This way you can see and learn how basic conditions are typed in IQL (IS Tools Query Language). After that, consider the following to create your own queries:

Then, you could type somthing like 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.

You may use other comparison operators such as =, !=, <, >, <=, >=,

in (The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions, and useful in nested queries. Must be used when comparing Drop-down fields.) , is null, is not null. There is also null, which can be used as a keyword to clear fields. For Text and Note fields, there is like (Works similar to =, with the difference that like is case insentive and can be used with * and ?) , startwith, endswith, contains. To combine conditions, there is AND (All conditions must be true) , OR (One condition must be true) , NOT (To indicate that the condition cannot be true) . Note that parentheses can be used as group separators. For example: (OrderStatus in(“Ready”) AND PlanedDeliveryDate ⇒ Today()) OR OrderStatus in(“Escalated”)

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.