Filtering with Microsoft Dynamics AX

Filtering on the source system is an efficient way to retrieve records from a specific table based on the conditions that you have defined under Filters Source (delimitations) section. Also, the filters are a great way to control the data that will be transferred to the destination system.

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

Here are the most commonly used SQL operators and examples when querying Microsoft Dynamics AX (or the underlying database tables in SQL Server):

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

Type <>'2'

• IN - The IN operator allows you to retrieve only the records that meet that criteria. If it is being used in combination with the WHERE clause, you are pointing to a different table available in your source system. Your current table and the referenced table have in common the field that the filter is set on. 

ProductNumber IN (SELECT ProductNumber FROM Products WHERE Name <> '')

OR 

ProductNumber IN ('CS-U012','CZ-U019')

• NOT IN - By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.

ProductNumber NOT IN ('CS-U012','CZ-U019')

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

ItemId = 'M1-1234'

• LIKE - The LIKE operator is used to search for a specified pattern while reading data from a source system. The % wildcard represents any number of characters, even zero characters. To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase. To return records that ends with a specific letter or phrase, add the % at the beginning of the letter or phrase.To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase.If no wildcard is specified, the phrase has to have an exact match to return a result.

tag_number LIKE 'HX%'

• NOT LIKE - The same as the LIKE operator, it searches for the specified pattern while reading from the source system.

tag_number NOT LIKE 'HX%'