Importing Customers from Excel and check if exist

emulsified · 2007-09-20

What is the best way to import a list of customers with their information into Navision (4.0 SP3)?

I need to check if they already exist first by their Business Name OR Phone so that no existing customers are overwritten.

RIM is out of the question - I've had problems and I don't trust it.

So a DATAPORT is what I would want to do but how do I check to see if they exist first by their Business Name OR Phone?

Thanks.

jlandeen · 2007-09-20

You should be able to write a simple dataport that reads in the values and then manually control how you handle the insert of the record. If you set all of the Auto* properties to false then you should be able to manually handle any inserts in the OnAfterImport record trigger.

The code shouldn't be too hard just search for any existing record and update as required or insert a new one.

Savatage · 2007-09-20

First I would import all data in to variable so I can fully control them.

vCustNo
vCustName
vCustPhoneNo
Etc etc

If you were going to compare phone numbers, unless you are totally sure that all phone numbers are entered the same i would clean them up before comparing
(meaning) is it like xxx-xxx-xxxx or are some (xxx)xxx-xxxx, etc

you are also going to need a get or find
ex/

vCustPhoneOrig := vCustPhoneNo.;
Customer.GET(vCustNo);
txtCharsToKeep := '0123456789'; 

//clean up imported phone#
vCustPhoneNo. := DELCHR(vCustPhoneNo,'=',DELCHR(vCustPhoneNo,'=',txtCharsToKeep));

//clean up customer phone#
Customer."Phone No." := DELCHR(Customer."Phone No.",'=',DELCHR(Customer."Phone No.",'=',txtCharsToKeep));
Something like
If Customer."Phone No." = vCustPhoneNo then
  CurrDataport.SKIP
Else
  //Map your imported fields to the Nav fields
  Customer."No" := vCustNo;
  Customer.name := VCustName;
  Customer."Phone No." := vCustPhoneOrig;
  etc 
  etc

There is an autoreplace option you can set to "No" but I am assuming you are auto gererating new Customer Numbers so there is no way you know if they will match anything?????

jlandeen · 2007-09-20

txtCharsToKeep := '0123456789';
//clean up imported phone#
vCustPhoneNo. := DELCHR(vCustPhoneNo,'=',DELCHR(vCustPhoneNo,'=',txtCharsToKeep));

Wow that technique to clean up text strings with the double Delchr function calls in it is brilliant. Simple & functional...and I don't think i've ever seen that programming pattern before.

Very useful!

Savatage · 2007-09-20

all credit for that code goes to Kriki
as seen here http://www.mibuso.com/forum/viewtopic.php?t=14879

You'd be surprized how many code gems are out there in these posts. That's why I try to read them all, even if they don't apply to a need of mine. You never know what tip you will find.

cnicola · 2007-09-20

That double DELCHR trick deserves a prize by itself. =D> =D>

Waldo · 2007-09-20

It's an old trick which has been in the VAT-codeunits for years

:

CheckNo("No." : Text[20]) : Boolean
Vatno := DELCHR("No.",'=',DELCHR("No.",'=','0123456789'));
IF STRLEN(Vatno) <> 9 THEN
  EXIT(FALSE);
EVALUATE(WorkVatNo,COPYSTR(Vatno,1,7));
EVALUATE(Ctrl,COPYSTR(Vatno,8,2));
WorkVatNo := 97 - (WorkVatNo MOD 97);
EXIT(WorkVatNo = Ctrl);

cnicola · 2007-09-20

Thanks Waldo for that reminder.
I almost forgot all those evenings I would relax with a glass of wine and a thorough read of the VAT codeunits.

Waldo · 2007-09-20

I got your point

Did you try codeunit 80? It's got a nice plot

emulsified · 2007-09-21

That's exactly what I need to do.
#-o Thank you.

Importing Customers from Excel and check if exist

Comments

Categories