In Dynamics 365 for Finance and
Operations (D365FO), you can insert records using X++ in several ways. Here are
the most common methods for inserting records with examples:
1.
Using insert() Method
The insert() method is used to add a new record to the database.
Example:
static void insertExample(Args _args)
{
CustTable custTable;
// Create a new record
custTable.AccountNum = 'CUST001';
custTable.Name = 'New Customer';
custTable.Currency = 'USD';
custTable.insert(); // Insert the record into the database
}
Explanation:
- We create a new instance of the CustTable
(customer table) and set its fields.
- The insert() method is called to save the record to the database.
2.
Using insert_recordset Statement
The insert_recordset statement allows you to insert multiple records at once.
This is often used for batch inserts, which improves performance.
Example:
static void insertRecordsetExample(Args _args)
{
CustTable custTable;
// Using insert_recordset to insert multiple records at once
insert_recordset custTable (AccountNum,Name, Currency)
values ('CUST002', 'Customer 2','USD'),('CUST003', 'Customer 3','EUR');
}
Explanation:
- insert_recordset
is used to insert multiple records into the CustTable in one operation.
- It reduces the number of database calls, making it more
efficient for bulk inserts.
3. Basic Syntax to use insertRecordList
The insertRecordList method is available on the Common class, which means it can be used with any table that
extends Common.
It allows you to insert multiple records at once into a table, reducing the
overhead of individual insert operations.
Steps
to Use insertRecordList:
- Prepare a List of Records: Create a list of records (usually in the form of a List or Query).
- Populate the Records:
Set the values for each record in the list.
- Call insertRecordList: Use the insertRecordList method to insert the batch of records.
Here’s a step-by-step example:
Example:
Using insertRecordList in X++ for CustTable
Scenario:
Insert a batch of customer records into the CustTable.
static void insertRecordListExample(Args _args)
{
CustTable custTable;
List custList = new List(Types::Record);
CustTable custRecord;
// Create a few customer records
custRecord = CustTable::construct(); //Create a new instance of CustTable
custRecord.AccountNum = 'CUST009';
custRecord.Name = 'Customer 9';
custRecord.Currency = 'USD';
custList.addEnd(custRecord); // Add the record to the list
custRecord = CustTable::construct();
custRecord.AccountNum = 'CUST010';
custRecord.Name = 'Customer 10';
custRecord.Currency = 'EUR';
custList.addEnd(custRecord); // Add the record to the list
custRecord = CustTable::construct();
custRecord.AccountNum = 'CUST011';
custRecord.Name = 'Customer 11';
custRecord.Currency = 'GBP';
custList.addEnd(custRecord); // Add the record to the list
// Insert all records at once
ttsBegin; // Begin the transaction
custTable.insertRecordList(custList); //Insert the list of records
ttsCommit; // Commit the transaction
}
Explanation:
- Creating and Populating the Records:
- We create instances of CustTable using CustTable::construct().
- For each customer record, we set the necessary fields
(AccountNum, Name, Currency).
- We then add each record to a list (custList)
using addEnd() method.
- Inserting the Records:
- We start a transaction using ttsBegin
and ensure all records are inserted as part of the same transaction.
- The insertRecordList(custList) method is called on the CustTable
instance to insert the entire list of records into the database.
- Committing the Transaction:
- Finally, ttsCommit commits the transaction, ensuring that all records
are inserted at once.
Performance Considerations:
- Efficiency:
The insertRecordList method is significantly more efficient than inserting
records one by one, as it reduces the overhead of multiple database round
trips.
- Transaction Control:
It's important to use ttsBegin and ttsCommit to ensure that all insert operations are performed as
part of a single transaction.
Notes:
- Ensure that the records in the list are properly
populated before calling insertRecordList.
- You can insert records into any table type that extends
Common, not
just CustTable.
4.Insert Records using Query::insert_recordset()
- As you can see, the first part of the below code builds a query using the QueryBuild* class hierarchy.
- The second part populates the target to source Map object, which maps the fields to insert into to their source.
- The third part actually invokes the operation, making sure we have the record populated in our "tmp" table beforehand.
public class DEV_Tutorial_InsertRecordset
{
public static Counter insertQueryInsert_Recordset(SalesId _salesId)
{
DEV_SalesLineHistory salesLineHistory;
Query query = new Query();
QueryBuildDataSource qbdsSalesLine = query.addDataSource(tableNum(SalesLine));
qbdsSalesLine.addSelectionField(fieldNum(SalesLine,SalesId));
qbdsSalesLine.addSelectionField(fieldNum(SalesLine, LineNum));
qbdsSalesLine.addSelectionField(fieldNum(SalesLine, InventTransId));
qbdsSalesLine.addSelectionField(fieldNum(SalesLine, SalesQty));
qbdsSalesLine.addSelectionField(fieldNum(SalesLine,SalesUnit));
qbdsSalesLine.addRange(fieldNum(SalesLine,SalesId)).value(queryValue(_salesId));
QueryBuildDataSource qbdsInventDim = qbdsSalesLine.addDataSource(tableNum(InventDim));
qbdsInventDim.addSelectionField(fieldNum(InventDim,InventLocationId));
qbdsInventDim.addSelectionField(fieldNum(InventDim, InventSiteId));
qbdsInventDim.relations(true);
QueryBuildDataSource qbdsPostingData =
qbdsInventDim.addDataSource(tableNum(DEV_SalesLineHistoryPostingDataTmp));
qbdsPostingData.addLink(fieldNum(SalesLine, SalesId),
fieldNum(DEV_SalesLineHistoryPostingDataTmp, SalesId), qbdsSalesLine.name());
qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp,PostedDateTime));
qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp,
PostedBy));
qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp,
PostingType));
Map targetToSourceMap = new
Map(Types::String, Types::Container);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesId),[qbdsSalesLine.uniqueId(),
fieldStr(SalesLine, SalesId)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory,
LineNum),
[qbdsSalesLine.uniqueId(), fieldStr(SalesLine, LineNum)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory,
InventTransId),
[qbdsSalesLine.uniqueId(), fieldStr(SalesLine, InventTransId)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesQty),[qbdsSalesLine.uniqueId(),
fieldStr(SalesLine, SalesQty)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesUnit),[qbdsSalesLine.uniqueId(),
fieldStr(SalesLine, SalesUnit)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory,
InventLocationId),
[qbdsInventDim.uniqueId(), fieldStr(InventDim, InventLocationId)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, InventSiteId),[qbdsInventDim.uniqueId(),
fieldStr(InventDim, InventSiteId)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory,
PostedDateTime),
[qbdsPostingData.uniqueId(),
fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostedDateTime)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, PostedBy),[qbdsPostingData.uniqueId(),
fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostedBy)]);
targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory,
PostingType), [qbdsPostingData.uniqueId(),
fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostingType)]);
ttsbegin;
DEV_SalesLineHistoryPostingDataTmp postingData;
postingData.PostedDateTime = DateTimeUtil::utcNow();
postingData.PostedBy = curUserId();
postingData.PostingType = DocumentStatus::Invoice;
postingData.SalesId = _salesId;
postingData.insert();
Query::insert_recordset(salesLineHistory, targetToSourceMap, query);
delete_from postingData
where postingData.SalesId == _salesId;
ttscommit;
return any2int(salesLineHistory.RowCount());
}
}
Summary:
- insert()
Used for inserting a single record.
- insert_recordset:
Efficient for inserting multiple records at once.
- InsertRecordList:
Efficient for inserting multiple records at once.
- Query::insert_recordset():
Efficient for inserting multiple records at once using query.