AX – D365FO – Avoids deleting a record if it has records related to other tables (with table Relations functionality)

If you want to avoid deleting record on a table that has child record related to other table just follow this instructions. You must create a relation between Table "Al0VendorActivity" and "Al0VendorSearcCriteria". We want to avoid delete record from "Al0VendorActivity" if at least one record exists in the table "Al0VendorSearcCriteria". Create a relation between the … Continua a leggere AX – D365FO – Avoids deleting a record if it has records related to other tables (with table Relations functionality)

D365FO – AX – Multiple tables join with Query AddLink syntax example

This example : Query q;QueryRun qr;QueryBuildDataSource qbds;QueryBuildDataSource qbds2;QueryBuildDataSource qbds3;QueryBuildRange qbr; qbds = q.addDataSource(tableNum(CustPackingSlipJour));qbr = qbds.addRange(fieldnum(CustPackingSlipJour, StatusExportDDTRhiagERA));qbr.value(QueryValue(NoYes::No));qbr = qbds.addRange(fieldnum(CustPackingSlipJour, ReturnItemNum));qbr.value(QueryValue("")); qbds3 = qbds.addDataSource(tableNum(EDIDocumentParametersERA));qbds3.relations(false);qbds3.joinMode(JoinMode::InnerJoin);qbds3.fetchmode(QueryFetchMode::One2One);qbds3.addLink(fieldnum(CustPackingSlipJour, OrderAccount),fieldnum(EDIDocumentParametersERA, CustAccount)); qbds2 = qbds.addDataSource(tableNum(InterfaceWMSParameterERA));qbds2.relations(false);qbds2.joinMode(JoinMode::InnerJoin);qbds2.fetchmode(QueryFetchMode::One2One);qbds2.addLink(fieldnum(CustPackingSlipJour, InventLocationId),fieldnum(InterfaceWMSParameterERA, IntInventLocationId));  qr = new QueryRun(q);   while (qr.next()) { //Insert code here } Produces this SQL output: SELECT *FROM CustPackingSlipJourWHERE (StatusExportDDTRhiagERA = 0 AND ReturnItemNum = '')JOIN EDIDocumentParametersERAON CustPackingSlipJour.OrderAccount = EDIDocumentParametersERA.CustAccountJOIN … Continua a leggere D365FO – AX – Multiple tables join with Query AddLink syntax example

D365FO – AX – How to add TWO links between two datasource in X++ Query

Reference article : https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/188732/how-to-add-two-links-between-two-datasource-in-x-query Here's a job I just wrote. static void Job1(Args _args) { Query q; QueryBuildDataSource qbds; QueryBuildRange qbr; QueryRun qr; str value; InventJournalTrans inventJournalTrans; InventTransOrigin inventTransOrigin; ; q = new Query(); qbds = q.addDataSource(tableNum(InventJournalTable)); qbr = qbds.addRange(fieldNum(InventJournalTable, JournalType)); qbr.value(SysQuery::value(InventJournalType::Transfer)); qbds = qbds.addDataSource(tableNum(InventJournalTrans)); qbds.relations(true); qbds.fetchMode(QueryFetchMode::One2Many); qbds = qbds.addDataSource(tableNum(InventTransOrigin)); qbr = qbds.addRange(fieldNum(InventTransOrigin, InventTransId)); value … Continua a leggere D365FO – AX – How to add TWO links between two datasource in X++ Query

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

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 D365FFO – AX – How joins in X++ select statement are translated into T-SQL

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