Inserting journal entries is more difficult then most tables, as journal entries have an additional requirement of needing at least 2 separate lines to create a new Journal Entry record. Using your CData Netsuite connector, you can insert the full LineListAggregate to the JournalEntry table instead of inserting the lines directly. The JournalEntry table accepts a full XML aggregate with all the 'linelist_*' fields defined.
The following method requires setting the “Aggregate Column Mode” setting located in the Miscellaneous section of your connection settings to “List.” Setting this option to “List” will expose the field and have better performance than ListAndRetrieve. ODBC Documentation on this property is linked below, and will be relevant for any of the CData Netsuite Connectors:
https://cdn.cdata.com/help/DNJ/odbc/RSBNetSuite_p_AggregateColumnMode.htm
After setting the Aggregate Column Mode, reset any previously manually defined metadata, if applicable. This step is only required if you have an on-prem install and have previously generated .rsd files. This is to ensure the new ‘List’ mode has taken effect.
If the change was successful, there should be a new column in the JournalEntry table, called LineListAggregate. You will insert the values for the Journal Entry Lines here. If you do not see this column after making setting the Column Mode to list, reach out to support@cdata.com.
To insert two lines, one for Credit and one for Debit, with a single insert, first prepare the Journal Entry Line items in the below format:
<JournalEntry_LineList>
<Row>
<LineList_Account_InternalId>24</LineList_Account_InternalId>
<LineList_Debit>250.00</LineList_Debit>
<LineList_Memo>Debit Line</LineList_Memo>
</Row>
<Row>
<LineList_Account_InternalId>25</LineList_Account_InternalId>
<LineList_credit>300.00</LineList_credit>
<LineList_Memo>Credit Line</LineList_Memo>
</Row>
</JournalEntry_LineList>
To add more then two lines, insert more <Row> </Row> entries.
Afterwards, prepare a SQL Insert statement, see the below example:
INSERT INTO JournalEntry (LineListAggregate, Subsidiary_InternalId) VALUES ("<JournalEntry_LineList><Row><LineList_Account_InternalId>24</LineList_Account_InternalId><LineList_Debit>250.00</LineList_Debit><LineList_Memo>Debit Line</LineList_Memo></Row><Row><LineList_Account_InternalId>25</LineList_Account_InternalId><LineList_credit>300.00</LineList_credit><LineList_Memo>Credit Line</LineList_Memo></Row></JournalEntry_LineList>", 1);
Running this statement will insert two rows into the Journal Entry Line Items table. If you run into any errors during this process, or additional questions, please reach out to support@cdata.com
