Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Cross-company query wh...
Microsoft Dynamics AX (Archived)

Cross-company query where the link exists across companies

(0) ShareShare
ReportReport
Posted on by lukbel 280

Hi all

Running AX 2012 R3 CU9.

Have a query which I need to feed to an SSRS report, but the query involves inter-company purchase and sales orders. I've, through testing, created a query which shows all information for all companies on the sales side through a view, and one which shows all companies' purchase information through a view, but any attempt made to make one view link to the other - or extend the sales query so it links SalesLine and PurchLine tables, results in no records.

I know my logic is correct because I'm testing the same links on SQL Server, and it's been running in Access for 2 years (we're trying to replace it with an SSRS report we can run in batch).

Is there some extra step involved in cross-company queries when the linked fields are in two different companies?

Thanks very much in advance for your help

Luke

*This post is locked for comments

  • lukbel Profile Picture
    lukbel 280 on at
    RE: Cross-company query where the link exists across companies

    Hi Martin

    Thank you for reformatting my log.

    How can I use this query dump to get the answer to my question if the dataareaid filter is applied elsewhere? As that is definitely the source of my issue for reasons stated above

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Cross-company query where the link exists across companies

    Filters of Partition and DataAreaId field are applied automatically by the kernel under the hood; you won't see them in this pseudo-SQL.

    By the way, let me format your code to make it easier to read:

    SELECT SalesId, InventSiteId, CustAccount, PurchOrderFormNum, SalesName FROM SalesTable(SalesTable)
JOIN LineNum, ItemId, SalesQty, CurrencyCode FROM SalesLine(SalesLine)
  ON SalesTable.SalesId = SalesLine.SalesId
JOIN inventBatchId FROM InventDim(InventDim_Sales)
  ON SalesLine.InventDimId = InventDim.inventDimId
JOIN  FROM InventTransOrigin(InventTransOrigin)
  ON SalesLine.InventTransId = InventTransOrigin.InventTransId
  JOIN StatusIssue FROM InventTrans(InventTrans)
    ON InventTransOrigin.RecId = InventTrans.InventTransOrigin
JOIN DeliveryDate FROM PurchLine(PurchLine)
  ON SalesLine.PurchorderFormNum = PurchLine.PurchId
  • lukbel Profile Picture
    lukbel 280 on at
    RE: Cross-company query where the link exists across companies

    Hi Martin

    I created a job which used:

    static void Job12(Args _args)

    {

       Query query = new Query("TEE_ConsignmentCalloff");

       info(query.toString());

    }

    (Perhaps I misunderstood? I thought QueryBuildDataSource would give only for one particular table in a query.) But at any rate, the output was:

    Query TEE_ConsignmentCalloff object 302b9a60: SELECT SalesId, InventSiteId, CustAccount, PurchOrderFormNum, SalesName FROM SalesTable(SalesTable) JOIN LineNum, ItemId, SalesQty, CurrencyCode FROM SalesLine(SalesLine) ON SalesTable.SalesId = SalesLine.SalesId JOIN inventBatchId FROM InventDim(InventDim_Sales) ON SalesLine.InventDimId = InventDim.inventDimId JOIN  FROM InventTransOrigin(InventTransOrigin) ON SalesLine.InventTransId = InventTransOrigin.InventTransId JOIN StatusIssue FROM InventTrans(InventTrans) ON InventTransOrigin.RecId = InventTrans.InventTransOrigin JOIN DeliveryDate FROM PurchLine(PurchLine) ON SalesLine.PurchorderFormNum = PurchLine.PurchId

    (Bear in mind I have only linked on PurchOrderFormNum == PurchId to test my link, it should be returning duplicates etc but isn't returning anything across company.

    From what I can see the SQL matches the logic it's supposed to, however there's no mention of DataAreaIds or company filtering. So I'm not sure where abouts this comes into the process.

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Cross-company query where the link exists across companies

    There are two main approaches:

    1. Use SQL Server tools, such as extended events or profiler, to collect the actual T-SQL query.
    2. Use tools inside AX to get a query string. Your options there depends on how you build the query. If you use select statements in X++, you can use generateOnly + getSqlStatement() to the get the actual T-SQL code. If you use an AOT query or the query framework, you won't get the real T-SQL, but you can get pseudo-SQL query by calling QueryBuildDataSource.toString(). SysDa framework allows both, I think. (I forgot we're talking about AX 2012; SysDa is only in D365FO).
  • lukbel Profile Picture
    lukbel 280 on at
    RE: Cross-company query where the link exists across companies

    Hi Martin

    How can I output the T-SQL query? :O Apologies, I was under the impression I only had the GUI to go on.

    For reference, the cross-company query I'm using involves:

    SalesLine>InventTransOrigin (SalesLine.InventTransId = InventTransOrigin.InventTransId)

    InventTransOrigin>InventTrans (InventTransOrigin.RecId = InventTrans.InventTransOrigin)

    and also

    SalesLine > InventDim (SalesLine.InventDimId = InventDim.InventDimId).

    This returns all records for SalesLines in both of our legal entities so long as the query is marked as AllowCrossCompany. To test it's working, I add it to a view also marked to allow crosscompany, and put that view in a form. (If there's a way of checking cross company records are working that involves less steps than this, pls share)

    However, I need to pull records for PurchLine records in one entity, adding PurchLine with constraint (PurchLine.PurchId == SalesLine.PurchOrderFormNum AND SalesLine.LineNum == PurchLine.LineNumber). In our current non-AX solution, and also on SQL Server writing it in flat SQL, this works fine. But I think the cross-company records are only checking for links within their own company. For example, if I remove the constraint for LineNum = LineNumber, the query will return a test value I entered where the SalesLine and PurchLine had the same value within one company.

    Is there a way I can attach the query I'm building so someone could have a look over it?

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Cross-company query where the link exists across companies

    I suggest you check the actual T-SQL query sent to the database - it'll tell you where the problem lies. It seems that you check only what the query returns, but not what query you've generated.

    Also, it's not clear how you've implemented the link. Consider creating a simplified example demonstrating your problem and sharing it with us.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans