24Feb

Uses of MS Dynamics NAV Query Object

Query is a new object in Microsoft Dynamics NAV 2013 that a programmer can use to define a set of data from the Microsoft Dynamics NAV database. Users can query the database to retrieve field’s information stored in the database from a single table or multiple tables using table relation. You can specify how to join tables in the query where you can define filter criteria to get the desired results. You can specify totalling methods on fields, such as sums and averages.

Query helps us to improve the overall performance of retrieving the data from database. Also one query object can be used in several objects depending on the need. This gives us the great reusability option as well.

In the query you have an option to either use the simple NAV query property to fetch the data based on the table relation defined between the data items in the query set or you can define your SQL methods to fetch the data using left outer join, inner join , right outer join, full outer join and cross Join.

Assuming that we all have a basic understanding of SQL joins and we know how it works let’s have a look at how this help us in NAV to query data from the database.

Creating simple Query:

  1. Create a simple Query Salesperson - Sales Statistics

Go to NAV development environment->Query-> click new query-> define table

1

In the above image you can see we have defined two data items to get the Salesperson sales statistics and using the query method type Total, NAV system use the SQL sum method to calculate the total value for the columns.

 

Now after defining the table structure if we run query object directly from Object designer then it opens a page in NAV windows client.

Image2

 

 

Query object can be useful in graphical representation of data and for that we can use NAV generic chart and can create our own new custom chart to view the statistics in the role centre page.

Creating chart using Query:

In addition to specific charts, such as the Finance Performance window that you cannot create from scratch Microsoft Dynamics NAV 2013 provides the flexible Generic Chart Setup window where you can combine any table or query data with multiple chart properties to create an unlimited number of generic charts for any user.

Below is the simple example of creating a chart using query object.

Go to Generic Chart setup-> click “New” action and enter the chart ID and description-> select source type and source ID (in this case define the newly created query object )-> define x-axis and Y-Axis column.

Image3

 

Preview of the newly created chart  

Image4

Show the Chart on Role centre:

Image5

 

Publish Query as Web service:

6  

 

Browse the published Odata web service in Internet explorer.

URL:  "localhost:9048/DynamicsNAV71/OData/Company('CRONUS%20International%20Ltd.')/Salesperson"

7

 

About ODATA web service:

The Open Data Protocol (OData) is a web protocol that is designed for querying tabular data and provides you with an alternative to SOAP-based web services.

ODATA builds on web technologies such as HTTP, the Atom Publishing Protocol (AtomPub), and JavaScript Object Notation (JSON) to provide access to information from different applications, services, and stores. OData uses URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with resources.

 

ODATA web service:

 

8

The ODATA web service URL will look similar to the following.

"localhost:9048/DynamicsNAV71/OData/Company('CRONUS%20International%20Ltd.')/Salesperson"

 

Related

What's New in Microsoft Dynamics NAV 2017

We were hearing about Dynamics NAV 2017 since long time that it will be releasing by the end of 2016...

Read More >

New and Important things to know for better RDLC report design in NAV 2013 R2.

We are all learning new things about RDLC report design and we know that still there are lot of thin...

Read More >

Design Microsoft Dynamics Navision 2013R2 RDLC Report Part-2

 In this post we will move to next step, and there we will go through two more data item groupi...

Read More >

How to write custom code in NAV 2013 R2 Report layout design?

You may want to add custom code to the RDLC report to do more actions than what's already provid...

Read More >

How to Build Online Help Pages in Bulk using Merge Tool.

Explains how to build online help pages in bulk for 2013 R2 version of NAV using merge tool. It incl...

Read More >

Design Microsoft Dynamics Navision 2013R2 RDLC Report

What is RDLC Report?The Report Definition Language (RDL) Data is a report component that is used by ...

Read More >

How to Build an Online Help Page in Microsoft Dynamics NAV 2013 R2

Online help page is a web page which displays information in a web browser when user presses the key...

Read More >

Share

Comments

Hi Subhash,I agree with the approach of using Query for charts. Wanted to add minor details on this blog. Query is basically data retrieval on quickest and best method possible from Database. Microsoft Dynamics NAV created query object so that large records (with or without combination of any other table - join) may be listed / used with least effect on performance.Best example in NAV (I recall) is scoring method for Bank Reconciliation during Auto match. Overall, this blog is very informative.Best Regards,Manish
9/8/2019 12:07:13 AM | Reply

Post a Comment

  • Recent
  • Popular
  • Tag
Tags
Monthly Archive
Subscribe
Name

Contact Us
  • *
  • *