Filtering with Microsoft Dynamics 365 Finance and Operations (F&O)

SQL operators are used to perform operations on data within a database. They help in filtering, aggregating, and manipulating data, making them essential for querying and reporting in a Finance and Operations database. Here’s a list of commonly used SQL operators along with examples relevant to Finance and Operations scenarios:

Note: When setting up any filter, the source field must be added in the field FIELD NAME. Then, the actual filter must be added in the field FILTER where the format should be : Field Name + SQL operator + value/expression (see examples below).

• Not Equal To ( denoted as <> , != or NE) is used to compare values in a database table and retrieve rows where a specific column's value does not match a given criteria or to exclude certain records when reading from the source system.

CustomerAccount ne "

• Equal To (denoted as = or EQ) is used to filter on a specific record on the source system.

CustomerAccount eq 'AB2221'

• OR is a logical operator that is being used to filter records based on more than one condition.

((ProjectCode eq '001') or (ProjectCode eq '002') or (ProjectCode eq '003'))

The below example can only be used if the field type is enum in F&O.Enums are under namespace Microsoft.Dynamics.DataEntities. Enums can be included in an OData query is by using the following syntax:

PersonGender eq Microsoft.Dynamics.DataEntities.Gender'Unknown'

• Greater Than or Equal To ( denoted as >= or GE) is used to compare values in a database table and retrieve rows where a specific column's value is greater than or equal to a given criteria.

EndDate ge 2024-01-01T00:00:00Z

Another option is to set up a filter on a datetime field and query a specific range:

DocumentDate ge datetimeoffset'@{addmonths(now(), -6)}'

More information regarding the QueryRange can be found here.