Trying to Insert a ManualJournal into Xero with ODBC

Live Connectivity CData Drivers
, , ,
1

I am trying to insert a manual journal entry into Xero using below SQL insert statement. And I get the following error. 
“A validation exception occurred: The total Debits (1.50) must equal total credits (0.00)" It suggests that cdata is not processing it as one API post but as 2 posts and Xero spits back an error because it expects both lines in the POST api.

INSERT into ManualJournals ( JournalLine_AccountCode, JournalLine_LineAmount, Narration) VALUES (620, 1.50, 'Test1'), (611, -1.50, 'Test1');

Any ideas would be appreciated.

2

Hi Andy,

Using a traditional insert in this case would result in a validation error. You’re correct to say that this is because the API calls for each insert are done individually and this results in an error since total debits =/= total credits

If I understand your use-case correctly, you need to insert multiple line items simultaneously. This can be done by using the JournalLineAggregate column. Please refer to this part of the driver’s documentation (under the INSERT sector)::

In your case, I believe you would need to use the following query:

INSERT INTO ManualJournals (Narration, JournalLineAggregate)VALUES ('Test1', '<JournalLine><LineAmount>1.50</LineAmount><AccountCode>620</AccountCode></JournalLine> <JournalLine><LineAmount>-1.50</LineAmount><AccountCode>611</AccountCode></JournalLine>');

This would issue an API request with the following POST body:

<ManualJournals>  <ManualJournal>    <JournalLines><JournalLine><LineAmount>1.50</LineAmount><AccountCode>620</AccountCode></JournalLine> <JournalLine><LineAmount>-1.50</LineAmount><AccountCode>611</AccountCode></JournalLine></JournalLines>    <Narration>Test1</Narration>  </ManualJournal></ManualJournals>

Please let me know if your issue persists.

3

That works perfectly @Erold_B   Thanks so much!!