One of the things that I’ve loved showing people over the years is advanced filter or sort in Dynamics 365 Finance and Operations. Now I’m going to show you.
Advanced filter or sort allows you as a user to filter one table on the results of another table? I’m sure that makes sense but let me give you an example. Let’s say you want to find which vendors in our demo environment have sold us item M0001. You could run a report OR you could use an advanced filter.
From almost any list page, click the “Options” button in the ribbon bar. Click the “Advanced filter or sort” button.
Clicking this button will open a weird little form. I still think it was easier to use in older versions of Dynamics AX, but I digress.
You can see, that the form by default allows you to filter on items that exist on the data source for the form. If you want to see all the ways you can filter this form by default, click on the “Table” drop down and examine all the different data sources.
To use this form, you select an item from table, derived table (which is usually the base table), and field. You can then enter the criteria you want the filter to evaluate. If you want to add more than one filter criteria, you can add multiple rows.
In our example, though, we want to filter our vendor table on records in the purchase order lines table. This isn’t one of the default data sources on this form, so we’ll have to do one more step.
Click the “joins” button in the form. What we need to do here is add the Purchase order lines table as a data source. It’s tricky to explain, so here’s a video that shows what you need to do.
There’s all sorts of complicated criteria you can use, too. If you don’t know the full number, you can enter a range by entering “100..199” or if you want to enter a string but aren’t sure how it’s entered you can use a wildcard by entering “* Toys” to find all records that end with Toys. Here’s a handy link to the criteria you can use.
Pretty easy, right?
Want more awesome tips? Learning paths.
Leave a Reply