web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Join the records of on...
Finance | Project Operations, Human Resources, ...
Unanswered

Join the records of one company with records of another company With AOT query and View

(0) ShareShare
ReportReport
Posted on by OsamaAG 65

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.

I have the same question (0)
  • OsamaAG Profile Picture
    OsamaAG 65 on at

    goshoom Can you help me with this please?

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    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.

  • OsamaAG Profile Picture
    OsamaAG 65 on at

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    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).

  • OsamaAG Profile Picture
    OsamaAG 65 on at

    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?

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 664 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 522 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 303 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans