Application Design Pattern: SELECT DISTINCT with Queries
This pattern explains how to perform SELECT DISTINCT by using queries in Microsoft Dynamics NAV. It is brought to you by Bogdana Botez from the Dynamics NAV team here at Microsoft Development Center Copenhagen (MDCC).
When you work with tables, sometimes you must perform a SELECT DISTINCT (also known as SELECT UNIQUE) from a table. As Dynamics NAV does not provide this out of the box, we present below a way to select unique records by using queries.
Problem Statement
Let’s consider the VAT Entry table:
The goal is to select one line for each separate document that produced VAT Entries. In other words, we want records grouped by Type, Document Type and Document No.. However, if there are multiple lines with the same value of the triad Type, Document Type and Document No. in the VAT Entry table, we only want to see one of them.
Solution
Create a new query object VAT Entry Distinct Document No., with a single DataItem sourced from VAT Entry table. Add the three desired group-by fields Type, Document Type and Document No. as columns.
To enable grouping, add one more column, with Method Type = Totals. This will automatically set the Group By checkbox to TRUE on the three precedent fields.
Note that the Group By field is read-only and trying to set it by hand will clarify that:
Running the query yields a single record per document. You can notice in the second line below for example, how the sales invoice number 103001 had 2 VAT Entries, but it shows up only once in the query:
Example
One thing is to be noted: there is a limitation to how much information you can take out from the records. For example, if we need to extract more information than just the one we already have in the columns, then the following apply: adding one more column of Method Type = None will indeed show more information, but it might affect the grouping. More details below.
