Issue:  I am creating Journal Entry Lines using the TIBCO Scribe® Online Connector For NetSuite (CData).  I need to create 2 journal entry lines, but the map fails after the first line with the error “The amounts in a journal entry must balance” and does not continue to insert the second line.

This article provides a method to insert NetSuite Journal Entry lines using the LineListAggregate field with a TIBCO Scribe® Online Connector For NetSuite (CData) connection.

Inserting journal entries can be challenging due to the requirement of needing at least 2 separate lines to create a new Journal Entry record.  Using the TIBCO Scribe® Online Connector For NetSuite (CData), 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.

This method requires the following Connection String Option added to the "Additional Parameters" field in the connection properties:   Aggregate Column Mode="List"

​Setting this option to “List” will expose the field and have better performance than ListAndRetrieve.  For additional  information, see the following in the CData ADO.NET Provider for NetSuite Online help:   Aggregate Column Mode


Steps:

1. Add the following Connection String Option to the connection Additional Parameters:     Aggregate Column Mode="List"

            
 

2. Reset metadata for the NetSuite (CData) connection. See Resetting Metadata in  the TIBCO Scribe® Online Help.

 

3. For the Create block, select the JournalEntry entity and map values for both lines in the LineListAggregate field.  See examples below.

            



Example 1 using hard-coding

“<JournalEntry_LineList>
     <Row>
             <LineList_Account_InternalId>73</LineList_Account_InternalId>
             <LineList_Debit>100.00</LineList_Debit>
             <LineList_Memo>Debit Line</LineList_Memo>
    </Row>
    <Row>
            <LineList_Account_InternalId>19</LineList_Account_InternalId>
           <LineList_credit>100.00</LineList_credit>
           <LineList_Memo>Credit Line</LineList_Memo>
    </Row>
</JournalEntry_LineList>”


Example 2 using “&” to concatenate the string with “JournalLines” source fields

"<JournalEntry_LineList>
  <Row>
         <LineList_Account_InternalId>" & JournalLines.Debit_Account & "</LineList_Account_InternalId>
        <LineList_Debit>" & JournalLines.Debit & "</LineList_Debit>
        <LineList_Memo>Debit Line</LineList_Memo>
 </Row>
 <Row>
       <LineList_Account_InternalId>" & JournalLines.Credit_Account & "</LineList_Account_InternalId>
      <LineList_credit>" & JournalLines.Credit & "</LineList_credit>
       <LineList_Memo>Credit Line</LineList_Memo>
 </Row>
</JournalEntry_LineList>"

For additional information, see the following in the TIBCO Scribe® Online Help:   TIBCO Scribe® Online Connector For NetSuite (CData)