Arguments in csv file for the dataport

Ilona · 2007-01-14

Still a newbie

I have to import customers through a dataport, which is of course pretty simple.

1 thing, which I also couldn't found on the forum, is this:

I have a csv file with the customers in there, and a field contain either the bankaccountnumber or the word Acceptgiro.
As soon as the number is in there the Transaction Mode Code should be INCASSO and the number should be placed in Bank Account Mode.
As soon as the word Acceptgiro is in there, the Transaction Mode Code should be ACCEPTGIRO and the Payment Terms Code should be 14DAGEN.

I don't get how I 'tell' the dataport that there is information in the csv that shouldn't go in a field, but is only needed for arguments as above.

I hope I explained it clearly. I assum it is possible......

ara3n · 2007-01-14

Basically you need to create global variable and set them in dataport field instead actual field. Then onafterimport add if globalvariable = somthing then do this.

Ilona · 2007-01-14

Ok, but what type of variable should it be?
Is there no need to 'reserve' some space for the field between the other dataport fields?

Savatage · 2007-01-14

No space needs to be reserved

here's an example to help explain

lets say I'm importing from a file
Cust No
Cust Name
Cust Address
Cust City
Cust St
Cyst Zip

Now all that will get filled in BUT on the onafterimport trigger I can do other things like

"Payment Terms Code" := 'NET30';
"Salesperson code" := 'ABC';
"Posting Group" := Customer;
etc etc

and since your setting these in code - you can also get creative

create a variable
TransModeCode type code
your csv file will import the data into this field by adding it to your dataport field list instead of "Transaction Mode Code" Directly.
then onafterimport
if TransModeCode = 'Acceptgiro'
Then Begin
"Transaction Mode Code" := 'ACCEPTGIRO';
"Payment Terms Code" := '14DAGEN';
End
Else Begin
"Transaction Mode Code" := 'INCASSO';
"Bank Account Mode" := TransModeCode;
End;

gook luck.

Savatage · 2007-01-14

Ilona wrote:

Ok, but what type of variable should it be?

Note it should match the field the variable is going to

the Variable should be type code for "Customer No"
since it's a code type
Ithe Variable should be type text for "Customer Name"
since it's a type text field.

all you have to do is view the fields in the table to see the fields and what there types are.

Ilona · 2007-01-14

Savatage, I still don't get it

but I did what you told me.

Now I'm getting this message while compiling:

The expression is Code. There must be a 'TRUE/FALSE' expression in the IF, WHILE or REPEAT sentence. For example:

bla
bla
etc.

Ilona · 2007-01-14

ok, tried over and over again en then suddenly it worked

this is the code I used , I guess TransCode := 'Acceptgiro' should be TransCode = 'Acceptgiro', but tried it before :shock:

IF TransCode = 'Acceptgiro'
   THEN BEGIN recCustomer."Transaction Mode Code" := 'ACCEPTGIRO' ;
   "Payment Terms Code" := '14DAGEN';
END ELSE BEGIN
   recCustomer."Transaction Mode Code" := 'RABO';
   recCustomer."Bank Account Code" := TransCode;
END;

I will now import the text file [-o<

Savatage · 2007-01-14

dont' forget to validate your customer No
VALIDATE("No.");

Ilona · 2007-01-14

Seems to work besides of a few things (as usual.. ](*,) )

for example this isn't working

recCustomer."Allow Line Disc." := AllowLineDisc;
AllowLineDisc := FALSE;

I created a variable AllowLineDisc for Customer."Allow Line Disc."

What do I forget?

Savatage · 2007-01-14

Ilona wrote:

Seems to work besides of a few things

This is a good thing

Ilona wrote:

for example this isn't working
recCustomer."Allow Line Disc." := AllowLineDisc;
AllowLineDisc := FALSE;
I created a variable AllowLineDisc for Customer."Allow Line Disc."
What do I forget?

Are you trying to set them all to false? if so you have to switch them.

AllowLineDisc := FALSE;
recCustomer."Allow Line Disc." := AllowLineDisc;
or just do
recCustomer."Allow Line Disc." := FALSE;

or is your csv file determining true of false?
recCustomer."Allow Line Disc." := AllowLineDisc;

your variable AllowLineDisc is type boolean correct?

baby steps 8) - you gotta walk before you can run

Ilona · 2007-01-14

Are you trying to set them all to false? if so you have to switch them.

Yes, so I switched them now, what also didn't work out.

AllowLineDisc := FALSE;
recCustomer."Allow Line Disc." := AllowLineDisc;

Ok, didn't work

or is your csv file determining true of false?
recCustomer."Allow Line Disc." := AllowLineDisc;

No, it's not in the csv.

your variable AllowLineDisc is type boolean correct?

yes, Navision is very clear in those error messages so the Option fields en the Boolean fields have the correct variables

This is my code now:

OnBeforeImportRecord

INIT;

OnAfterImportRecord

CopySellAddress := 1;      //for example should be Person, but is Company
recCustomer."Copy Sell-to Addr. to Qte From" := CopySellAddress;
PrintStatements := FALSE;
recCustomer."Print Statements" := PrintStatements;
PriceInclVAT := TRUE;
recCustomer."Prices Including VAT" := PriceInclVAT;
CombineShipments := FALSE;
recCustomer."Combine Shipments" := CombineShipments;
GenBusPostGr := 'KINDOPVNG';
recCustomer."Gen. Bus. Posting Group" := GenBusPostGr;
RemTermCode := 'KINDEROPV';
recCustomer."Reminder Terms Code" := RemTermCode;
Comment := FALSE;
recCustomer.Comment := Comment;
TaxLiable := FALSE;
recCustomer."Tax Liable" := TaxLiable;
VATBusPostGr := 'BINNENLAND';
recCustomer."VAT Bus. Posting Group" := VATBusPostGr;
Reserve := 1;
recCustomer.Reserve := Reserve;
ShipAdvice := 0;
recCustomer."Shipping Advice" := ShipAdvice;
QueuePrio := 0;
recCustomer."Queue Priority" := QueuePrio;
AllowLineDisc := FALSE;
recCustomer."Allow Line Disc." := AllowLineDisc;
recCustomer."No." := CustNo;
recCustomer.Name := CustName;
recCustomer."Search Name" := CustSearchName;
recCustomer.Address := CustAddress;
recCustomer.City := CustCity;
recCustomer."Payment Terms Code" := PayTermsCode;
CustPostGr := 'DEBITEUREN'; //remains empty
recCustPostGroup.Code := CustPostGr;
recCustomer."Invoice Disc. Code" := InvDiscCode;
recCustomer."Transaction Mode Code" := TransActModeCode;
recCustomer."Bank Account Code" := BankAccCode;
recCustomer."Last Date Modified" := LastDateMod;
recCustomer."E-Mail" := Email;


//this isn't doing anyting at all
IF recCustomer."Address 2" = 'Acceptgiro' //used address2 for the collomn in the csv file, else it would'nt work
   THEN BEGIN recCustomer."Transaction Mode Code" := 'ACCEPTGIRO' ;
    "Payment Terms Code" := '14DAGEN';
    recCustomer."Application Method" := 1;
  END ELSE BEGIN
    recCustomer."Transaction Mode Code" := 'RABO';
    recCustomer."Bank Account Code" := recCustomer."Address 2";
    recCustomer."Application Method" := 0;

END;
INSERT(TRUE)

It seems like all the fields that are not in the csv, are displayed in the default value, so nothing happens.

There must be something I'm missing.....

Savatage · 2007-01-14

just curious what's the recCustomer all about?

do you have recCustomer-Record-Customer in globals?
the first thing that's imported should be the cust no validated. if you validate later on it will reset fields.

Ilona · 2007-01-14

just curious what's the recCustomer all about?
do you have recCustomer-Record-Customer in globals?

Yes, I made a recCustomerRecord and made a variable for all the fields that have to be filled out.

the first thing that's imported should be the cust no validated. if you validate later on it will reset fields.

Now I placed it on the first line of OnAfterImportRecord, is that correct?

Savatage · 2007-01-14

yes check the # first & then change the fields as you did.

//this isn't doing anyting at all
IF recCustomer."Address 2" = 'Acceptgiro' //used address2 for the collomn in the csv file, else it would'nt work

not sure what you did here.

for example if your csv file is layed out like

no,name,address,"address 2",city etc
and you want to make them variables you have to type in the dataport fields the variable names in the same order as if you were importing them directly into those fields. & remove the original field names
recCustomerNo
recCustomerName
recCustomerAddress
recCustomerAddress2
recCustomerCity
etc.

then later on you can bring back the info into the correct fields
on the onafterimport trigger.
"No." := recCustomerNo;
Name" := recCustomerName;
etc, etc

Are you feeling like you are getting the hang of it yet? It seems like you are.

Savatage · 2007-01-14

CustPostGr := 'DEBITEUREN'; //remains empty
recCustPostGroup.Code := CustPostGr

Are you also inserting info into another table other than customer?

see first you tell Navision
then CustPostGr := 'DEBITEUREN';
Then you appliey to the correct field
"Cust. posting group" := CustPostGr;

note:
I'm not sitting infront of any nav computer so
"Cust. posting group" might not be exactly the field name in the cust table.
you'll have to correctly enter the correct field names - I'm just trying to guide you.

Good Luck!

David_Cox · 2007-01-14

Remember on before import record to clear any variables and INIT;

It May be as you are using "Name 2" to hold the AceptGiro value, it is case sensitive try

IF UPPERCASE("Address 2") = 'ACCEPTGIRO'  THEN BEGIN 
   "Transaction Mode Code" := 'ACCEPTGIRO' ; 
    "Payment Terms Code" := '14DAGEN'; 
    recCustomer."Application Method" := 1; 
  END ELSE BEGIN 
    "Transaction Mode Code" := 'RABO'; 
    "Bank Account Code" := recCustomer."Address 2"; 
    "Application Method" := 0; 
END; 
//Clear the Address 2 field of the tempory value
"Address 2" := '';
INSERT(TRUE)

Ilona · 2007-01-14

//this isn't doing anyting at all
IF recCustomer."Address 2" = 'Acceptgiro' //used address2 for the collomn in the csv file, else it would'nt work

What I ment earlier is the collumn where the banknumbers or the word Acceptgiro is in. If I don't mention it in Datafields, the banknumber from the second record is seen as the customer no.
Thats why I asked where tot tell Navision that there is a collumn in the csv that isn't an existing field, but has the translated/formed to more than 1 field.

I guess I still don't get it

, it's after midnight here already. :?

Savatage · 2007-01-14

see davids post above.

ok take a step back the scond line should not consider a different field the cust #

so make sure the # of fields in your csv file is the same amt of fields you have listed in your dataport fields.

if your csv has 10 fields and your dataport has 9 listed - then everything gets moved over by 1 throwing it all off.

as i understand it is you have 1 column in your csv that contains info for 2 things either a name or a number (right?) then import that field into 1 variable as we did and then you can move it where needed using the if..then..else.

--After midnight???? sometimes it pays to take a break & come back with fresh eyes. I don't think you're too far off now.

Re-read the post from the beginning you will see you learned lots of useful info.

Ilona · 2007-01-14

Last try for now

I tried your code David, it didn't make any sense.
To clear the variables, is it correct to put it in the BeforeImportRecord section?
And is a CLEAR(recCustumer); correct?

Savatage · 2007-01-14

I've gotten confused. Is anything working?

You can always try
http://www.mibuso.com/dlinfo.asp?FileID=360

Ilona · 2007-01-14

Seing things positive Savatage \:D/ the thing that is working are the imported fields from the csv file.

Couldn't it be a tiny little stupid checkmark I forgot?
And yes, this time I tried all the options with autoupdate, autosave etc.

I downloaded the tool and imported the fob, but where is it now :shock:

Ilona · 2007-01-16

I imported the customers with a little help of Crystal Reports, so now I didn´t need any code.
But even tough I´m curious why it´s not working. ](*,)

Arguments in csv file for the dataport

Comments

Categories