DataPort import of Table 81, Gen. Journal Line

kenl · 2005-05-16

Hello,

I create a dataport to import table 81. I have the following fields:

Journal Template name
Journal Batch name
Line No
Source Code
Allow Application
Posting Date
Document Date
Account Type
Account No.
Document Type
Document No.
Description
Bal. Account Type
Bal. Account No.
Currency Code
Currency Factor
Amount

I set the dataport field property "CallFieldValidate". However, many fields are not initialize correctly.

Eg.
Template, it doesn't init fields like "Source Code"/ "Reason code".
Amount, it doesnot initlialize fields: Debit Amount/Credit Amount/ and those LCY amount fields.

Does anyone try to import data for Table 81 before ? Any hint ? It seems like import data to table 81 "Gen. ledger entry" is quite difficult.

Thanks.

kenl · 2005-05-16

Hello,

So far I find that:

I have to add the following code in dataport field "Amount"

OnBeforeEvaluateField(Text)

Evaluate("Gen. Journal Line".Amount, Text);
"Gen. Journal Line".Validate(Amount);

In this way, system seem to successfully init those amount fields. I really don't know why.

Besides the "Amount" field, "Currency Code" is even odd. I set the property "CallFieldValidate" to yes, but it just seem ignore it. My result currency factor is always 0. And when the amount is not zero, system will throw me error, saying that cannot divide by zero.

I set the break point, I see that the "Currecny factor" is zero value, so that when I call validate(Amount), it will throw me an error.

Anyone encounter similar problem before? Any idea?

Thanks

tgupta · 2005-05-16

Hi Kenl,
I wrote something long time back like this
On before importrecord trigger write this piece of code

Gen. Journal Line - OnBeforeImportRecord()

"Gen. Journal Line"."Journal Template Name" := GenTemplateName;
"Gen. Journal Line"."Journal Batch Name" := GenBatchName;
"Gen. Journal Line"."Account Type" := "Gen. Journal Line"."Account Type" :: "G/L Account";

GenJnlLine.RESET;
GenJnlLine.SETRANGE("Journal Template Name","Gen. Journal Line"."Journal Template Name");
GenJnlLine.SETRANGE("Journal Batch Name","Gen. Journal Line"."Journal Batch Name");

IF GenJnlLine.FIND('+') THEN BEGIN
LineNo += GenJnlLine."Line No."
END ELSE BEGIN
LineNo:=0;
END;

And on afterimporttrigger like this

Gen. Journal Line - OnAfterImportRecord()
LineNo+=10000;
"Gen. Journal Line"."Line No.":=LineNo;

VALIDATE("Gen. Journal Line"."Posting Date",ImportPostingDate);
VALIDATE("Gen. Journal Line"."Account No.");
VALIDATE("Gen. Journal Line".Amount);
VALIDATE("Gen. Journal Line".Description, DELCHR(COPYSTR(ImportDescription, 1, 50), '<>', ' '));

Now you can add another fields which u want to validate on the afterimportrecordtrigger.
Hope it will help .

Cheers,
Tarun

kriki · 2005-05-17

This is what I do with a dataport into a journal-table:
I use the correct table, but put the properties "AutoSave","AutoUpdate","AutoReplace" to No. With this Navision does not write the records to the DB automatically, but I have the control with the programming.
The best way to fill these tables is : try to simulate the form in which the user fills the table and do the same things (Tools=>Client Monitor is a good way to find out what is done).

Here is some example for this table:

"OnBeforeImportRecord()"-trigger:
CLEAR("Gen. Journal Line"); // this is necessary to clear all fields in the dataport, because if a field is blank and in the record before it was not blank, it retains the old value.

"OnAfterImportRecord()"-trigger:
CLEAR(recGenJournalLine); // record-variable in globals
recGenJournalLine."Journal Template Name" := "Journal Template Name";
recGenJournalLine."Journal Batch Name" := "Journal Batch Name";
recGenJournalLine.SetUpNewLine(); // this function is launched in the form- of the journal in trigger "OnNewRecord", so you also have to trigger it here.
recGenJournalLine."Line No." := "Line No."; // you can also do a find('+') on the table to find the last line no. and then add 10000.

recGenJournalLine.VALIDATE("Field1","Field1");
recGenJournalLine.VALIDATE("Field2","Field2");
...

validate the fields in the same order as a user would fill them in the form.
afterwards, add the fields that don't appear on the form (if needed).

recGenJournalLine.INSERT(TRUE);

kenl · 2005-05-17

Hello Kriki and tgupta,

Thank you so much for your suggestion. I totally agree with you way: simulate the user action.

However, for the function GenJournalLine.SetUpNewLine(), it needs to take some arguments. What argument do I suppose to pass? :-k

I am using Navision 4.0.

Best regards,
Ken

kriki · 2005-05-17

LastGenJnlLine = the record you are filling up (it just copies some fields to initialise the new record, but you will overwrite the fields anyway)
Balance = 0
BottomLine = TRUE

kenl · 2005-05-17

Hello kriki,

I add the code at OnAfterImportRecord():

GenJournalLine."Journal Template Name" := "Journal Template Name";
GenJournalLine."Journal Batch Name" := "Journal Batch Name";
GenJournalLine."Line No." := "Line No.";
GenJournalLine.SetupNewLine(GenJournalLine, 0, true);
...

But system throws error at CodeUnit 396, function "TryGetNextNo". The error message say that I have to commit before calling this function.

Do I have to write commit before calling SetupNewLine() ?

Thanks,
Ken

kriki · 2005-05-17

Forgot that problem.

In the case you are already in a transaction, you must substitute the line
"GenJournalLine.SetupNewLine(GenJournalLine, 0, true); "

with the code from the procedure but only what you need, I think it will be something like this:

GenJnlTemplate.GET("Journal Template Name");
GenJnlBatch.GET("Journal Template Name","Journal Batch Name");
GenJournalLine."Posting Date" := WORKDATE;
GenJournalLine."Document Date" := WORKDATE;
GenJournalLine."Source Code" := GenJnlTemplate."Source Code";
GenJournalLine."Reason Code" := GenJnlBatch."Reason Code";
GenJournalLine."Posting No. Series" := GenJnlBatch."Posting No. Series";
GenJournalLine."Bal. Account Type" := GenJnlBatch."Bal. Account Type";
GenJournalLine.VALIDATE("Bal. Account No.",GenJnlBatch."Bal. Account No.");

Check out you local version, because it is possible that you need to copy also some other code. (eg. in the Italian DB, I would have to add this:GenJournalLine."Operation Occured Date" := WORKDATE;)

Shenpen · 2005-05-17

Please never import to any Navision table directly, because if you do that, you cannot separate a) user data errors b) technical data errors (charset etc.) c) your errors.

Do the following. Create a table with30 text 50 fields named f1...f30 and one integer field to use as primary key. Create a dataport for it. In OnBeforeImport, increment the primary key by one. (For example you can do it with an integer variable). Dont forget to do the infamous INIT

For every table to import, "save as" this table and dataport, changing the dataport to import to the new table of course.

You should be able to import anything into these new table by your new dataports. If it is truly so, drink a beer, because the harder part is now over: 10 000 ugly black devils are trying to stop you from successfully importing something into Navision like Windows charsets, field separators in data etc., so when it's "in", rejoice

Then, make a codeunit maybe called KrikiTools

and first make code that checks the data. For example you should check all account numbers are actually existing, customers and vendors are existing etc. Be paranoid - there is not limit to human foolishness, always be prepared to find stuff like string "maybe ten bucks" in a decimal Debit Amount field

Try evaluating all decimal and date fields too. This code should maybe set field 30, which is usually empty with an error message. Then, you just fitler the table for this field <>'' and can send these records back to the customer with a joyful "f*** off"

Then, write code to validate data to Gen. Journal Line exactly_the_same_order you would do it by hand on the form. Use evaluate with decimal or date fields. If you import documents like sales order, always insert the record first, then set fields and modify(true) it.

Long ago, when imported directly to Navision tables many, many frustration hit me. Now, with this strategy I was able to do unshipped sales orders, unshipped purchase orders, customer open entries and vendor open entries in one day! Really an amazing speed-up.

kriki · 2005-05-17

Shenpen is right. I also use that technique if I am not sure the data is polished.
The negative of that technique is that it takes some more programming, but for dirty data it is a timesaver.

BTW : as a seperator, always use a TAB (in Excel : save as TAB-seperated ; In Navision : Property Seperator : <TAB> [including < and > and TAB in uppercase]). At least this seperator you will never find in the data to be imported.

Shenpen · 2005-05-17

Really?

Once upon a time, I even found newline (carriage return / line feed, basically: Enter key) characters in data to import

It was a from-Navision to-Navision CRM migration, where users copy-pasted e-mails into Contact comments...

It had newlines, tabs, everything.

This was the time when I had to invent the Sanskrit Separated Values file format

Field separator was PRATITYA-SAMUTPADA, record separator was MAHAMADYAMIKA

kriki · 2005-05-18

Let's hope Navision is never used to store Sanskrit

kenl · 2005-05-18

Hello kriki and shenpen,

Thank you so much for your sharing. Now I am able to import data into journal line.

I am just thinking, the propery "CallFieldValidate" is not working correctly. Otherwise we don't need to write program to import data (Manually call validate and manually insert) :roll:

Hope that MBS will fix this problem soon.

Best regard,
Ken

kriki · 2005-05-18

To be honest : I never used the propery "CallFieldValidate" because these are triggered in the order the fields are defined in the dataport (At least I think) and in general I have another order.

DataPort import of Table 81, Gen. Journal Line

Comments

Categories