Skip to main content

Use temporary table as form datasource in D365FO

In this article, we will learn how we can use the temporary table as a form datasource in D365. In our scenario, we will populate the temporary table at runtime and bind it with the form datasource. firstly, we will pass an argument on the clicked event and write the form datasource  init  method to populate the data in it.  In our scenario we will work on the CustTable form, we will populate the customer group in the temporary table which we create on the selected customer while clicking the button. Step 1:  Create a new table of type TempDB and add two new fields i.e., CustGroupId and CustGroup. Step 2:  We will create a new form with the pattern (List Type), then add our previously created temporary table as a datasource, and then drag both the fields CustGroupId and CustGroup on the form grid part. After the form creation, we will also create the display menu item of type  form  and use our form on it. Step 3:  We will create an extension of the form ' CustTable ' and creat
Post a Comment

Inserting Data in Table using Query::insert_recordset

When dealing with performance, there is a query system control named Query::Insert_Recordset. This allows multiple records to be inserted into a table in the same way the Insert_Recordset command works. This will also eliminate the need to loop over hundreds of records, as well as roundtrips between the SQL server and the AOS server. 

Here is a quick demo of how it works: 

Query                       query = new Query(queryStr(MDTrvExpenseDetailsQuery));
QueryRun                    queryRun;
QueryBuildDataSource        qbdsTrvExpTrans,
                            qbdsTrvExpTable,
                            qbdsProjTable,
                            qbdsHcmWorker,
                            qbdsDirPerson;
Map                         fieldMapping;
        
HcmPositionWorkerAssignment hcmPositionWorkerAssignment;
HcmPosition                 hcmPosition;

MDTrvExpenseDetailsContract contract = this.parmDataContract() as MDTrvExpenseDetailsContract;
paymentStatus = contract.parmPaymentStatus();

query.clearAllFields();
qbdsTrvExpTable = query.dataSourceTable(tableNum(TrvExpTable));
qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, ExpNumber));
qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, PaymentStatus));

qbdsTrvExpTrans = query.dataSourceTable(tableNum(TrvExpTrans));
qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, AmountMST));
qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, ApprovalStatus));

qbdsProjTable = query.dataSourceTable(tableNum(ProjTable));
qbdsProjTable.addSelectionField(fieldNum(ProjTable, ProjId));
qbdsProjTable.addSelectionField(fieldNum(ProjTable, Name));

qbdsHcmWorker = query.dataSourceTable(tableNum(HcmWorker));
qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, PersonnelNumber));
qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, RecId));

qbdsDirPerson = query.dataSourceTable(tableNum(DirPerson));
qbdsDirPerson.addSelectionField(fieldNum(DirPerson, Name));

//Add range on the Payment status of travel expenses table
qbdsTrvExpTable.addRange(fieldNum(TrvExpTable, PaymentStatus)).value(queryValue(paymentStatus));


fieldMapping = new Map(Types::String, Types::Container);
//Specify the mapping between target and source
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Amount), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, AmountMST)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ApprovalStatus), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, ApprovalStatus)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ExpNumber), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, ExpNumber)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PaymentStatus), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, PaymentStatus)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjId), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, ProjId)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjName), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, Name)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PersonnelNumber), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, PersonnelNumber)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Worker), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, RecId)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Name), [qbdsDirPerson.uniqueId(), fieldStr(DirPerson, Name)]);

Query::insert_recordset(mdTrvExpenseDetailsTmp, fieldMapping, query); 
Firstly I have also mapped the position ID within the fieldmapping but due to the error below, I reverted the logic and overcame this with update_recordset: 
Cannot insert multiple records. The SQL database has issued an error. Cannot insert the value NULL into column ‘ ‘, table ‘ ‘; column does not allow nulls. INSERT fails.
It is thrown when no corresponding record is found in the outer joined table and thus null value is fetched and tried to be inserted into the Dynamics AX table column, as the Dynamics AX table structure does not allow nulls (providing constraints for adding default values for columns).

SQL statement: INSERT INTO … SELECT … containing null values cannot be executed successfully.

To overcome the above limitation we have used update_recordset to add the position ID in our table: 
update_recordset mdTrvExpenseDetailsTmp
    setting PositionId = hcmPosition.PositionId
    join hcmPositionWorkerAssignment
        where mdTrvExpenseDetailsTmp.Worker == hcmPositionWorkerAssignment.Worker
    join PositionId from hcmPosition
        where hcmPositionWorkerAssignment.Position == hcmPosition.RecId;

Comments

Post a Comment

Popular posts from this blog

Use temporary table as form datasource in D365FO

In this article, we will learn how we can use the temporary table as a form datasource in D365. In our scenario, we will populate the temporary table at runtime and bind it with the form datasource. firstly, we will pass an argument on the clicked event and write the form datasource  init  method to populate the data in it.  In our scenario we will work on the CustTable form, we will populate the customer group in the temporary table which we create on the selected customer while clicking the button. Step 1:  Create a new table of type TempDB and add two new fields i.e., CustGroupId and CustGroup. Step 2:  We will create a new form with the pattern (List Type), then add our previously created temporary table as a datasource, and then drag both the fields CustGroupId and CustGroup on the form grid part. After the form creation, we will also create the display menu item of type  form  and use our form on it. Step 3:  We will create an extension of the form ' CustTable ' and creat
Post a Comment
Read more

Deploy Package to LCS for Dynamics 365 Finance & Operation

In this blog, we will learn how to extract deployable packages from the Azure DevOps Pipeline and then deploy the specific package to the LCS Standard Acceptance Testing (UAT) Environment. Extract Package from Pipeline and Upload to LCS Step 1 : Go to Azure DevOps ->  Pipelines ->  Recent ->  10.0.37-Gated Build Step 2 : Go to recent check-in Step 3 : Go to published Step 4 : Click on AXDeployableRuntime_7.0.7068.67_2023.12.6.1.zip Step 5 : After downloading the Deployable package Go to Lifecycle Services -> Asset library Step 6 : Go to Software deployable package Step 7 : Navigate and select Software deployable package and select the  + button to add the package to the folder and confirm. Refresh the screen and wait for the package to get validated, the tick will appear once validated. Note: The package will be successfully uploaded to the LCS Environment after the above step. Now we will deploy this package to the Standard Acceptance Testing (UAT) environment. Deploy Pack
Post a Comment
Read more

Using Complex Ranges on Date Fields in Query

In some of the scenarios,  we want to filter out the from-date and to-date ranges on the basis of different fields and  we are not getting the outcome in which we want to apply the AND/OR clause between date ranges on different date fields and we cannot acquire this result using out-of-the-box query classes. So we have applied complex date ranges to get the desired outcome: void initQuery() { Query                          query; QueryRun                queryRun; QueryBuildDataSource      qbdsProjPeriodLine; QueryBuildRange rangePeriodId; query = queryRun.query(); qbdsProjPeriodLine = query.dataSourceTable(tableNum(ProjPeriodLine)); if (periodFrom && periodTo) { qbdsProjPeriodLine.addRange(fieldNum(ProjPeriodLine, PeriodFrom)).value(strFmt('((%1.%2 >= %4) && (%1.%3 <= %5))',                                  qbdsProjPeriodLine.name(),
Post a Comment
Read more