Business Central Queries VS Views
Queries and Views are two very interesting objects for Business Central, that can be used for various purposes. As both can be used, I point out the differences and the ways of using them with a couple of examples.
In this Scenario the battle card is: “Queries VS Views”
…a glorious battle like in GOT (Games of Thrones)… between the lord of the night and the lord of the light.
For developers, pages, reports, APIs and Odata required to show queries
“A query describes a dataset of Dynamics 365 Business Central. You can query to retrieve fields from a single table or multiple tables. You can specify how to join tables in the query and filter the result data, and you can specify totalling methods on fields, such as sums and averages. Queries retrieve records from one or more tables and combine the records into rows and columns in a single dataset. You create a query by adding a Query object file to your project. In the Query object, you define dataitem and column elements in the elements section. The dataitem element specifies the table to retrieve records from. The column element specifies a field of the table to include in the resulting dataset of a query.”
Scope of queries: query is an old NAV object (since NAV 2013R1), very useful to show and aggregate Data, a query can read from more tables and can aggregate data; useful also for Web Services and Odata publications.
Limitations: consumes a query object .. and a page\report to show data
A new Query to Show “Warehouse Entries” aggregated for Item\Lot\Location\Bin\Date, very useful for warehouse employ
AL Code is available On GITHUB
For developers and end-users
“Views in Dynamics 365 Business Central are used on list pages to define a different view of the data on a given page. Views can be defined for Pages, Page Extensions, and Page Customization. Views are defined on page extension objects to provide an alternative view of data and/or layout on an existing page, and in views on page customization objects, they can be used to provide an alternative view for a certain profile.”
Scope of views: views can be used to save time applying filters on the page lists; this feature (available with “Save As” button to-date only in Windows Client) is very useful and will be available soon in Business Central SaaS… with a minor update for April’19 Release; we are waiting for this feature
But….. Now in Business Central with AL Code, we can create views with required filters applied to a page and use it for future using like a filter template.
Limitations: You can use only tables and tables fields in views, you can use only “List Pages” for Views, you can’t add controls, actions etc.
A new View to show Production Order Lines status (completed and open orders)
- Fields: Quantity, Remaining Quantity, Finished Quantity
Create a new Profile
#1 – Create a Profile: CUSTOMPRODUCTION
//#1 – CREATE A NEW PRIFILE mapped to ROLE CENTER – duplicated from “Production Planner Role Center” in this case
Description = ‘CUSTOMPRODUCTION’;
RoleCenter = “Production Planner Role Center”; //New Profile
Customizations = ViewProdLineViews;
Create a New Page
#2 – Create a new page, an extension to subpage 99000832 (already existing) it wouldn’t work because this page is not a “list page”
//#2 – CREATE A NEW PAGE (BASE FO VIEWS) – Only List Pages are used in Views
page 50205 CustProdOrderLine
Create a New Page Customization
#3 – Create a new pagecustomization object
//#3 – CREATE VIEWS based on “ProdOrderLine” List Pages (two in this sample)
pagecustomization ViewProdLineViews customizes “CustProdOrderLine”
//Customize and put the Views in Customizations List
AL Code is available On GITHUB
PUBLISH AND TEST QUERIES AND VIEWS
Change your role CUSTOM ROLE: CUSTPRODUCTION
Search the page “ProdOrderLine” in the Search Menù
Open the page now you can use Saved Views and Filters
Sure, you can have errors during publishing.
*** You need RUNTIME 3.0 for Customized Views > April’19 Release
TEST PAGE ON QUERY
Search the page “Lot Inventory at Date” in the Search Menù
OPEN THE PAGE
The Page is reading data from Query and after load data into a temporary table.
RESULTS (like in Views)