Coding for protection against duplicate customer entries.

Business Central/NAV Developers Forum
1

Hi Guys,

I need a little help with this one.

Our DB is full of duplicate customer entries and I’d like to raise an alert if it appears that a duplicate is about to be added.

I think the best method, for us, would be to find the first instance, of a Customer record, with matching “Address”, “Address 2”, & “Phone No.” fields. However, I can’t seem to get my code in the right trigger and it’s alerting me at the wrong time(s). I would like for an alert to be raised after these 3 fields have been entered on the customer card. (no specific order). Would it best to use a trigger on the Customer form or in the Customer table? Which trigger exactly would be best, considering I’m using Navision 2.0?

Thanks in advance,

Mark

2

I would make a function in the table Customer and call that function from each fields OnValidate trigger in the table. That way you only have to code your functionality once and it works from every form you use to insert or change customers.

3

Create 2 Variables type text with the number of dimensions you require

Create a function then fill the new customer Array

CLEAR(NewCustAddr)

NewCustAddr[1] Address;
NewCustAddr[2] := “Address 2”;
NewCustAddr[3] := “Post Code”

CustExists := FALSE;
Customer.RESET;
Customer.FIND(’-’);
REPEAT
CLEAR(OldCustAddr);
OldCustAddr[1] := Customer.Address;
OldCustAddr[2] := Customer.“Address 2”;
OldCustAddr[3] := Customer.“Post Code”

CustExists := (NewCustAddr[1] = OldCustAddr[1]) AND
(NewCustAddr[1] = OldCustAddr[1]) AND
(NewCustAddr[1] = OldCustAddr[1]);
UNTIL (Customer.NEXT = 0) OR CustExists;

Then IF CustExists ; or if in a function EXIT(CustExists);

4

Create a function in the Customer Table - CheckUnique and call this function from the OnInsert, OnModify and OnRename table triggers.

This is consistent with the standard product and is available from any other object.

[:)]

5

I think it might be better to create a wizard that would require the input of the values that you want to check for duplicates, then give the user the ability to select to continue and then move the info to the new customer card after the check and allow the user to continue to enter the other info on the customer card. This would stop the insertion of the customer (the customer record is inserted when the customer no. is entered) then having to delete the record if the customer is a duplicate.

I am not sure where there is any standard product checking of a unique master records.

The idea is to enter and hold the “checking fields” till you determine if it is a duplicate record.

6

Many countries (including mine - Latvia, and as I know, all EU countries) have an unique ID for every company - VAT registration No, used by tax authorities. You can use this field duplicate check simply adding UNIQUE index on it - of course if you have such a parameter in your country :slight_smile:

7

Now…

You could also look at the Check Duplicates fuctionality in the Sales & Marketing module. This will read throught the selected list of contacts and build a list of so-called duplicates. It is some time since I looked at this area but I remember there are parameters for setting the fields and the number of characters to compare.
With some small adptation this could be useful for finding already duplicated entries.
OR
you could use the VAT Registration Number as a unique key.

8

I had just started posting the same thing sd jdsrk when I got a SQL server error on dynamicsusers.

The dupilcate function in Relationship Management works fine, and even if you have to purchase the granule, it will be cheaper than developing your on solution.