Data import validation

hant · 2006-04-13

Dear all,

I am using dataport to import data into Navsion. Does anyone know is there any way to validate the data during import? For example, I want to import a PO into Navision. I can import it even there is an item in PO not exist in the system. How the system can warn me about the inexistent item?

Your comment is highly appriciated.

Regards,

Hant

DenSter · 2006-04-13

You put a VALIDATE command in the OnAfterImportRecord. So let's say you import into the Purchase Line table, and you want to validate the Item number. You would program "Purchase Line".VALIDATE("No.") in that trigger. The best way to use dataports though is to import into text variables and use the values to set tablevalues by C/AL code.

sgg · 2006-04-13

You May try something like this:

Add the following Codes to the Trigger OnAfterImportRecord of the Dataport.

1. if you want the Importation to halt then try
as earlier suggested By Denster Validate("No.")

2. if you DO NOT want the Importation to halt but for the system to skip importing the records with non-existing Items, then try

Dataport Field :"No."
Variable : ItemRec

if not ItemRec.get("No.") then
CurrDataport.SKIP;

In this case, you will have to write similar codes for other Types on the PO Lines : G/L Account, FA, Charge(Item).

Savatage · 2006-04-13

Remember there is a CallFieldValidate checkbox on the Dataport Fields of the Dataport. But as stated by Denster the code ALWAYS works.

DenSter · 2006-04-13

You know I have not yet been able to figure out exactly how to make that work. I REALLY dislike dataports. The only reason I use them is that they have the file access built in, and it parses the incoming data. Fortunately I have been able to steer away from them pretty well

ara3n · 2006-04-13

Dataports work well for me. I allways use Integer as dataItem and an MyArray[1],MyArray[2] , as datafields. Onafterimport record, I write my code.
The integer property is set to no for autinsert, autupdate, autosave.

This way I reuse the same dataport every time i create a new one, All Ihave to do is change the coding in onafterimport.

DenSter · 2006-04-13

I didn't say I can't make them work, I just really dislike them

I also use variables for everything, I just give them meaningful names.

Personally I don't like variablenames like MyArray[1] to store something like Description. I'd rather create a variable called MyDescription or something like that. It doesn't matter though it will both work.

ara3n · 2006-04-14

Lol Denster. That's the first thing I heard you say something negative about Navision.

hant · 2006-04-14

Thanks for your sharing. I will try this way.

hant · 2006-04-22

Dear all,

I try to put validation command in dataport as your recommendation and it works well except reseting other importing information such as Direct unit cost Excl. VAT. I want to validate the item no. but still keep others. Do you have any idea?

Thanks,

hant

sgg · 2006-04-22

Have you tried the following ?

sgg wrote:

Dataport Field :"No."
Variable : ItemRec

if not ItemRec.get("No.") then
CurrDataport.SKIP;

This is assuming that you only Have Items on the PO Lines. Else you may have to do something like the following :

CASE Type of
Type::Item :
if not ItemRec.get("No.") then
CurrDataport.SKIP;

Type::"Fixed Asset" :
if not FARec.get("No.") then
CurrDataport.SKIP;
...

END;

Where FARec is a Variable of SubType = Rec "Fixed Asset"

hant · 2006-04-24

I would like to inport Item No, Quantity and Unit Price into Purchase Line table. If no validation Item No would be A, Quantity is 1 and Unit Price is 10. If setting validation Item No would be A, Quantity is 1 but Unit Price would be 0.
Is there any way to call the validation for the field Item No before the value of Unit Price being imported so that Unit Price would not be reset to 0.

Shenpen · 2006-04-24

The problem with DenSter's solution is that if you got 1000 records and 50 of them are wrong in some way, then your import will stop 50 times, you remove the bad record, import again... 50 times.

Use this approach instead, this is exactly aimed to solve this problem:

http://mibuso.com/dlinfo.asp?FileID=579

awarn · 2006-04-24

Another idea, I use quite often.

Create a copy of the Purchase Header and line table, can be outside of the license range if only you are going to use it. Remove all code from the table.

Create a new table, called Import errors, with an entry #, document #, and 'Error Text' field.

Make the dataport import to the copy of the table.

Make a codeunit that scans the purchase header table, and checks for invalid customers. The codeunit can also scan the line table and check for invalid items. When you encounter an error add a record to your error table. Then you will end up with all of the errors together, you can copy this to excel, and give to the client to correct the errors and re-send you the file.

It sounds like a lot of work, but if you are doing initial data load of a system and have to for example import item files 20 times for testing in the long run it will save you time. If you don't plan on running the dataport often then this of course would not be practical.

-a

aheresq · 2006-11-14

Hi all, i have to do some like this, with a dataport need to populate the "Gen. Journal Line" table, i can find the account before validate the field "Account No.", but my trouble is, in the Amount field (by example) the validation is not relative just a value, the OnValidate function make others thinks and validate field from others tables, if that other validation field break and if the validation is inside of some function or codeunit than called from the dataport, the break do finish all process, then i need to make the validation in some way that can manipulate the validation break, sent to a log and continue with the others records. I hope to have been explicit and somebody have a solution.
Regards

P.D. sorry my english.

Data import validation

Comments

Categories