Skip to main content

Notifications

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Join the records of one company with records of another company With AOT query and View

    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.

  • OsamaAG Profile Picture
    OsamaAG 65 on at
    RE: Join the records of one company with records of another company With AOT query and View

    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 230,817 Most Valuable Professional on at
    RE: Join the records of one company with records of another company With AOT query and View

    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
    RE: Join the records of one company with records of another company With AOT query and View.

    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 230,817 Most Valuable Professional on at
    RE: Join the records of one company with records of another company With AOT query and View.

    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
    RE: Join the records of one company with records of another company With AOT query and View.

    goshoom Can you help me with this please?

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans