Display only unique search results

Business Central/NAV Developers Forum
1

I have been looking for a way to search a table in Navision. From previous posts I have discovered that I can loop through a table setting marks on records which match my criteria then show only the marked as below // Filter on FieldA SETCURRENTKEY(FieldA); SETRANGE(FieldA, ValueA); // Mark records IF FIND(’-’) THEN REPEAT MARK(TRUE); UNTIL NEXT = 0; // Remove filter again SETRANGE(FieldA); //set primary key and display marked only Whether this is the best way to do it or not I am unsure. My problem is that I want to display only one field of the results e.g FieldB. Because FieldB is not the primary key of the table there are duplicate values. I only want to display unique values. Is there a navision function for doint this or a way around it?

2

I don’t know special function for this. You can mark records or use a buffer table.

3

If I understand you correctly something like this might work: OnAfterGetRecord() SETRANGE(FieldB, FieldB); FIND('+'); SETRANGE(FieldB);

4

It will perhaps be better if you make use of a temporary table. At least, it will become easier to understand in the future.

5

When people refer to temporary tables do you mean that I need to create an array of type record variable and mark this as temporary? There is no other way of creating a temporary table? I ask because resumably this means I need to know the dimensions (or the max number of results returned) before I start. Is there any performace issues related to declaring lots of variables that are not used in Navision?

6

No, a temporary table is not an array. You declare a recordvariable and set the property of this variable to Temporary (leave Dimensions Undefined). You treat it like any other recordvariable, but obviously changes made to it won’t be stored to the database (but to memory; thus temporarily). BTW pls. disregard my previous posting (I was mistakenly under the impression that you were creating a Report)

7

Hi Debbie, Take a look at standard Report 206 Sales - Invoice. There is a record variable called VATAmountLine which is declared as Temporary. For each Invoice Line, a record is either inserted or modified. The resulting temporary records (usually 1 per each VAT %) are then used to print the VAT Amount Specification on the Report. I hope you can relate this example to your specific issue. Good luck.

8

Hi Thanks for your replies. The reason I thought that you might have to declare an array of record variables is that each time I use insert on a temporary record variable e.g. recvar.INIT; recvar.field1 := value1; recvar.field2 := value2; recvar.INSERT(TRUE); It seems that there is just one record that is being written over all the time. If I then assign a field in the recvar to be the in a table box every value is the last value assigned as if it has been wittn over every time. What am I doing wrong? This makes sense to me because as I understand it the recvar is only one record which holds what is to be inserted so how do I referance the tempory table that has now been created and assign it to be shown in tablebox?

9

Hi Thanks for your replies. The reason I thought that you might have to declare an array of record variables is that each time I use insert on a temporary record variable e.g. recvar.INIT; recvar.field1 := value1; recvar.field2 := value2; recvar.INSERT(TRUE); It seems that there is just one record that is being written over all the time. If I then assign a field in the recvar to be the in a table box every value is the last value assigned as if it has been wittn over every time. What am I doing wrong? This makes sense to me because as I understand it the recvar is only one record which holds what is to be inserted so how do I referance the tempory table that has now been created and assign it to be shown in tablebox?

10

The recvar is only a “view” into the table (be it temporary or not). It will always hold just a single record (the “current record”). If you want to access other records in the table, you need to use FIND and NEXT.