Answered

CrossCompany and DataAreaID in X++ job (error)

(0) Share

Report

Posted on by Community Member

I have a job that I run that looks through specific tables using Cross Company -

Since it is grabbing salesline from all the different companies, I want to be sure to link the correct record in all the other tables - so I am using DataAreaID to make sure I only match from like companies. But when I add the DataAreaID lines I get the following error:

Invalid use of the DataAreaId field in a cross-company query.

I have searched the web, but haven't found any information that tells me what I am doing wrong. Can someone point me in the correct direction on this? (I pulled out the partition lines for brevity) Thanks!

WHILE Select crossCompany * From salesLine
where salesLine.createdDateTime >= startOfDateRange
join salesTable
where (salesTable.DataAreaID == salesLine.DataAreaID)
&& (salesTable.SalesID ==salesLine.SalesID )
join inventDim
where inventDim.DataAreaID == salesLine.DataAreaID
&& inventDim.InventDimID == salesLine.InventDimID
join inventTableModule
where inventTableModule.ModuleType == 0
&& inventTableModule.ItemID == salesLine.ItemID
&& inventTableModule.DataAreaID == salesLine.DataAreaID
{

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Arkadia2 427 on at

Like (1)

Report
Copy link

Link copied!

Hello,

Maybe if you try to enclose your "while select" in a changeCompany loop like this:

while select dataArea where !DataArea.isVirtual

{

changeCompany(dataArea.dataAreaId)

{

WHILE Select * From salesLine

...

}

}

You must remove the crosscompany keyword and the join on the dataAreaId fields

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,152 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Let's test your assumption that AX doesn't handle DataAreaId fields in cross-company queries.

I'll use a simpler query:

select generateOnly crossCompany SalesId from st join ItemId from sl where sl.SalesId == st.SalesId;

Now let's look at the actual SQL query (generateOnly keyword above makes this possible):

SELECT T1.SALESID,T1.DATAAREAID,T1.RECID,T2.ITEMID,T2.DATAAREAID,T2.RECID FROM SALESTABLE T1 CROSS JOIN SALESLINE T2 WHERE (T1.PARTITION=5637144576) AND ((T2.PARTITION=5637144576) AND (T2.SALESID=T1.SALESID AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)))

As you see, AX correctly applies DataAreaId in joins, it just doesn't filter the query by the current company. That's why adding the conditions again by yourself would be wrong.

For completeness. this is what you get without crossCompany:

SELECT T1.SALESID,T1.RECID,T2.ITEMID,T2.RECID FROM SALESTABLE T1 CROSS JOIN SALESLINE T2 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'dat')) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'dat')) AND (T2.SALESID=T1.SALESID))

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Ah! Now it makes complete sense. Thanks!

Was this reply helpful? Yes No
DaveCline 93 on at

Like (0)

Report
Copy link

Link copied!

Martin, if I may...

If one wanted to perform an "intercompany" lookup (where one had data in another company -- workflow specifically) is there a way to avoid the query engine automatically adding the T2.DATAAREAID = T1.DATAAREAID?

I've got this very problem.

I created the SQL in SQLServer and it performs my "cross company" lookup (field X in table T1(DAT) needs to be used in field Y in table T2(MAST)). Every time I try to either hand code a query, or design one, T1 and T2 get erroneously joined with DATAAREAID.

What I ended up doing (not ideal but I had no choice) was to work the lookup into X++ during the temp table population (for a report), using changeCompany().

Thoughts?

-DC

Was this reply helpful? Yes No