Introduction
In this demonstration we will explore how to utilize query objects to provide visual statistical data in FactBoxes. For this purpose we will be using Dynamics NAV 2013 R2 version.
The FactBox control in Dynamics NAV is a non-editable right-side pane window which is aimed at providing a quick overview of some statistics for the selected entity (customer, item, invoice line, etc.). The idea is to use the pre-generated query to fill in the FactBox data and display it on a page. To achieve this, we will be going through the following steps:
- Design the query
- Create the FactBox
- Add the FactBox to the page
Design the Query
In Dynamics NAV 2013 R2 Development Environment, open the Object Designer and select the Query object type. Click New to create the new query:
In the Query Designer, fill in the following query data structure:
The field GroupBy is filled in automatically. We added the Salesperson/Purchaser table as the top-level data item. This is the table we are going to use to get the names of our salespeople. We have added one column called Name to this data item.
Next, we have added the Sales Invoice Header data item indented below the Salesperson/Purchaser table. We did not add fields from the Sales Invoice Header table itself, but we changed the Method Type field to Totals and Method to Count. This tells Dynamics NAV to count the total records.
To connect the two tables and calculate the number of invoices per salesperson, select the Sales Invoice Header data item and click SHIFT+F4 to open its properties. Modify the properties as follows:
Click File, Save As and save the query as Salesperson Activity with a sample ID number. The query is generated as follows:
Create the FactBox
In this step we will create the FactBox where we will incorporate the data passed from the query designed previously. In the Object Designer, select Page object type and click New to create the new page. Save the page as Salesperson Activity FactBox with a sample ID. Click View, C/AL Gblobals and add the following variable:
Enter the following page structure:
Select the first empty row in the page designer and click SHIFT+F4 to open the page properties. Change the following properties as shown:
The source table for the page will already have the name of the salesperson to be displayed. What we need to do next is fill in the total quantity of invoices for this salesperson. We will be using the variable InvoiceCount we added above to store the values from the query we created earlier.
Click View, C/AL Globals, and on the Functions tab, add the new function FillTempTable. Close the Globals window and press F9 to open the code designer. Scroll down to the FillTempTable function and while inside the function code, click View, C/AL Locals and add our query as a local variable:
Close the C/AL Locals window and add the following code to the function:
What we do here essentially is:
- Reset the count variable for each salesperson;
- Set the query data according to the values in the Salesperson/Purchaser table
- Open and read the query to calculate the invoices for each salesperson
- Close the query
Next, we need to place this function on the appropriate page trigger. Scroll up to the following triggers and add the code as shown:
In this way, we tell the system to run and process the query after retrieving the record from the database.
Close and save the page. When we run this FactBox page, we see the following:
Add the FactBox to the Page
The final step is to incorporate the FactBox page into the Salesperson/Purchaser Card page. In the Object Designer, find page 5116 Salesperson/Purchaser Card and click Design. Navigate to the FactBoxArea container and add our FactBox as shown:
Click SHIFT+F4 to open the properties for this page part and change them as follows:
The SubPageLink property actually links our FactBox data to what is displayed on the Salesperson/Purchaser Card at the moment. We are lining the data by the Code field. Save and close the page. When run, we can see the following:
Conclusion
This simple example shows how powerful the combination of Query and FactBox can be. The aim of a query in these cases is to join several tables’ data and display it in an informative view via FactBox control.
Let us know in the comments if you were able to successfully use Query and FactBox together. We’d love to hear about the solutions you come up with!
