TDV Adapter for NetSuite

Build 22.0.8462

Inserting Parent and Child Records

Use Case

When inserting records, often there is a need to fill in details about child records that have a dependency on a parent.

For instance, when dealing with a CRM system, Invoices often may not be entered without at least one line item.

Invoices may have many line items, with each line item made up of several fields. This presents a unique challenge when offering the data as relational tables.

When reading the data, it is easy enough to model an Invoice and an InvoiceLineItem table with a foreign key connecting the two.
But during inserts, the CRM system will require both the Invoice and the InvoiceLineItems to be created in a single submission.

To solve this sort of problem, our tools offer child collection columns on the parent.
These columns may be used to submit insert statements that include details of both the parent and the child records.

From our example, the Invoice table may have a column called InvoiceLineItems.
During the insert, we can pass in the details of the records that would need to be inserted to the InvoiceLineItems table into the InvoiceLineItems column of the Invoice record.
This can be done using the following methods:

Methods for Inserting Parent/Child Records

The adapter facilitates two methods for inserting parent/child records: temporary table insertion and XML/JSON aggregate insertion.

Temporary (#TEMP) tables

The simplest way to enter data would be to use a #TEMP table, or temporary table, which the adapter will store in memory.

Reference the #TEMP table with the following syntax:

TableName#TEMP

#TEMP tables are stored in memory for the duration of a connection.

Therefore, in order to use them, you cannot close the connection between submitting inserts to them, and they cannot be used in environments where a different connection may be used for each query.

Within that single connection, the table remains in memory until the bulk insert is successful, at which point the temporary table will be wiped from memory.

For example:

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

Once the InvoiceLineItems table is populated, the #TEMP table may be referenced during an insert into the Invoice table:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

Under the hood, the adapter will read in values from the #TEMP table.

Notice that the ReferenceNumber was used to identify what Invoice the lines are tied to.
This is because the #TEMP table may be populated and used with a bulk insert, where you will have different lines for different Invoices.
This allows the #TEMP tables to be used with a bulk insert. For example:

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

In this case, we are inserting two different Invoices. The ReferenceNumber is how we determine which Lines go with which Invoice.

Note: The tables and columns presented here are an example of how the adapter works in general. The specific table and column names may be different in the adapter.

XML/JSON Aggregates

As an alternative to #TEMP tables, direct XML/JSON may be used. Since #TEMP tables are not used to construct them, it does not matter if you use the same connection or close the connection after insert.

For example:

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

OR

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

Note that the ReferenceNumber is not present in these examples.

That is because the XML/JSON by its nature is not being passed by reference, but passed in full per insert against the parent record.
There is no need to provide something to tie the child back to the parent since the complete XML/JSON must be constructed and submitted for each row.

Then, insert the values:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

OR

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Example for NetSuite

The following example uses Aggregate Columns. All columns in the SuiteTalk schema that are suffixed with the word "Aggregate" identifies a column that represent a colleciton of objects. The AggregateColumnMode connection property must be set to a minimum of List in order for them to show up and be availalbe for inserts. The default for AggregateColumnMode is Ignore.

For a working example of how temp tables can be used for bulk insert in NetSuite, please see the following.

// Insert into JournalEntry_LineList child table
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPY','1','SINC191114T875102','7','2019-11-14', 'ACC1113Y', '150019', '0', 'INC1411437', 'a');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPY','1','SINC191114T875102','7','2019-11-14', 'ACC1113Y', '0', '150019', 'INC1411437', 'a');

INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPQ','2','SINC191114T875102','7','2019-11-15', 'ACC1114Y', '190019', '0', 'INC1411439', 'b');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPQ','2','SINC191114T875102','7','2019-11-15', 'ACC1114Y', '0', '190019', 'INC1411439', 'b');

INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPS','3','SINC191114T875103','8','2019-11-19', 'ACC1115Y', '110019', '0', 'INC1411441', 'c');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPS','3','SINC191114T875103','8','2019-11-19', 'ACC1115Y', '0', '110019', 'INC1411441', 'c');


// Insert into JournalEntry parent table
INSERT INTO JournalEntry#TEMP (ExternalId, LineListAggregate) VALUES ('SINC191114T875102', 'JournalEntry_LineList#TEMP');
INSERT INTO JournalEntry#TEMP (ExternalId, LineListAggregate) VALUES ('SINC191114T875103', 'JournalEntry_LineList#TEMP');


// Execute the bulk insert
INSERT INTO JournalEntry (ExternalId, LineListAggregate) SELECT ExternalId, LineListAggregate FROM JournalEntry#TEMP

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462