Hi Everyone,

Hope you are doing great!

List/Grid page are essential part of D365 Finance and Operations. Most of the time we use basic filtering on the list page to extract the meaning full data from the raw data. However basic filtering is not sufficient when we want to extract the data from multiple source/table.

For Example, we have Vendor List page, which list all the vendors in USMF Legal entity. We can use Vendor group for Filtering the vendor from specific group only, say intercompany Vendor. (Basic Filtering)

What if Instead of all vendors, we only want to check the vendors with whom we have booked purchase order. This detail is neither available on the vendor list page nor we can personalize and add column for the same.

To address this kind of scenario we can join the related table to filter out the data. In our case we can join purchase order table with the vendor table to find the list of vendors with whom we have booked purchase order, rather than all the vendors.

Accessing Vendor List page

Let’s go to the Vendor List page (Account Payable > Vendors > All vendors) This is Vendor List grid page, which give data from Vendor Table.

Joining related table

Above form gives the list of all vendors including vendors with whom we have not placed purchase order. Let’s join the purchase order table to vendor table. Go to Options > Page Options > Advanced filter or sort.

Next form will give us options to filter the data using criteria (Range), sort the data (Sorting) and option to join the tables (Joins). Under the Join tab, we can see the list of tables present on the form and its default relations. In our case Main table is Vendors table and its default relation is with Global address book, Worker, Dimension code combination, and 1099 fields.

Note that by default, there is no relation with Purchase table, so we need to set the relationship between both the table. (Apart from purchase table, it can be Vendor Invoice journal or Vendor Transaction or any other table having logical relation with Vendors, depending on the data to be extracted)

Click on Vendors and then click add table join.

Selecting related table

On Next page we can select the applicable table for joining. Click on Show details, it will show the list of all table which can have logical relations with Vendor table.

In our case, Related table for filtering vendors data is Purch Table. So, Lets filter with PurchTable and select it for Joining.

Once joining is done Purchase orders table will appear in list of related tables. This means that now purchase order table can be used for filtering the record of Vendors table using relationship with Purchase Order Table.

Filtering the data based on new table

Since joining has been done and related table has been added, its time for filtering the data.

Let’s go to Range and filter the Vendor list with Purchase order table. In table & derived table, select newly added Purchase Order table. In field you can select various column from the related table. Lets Select Invoice Account.

In the criteria I am using wildcard character !""(not blank). Depending on the data sought to be filtered.Various other wildcard character can be used. Please refer Microsoft documentation for wildcard character.

Checking the Filtered data(outcome)

Once table and criteria has been added, click ok. Vendor list will be filtered with the given criteria, which in our case is to get the list of vendors with whom purchase order has been booked.

Saving the query

Further, we can save this query for using it on daily basis, as & when required, which can save a lot of time.

Conclusion

This is a good feature, which on back-end access the Production SQL database and perform joining directly into Application itself.

We have used vendors as example, but it can be used for logical relations between for customer, Projects, Fixed Assets module or any form whether from Ledger or Sub Ledger. Using this, we can use multiple queries on the same form and extract the meaningful information from the raw data.