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):
<> , != 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 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')
ProductNumber NOT IN ('CS-U012','CZ-U019')
= or EQ) is used to filter on a specific record on the source system.ItemId = 'M1-1234'
tag_number LIKE 'HX%'
tag_number NOT LIKE 'HX%'