Today I would like to briefly talk about an interesting topic, how to search data from multiple tables simultaneously in Business Central.
This is a question I saw in Dynamics 365 Business Central Forum last week.
How to Search for Sales Quote Number – Dynamics 365 Business Central Forum Community Forum
How can we search for specific Quote Number once it is converted to Sales Order or Invoice? Archiving for Sales Quote is not Turned on. The Quote Number could be in Sales Order, Sales Invoice or Posted Sales Invoice etc. Is there way to search quickly without searching each list separately?
In short, he wants to search for Sales Quote Number in different tables. So how to do?
It’s actually not that difficult. This time we just need to use the new feature from Business Central 2022 wave 2 (BC21) below.
Dynamics 365 Business Central: Search for Specific Data (Search in company data)
Let’s look at a simple test.
First I created a Sales Quote with the number 1005. Then manually run Archive Document once.
Choose Make Order to convert the sales quote to a new sales order.
So far, we will have two versions of the Sales Quote Archives and a new sales order (101043).
Finally, post this sales order in two parts. Once for Ship and invoice and once for Ship.
We can get one Posted Sales Invoices and two Posted Sales Shipment containing the Quote No.
That’s the end of the preparation, so we need to search for Quote No. 1005 from
Sales Header Archive (5107), Sales Header (36), Sales Invoice Header (112) and Sales Shipment Header (110). This is also not very simple to do if customized.
Well, let’s do it in standard.
Start a search on your Role Center in the following ways:
- Use the Search in Data action
- Use the Ctrl+Alt+F shortcut key combination.
- Enter Search in company data in Tell Me, and then choose the related link.
Choose Results -> Show tables to search
Then choose Actions -> Show all tables in Search Setup (Table) List
Find the table you need to query, and then enable Search Enabled.
Then click Table Name, for example Sales Shipment Header, find the field you need to query, and enable Enable search.
Enter Quote No. 1005, you will have all the records you need. 👏👏👏
Click Description in the Results to open the record directly.
Very convenient, give it a try!!!😁
Hope this will help.
Thanks for reading.