Filters in Dynamics 365 for Finance & Operations
When it comes to a fully integrated ERP system, one of the main benefits is that you have quick and real-time access to data and information. If the ERP solution in question offers a sophisticated, user friendly, and versatile selection of ways to quickly and efficiently find information, it creates a competitive advantage to users. Dynamics 365 for Finance and Operations and all its predecessors were always known for the extended list of options available for users to search and filter through.*
Below you will find a selection of the most commonly used search and filter options that will help a user find the information they are looking for. Please note that this post focuses only on filtering/sorting data on a grid type form in the system and is not addressing general navigation tips and tricks.
The search bar is located at the top of most list pages. The search bar allows you to filter by complete or partial information and it will auto-recommend the column/data area to search in based on the value you entered. Be sure to select the correct area to ensure the expected result.
Make sure you do not hit enter once you’ve typed the value as that will result in the system searching in the first recommended data area, which might not be the one you are looking for. In the example below, if you hit enter it would look for ‘Contoso’ in the customer account field. Click the ‘X’ in the corner of the search bar to clear results.
The search bar is the quickest and simplest way to find a record in a list page. It is also relatively limited in its complexity.
Filter by Field Options
On all list pages users can click on the column headers to open a filter/sort option for the column. Please note that there are exceptions where data in a particular column cannot be filtered/sorted because of its data type.
You may sort the values on the grid in ascending or descending order by clicking the ‘Sort’ options.
You may also use the filter options by entering a complete or partial value in the search bar and adding instructions on how to apply that value as a filter. You can do this by opening the drop down in the top right corner over the search bar and selecting the appropriate command. Click ‘Apply’ to apply the filter.
Once you have applied a filter to a column, a funnel icon will appear. The list of data you see is not the full list of data in this area. You will see a downward or upward pointing arrow if a sort has been applied to a column.
You may remove any filtering or sorting by clicking ‘Clear’.
Multiple Grid Filters
If there is a need to apply multiple filters at the same time, users might decide to use a query by clicking the funnel icon located on the left side of the screen.
A section will open with one or more standard/basic filter options available. You may use these options and apply the same logic you did with the column header filters. Or you may decide to add further filters by clicking the ‘Add a filter field’ button.
A drop-down list appears with a list of fields that are available to be used as filters. Select the field from the drop-down list that you would like to add. It will appear now as a filter field for you to add the filter criteria.
To clear filters, click the ‘Reset’ button. To hide the filter pane, click the funnel icon.
In all list page grids if you navigate to the Options tab in the ribbon, you will find ‘Advanced filter/sort’ under the Page Options section. This will open a query form where users can build more complex filter options and save them for regular use.
There will be an out of the box filter list provided where users must only enter the filter criteria next to each filter and click ‘OK’ at the bottom of the form to apply all filters at the same time. Please note that drop down lists, calendars, etc. will be available for ease of use in the criteria fields.
If the desired field is not listed as a filter option, click ‘Add’ located at the top of the form. This will create a new line on the filter grid, and once the ‘Table’ and ‘Derived table’ are selected, the Field drop down list will allow you to add any of the fields from these tables as a filter.
Please note that values entered in the Criteria field will be searched for as exact match requirements. For partial searches, you can search for ranges of data, exclusions, etc. but users need to use special characters. The most important one to be familiar with is the * which works as a ‘Joker’ character and represents unknown value.
For example, if we would like to find all customers whose names start with the word ‘Contoso’, we could enter Contoso* in the criteria; the * represents that there might be an unknown value following the word Contoso. If we were looking for all customers whose names included the word Contoso, we would place the Joker character both before and after the word *Contoso* to indicate a potential unknown value in both places.
For a full list of special characters and how to apply them, please refer to this article. Please note that this article references AX 2012 but the information is valid for Dynamics 365 for Finance and Operations.
If a user builds up a query that they would like to use on a regular basis, they may save the query by clicking ‘Modify/Save as’ once all filters and criteria are added.
The user should then give the query a descriptive name that will help identify it in the future.
Once saved, the query can be selected from the drop-down list under ‘Select query’ located at the top of the form. Once selected, filters and criteria are automatically loaded into the query form for the user to apply.
Contact us if you have any questions about Dynamics 365 for Finance and Operations.
* Most customers still call it Finance & Operations, but Microsoft now licenses it as 2 different apps: Dynamics 365 Finance and Dynamics 365 Supply Chain Management.
We specialize in providing technical assessments, break-fix support, optimization services, and solution expansion projects for Dynamics 365 for Finance and Operations.