Источник:
http://daxonline.org/1686-how-to-cre...r-company.html
==============
There is how you can select cross company all customers which does not exist in specific company. This can be adjusted to select eny entity in AX cross company, which does not exist in specific company.
CustTable custTable, custTableCompany; K3TmpRecIdFilter tmpRecIdFilter; insert_recordset tmpRecIdFilter (RefRecId, RefTableId) select Party, TableId from custTableCompany group by Party; while select crossCompany custTable where custTable.DataAreaId != curExt() notexists join tmpRecIdFilter where tmpRecIdFilter.RefRecId == custTable.Party { // insert }
K3TmpRecIdFilter is duplicate of standard TmpRecIdFilter table but with two changes:
- Save Data Per Company: No
- Country Region Codes: clear value
select generateOnly crossCompany custTable where custTable.DataAreaId != curExt() notexists join tmpRecIdFilter where tmpRecIdFilter.RefRecId == custTable.Party; info(custTable.getSQLStatement());
Gives us what we expect to get:
SELECT * FROM CUSTTABLE T1 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID?)) AND NOT (EXISTS (SELECT 'x' FROM tempdb."DBO".t66271IISREGFO1DEV05142404_B47785E49D4546B8A1B4BDDE481AB759 T2 WHERE ((T2.PARTITION=5637144576) AND (T2.REFRECID=T1.PARTY)))) Following select statement will not work as expected:
select generateOnly crossCompany custTable where custTable.DataAreaId != curExt() notexists join custTableCompany where custTableCompany.Party == custTable.Party &&custTableCompany.DataAreaId == curExt();
There is how it will be translated to T-SQL:
SELECT * FROM CUSTTABLE T1 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID?)) AND NOT (EXISTS (SELECT 'x' FROM CUSTTABLE T2 WHERE ((T2.PARTITION=5637144576) AND ((T2.PARTY=T1.PARTY AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T2.DATAAREAID=?)))))
Please notice T2.DATAAREAID = T1.DATAAREAID condition.
Источник:
http://daxonline.org/1686-how-to-cre...r-company.html