If you can’t beat ’em, get joining some tables!

You can’t always get all the information you want or need from one table or default view in F&O.

For example, viewing the Open purchase order lines form gives valuable access to all lines, across all purchase orders, that have the status “Open order”. But what would you do if you wanted to filter on something that’s not available in this view, like Cost centre?

Maybe export all the data to Excel and play with it there, as and when you need to? Or build a custom Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding. report, so you can run the report whenever you need it? Here’s another idea you may not have thought of…

Advanced filter or sort

Paul did a great post about Filtering in D365 F&O that has proved really popular with our subscriber base. We’re going to step up a gear here, and go into the Advanced filter or sort dialog, found under the Options tab in the Action Pane on most forms:

The first tab in the dialog is called Range. This is where the detail of the current filter is defined. Note the tables that are available by default:

N.B. There are some really powerful tricks that can be achieved on the Range tab – this will be the subject of another post.

Make the connection

We’re going to head to the Joins tab, where we can see the same tables and how they are linked by default to Purchase order lines:

Select the Purchase order lines table and click Add table join. You’ll be presented with a list of tables that have the potential to be joined.

Health warning: Despite being listed, not all tables join logically and this functionality may require some trial and error and generally prove tricky to get to grips with.

Scroll to find the Dimension code set. For other use cases, you might want to toggle Show details to find the specific table or detail you are looking for:

Once you’ve selected the table, click Select. You’ll be returned to the view below, where Dimension code set will now be seen:

Click OK before continuing to the next step.

Bringing it back together

Re-open the Advanced filter or sort dialog. Add a new row to the Range tab and you’ll now see the Dimension code set, or table(s) that you selected, available as an option:

Complete the filter criteria – in our case, down to the CostCenter field and a specific value of “T302”:

When we click OK, the filter will be applied, as shown below. You now have the option to save this view (e.g. “Open PO lines for CC=T302”), before adding it as a tile or list to a workspace of your choice for quick reference.

Conclusion

Table joins are standard stuff for database administrators and technical consultants, but this functionality brings the power to functional consultants and end users.

Try it out – you could save a lot of effort compared to Excel, Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding., or worse still, over-customising your solution when out-of-the-box features like this can do exactly what you need!

If this has inspired you, please do share your own ideas or use cases in the comments.