Filters (OData) - Advanced

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, Workflows and even URL Fields. Some of these 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 examples 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 >
• ge for >=
• lt for <
• le for <=
• ne for <>
• eq 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.

E.g.

$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.

Or Filter Requirement

Note: When using an 'or' in a base filter it will need to be contained in brackets otherwise if a filter is applied over it in workspace it will add it to the end of the base filter and not add an separate addition to the filter. Example below

Wrong Scenario is:
Base filter: $filter=[Status] ne 'Green' or [Status] ne 'Amber'
Workspace filter: [Owner] eq 'Mark'

• Combined, this gives
  $filter=[Status] ne 'Green' or [Status] ne 'Amber' and [Owner] eq 'Mark'

Correct Scenario is:
Base filter: $filter=([Status] ne 'Green' or [Status] ne 'Amber')
Workspace filter: [Owner] eq 'Mark'

• Combined, this gives
  $filter=([Status ne 'Green' or [Status] ne 'Amber') and ([Owner] eq 'Mark')

Different Values

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: datetime'2021-01-01T00:00:00'
  • e.g. [FieldName] gt datetime'2021-01-01T00:00'

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)

Application Exceptions - Templated Reports and URL Fields

For both of these exceptions, the Filter String is required to be in a URL encoded format this consists of using different characters to represent others, for example %5B represents an open square bracket '['. There are a number of free online URL encoder and decoder tools that may help you with this in addition to example below.

Templated Reports 

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.

General Expression

• @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 a 'Sessions' App called 'Sessions' containing only those with a Green 'Status' and ordered by subject. For this you would use:

• @Report(Sessions, Sessions, filter=%5BStatus%5D%20eq%20%27Green%27&orderby=%5BSubject%5D%20asc)

As you can see above the filter is quite complicated, the filter needs to be encoded in a certain format so it can be used in a URL when exporting the report. Above is an example of how an odata filter string is encoded

In a filter string, there are 3 types of functions you can apply

• filter=  ----- This function allows you to show and hide data on a specific condition like mentioned above
• orderby=  ----- This function allows you to order by a certain field
• groupby=  ----- This function allows you to group by a certain field

Here are 2 links you can use to Encode and Decode and URL filter string

• Encoder - URL Encode Online | URLEncoder
• Decoder - URL Decode Online | URLDecoder

Note: in the links above it will encode the = and the &, this is wrong, these will need to be turned back into the symbol - to find a step to step process and example please refer to this community post.

Below is an example of the process I went through to get the right filter:

1- The filter I wanted encoded:

• filter=[Status] eq 'Green'&orderby=[Subject] asc

2 - What was encoded:

• filter%3D%5BStatus%5D%20eq%20%27Green%27%26orderby%3D%5BSubject%5D%20asc

3 - The filter needed after adding back in the symbols:

• filter=%5BStatus%5D%20eq%20%27Green%27&orderby=%5BSubject%5D%20asc

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.