Using the power of Queries to bring some Smartness into Business Central

Currently there’s a lot of talk about Business Central performance. Microsoft has released and is releasing guidelines, tools about mechanisms to program for performance, analyse and monitor performance. At the same moment there’s also discussions going on on public and private fora about database size and limitations. And last but not least Artificial Intelligence is also a hot topic, making Business Central smarter, so it can help you in your daily business.

And then I suddenly thought about something I did a long time ago, for Dynamics NAV, using C/Side, and I figured, well actually this is something that can do all three things using standard objects. So I will present a way on how you can use simple query objects combined with a temp-table as a source for a fact-box, to bring some smartness into Business Central and still keep your footprint low regarding performance and database space usage.

The idea is simple. What I want is some help when I have the customer on the phone and he/she is ordering items and at the same moment I’m entering the sales order into the application and I want to do some cross selling. Very similar then also available on many online web-shops, when I enter a product (an item) on a sales order, then I want the system (Business Central) to also propose similar items that other customers also bought when they ordered the same item. Similar to when you order a book on Amazon, the suggestion that popups: other customers also bought these books…

So what I want is this:

So when I enter an item on a Sales Line, then the fact-box should go and look what other customers bought, this means looking in the posted sales invoices for other customers that bought the same item, and show these items, including the current price and availability. Not only that but this information will not be stored in the database because I’m trying to keep my footprint low and furthermore the information needs to be found fast, preferably with a low impact on performance.

Well, I’m a simple developer, so I will use simple tools and simple objects. I’m sure I would be able to create a great machine learning model, train it and use it to get maybe much better results, but I’m not going to do that. I’m sticking to simple AL.

The first thing you need is a query. This query uses the Sales Line as a starting point. The query also needs to have the possibility to be filtered, using for example: an item number, a customer number, and other fields. These fields do not need to be in the result-set of the query, which makes it faster.

So you can start the query as follows:

Based upon these filters I can than go and find Sales Invoice Lines, but also these posted lines might need to be filtered on a certain period, so I will do the following:

So I’m adding a data item, for the Sales Invoice Line, with a filter option on Posting Date, and only lines of type Item. Then I’m not fetching each and every Sales Invoice Line found, but instead I will count the number of items and group by item number.

Then I will add another data item as follows:

Here I’m fetching the item description and unit price.

Voila, that’s it. This is the whole query that I will use, as the business logic for the fact-box.

Let me provide a screenshot of the whole query:

Now it’s time to build something that can display the results of the query. For this I will create a fact-box. Here’s the code:

Notice that the fact-box uses the Sales Line table as a temp-table, and as fields I’m using the fields that my query will return.

To provide a drill-down on the item availability and calculate the item availability I use the following code:

So the function SalesInfoPaneMgt.CalcAvailability(Rec) will calculate the availability, and the codeunit Item Availability Forms Mgt has a function called ShowItemAvailFromSalesLine to visualize it.

Then when the fact-box page is called it will run the following code:

The function FillTempTable runs the query and uses the results to fill the fact-box:

How cool is that! But you’re not ready yet. Now you need to plugin the fact-box on the Sales Order page:

To link the sourcetable of the fact-box to the lines part of the Sales Order page I use the Provider property, and then it’s a simply matter of setting the SubPageLink, which will trigger the code of the fact-box.

Voila, you’re ready, the magic is done.

See how easy it is? No special tricks, no special technology, only simple basic AL objects and simple AL code.

That’s the power of queries. Not only are queries very good for reporting but as you can see yo can also use them in page objects.