How to avoid overwriting of table data with blank values in importing file?

Business Central/NAV Developers Forum
1

Dear all,

I have a problem. We have updated from version 2.65 to 2009, and when the conversion was done, the table contact did not fill all the address fields / communication fields of the type Person with the values of the dependant company, and I want this values to be copied.

I have done a dataport to export this contacts and then reimport them using validate to copy the information of the company, but before this, I have checked some contacts, and I have found that some of them have special e-mails or telephone numbers, so when I import validating the dependant company…the dataport overwrites the REAL values of these fields. Then, I have exported the data of these fields also, to reenter them…but then as some values are blank, it overwrites the values , leaving the fields blank again.

So my question is…how I can import the data overwriting the old values with the new ones, only if the new ones are not blank?

I have made a search on the forums, but I could not find the solution.

Please, if you could explain it step by step, I will be very happy.

Thanks and best regards,

2

You can play with the autosave, autoupdate and autoreplace options, but its probably easier to manually put code in the dataport.

3

And consider NOT using validation for these fields.

4

As David Singleton says, use code as an option.

It tales a bit longer but gives great results.

For example, the price list update, they want the new prices but if the description is not blank, they do not want to change it.

The dataport ONLY uses variables in the Dataport Fields, the code in the OnAfterImport is (just a snippet here):

In this case also, the dataport only updates indirectly using a record variable.

//Vars used for the csv ItemNoFinal, Desc, UnitPrice

Desc := COPYSTR(Desc,1,50);
//this shows how you can check the length in case the original is too long, the extra characters are inserted into the Extended Text table

recItem.SETFILTER(“No.”,ItemNoFinal);
IF recItem.FIND(’-’) THEN BEGIN
IF recItem.Description = ‘’ THEN BEGIN
recItem.Description := Desc;
recItem.“Search Description” := Desc;
recItem.CALCFIELDS(“Extended Text Exist”);
IF (DescExtra <> ‘’) AND (“Extended Text Exist” = FALSE) THEN BEGIN
ExtTxtHeader.SETRANGE(“Table Name”,ExtTxtHeader.“Table Name”::Item);
ExtTxtHeader.SETRANGE(“No.”,ItemNoFinal);
IF ExtTxtHeader.ISEMPTY THEN BEGIN
ExtTxtHeader.“Table Name” := ExtTxtHeader.“Table Name”::Item;
ExtTxtHeader.“No.” := ItemNoFinal;
ExtTxtHeader.“Language Code” := ‘’;
ExtTxtHeader.“Text No.” := 1;
ExtTxtHeader.INSERT;
END;
ExtTxtLine.SETRANGE(“Table Name”,ExtTxtLine.“Table Name”::Item);
ExtTxtLine.SETRANGE(“No.”,ItemNoFinal);
ExtTxtLine.SETRANGE(“Text No.”,1);
ExtTxtLine.SETFILTER(“Line No.”, ‘%1’, 10000);
IF ExtTxtLine.ISEMPTY THEN BEGIN
ExtTxtLine.“Table Name” := ExtTxtLine.“Table Name”::Item;
ExtTxtLine.“No.” := ItemNoFinal;
ExtTxtLine.“Language Code” := ‘’;
ExtTxtLine.“Text No.” := 1;
ExtTxtLine.“Line No.” := 10000;
ExtTxtLine.Text := DescExtra;
ExtTxtLine.INSERT;
END;
END;
END;
recItem.“Unit Price” := UnitPrice;
recItem.“Last Date Modified” := TODAY;
IF recItem.“Date Created” = 0D THEN
recItem.“Date Created” := WORKDATE;
IF recItem.“Vendor No.” = ‘’ THEN
recItem.“Vendor No.” := VendorCode;
recItem.“Drop Shipment” := TRUE;
recItem.MODIFY;
END ELSE BEGIN

etc

5

Hello Colin,

thanks for your explanation, it was very clear and helped a lot…but I have another problem in the same dataport. I will try to explain clearly:

I have the contact data (is a person), without the address / communication of the related company, and I want that when I enter the information in the CSV file with the Dataport, it updates the information of the person with the address / communication of the related company. I have tried to put the field “Company No.” in the list of the imported fields, and then set the property CallFieldValidate as TRUE, but it does not work.I also put the code

reccontact.VALIDATE(reccontact.“Company No.”);

In the OnAdterImportRecord section.

So, for example, I have actually in the table:

Contact Nr. Name Address Phone Mail

CT-001 Mr. Smith “blank” “blank” smith@yahoo.com

And in the related company, address is for example “Seydon Str.”, phone “+1 324 2364 232” and e-mail “info@yahoo.com”, and I want that when importing data it updates the address and Phone with the information on the related company address, but keep the value of the e-mail. With the previous post you sent, I’m able to put the e-mail value of the imported file…but I could not “reload”-validate the other fields.

Do you have any idea on how I can do that?

thank you very much again for your help.

Best regards,

6

You need to consider using table variables.

In this instance the Company No. is used to GET the company record.

Then you can specify the information in the fields for the contact as you wish.

Likewise you can update the company record.

In the example I gave earlier, I used the concept of a variable record to insert or modify, this looks very similar.

Cheers,

Colin

7

Hello Colin,

thank you very much for the help. I have previously made a two step dataport, the first one with the contact Nº and the related company nº, using CallFieldValidate, and on a second step, I have made a second dataport with the “old personalized information”, using you first reply to insert or not the old value depending if it is blank or has any different value.

I will keep yor second reply for a next problem.

Thank you very much again and best regards