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.

SCHEMA

QUERIES

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

Source https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-object

Example:

A new Query to Show “Warehouse Entries” aggregated for Item\Lot\Location\Bin\Date, very useful for warehouse employ

AL Sample

Query

Page

AL Code is available On GITHUB

https://github.com/rstefanetti/AL-Samples/tree/AL-Queries

VIEWS

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 PagesPage 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.

Source https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-views

Example:

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

profile CUSTOMPRODUCTION

{

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

AL Code is available On GITHUB

https://github.com/rstefanetti/AL-Samples/tree/AL-Views

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

ERRORS!

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