SQL Insert New GL Entry

Business Central/NAV Technical Forum
1

I am attempting to import external information to the GL Account.

I did my development by creating a duplicate GL Entry table called TestGL. All of the code works, the accounts balance. Life is good. The only adjustment that I made to this table was seting Entry No_ to be an identity column.

As a simple test, I get an old GL Entry, change the amount to zero, the debit/credit to zero and attempt to write it back to a new entry. I get duplicate entry error. I noticed that there is no identity column defined. So I have attempted to change the Entry_No_ by getting the max entry and adding one to it. I get Duplicate Key error.

When I tried to insert records i filled in from my external source, the code threw and ARITHABORT exception - Again, debugging, i verified columns appear to be generally the same as the sample entry I pulled out of the GL.

Any broad suggestions on how to proceed? Pointing me at documentation or samples would be great help.

2

just a doubt. what is an ARITHABORT exception ? never came across such an error till now.

3

Hi Dan

welcome to the Dynamics User Group [<:o)]

Well, I’m not sure if I understood your problem correctly:

You have a new table - designed in NAV or SQL? - which is a copy of T17 “G/L Entry”; the “Entry No.” in that new table is an IDENTITY column. Now if you insert a record into this new table you get those errors, either “Duplicate Key” or "ARITHABORT " …

How do you insert the data? Via some NAV code or via SQL features? Could you give some code examples?

Cheers,
Jörg

4

I am trying to import credit card transactions. (our spending using cards)

I have written a DotNet application to validate the credit card information and store it into a separate audit table outside of Navision. (SQL Table).

Once verified, I want to create a new entry into the GL Entry table, writing directly from DotNet using LINQ to SQL. I copied the schema for the Naviaion GL Entry table to test my business logic. I did set that table to have an IDENTITY column - [Entry No_]. Code worked fine. I figured I was ready to test against the real GL. So I just changed the table name to G_L_Entry table. I quickly found that there was no auto-increment in the Navision SQL table. So I added code to get the highest record index, add 1 and try that as my key. ([Entry No_]).

On running my code I get: “INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’.”

’ Build entries for the Navision GL

Dim GLDebit As New HUBBARD_ENTERPRISES_G_L_Entry ’ Defined in dbml by dragging G_L_ENTRY table onto design pane

Dim GLCredit As New

HUBBARD_ENTERPRISES_G_L_Entry

SetDefaults(GLDebit) ’ Routine iterates through the object using reflection to set all variables to non-null except timestamp.

SetDefaults(GLCredit)

With

GLDebit

’ Fill in standard tags.

.Gen__Bus__Posting_Group = 0

.Source_Type = 0

.Bal__Account_Type = 0

.System_Created_Entry = 0

.External_Document_No_ = 0

.Document_Type = 0

.Prior_Year_Entry = 0

.User_ID =

“EP User”

.Reversed = 0

’ Build our record

.Source_Code =

“GENJNL”

.Journal_Batch_Name =

“ExpPt”

.Posting_Date = PostingDate

.Document_No_ = DocumentNo

.Transaction_No_ = TransactionNumber

’

’ Copy information over…

.Job_No_ = Charge.RecNo

.Global_Dimension_1_Code = Charge.Department

.Document_Date = Charge.TransDate

.Description = Charge.Description

’ Charge.CardHolder

’ Charge.Account ’ CC Account

’ Charge.Details

.Source_No_ = Charge.Issuer

’ Set Debit/Credit entries

.G_L_Account_No_ =

“636300”

.Amount = Charge.Amount

.Debit_Amount = Charge.Amount

.Credit_Amount = 0

End With

‘’’ Now submit to database

Dim LastEntryNo = (From E In dc.HUBBARD_ENTERPRISES_G_L_Entries Order By E.Entry_No_ Descending Select E.Entry_No_).First

GLCredit.Entry_No_ = LastEntryNo + 1

GLDebit.Entry_No_ = LastEntryNo + 2

dc.HUBBARD_ENTERPRISES_G_L_Entries.InsertOnSubmit(GLCredit)

dc.HUBBARD_ENTERPRISES_G_L_Entries.InsertOnSubmit(GLDebit)

’ Write them

dc.SubmitChanges()

On executing the above submit, the exception path is taken - ARITHABORT.

I believe that the created object matches a system generated GL record, other than the amounts, description, etc.

I did try turnning ARITHABORT off - I think.

Prior to the above code, I had

dc.ExecuteCommand("set arithabort off ")

No difference.

I had hoped to just write two simple offseting entries in the GL. Seems so simple. But I can’t get past this Insert.

1- I did notice that there are VSIFT views attached to the G_L_ENTRY. Do I have to do anything to update those?

I suppose the alternative is to code in C/AL. But I am very disappointed with the documentation for Navision. I read that a Best Practices approach is to import into a Navision Journal, then Post. Examples of that strategy would help, if C/AL really is the right way to go.

Thanks

5

welcome on aboard Dan:)

6

Well, I have to admit that I have no clue about DotNet developmen (yet) so I cannot tell what’s wrong with that code …

But: you should NEVER NEVER NEVER write from otside any data into NAV ledger entry tables, especially not to “fiscally relevant” tables like G/L Entry & Co. as this will jeopradize the data integrity and the correctness of your book-keeping!!!

The only correct way to create G/L Entries (& Co.) is to run real NAV site postings through NAV site journals. Have in mind that there are lots of additional records required, e.g. the Registers, maybe Dimensions, related VAT Entries, etc…

Using IDENTITY_INSERT (speaking NAV “AutoIncrement”) may cause gaps in the numbering of G/L Entries which is IMHO illegal (at least in most European countries).

So I strongly recommend to NOT DOING this!

I’d rather import the Credit Card data into this separate audit table (insert the records using SQL or whatever) and link it to NAV, then using NAV code to create the appropriate journals in NAV, and then post the journals with NAV.
Anything else might srew up your book-keeping …

7

Thank you for you suggestions. I am coming to the same conclusion that I should move it through a Journal and Post.

Can you point me to any documentation or sample code as to how to do that? I figure I can use my Dot Net to XML and import into a journal table. But how do you post from journal to GL? Can I simply extend an existing method? Or do I have to start from scratch on writing the GL entry? If the latter, I am back to having to code all the verification rules.

8

Even this I would NOT recommend. Whenever you write data into NAV tables from external applications (e.g. DotNet etc.) you bypass the NAV business logic.
If you create a Journal Line within NAV there are a lot of validations etc. triggered by the NAV code, which finally grants NAV’s data consitency. If you bypass all this business logic you probably will create inconsitent data.

I suggest this:

  1. Create a new table in NAV; let’s call it “Credit Card Data”; just a simple table to store the records
  2. Import the CC data into this table, e.g. by using DotNet or something. Regard the NAV conventions about data types etc.!
  3. Then create a NAV codeunit which processes the “Credit Card Data” to create the NAV journal lines; here doing all the VALIDATEs which are crucial.
  4. Then post the Journal within NAV.

I’m just guessing, but I feel that you are not a NAV developer? Fiddling with postings in NAV is something which only should be done by experienced developers/consultants who understand the NAV business processes and required program routines; else you might screw something up …
Maybe you could contact your Dynamics Partner for assistence?!

9

its the ONLY way to go. [;)]

10

THanks. You are right that I am not a seasoned NAV developer. I am a long term DotNet developer. I have been working with NAV for about a year, but mosting making reports against the SQL data… I have developed extensions for our manufacturing system to handle material and machine planning. This has involved changing NAV screens, adding columns to tables, etc.

I have been very frustrated with the trainng material for NAV. The only material I could find were poor handouts from some training class and Studebaker’s book: Programming Microsoft Dynamics NAV. None of this really addresses the Best Practices development cycle for posting. I can’t find classes here in the US that are available. It seems that I need MS Class 8873A: C/SIDE Solution Development in Microsoft Dynamics NAV 5.0. But the only one I found is in mid August - I don’t have 4 weeks to get this done - Certainly not 4 months to wait for a class.

Do you have any suggestions for acquring documentation, training quickly?

Thank your your help

11

Thanks for your guidance.

I was able to change over to this strategy in just under an hour. Big, big help.

I wrote my record directly to the General Journal via DotNet. From there, we go into the Navision General Journal Batch and get one last chance to verify that the data looks right. Then we post. Simple. Follows the rules (other than usig SQL versus more labor-prone DataPort).

Dan Beadle

12

Actually writing straight into the Journal table is also not something I would personally do. There’s all sorts of business logic that executes through field validation, which does not happen when an external program writes straight into the tables. Did you duplicate all that business logic in your .NET program? How did you determine what the logic was?

If it were me, I’d have the .NET process write into a staging table and have a NAV process copy the data into the journal tables, so that this can be validated properly. You can then also use the same process to start the posting process.

13

If your company is up to date on the enhancement fee, you should have access to customersource, which is where you can download all the NAV training material. Get in touch with the person that is in charge of NAV at your company and ask for customersource. If they don’t know, then contact your NAV partner, they will be able to provide access.

14

Hi Jorg,

Your knowldge on witing to Navision GL is impressive. I have a question, perhaps you could assist me? I am currently working on an integrated HR & payroll implementation. The legacy payroll system is a rudimentry “bolt on” to the Navision finance system (called “Dialogue”)

We are replacing it with another system. HOWEVER, this raises the issue that we must now create a way to “write” salary costs to the GL (in Navision). I had assumed there would be some sort of file between Dialogue and Navision and that we could simply replicate this file. However, the vendor we deal with for Navision says there is no file, rather - and I am quoting directly "There is presently no file passed from the payroll system to NAV … the current payroll system knows all about the dimensions, g/l accounts etc and generates the correct journal by storing the data directly into a NAV journal.

herefore, we have to build this interface file from scratch. We will need to build mappings to appropriate g/l accounts from the various types of payroll postings that are supplied by your new system, and load these into a journal ready for review prior to posting"

Would you be able to advise tany helpful infomration to assist us to expedite this? I know this is a broad question, but I am not even sure of what question to ask - any help/ advice/ pointers you could give me would be greatly appreciated.

15

Hi Bernadette,

thanks a lot for your trust in my experience. At first, a warm welcome to the Dynamics User Group [<:o)]

Well, as I dont know the requirements and capabilities of this new PR system it’s hard to advice how to transfer the data at best …

Technically there are a lot of options: flat file import via Dataport, xml transfer via XMLPort, exchange via SSIS, direct SQL-to-SQL transfer (e.g. Lined Server etc.).
But in any case the data has to be transferred into a buffer table in NAV, then into G/L Journal to validate the data and to perform the actual posting.

NEVER write directly into the Ledger Entry tables, as this will screw up the data integrity in NAV!
Thus, the technical part could be accomplished in many ways, but the processing of the data must be done in NAV.

Best regards,
Jörg

16

Jorg,

Thank you so much for this infomration and the options. This gives me a really good “starting point”. Once again I would like to thank you very much.

I’ll let you know what we end up doing.

Kind Regards,

B.