Filters allow for more efficient sorting of data and are a great tool when it comes to displaying or retrieving concise and relevant data. The '$filter=' type of Filter uses OData query language and can be used in Form Visibility Rules, Base Filters, Word Export Reports and Workflows. They require configuration in App Studio. Hence if you don't have access to such you may need to contact your administrator.
Generic Filter Structure
- $filter=[FieldIdentifier] eq 'Value'
- $filter=[FieldIdentifier] ne 'Value'
Note: Only direct Field ID references as seen above can be used in Filter strings - Expressions are not recognised. For example '$filter=[DateField] eq dateNow()' will not work.
Think of filter expressions as conditions that are either met or not. Above are the two main syntaxes for filter building, based on the reference of a particular field and whether it equals 'eq' or not equals 'ne' a given value will determine if the target item is hidden / filtered. It is only in the case where the overall condition is met that the target item will be hidden / filtered, otherwise it will still show / won't be filtered.
These can also be used where applicable:
- gt for >
- gte for >=
- lt for <
- lte for <=
Note: If suited to the use case, you can build more complex Filter strings or sets of conditions to be met / not. This is done by using and / or in the strings with brackets and follows boolean logic to derive an overall value of true or false.
$filter=([FieldIdentifier1] eq 'Value' and [FieldIdentifier2] eq 'Value') or [FieldIdentifier3] ne 'Value'
In the above, the Filter conditions will be met if Field1 and Field2 equal their specified values OR if Field3 is not equal to its specified value.
The given value will vary depending on what type of Field is initially referenced, these can be number values in the more traditional sense or text ones:
- [TextFieldIdentifier] - Quote the desired text value: 'Text'
- [NumberFieldIdentifier] - Quote the desired number, without quote marks: 1
- [BitFieldFieldIdentifier] - Quote the desired Boolean value, without quote marks: true or false
- [DateFieldIdentifier] - Quote the desired date value: 2021-01-01T00:00:00Z
NOTE: You can also use '' for the null case on text based values, to test whether a Field is empty or not. (See Template Visibility example below)
The construction of Templated Reports will not be covered in this article, for that please see here. Instead the function Filter Strings can provide to Templated Reports will be outlined. For this there are two main use cases, at the App Level Report and the Record Level Report.
- @Report(AppID,ReportID,Filter String)
App Level Report
Filter Strings at the App Level, used in a Templated Report act like the Filter that you may usually apply to a List Report. For example you may wish to export a List Report from an 'Incident' App called 'Incidents' containing only those with a red 'Status'. For this you would use:
- @Report(Incident,Incidents,$filter=[Status] eq 'Red'
Record Level Report
The same principles as above apply to the structure of the expression at the Record Level also; only the function it performs changes slightly. Here the Filter String would be used to Filter and export a Report from a Child App as opposed to directly from the Parent (or just singular App) like at the App Level.