Filtering Record in Dynamics Ax

December 8, 2007

Filtering capability is vital in making huge set of data manageable. Dynamics Ax offers a range of features to filter records.

Dynamics Ax filter toolbar

Filtering data enables users to focus on the subset of data relevant to their intended task. Dynamics Ax offers three ways to filter at its user interface. This is consistent across the whole system. In case there are needs for more specialized filtering, you may engineer them with X++.

Advanced Filter

This filtering option allows you to construct a query to filter records. You may access this feature by pressing Ctrl+F3 or selecting the Advanced Filter/Sort button on the toolbar. You will be presented with the Inquiry form. This form has an interface that is used in many occasions where filtering is applicable. You may define criteria in any field from the tables relevant to the form you are working on. This option allows multiple criteria on a single field. The following figure shows the familiar Inquiry form used for Advanced Filter.

Dynamics Ax advanced filter

This feature might be the most complete filtering option on Dynamics Ax user interface but it requires a little learning curve. It is not as intuitive as the other options where you work directly on the form. The user has to know the name of the field she wants to set filtering criteria.

Having said so, the other methods we will visit shortly sort of fills up this Inquiry form for the user. The user is presented with a more intuitive way of entering filter criteria.

Filter by Grid

This filtering option presents a way to enter criteria at the top of the grid. Criteria could be set for multiple columns and the result fulfills all criteria. The following figure illustrates the Filter by Grid feature. This feature however can only filter base on fields in a grid.

Dynamics Ax filter by grid

When you press the button at the side of the filter column, you will see a drop down list with a wealth of criteria type. Selecting these criteria type would append the syntax for the type. This exposes the choices of criteria at the fingertip. Once you have used to it however, you shall find typing on your own works more efficiently. We will discuss each type in a later section as they are applicable for other filtering options as well.

Filter by Field

This filtering option is accessible from the context menu on form itself. It is applicable to any field on the form by pressing the menu or right clicking on the field. The following figure shows the context menu for field Customer account in Sales order form. The menu items Filter By Field and Filter By Selection are where you access this filtering feature.

Dynamics Ax filter by field

Selecting Filter By Field will bring up a dialog box for you to fill in the filter criteria. The format of criteria is consistent with those in other search and will be discuss in greater detail later.

Filter By Selection will filter the record with the value of the field you selected. It is a Filter By Field with the value inserted for you.

Saving Filter Criteria

The ability to save filtering criteria is useful when the user has a few repetitive tasks that require different subset of records. Saving of filtering criteria could be done for all methods stated above. You could do so through the context menu (see figure above) as well as the Advanced Filter Inquiry form. Use Save filter As to create a new entry whereas use Save filter to save modification on an existing query.

On top of that, there will also be an auto saved query named Previously used query. The last defined filter will be saved under this name. This happened to all the filtering methods mentioned above.

Criteria Format

The filter criteria formats are base on a few elements. The following describe the elements and the special character.

CharacterDescription
*Could be substituted by any zero or more characters in a string.
?Could be substituted by one character.
!Exclude.
.. Inclusive range.
A .. B - between A and B inclusive of A and B.
A .. - greater than or equal to A
.. A - less than or equal to A
<Less than.
>Greater than.

The following table shows the construct of the criteria formats we saw in the Filter by Grid context menu earlier. As you can see, they are a combination of one or more elements from the table above. Much more formats are possible by combining more of these elements.

Criteria TypeFormat
Contains*[text]*
Does not contain!*[text]*
Starts with[text]*
Ends with*[text]
Is (exactly)[text]
Is not![text]
More than>[text]
Less than<[text]
More or equal[text]..
Less or equal..[text]

Final Thoughts

Technically, filtering is performed at the form data source level. Additional options are more towards user interface enhancement. More intuitive ways are definitely possible with some creativity.

3 responses :

Anonymous said... (February 23, 2009 at 10:36 AM)

Hi.

Thank you for this info.

I have a problem when I try to filter using sql.

I would like to find all projects at level 4. Thus I add the sql statement:
(Projtable.name like ("%-%-%-*"))

Unfortunately this does not work in my Ax 4.0 DK-version. In generale I have problems with sql statements in the filters.

Do you have any idea to solve my problem ?

Regards

Peter Grandjean

Jaffar said... (March 19, 2009 at 8:56 PM)

Thanks for the article.

How do i remove/add column from Advance Filter in CUS layer level. (not on the user setup). I want to do change on layer level so that it will be applicable for all users initially.

Jaffar

Gert Cuppens said... (June 3, 2009 at 12:06 PM)

Just the article I was looking for. I will refer to this page in one of my websites on wikidot.