How to create missing records in another company

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;
        VKTmpRecIdFilter    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
        }
VKTmpRecIdFilter 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.

Search

About

DaxOnline.org is free platform that allows you to quickly store and reuse snippets, notes, articles related to Dynamics AX.

Authors are allowed to set their own AdSense units.
Join us.

Blog Tags