insert_recordset is one the greatest enhancement in AX 2012 which allows us to insert records from one to another table in single client-server trip. This is an X++ SQL statement and is similar to update_recordset which i explained in my this post.
Sometime we may require to insert values into destination table’s column which do not exists in source table’s column.
To elaborate it more let’s have an example and then I will explain you how it works;
static void testJob(Args _args)
{
ProjTable projTable, projTableInsert;
ProjStatus enumValue = ProjStatus::Created;
str 30 strValue = "test";
update_recordSet projTable
Setting Name = "test"
where projTable.projId == "Foobar";
insert_recordset projTableInsert (Name, ProjId)
select strValue, ProjId
from projTable where projTable.projId == "Foobar";
// You can set to an enum value, providing that
update_recordSet projTable
Setting status = ProjStatus::Created
where projTable.projId == "Foobar";
insert_recordset projTableInsert (Status, ProjId)
//select ProjStatus::Created, ProjId NOT ALLOWED
select enumValue, ProjId
from projTable where projTable.projId == "Foobar";
}
I have two variables StrValue of string type and enumValue of enum (ProjStatus) type.
You can set enum value directly into update_recordset but this is NOT ALLOWED in insert_recordset. For this reason I declared a variable enumValue and used later in insert_recordset statement.
another complex example for insert_recordset;
// I created this method to insert records into customized table (JobProfitDetailsSw) from different tables.
private void insertPurchaseOrderTypeProjCostTrans(ProjId _projId)
{
JobProfitDetailsSW jobProfitDetailsSW;
ProjCostTrans projCostTrans;
VendInvoiceJour vendInvoiceJour;
str 20 jobProfitSourceSw = enum2str(JobProfitSourceSw::BCIInvoice);
str 20 jobProfitTypeSw = enum2str(JobProfitTypeSw::Cost);
insert_recordset jobProfitDetailsSW
(Amount, Description, JobProfitSourceStr, JobProfitTypeStr, ProjectDate, ProjId, CategoryId)
select sum(TotalCostAmountCur), Txt, jobProfitSourceSw, jobProfitTypeSw, AsOfDate, _projId, CategoryId
from projCostTrans
group by Txt, projId, CategoryId
where projCostTrans.TransDate <= AsOfDate &&
projCostTrans.TransactionOrigin == ProjOrigin::PurchaseOrder &&
projCostTrans.ProjId == _projId
join vendInvoiceJour
group by Purchid
where vendInvoiceJour.LedgerVoucher == projCostTrans.VoucherJournal;
}
another complex example for insert_recordset;
// I created this method to insert records into customized table (JobProfitDetailsSw) from different tables.
private void insertPurchaseOrderTypeProjCostTrans(ProjId _projId)
{
JobProfitDetailsSW jobProfitDetailsSW;
ProjCostTrans projCostTrans;
VendInvoiceJour vendInvoiceJour;
str 20 jobProfitSourceSw = enum2str(JobProfitSourceSw::BCIInvoice);
str 20 jobProfitTypeSw = enum2str(JobProfitTypeSw::Cost);
insert_recordset jobProfitDetailsSW
(Amount, Description, JobProfitSourceStr, JobProfitTypeStr, ProjectDate, ProjId, CategoryId)
select sum(TotalCostAmountCur), Txt, jobProfitSourceSw, jobProfitTypeSw, AsOfDate, _projId, CategoryId
from projCostTrans
group by Txt, projId, CategoryId
where projCostTrans.TransDate <= AsOfDate &&
projCostTrans.TransactionOrigin == ProjOrigin::PurchaseOrder &&
projCostTrans.ProjId == _projId
join vendInvoiceJour
group by Purchid
where vendInvoiceJour.LedgerVoucher == projCostTrans.VoucherJournal;
}
