Use RecordInsertList in D365

Simple way to insert record list

Just for an example, we need to insert records in a regular table but for temporary purpose.

void method()
{
InventTable inventTable;
        InventDimCombination    inventDimCombination;
SMCItemPriceTmp itemPriceTmpTable;
RecordInsertList    itemPriceTmpTableList = new RecordInsertList(tableNum(SMCItemPriceTmp), false, false, false, false, false, itemPriceTmpTable);

ttsbegin;
        itemPriceTmpTable.selectForUpdate(true);
        delete_from itemPriceTmpTable;
        ttscommit;

while select inventDimCombination
            where inventDimCombination.ItemId == inventTable.ItemId
        {
            itemPriceTmpTable.RetailVariantId = inventDimCombination.RetailVariantId;
 
    ecoResProductMasterDimValueTranslation.clear();
            ecoResProductMasterColor.clear();
            ecoResProductMaster.clear();
            ecoResColor.clear();
            inventDim.clear();

            select firstonly Description from ecoResProductMasterDimValueTranslation
            exists join ecoResProductMasterColor
                where ecoResProductMasterColor.RecId == ecoResProductMasterDimValueTranslation.ProductMasterDimensionValue
            exists join ecoResProductMaster
                where ecoResProductMaster.RecId == ecoResProductMasterColor.ColorProductMaster
                &&    ecoResProductMaster.RecId == ecoResProduct.RecId
            exists join ecoResColor
                where ecoResColor.RecId == ecoResProductMasterColor.Color
            exists join inventDim
                where inventDim.InventColorId == ecoResColor.Name
                &&    inventDim.inventDimId == inventDimCombination.InventDimId;

            itemPriceTmpTable.Description = ecoResProductMasterDimValueTranslation.Description;

            itemPriceTmpTableList.add(itemPriceTmpTable);
        }

        ttsbegin;
        itemPriceTmpTableList.insertDatabase();
        ttscommit;
}

Happy DAXing...

Data Query ranges and query filter in AX

Scenario#01:

static void GetRangeValueOfQuery(Args _args)

{

Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
Bill_Table vendTable;
QueryBuildRange range;
int ct, i;

qbd = query.addDataSource(tablenum(Bill_Table));
queryRun = new QueryRun(query);
queryRun.prompt(); // To Prompt the dialog
ct = queryRun.query().dataSourceTable(tablenum(Bill_Table)).rangeCount();
for (i=1 ; i<=ct; i++)
{
range = queryRun.query().dataSourceTable(tablenum(Bill_Table)).range(i);
info(strfmt(“Range Field – %1, Value – %2”,range.AOTname(),range.value()));
}
range = qbd.addRange(fieldnum(Bill_Table,ItemName));
while (queryRun.next())
{
vendTable = queryRun.get(tablenum(Bill_Table));
info(strfmt(“Item – %1, Name – %2”,vendTable.ItemName, vendTable.CustName));
}
}

Scenario#02:

static void Johnkrish_GetRangeValueOfQuery(Args _args)
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbd;
GeneralJournalAccountEntry vendTable;
QueryBuildRange range;
QueryFilter qf;
GeneralJournalAccountEntry generalJournalAccountEntry;
int cnt, i;

query = new query();
qbd = query.addDataSource(tablenum(GeneralJournalAccountEntry));
queryRun = new QueryRun(query);
queryRun.prompt();
query = queryRun.query();
cnt=query.queryFilterCount();
for (i = 1; i <=cnt ; i++)
{
qf = query.queryFilter(i);
info(strFmt(“Range Field – %1: Value – %2”, qf.field(), qf.value()));
}
while (queryRun.next())
{
generalJournalAccountEntry = queryRun.get(tablenum(GeneralJournalAccountEntry));
info(strfmt(“PostingType – %1, LederAccount – %2”,generalJournalAccountEntry.PostingType, generalJournalAccountEntry.LedgerAccount));
}
}

Scenario#03:

Use the following code in the init method of datasource Test to get the latest data for every record(used groupby on customer)
public void init()
{
   QueryBuildDataSource qbds;
   super();
   qbds = this.query().dataSourceTable(tableNum(Test));
   qbds.addSelectionField(fieldNum(Test, CustAccount));
   qbds.addSelectionField(fieldNum(Test, Date), SelectionField::Max);
   qbds.addGroupByField(fieldnum(Test, CustAccount));
   qbds.orderMode(OrderMode::GroupBy);
}

Scenario#04:

public void executeQuery()
{
    this.queryBuildDataSource().validTimeStateAsOfDate(_dateValue);
    super();
}

If you have an interval, use this instead:

this.queryBuildDataSource().validTimeStateDateRange(fromDate, toDate)

Scenario#05:

public void lookup()
{
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
     QueryBuildRange queryBuildRange2;
     QueryBuildRange queryBuildRange3;

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SalesTable), this);

    if(DateFrom.dateValue() && DateTo.dateValue())
    {
        sysTableLookup.addLookupfield(fieldNum(SalesTable, CustAccount));

        queryBuildDataSource = query.addDataSource(tableNum(SalesTable));

        queryBuildDataSource.addGroupByField(fieldNum(SalesTable, CustAccount));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime));
        queryBuildRange.value(SysQuery::range(this.dboConvertDateToDateTime(DateFrom.DateValue()), dateNull()));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, createdDateTime));
        queryBuildRange.value(SysQuery::range(dateNull(), this.dboConvertDateToDateTime(DateTo.dateValue())));

        queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, InventSiteId));
        queryBuildRange.value(editInventSiteId.text());

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

    }
    //super();