AX – D365FO – How to count affected/updated rows in a Update_recordset statement

How to know how many rows have been updated in a Update_recordset statement? It's simple, you can just use rowCount() method of the table after the update statement like shown below update_recordset inventTable setting ERACommercialGroupId = comunanzaERA.SubGroupId join comunanzaERA where inventTable.ItemId == comunanzaERA.comunanzaCode && comunanzaERA.SubGroupId != ''; info(strFmt("Copy Items from Comunanza ERA Subgroup Id to … Continua a leggere AX – D365FO – How to count affected/updated rows in a Update_recordset statement

AX – D365FO – Update a field value of a table with the value of a joined table in update_recordset

This example shows that the update_recordset statement supports the joining of several tables. Data from the joined tables can be used to assign values to fields in the table that is being updated. static void Join22aJob(Args _args) { TableEmployee tabEmpl; TableDepartment tabDept; TableProject tabProj; ; update_recordset tabEmpl setting currentStatusDescription = tabDept.DeptName + ", " + … Continua a leggere AX – D365FO – Update a field value of a table with the value of a joined table in update_recordset

AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)

CustInvoiceJour custInvoiceJour; CustInvoiceTrans custInvoiceTrans1; CustInvoiceTrans custInvoiceTrans2; while select InvoiceAccount,InvoiceId from custInvoiceJour group by InvoiceAccount outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1 where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId && custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011′) && custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011′) outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2 where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId && custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012′) && custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012′) { info( strFmt("Customer:%1 2011:%2 2012:%3", … Continua a leggere AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)

D365FO – AX – Optimizing Record Inserts with RecordInsertList

Array inserts, sometimes referred to as bulk inserts, are implemented in the kernel. They buffer a group of rows and insert them in a single trip to the SQL data store. This vastly reduces the number of trips, and speeds up inserts by approximately a factor of 2 – 10. You can use RecordSortedList or RecordInsertList to hold your … Continua a leggere D365FO – AX – Optimizing Record Inserts with RecordInsertList

How joins in X++ select statement are translated into T-SQL – D365FFO – AX

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server. 1. join in X++: select AccountNum from custTablejoin TaxGroupId from custGroupwhere custGroup.CustGroup == custTable.CustGroup; CROSS JOIN in T-SQL: SELECT T1.ACCOUNTNUM, … Continua a leggere How joins in X++ select statement are translated into T-SQL – D365FFO – AX