wiki.dynamicsbook: Show distinct records in NAV

[Blog bot]

Summary: /* Distinct indicator field */

For people coming from the SQL world into Navision, a common request is how to emulate the SQL "DISTINCT" command on a Form or Report.

SELECT DISTINCT column_name(s)
FROM table_name

The key issue here is that this construct can not be simply replicated in Navision. Normally in Navision we have a [[Header record]] and a [[Line record]] or a [[Main table]] and an [[Entry table]] that eliminates the need for DISTINCT.

But what about the cases where we do need this, and why would we need it.

Without doubt the number one reason that "developers" need this functionality it because they have already dug them selves into a deep hole with a badly designed database structure. Often in an attempt to save on purchasing additional tables.

But lets assume that you have explored all possibilities, and now you need to show distinct records. Lets look at some methods:


== Highlighting ==
Highlighting is where we actually display all the records, but we in some way high light one (generally the first) distinct record.

The most obvious place we see this is in the Apply Charge Items function.

== Mark ==

[[Mark in NAV|Marks]] are probably the simplest of the distinct functions to implement, but they can have significant performance issues. Especially if we are not sorting by the [[Primary key]].

== Temporary table ==

In following [[The Navision Way]] using a [[Temporary table]] is possibly the most conventional method of processing a Distinct operation.

== On the fly "filtering" ==

This is where you have code in the form that skips records that are not distinct. This method is not generally recommended if other solutions can be found.

== Distinct indicator field ==

In this case, a new field is added to the source table, that marks the record as unique. Generally the field will be either a Boolean or a Code field of the same type as the field we are attempting the Distinct operation on. There are advantages and disadvantages of both.

The update of the field can be done either through validation at the table level, by a batch update routine or on the fly as needed. In most cases, if the plan is to update on the fly, then this is not the correct method to use.

== External links ==

* [http://www.w3schools.com/sql/sql_distinct.asp Example of using DISTINCT in MS SQL]

==See also==
* [[Make a field Unique in NAV]]
* [[C/SIDE]]
* [[NAV create a new table]]

[[Category:TipsTricks]]
[[Categoryevelopment]]
[[Category:User]]
[[Category:HowTo]]

Источник http://wiki.dynamicsbook.com/index.p...records_in_NAV