I’m writing a routine to create G/L records. Am trying to get the last current Entry No. in the table, so that I can add one higher than this for the new record. In SQL, would use MAX to get highest number, then add one. What is equivalent in Navision? Also, do I need to lock the table when performing a function such as this?
IF GLEntry.FIND(’+’) THEN NextNo := GLEnry.“Entry No.” + 1 ELSE NextNo := 1; But the normal way is to fill up a Gen Jnl Line and call the posting routine to post that line. That’s the way You should do it. In Navision we hardly ever write directly to ledger entries. Instead there are existing routines for posting journal lines which automatically does all the necessery controls (posting dates, dimensions and so on) for You.
Lars: Thanks, great reply, worked perfectly. True, normally would post into table from Journal. This is a specialized case where we are creating ‘dummy’ summary stat records solely for purposes of feeding them over to secondary reporting package. So, posting from journal is not a req’t (or so I’ve been told! [:D])
I whole heartedly agree with what Lars said. You should always import into Journals and never to Ledgers.
quote:
Originally posted by larryi …(or so I’ve been told! [:D])
Larry, find them and shoot them. Even just for stats, you should never manually insert records directly into Navision. You may have corupted your database, and not even know it. Then someone comes to you at the begining of next year, and says “why can’t I close out the year? I keep getting a message about the gl entries no being concistant?” That or you get an error with rounding on a flow field that is near impossible to locate. Create the journal, and post it that way, you will save time, and have less clint issues later. Even if you have already done it, then for your clients sake, delete the entries and do it correctly.
Larry, Take note of what these guys say. [;)] Many years ago, I tried the solution you are seeking, and sure enough I encountered all sorts of post development problems. Nightmare or what! [V] The sensible solution as these guys say, is using the journal as a transport and posting through the standard codeunits. You will save yourself a load of hassle! [:D] [;)]
Larry, Take note of what these guys say. [;)] Many years ago, I tried the solution you are seeking, and sure enough I encountered all sorts of post development problems. Nightmare or what! [V] The sensible solution as these guys say, is using the journal as a transport and posting through the standard codeunits. You will save yourself a load of hassle! [:D] [;)]
Larry, Take note of what these guys say. [;)] Many years ago, I tried the solution you are seeking, and sure enough I encountered all sorts of post development problems. Nightmare or what! [V] The sensible solution as these guys say, is using the journal as a transport and posting through the standard codeunits. You will save yourself a load of hassle! [:D] [;)]
hi hi hi connull connull connull mouse mouse mouse problems problems problems ? ? ?
OK, message heard loud and clear! I will write to Journal, then post from there. This is all quite new to me, so one more question: If I create Journal records for this specialized function, do I need to create new, separate routines that will post these records into G/L, or can the current posting routines accomodate? Thanks all for the helpful, spirited responses; very much appreciated.
The whole point made by Lars, David and Connull is that, once you import the records into a journal, you don’t have to worry about anything else. You just call the posting routine and let it take it from there. Remember: you can either do it automatically or just leave the records in the journal for a user to check before posting.
Hi. Here’s some code from cu80 which might give som hints: As You can see You assign some values to a variable (a Gnl.Jnl. Line) and call the posting routine. Simple isn’t it! And You have all validations for free. GenJnlLine.INIT; GenJnlLine."Posting Date" := "Posting Date"; GenJnlLine."Document Date" := "Document Date"; GenJnlLine.Description := "Posting Description"; GenJnlLine."Shortcut Dimension 1 Code" := "Shortcut Dimension 1 Code"; GenJnlLine."Shortcut Dimension 2 Code" := "Shortcut Dimension 2 Code"; GenJnlLine."Reason Code" := "Reason Code"; GenJnlLine."Account Type" := GenJnlLine."Account Type"::Customer; GenJnlLine."Account No." := "Bill-to Customer No."; IF "Document Type" = "Document Type"::"Credit Memo" THEN GenJnlLine."Document Type" := 0 // Blank ELSE GenJnlLine."Document Type" := GenJnlLine."Document Type"::Payment; GenJnlLine."Document No." := GenJnlLineDocNo; GenJnlLine."External Document No." := GenJnlLineExtDocNo; IF "Bal. Account Type" = "Bal. Account Type"::"Bank Account" THEN GenJnlLine."Bal. Account Type" := GenJnlLine."Bal. Account Type"::"Bank Account"; GenJnlLine."Bal. Account No." := "Bal. Account No."; GenJnlLine."Currency Code" := "Currency Code"; GenJnlLine.Amount := TotalSalesLine."Amount Including VAT" + CustLedgEntry."Pmt. Disc. Possible"; GenJnlLine."Source Currency Code" := "Currency Code"; GenJnlLine."Source Currency Amount" := GenJnlLine.Amount; GenJnlLine.Correction := Correction; CustLedgEntry.CALCFIELDS(Amount); IF CustLedgEntry.Amount = 0 THEN GenJnlLine."Amount (LCY)" := TotalSalesLineLCY."Amount Including VAT" ELSE GenJnlLine."Amount (LCY)" := TotalSalesLineLCY."Amount Including VAT" + ROUND( CustLedgEntry."Pmt. Disc. Possible" * CustLedgEntry."Adjusted Currency Factor"); IF SalesHeader."Currency Code" = '' THEN GenJnlLine."Currency Factor" := 1 ELSE GenJnlLine."Currency Factor" := SalesHeader."Currency Factor"; GenJnlLine."Applies-to Doc. Type" := GenJnlLineDocType; GenJnlLine."Applies-to Doc. No." := GenJnlLineDocNo; GenJnlLine."Source Type" := GenJnlLine."Source Type"::Customer; GenJnlLine."Source No." := "Bill-to Customer No."; GenJnlLine."Source Code" := SrcCode; GenJnlLine."Posting No. Series" := "Posting No. Series"; GenJnlPostLine.RunWithCheck(GenJnlLine,TempJnlLineDim);
[ code] … [ /code] added
There is one thing I don’t quite grasp, when you say:
quote:
Originally posted by Larry This is a specialized case where we are creating ‘dummy’ summary stat records solely for purposes of feeding them over to secondary reporting package.
What does it mean? Are you exporting the data from Navision into a different program and you need these entries only because of this secondary program? When you post these G/L Entries, will it not make your Accounts reflect an unrealistic situation? Is it not better to simply add these summary entries during the export phase and not create them inside Navision?
To avoid affecting the Accounts use as balance account the same one you’re posting to… so it will clean itself.
Hi David. It wasn’t me - must be this dodgy web site [:D]
Thanks to everyone’s help and input, I have this routine working properly (yes!). Have one final issue to deal with - slowness. I built up the functionality incrementally, adding features bit by bit. At one point, I had program create Gen. Journal Lines, and then manually posting these lines in. This worked fine. But when I have RunWithCheck post programatically, it’s dog slow. Instead of a 5 minute runtime, it’s over 1/2 hour. Any way to change the way I call the posting routine to make it more efficient, or something else I can change?
this is because of dimensions. at 1/2 hour for a one off routine, just leave it. If you monitor the database reads during a g/l journal post, you will see that the number or reads is proportional to the square of the number of lines that have dimensions. I am pretty sure I discussed this in an earlier thread, so try searching. You can speed it up by posting many smaller journals. I had thought it was fixed in 3.60 though.