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