how to get unique records from a table

Business Central/NAV Developers Forum
1

Hi All,

I would like to know how do we extract unique records from a table. For eg. in Sales Inv Header table there are many rows for each customer number. I would like to get rows with unique customer number. Thanks in advance

2

What about:

SalesInvHeader: Rec112;
YourUniqueCustomerNo: Code20

SalesInvHeader.setcurrentkey(Sell-to Customer No.,No.);
SalesInvHeader.setrange(Sell-to Customer No., YourUniqueCustomerNo);
if SalesInvHeader.find(’-’) then repeat
// possible records SalesInvHeader have your information
until SalesInvHeader.next = 0;

3

First: HAPPY NEW YEAR to all of you! [<:o)]

Hi NaviKiller!

Well, I’m not sure about what you are looking for …

The uniqueness of a record is definded by the Primary Key, so in case the of the “Sales Invoice Header” table it is “No.”.

I guess you want to filter the records, to get only those of a specific Customer displayed (or something). In this case you could filter on the “Sell-to Customer No.” or the “Bill-to Customer No.” by the relevant “No.” of the Customer. This could be done by using SETRANGE or SETFILTER, or setting the properties TableView, RunFormLink etc., depending on your demands.

I hope this helps a little …

Best regards,

4

The way I do this is pretty easy.

I create a new flow field in the Sales Header Table (T36) the flow field is a COUNT of record 36. FIltered by Sell-to customer number.

Then just display this on the list form, and filter on this field of 1 or >1 depending on what you want. This is quick simple and easy. And you can do it witht he basic designers, no code needed.

5

Yet another idea. If you are wishing to extract unique customer no from the sales Invoice headers, just create a temporary Customer record variable and run through Sales Invoice Headers inserting the customer no. into the temporary customer record. At the end you will have a temporary list of (perhaps) what you were looking for. You can display this temp record in a list or something.