Newbie dataport problem

Ilona

I'm trying to import files into the Job Journal Line.
The problem I have is counting up the Documentnumber automaticly, adding 1 each time is not working because the Documentnumber doesn't exist only of numbers. Also setting the global variable isn't working for the document number, type conversion isn't correct.

I'm a newbie, sorry

Marije_Brummel

You can increment a code field with INCSTR if the code contains at lease one integer character.

Ilona

Thank you very much, at least I do not receive any error messages now.
And knowing the term incstr, makes searching on the forum much easier ;-)
The data port is running, but nothing is imported. Does it make any sense to use the debugger in this case?

Mbad

Do you make and insert anywhere?

Ilona

:oops: uhm, no

I'll give it a try again ;-)

Arjan_Somers

To use incemental strings you could also use the "No. Series" functionality of Navision

Just search the forums for this, or look at the tables in you navision db.

And if you do not have any INSERT calls, it's clear why there is no imported data, but i also had some weird cases where no data was imported, and no errors where generated. In those cases, a COMMIT call solved the problem.

Never really found out why it worked, or why the data dissapeared otherwise, but it did work.

[edit]
I found out why it didn\t import in some cases:

When you run a dataport using ctrl-r, it doesn's really insert your data, you have to run the dataport using the object designer.
[/edit]

Ilona

Still not able to increase, maybe I should give some more info.
We've developed an work registration application in Notes. From Notes there will be an txt or csv file with the following fields wich I now have in "dataport Fields":
Posting Date (for each worked day)
Job No ( Project their working on, can be more than one per person per day)
Type (Always Resource)
No ( Resource Number)
Quantity (worked hours)

This should be imported to table 210 Job Journal Line.
In this table contains also a field Journal Template Name wich should always be "Project" and a field Journal Batch Name that always should be "Chantalle".
Further, the Document No. should be increased every time the dataport Runs and the Line No. should be increased every line per Document No. .

This is what I tried this time, but of course the dataport complains about using the same number twice.

IF "Job Journal Line".FIND THEN BEGIN
"Job Journal Line"."Document No." := INCSTR ("Job Journal Line"."Document No.");
"Job Journal Line".INSERT
END

Arjan_Somers

Try taking a look at the threads on Number series.

(Er waren er laatst ook wat in het NL-forum)

Number series make it easy to get numbers which are incremented automaticly. It's quite a hassle to set up, but it's works quite nice when you get the hang of it.

Saint-Sage

First of all, the recommended process is to import the raw data into a temp table, then do whatever formatting you need and then move it to a journal to review before posting. This prevents you from sticking non-validated data into a table directly. Another perk to this method is that you now have a record of the actual data which came from the data file. A month or so from now if you found a discrepency in the job table you could go back to your temp table and see if it came directly from the file and not some error in your code. You also get the first part working, verify that your data is in the temp table, then get the codeunit or report that processes and moves the data. Thus it is more incremental and easier to debug.

BTW : The INCSTR option just increments the first number it finds in the string. So if you have 73ASF55 as your number, it would increment the first digit giving you 83ASF55 instead of 73ASF56.

A better approach would be to lastChar :=COPYSTR((MAXSTRLEN(DocumentNo) -1),1);

-- This will give you the last character of the string. Then INCSTR(lastChar) that number and then do a :

newDocNo := COPYSTR(1,(MAXSTRLEN(DocNo) -1) + lastChar) to get a new document number consisting of all but the last characters of the original document no, and the last character of the original string incremented.

:-k

David_Cox

Use 4 Variables and check the batch is valid and if there are any lines are in the batch already

JobJnlBatch (Record = Job Journal Batch)
JobJnlLine (Record = Job Journal Line)
DocNo (Code = 20)
LineNo (Integer)

Then on the trigger
OnPreDataPort()
CLEAR(LineNo);
CLEAR(DocNo);
// Errors if not found
JobJnlBatch.GET('PROJECT','CHANTALLE');
// Find the last line and get values
JobJnlLine.RESET;
JobJnlLine.SETRANGE("Jnl. Template Name",JobJnlBatch."Template Name");
JobJnlLine.SETRANGE("Jnl. Batch Name",JobJnlBatch.Name);
IF JobJnlLine.FINDLAST THEN BEGIN
LineNo := JobJnlLine."Line No.";
DocNo := JobJnlLine."Document No.";
END;

//No DocNo then Create one EG: 20061227_000
//Nice sequencial way of formatting document numbers
IF DocNo = '' THEN
DocNo := FORMAT(TODAY,0,'<Year4><Month,2><Day,2>')+'_000';

Then Triggers
OnBeforeImportRecord()
INIT; //Clears any values from last line

OnAfterImportRecord()
LineNo := LineNo + 10000;
DocNo := INCSTR(DocNo);
"Jnl. Template Name" := JobJnlBatch."Template Name"
"Jnl. Batch Name" := JobJnlBatch.Name;
"Line No." := LineNo;
"Document No." := DocNo;
// The rest you know!

Ilona

Thank you very much!
#-o I guess the FINDLAST isn't working because we don't have the sp1 version. Is there an alternative? Something like If Then Else?

garak

If you doesn't have the sp1 than use find('+') <- is the last record

also:

JobJnlLine.RESET;
JobJnlLine.SETRANGE("Jnl. Template Name",JobJnlBatch."Template Name");
JobJnlLine.SETRANGE("Jnl. Batch Name",JobJnlBatch.Name);
IF JobJnlLine.FIND('+') THEN BEGIN
  LineNo := JobJnlLine."Line No.";
  DocNo := JobJnlLine."Document No.";
END; 

regards

Ilona

=D> :whistle:

Hurray, This is (almost) working! Thank you David and the others!


OnPreDataport

CLEAR(LineNo);
CLEAR(DocNo);
//Errors if not found
JobJnlBatch.GET('PROJECT','CHANTALLE');
//Find the Last line and get values
JobJnlLine.RESET;
JobJnlLine.SETRANGE("Journal Template Name",JobJnlBatch."Journal Template Name");
JobJnlLine.SETRANGE("Journal Batch Name",JobJnlBatch.Name);
IF JobJnlLine.FIND ('-') THEN BEGIN
LineNo := JobJnlLine."Line No.";
DocNo := JobJnlLine."Document No.";
END;

//No DocNo then create one EG> 20061227_000
IF DocNo = '' THEN
DocNo := FORMAT(TODAY,0,'<Year,4><Month,2><Day,2>'+'_000');

OnBeforeImportRecord

INIT;

OnAfterImportRecord

LineNo := LineNo + 10000;
DocNo := INCSTR(DocNo);
"Journal Template Name" := JobJnlBatch."Journal Template Name";
"Journal Batch Name" := JobJnlBatch.Name;
"Line No." := LineNo;
"Document No." := DocNo;


But at I discovered the next 'challenges'. When really booking, it doesn't work because these fields have to be filled automaticly:

Name (of Resource)
Unit of Measure Code
Shortcut Dimension 1 Code
Source Code
Gen. Prod. Posting Group

What is the trick to pull those entries? When doing the batch manual, after the enter they automaticly appear.

Savatage

Ilona wrote:

But at I discovered the next 'challenges'. When really booking, it doesn't work because these fields have to be filled automaticly:

Name (of Resource)
Unit of Measure Code
Shortcut Dimension 1 Code
Source Code
Gen. Prod. Posting Group

What is the trick to pull those entries? When doing the batch manual, after the enter they automaticly appear.

If they appear when you manually enter then your missing a VALIDATE

for example when I import orders via my dataport I added
OnAfterImportRecord()
For the Header
"Sales Header".VALIDATE("Sell-to Customer No.");
For the Lines
"Sales Line".VALIDATE("No.");
Fills in all the missing pieces.

So you need to validate the field "when entered manually" that does the same. I find that CallFieldValidate Checkbox next to Dataport fields doesn't always work as promised. That's why I use the code.

David_Cox

Ilona wrote:

"]quot;] =D> :whistle:
Hurray, This is (almost) working! Thank you David and the others!

IF JobJnlLine.FIND ('-') THEN BEGIN // Find First

But at I discovered the next 'challenges'. When really booking, it doesn't work because these fields have to be filled automaticly:

Should be Find Last (+) not Find First (-)
IF JobJnlLine.FIND ('+') THEN BEGIN // Find Last

Clue to the other bit as above
Type := Type::Resource; // Dont Validate as it resets (Clears) the "No."
VALIDATE("No."); // This fills the data you want plus cost etc:
VALIDATE(Quantity); // Calculates the total

A good practice is to do a manual journal then you know which fields to fill in, if you VALIDATE the Type you will loose the Quantity and No. field data, that is why we use the INIT OnBeforeImport that will reset the line before Import.

=D>

Ilona

:roll: Everything is working fine now, execpt the document number.
Importing works fine. I really love the date in the number. But it seems that the document number has to be the number taken from the number series as set in the configuration.
As soon as pressing F11 this message appears :

Document No. must be PRJ-6-C0028 in Job Journal Line Journal Template Name='PROJECT',Journal Batch Name='CHANTALLE', Line No.=10000.

Of course I could change that number serie to something like 0001, but with so many number series....

By the way, I learned so much in the last days, amazing!

Savatage

click on the no series and check off manual - this way both #series and manual numbers can be used.

jversusj

Ilona wrote:

:roll: Everything is working fine now, execpt the document number.
Importing works fine. I really love the date in the number. But it seems that the document number has to be the number taken from the number series as set in the configuration.
As soon as pressing F11 this message appears :

Document No. must be PRJ-6-C0028 in Job Journal Line Journal Template Name='PROJECT',Journal Batch Name='CHANTALLE', Line No.=10000.

Of course I could change that number serie to something like 0001, but with so many number series....

By the way, I learned so much in the last days, amazing!

Ilona,

I have seen this error before. IF i remember right...
If you are importing the document no. and have that journal batch set-up with a No. Series, you will get the error you describe.

If you go to your job journal and click the lookup arrow on the Journal Batch name, the Journal Batch form will open. You should see two columns (show/hide as necessary). One will be No. Series, the other will be Posting No. Series.

If you have a No. Series entered for the journal you are importing to, the system will say, "hey, you entered XXX but i'm supposed to use YYY." Delete the No. Series and the journal will let you import whatever you want. Of course, if you have the Posting No. Series defined, whatever doc. no. you load will be replaced on post with the posting no.

maybe this helps you? I wish i could remember right for sure.

//edit or just listen to Savatage! he knows better than i do!

Ilona

Happy New Year every one!

jversusj and Savatage, I tried your suggestions, but nothing happens. De document number remains empty ;-(

Savatage

That seems pretty strange... you've seemed to make many tweaks since your first post of the dataport.. perhaps you can post the updated code you're working with.

Ilona

ok, this is what is there now:

On Pre Data Port

CLEAR(LineNo);
CLEAR(DocNo);
//Errors if not found
JobJnlBatch.GET('PROJECT','CHANTALLE');
//Find the Last line and get values
JobJnlLine.RESET;
JobJnlLine.SETRANGE("Journal Template Name",JobJnlBatch."Journal Template Name");
JobJnlLine.SETRANGE("Journal Batch Name",JobJnlBatch.Name);
IF JobJnlLine.FIND ('+') THEN BEGIN
LineNo := JobJnlLine."Line No.";
DocNo := JobJnlLine."Document No.";
DocDate := JobJnlLine."Document Date";
END;



//Tried this one earlier, but didn't work out
//No DocNo then create one EG> 20061227_000
//IF DocNo = '' THEN
//DocNo := FORMAT(TODAY,0,'<Year,4><Month,2><Day,2>'+'_000');

On Before Import Record

INIT;

On After Import Record

LineNo := LineNo + 10000;
"Document No." := DocNo;
// DocNo := INCSTR(DocNo); tried this one also
"Journal Template Name" := JobJnlBatch."Journal Template Name";
"Journal Batch Name" := JobJnlBatch.Name;
"Line No." := LineNo;
Type := Type::Resource;
"Job Journal Line".VALIDATE("Unit of Measure Code");
"Job Journal Line".VALIDATE("Shortcut Dimension 1 Code");
"Job Journal Line".VALIDATE("Source Code");
"Job Journal Line".VALIDATE("Gen. Prod. Posting Group");
"Job Journal Line".VALIDATE(Description);
"Job Journal Line".VALIDATE("Document No.");

[/code]

Savatage

So when does your DocNo actually become something?
try switching this to see what happens
//DocNo := JobJnlLine."Document No.";
to
DocNo := FORMAT(TODAY,0,'<Year,4><Month,2><Day,2>'+'_000');

Note I didn't check all of your validates but one could be resetting it
I don't use the Job Journal - but does the validate of the doc # update all those fields you mentioned earlier?

"Job Journal Line".VALIDATE("Document No.");

You're importing right? so there are no journal lines yet????
LineNo := JobJnlLine."Line No.";
DocNo := JobJnlLine."Document No.";
DocDate := JobJnlLine."Document Date";

David_Cox

Back to basics the only fields you need to Import are "Job No", "No." and Quantity
Optional Field #1 "Shortcut Dimension 1 Code"
This should really come across from resource when "No." is validated
Optional Field #2 "Unit of Measure"
Only needed If value is different From Resource "Base Unit of Measure"

On PreDataPort()
CLEAR(LineNo);
CLEAR(DocNo);
//Errors if not found
JobJnlBatch.GET('PROJECT','CHANTALLE');
//New Code to stop your errors
//No Number Series Needed
JobJnlBatch.TESTFIELD("No. Series",'');
JobJnlBatch.TESTFIELD("Posting No. Series",'');
//Find the Last line and get values
JobJnlLine.RESET;
JobJnlLine.SETRANGE("Journal Template Name",JobJnlBatch."Journal Template Name");
JobJnlLine.SETRANGE("Journal Batch Name",JobJnlBatch.Name);
IF JobJnlLine.FIND ('+') THEN BEGIN
LineNo := JobJnlLine."Line No.";
DocNo := JobJnlLine."Document No.";
DocDate := JobJnlLine."Document Date";
END;
//No DocNo then create one EG> 20061227_000
IF DocNo = '' THEN
DocNo := FORMAT(TODAY,0,'<Year,4><Month,2><Day,2>'+'_000');

OnBeforeImportRecord()
INIT;
// New Code
CLEAR(TempDimCode);
CLEAR(TempUOM); 
CLEAR(TempQty);

OnAfterImportRecord()
LineNo := LineNo + 10000;
DocNo := INCSTR(DocNo); 
"Document No." := DocNo;
"Journal Template Name" := JobJnlBatch."Journal Template Name";
"Journal Batch Name" := JobJnlBatch.Name;
"Line No." := LineNo;
Type := Type::Resource;

//New Code Start 3 new variables
//Move up Imported data to thge variables
TempDimCode := "Shortcut Dimension 1 Code"; // Optional
TempUOM := "Unit of Measure Code"; Optional 
TempQty := Quantity;
//New Code End

// First bring in all the other Values
VALIDATE("Job No."); 
VALIDATE("No."); 
// Validate the Optional Fields
VALIDATE("Shortcut Dimension 1 Code",TempDimCode);
VALIDATE("Unit of Measure Code",TempUOM);
// Last is the Quantity
VALIDATE(Quantity,TempQty);

Ilona

Hi Guys, maybe I didn't explain good enough. The number with the date in it is working fine. But when pressing F11 in the batch, there is the error message as I described below. Al the other things are filled out perfectly.

Everything is working fine now, exept the document number.
Importing works fine. I really love the date in the number. But it seems that the document number has to be the number taken from the number series as set in the configuration.
As soon as pressing F11 this message appears :

Document No. must be PRJ-6-C0028 in Job Journal Line Journal Template Name='PROJECT',Journal Batch Name='CHANTALLE', Line No.=10000.

Of course I could change that number serie to something like 0001, but with so many number series....

David_Cox

Document No. must be PRJ-6-C0028 in Job Journal Line Journal Template Name='PROJECT',Journal Batch Name='CHANTALLE', Line No.=10000.

Of course I could change that number serie to something like 0001, but with so many number series....

Simple as has been said several times above Check or Delete the Number Series

If you are entering or creating a manual number either Check Manual on the "Number Series" or remove the Number Series from the "Job Journal Batch", If you use this batch for other other postings, just create a new batch for the Import with no Number Series

Ilona

David, thank you for you being patient I feel really stupid now

This is what I tried so far:

Resource planning Job Setup --> Removed the No series from the Journal Template "Project"

IT Administration --> General Setup--> No. Series --> deleted the complete series for PRJ-DB-CHA and recreated them without no. series.
Turned of Default Nos and Manual Nos and many varities of combinations.

After that, the number with the date (eg 00070103_005) in the first batch works! But trying a second batch will start again with 00070103_001, resulting in a error message complaining about the no series.

Of course, me being a dummie, also tried removing the complete No series, but then the batch is complaining about missing no series.

David_Cox

If your Job Journal Template and Batch both have no Number Series entered then it will accept whatever "Document No." you use!

So do one or the following options
1. Delete all the "No. Series" from both Job Journal Template (PROJECT) and the Job Journal Batch (CHANTALLE)
2. Create a brand new Template and Batch with no "No. Series", and use these in the dataport.

I checked the help on the No. Series field Manual and learned something new myself, Check markes don't work on Journals, so you need to insert no "No. Series" to use manual numbers :shock:

Manual Nos. Field
The No. Series Table

A check mark in this field indicates that the program will allow you to enter numbers manually instead of always using this number series.

For example, you can create a number series with a check mark in this field and link it to customers. Then when you create a new customer, you can assign a number manually instead of using the next number in the series.

Note
A check mark in this field has no effect for number series used in journals.

Ilona

David, then indeed it's working. But when running the batch again, the document number starts with 00070103_001. This number already exists.
It should increase the last used document number.
With running again, I mean after clearing/booking table 210 with F11 in the batchscreen, importing another text file.
Because this is something that happens every few days.
People are registering their worked hours in a Lotus Domino application daily. Weekly those hours will be exported to an txt file en then imported in Navision through this dataport.

David_Cox

Ilona wrote:

David, then indeed it's working. But when running the batch again, the document number starts with 00070103_001. This number already exists.
It should increase the last used document number.
With running again, I mean after clearing/booking table 210 with F11 in the batchscreen, importing another text file.
Because this is something that happens every few days.
People are registering their worked hours in a Lotus Domino application daily. Weekly those hours will be exported to an txt file en then imported in Navision through this dataport.

Are you Importing more than 1 file a day?
If you are then this Document No. system will not work for you, as there is no way of saving the last number used, you will have to use the number series instead.

Ilona

Hi David,

Normally they would import no more than 1 file a day. It's only an issue with testing then and at moments that they are having troubles.
If that is the worst case scenario....

Friday is D day. 8-[ It has to work than. I'll post a message about the results. Maybe this topic can be finally closed than

Savatage

That would work for 1 time a day - but if you think it will happen more than once (thinking ahead) you might want to maybe add a time stamp to the doc # or change the numbering system all together.