Hi Everyone,
Is there a way I can I join records of Table A that exists in Company A with the records of of Table B that exists in Company B. I want to achieve it with AOT query and view.
I have enabled the Allow-Cross-Company to be True but it does not seem to produce the results in SQL that I am wanting ( expected result mentioned above ).
If anyone could help me with this?
Note: I could achieve this with cross-company with select in X++ code but I want to do it through AOT query and view.
Thanks.
Intercompany is still related to a company, just not the current one. The join can't completely ignore companies.
I suspect that views don't allow you to remove the link over DataAreaId when joining two company-specific tables. At least I didn't succeed when I did a quick test.
goshoom What I am trying to do is get Intercompany Inventory.
The Intercompany Inventory exists in Company A. but I need to join InventSum data ( Intercompany ) with my custom table data that exists in Company B.
Is there any way I can do this through Views?
Your requirement looks logically wrong to me. At least in theory, item ID "X" in company A may be a completely different thing than item ID "X" in company B. Therefore the join over DataAreaId is necessary, to get meaningful data, but then you can query the result across all companies (e.g. you'll get InventSum for "X" in company A and also InventSum for "X" in company B).
Thanks goshoom for the quick reply,
Sorry for the confusions.
Firstly I am on D365.
Secondly, my concise questions would be that how can I remove Filter of DataAreaId when two tables are joined in AOT query ( that query is then used in View ).
For example the query generated is following:
select * from InventSum ins join MyTable mt
on mt.ItemId = ins.ItemId and ins.DataAreaId = mt.DataAreaId
what I don't want is the filter of following in the query:
ins.DataAreaId = mt.DataAreaId
Is there any way I can achieve the above mentioned through AOT query used in a view. ( I have set AllowCrossCompany to True )
Note:
I did find in the documentation to use a Table that has SaveDataPerCompany set to NO as root datasource in order to achieve this but, I am not aware of any table that has SaveDataPerCompany set to NO for my scenario in order to work a way around.
No, not without more information from you.
First of all, specify the version of AX. If you didn't select the AX version because your question is actually about F&O, I'll move your question to the right forum.
Then explain your situation and the problem. "It does not seem to produce the results in SQL that I am wanting" can mean anything and it may by a bug in your implementation doesn't have anything to do with cross-company logic.
For example, show us what SQL query you've generated and explain what you actually want to get instead.
goshoom Can you help me with this please?
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156