Crosscompany join

Finance and Operations/AX Developer Forum
1

Hi,

is possible to make crosscompany join in AX? I have this X++ code:

PurchTable purchTable;

SalesTable salesTable;

container c = [curExt(), ‘XYZ’];

;

select firstonly crosscompany:c PurchId from purchTable

where purchTable.dataAreaId == curExt()

&& purchTable.PurchId == ‘AAA’

exists join salesTable

where salesTable.dataAreaId == purchTable.InterCompanyCompanyId

&& salesTable.SalesId == purchTable.InterCompanySalesId;

This generate SQL with relations on DataAreaId between tables. Is possible to disable this relations?

SELECT A.PURCHID, A.DATAAREAID, A.RECID
FROM PURCHTABLE A
WHERE ((A.DATAAREAID IN (?,?) )
AND ((A.DATAAREAID=?)
AND (A.PURCHID=?)))
AND EXISTS
( SELECT ‘x’
FROM SALESTABLE B
WHERE ((B.DATAAREAID IN (?,?) )
AND ((B.DATAAREAID=A.INTERCOMPANYCOMPANYID
AND (B.DATAAREAID = A.DATAAREAID))
AND (B.SALESID=A.INTERCOMPANYSALESID
AND (B.DATAAREAID = A.DATAAREAID)))))

Thanks

2

Hi Daniel,

Even though this problem may not be relevant to you any longer, but I will post a reply which may be beneficial to someone else.

I was also struggling with the same issue: the kernel was adding the (B.DATAAREAID = A.DATAAREAID) clause automatically if it detected a join by some fields between those tables. In your case it is
salesTable.SalesId == purchTable.InterCompanySalesId that causes the problem.

If you would change this line to something like salesTable.SalesId == ‘SO-00001’, the kernel would not add the (B.DATAAREAID = A.DATAAREAID) clause and the query would return (some) result.

This discovery lead me to the following workaround: it is possible to “fool” the kernel by changing your select statement in the following way:
salesTable.SalesId == purchTable.InterCompanySalesId+’’.
Yay!!! :slight_smile:

Now I wish I could figure out a workaround for the same problem in a Query…

3

Actually, it works fine with Query also.

You can achieve this by using Query range expression. In this case it would look something like this:

queryBuildRange.value(strFmt(’(%1.%2 == %3.%4+"")’,
qbdsPurcTable.name(),
fieldStr(PurchTable, IntercompanySalesId),
qbdsSalesTable.name(),
fieldStr(SalesTable, SalesId)));

Notice the %3.%4+"" part in expression which does the trick. Kernel query engine ‘thinks’ that is not a join, but a complex expression and doesn’t bother with it :slight_smile:

Same technique can be used if you want to filter by DataAreaId field and avoid the “Filtering at the data source level is not allowed for a cross-company query.” error message.