Get Contact with highest No.of Matching Strings?

navvy · 2008-02-17

Hi,

In the table 5085 "Contact Duplicate" I try to get the Contact which has:
1. a specified Contact No
2. the highest No. of Matching Strings

Example: Table Contact Duplicate

I want to get the Contact "44444444" which has the highest No. of Matching Strings (with the value "2" in this example).

First, I have to set the "Contact No." - filter on the ""Contact Duplicate" record:

Duplicates.SETFILTER(Duplicates."Contact  No.", '44444444');

And then, how can I now set an additional filter to get the appropriate record with the highest "No. of Matching Strings" ? #-o

Revolution1210 · 2008-02-17

There are a number of ways you could do this. This is just one:

Add a new key to the Contact Duplicate table:
No. of Matching Strings

Then,

ContactDuplicate.SETRANGE("Contact  No.",'44444444');
ContactDuplicate.SETCURRENTKEY("No. of Matching Strings");

IF ContactDuplicate.FINDLAST THEN
  ..
  ..

Of course, you need to bear in mind that there could be more than one record with the same number of matching strings, so you will need to decide how you want to handle that.

garak · 2008-02-17

you have two options.

first: Make an new key in the table with No. of Matching Strings and than use setcurrentkey("No. of Matching Strings")

second: You create in your function an loop and go throw the records in filter and select there the rec with the highest No. of Matching Strings

But it's possible, that you have more than one record with the same No. of Matching Strings,

Regards

navvy · 2008-02-17

thanks for the fast response

I've tried it with the following code...

Duplicates.SETRANGE("Contact  No.", '44444444');
Duplicates.SETCURRENTKEY(Duplicates."No. of Matching Strings");
IF Duplicates.FINDLAST THEN BEGIN

... and received the following error-message: Message :-k

Waldo · 2008-02-17

Are you sure you created the extra key on table "Contact Duplicate"? I tried the code and works...

Revolution1210 · 2008-02-17

navvy wrote:

... and received the following error-message: Message :-k

You need to add the No. of Matching Strings key to the Contact Duplicate table.

navvy · 2008-02-17

ok, that means I have to add a new field (key) to the table "Contact Duplicate" (open the table over the object designer and change) ?

In this case, I can't use this solution (can't change the table) ....

Revolution1210 · 2008-02-17

navvy wrote:

ok, that means I have to add a new field (key) to the table "Contact Duplicate" (open the table over the object designer and change) ?

In this case, I can't use this solution (can't change the table) ....

You have a developers license, no??

navvy · 2008-02-17

I have a developer license, but that's not the problem: I can't change any tables, because it should not be necessary to make changes to NAV to run the codeunit (which includes the search contact duplicate procedure).

Revolution1210 · 2008-02-18

navvy wrote:

I have a developer license, but that's not the problem: I can't change any tables, because it should not be necessary to make changes to NAV to run the codeunit (which includes the search contact duplicate procedure).

Ok, then you will need to go for a solution using a loop.

Something along these lines (code untested):

ContactDuplicate.SETRANGE("Contact  No.",'44444444'); 
IF ContactDuplicate.FINDFIRST THEN REPEAT
  IF ContactDuplicate."No. of Matching Strings" > MaxNoOfMatchingStrings THEN
    MaxNoOfMatchingStrings := ContactDuplicate."No. of Matching Strings";
UNTIL ContactDuplicate.NEXT = 0;

ContactDuplicate.SETRANGE("No. of Matching Strings",MaxNoOfMatchingStrings); 
IF ContactDuplicate.FINDFIRST THEN BEGIN
  ..
  ..
  ..
END;

The first section determines what the maximum No. of Matching Strings value is.

The second section sets a range on this value (remember there could be more than one record).

Hope this helps

Waldo · 2008-02-18

I would go for something like:

ContactDuplicate.SETRANGE("Contact  No.",'44444444'); 
IF ContactDuplicate.FINDFIRST THEN BEGIN
  rectmpMaxContactDuplicate := ContactDuplicate; 
  REPEAT 
    IF ContactDuplicate."No. of Matching Strings" > rectmpMaxContactDuplicate."No. of Matching Strings" THEN 
    rectmpMaxContactDuplicate := ContactDuplicate; 
  UNTIL ContactDuplicate.NEXT = 0; 
END;

After the loop, you've got your record in the temp variable.

This way, you avoid to send a query with a filtering on a field you don't have an index on... . And it's less traffic.

Just my two cents... too bad you're not allowed to create a key on the field. ](*,)

Revolution1210 · 2008-02-18

Waldo wrote:
I would go for something like:
ContactDuplicate.SETRANGE("Contact  No.",'44444444'); 
IF ContactDuplicate.FINDFIRST THEN BEGIN
  rectmpMaxContactDuplicate := ContactDuplicate; 
  REPEAT 
    IF ContactDuplicate."No. of Matching Strings" > rectmpMaxContactDuplicate."No. of Matching Strings" THEN 
    rectmpMaxContactDuplicate := ContactDuplicate; 
  UNTIL ContactDuplicate.NEXT = 0; 
END;
After the loop, you've got your record in the temp variable.

This way, you avoid to send a query with a filtering on a field you don't have an index on... . And it's less traffic.

Just my two cents... too bad you're not allowed to create a key on the field. ](*,)

Nice

but.... this will only ever return 1 record.

There could be >1 record with the same No. of matching Strings

This is why you need to find out what the maximum number of matching strings is before you do anything else.

@ Navvy - Adding a key to the table is the lowest impact way to go though.

Waldo · 2008-02-18

I though he only neede one :-k (cfr. his FINDLAST statement).

Furthermore, filling a temp table in background is also a way to go. If you filter then on the "No of Matching Strings", it will be done on the client, not on the server... .

Revolution1210 · 2008-02-18

Waldo wrote:

I though he only neede one :-k (cfr. his FINDLAST statement).

Furthermore, filling a temp table in background is also a way to go. If you filter then on the "No of Matching Strings", it will be done on the client, not on the server... .

If he only want the first or last then this method is ok.

The fact remains, there is still the possibility of more than one record with the same No. of Matching Strings. This being the case, without using the additional key on the table, you have to find the maximum value somehow before you can do anything else.

I think a little more info on the exact requirements may be needed

[/i]

Waldo · 2008-02-18

I agree 8)

garak · 2008-02-18

Hehe, 14 relies for this little question

navvy · 2008-02-18

Thanks once again for all the great replies

I need only 1 record, so this solution will be perfect:

ContactDuplicate.SETRANGE("Contact  No.",'44444444');
IF ContactDuplicate.FINDFIRST THEN BEGIN
  rectmpMaxContactDuplicate := ContactDuplicate;
  REPEAT
    IF ContactDuplicate."No. of Matching Strings" > rectmpMaxContactDuplicate."No. of Matching Strings" THEN
    rectmpMaxContactDuplicate := ContactDuplicate;
  UNTIL ContactDuplicate.NEXT = 0;
END;

Revolution1210 wrote:

I think a little more info on the exact requirements may be needed

I'm developing an external add-on for NAV (screenshot.jpg). If there are more than one record (= possible contact duplicates), the user has to go into NAV and find the correct contact.

If the user doesn't go to NAV and search the correct contact manually, or this process is automated, NAV should take the contact with the highest "No. of Matching Strings".

I hope, this information helps you to understand my situation 8)

Waldo · 2008-02-18

It sure does! Thanks.
Can you pust [Solved] in the Subject?

Get Contact with highest No.of Matching Strings?

Answers

Categories