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, ... / How to join query data...
Finance | Project Operations, Human Resources, ...
Unanswered

How to join query datasource to EITHER its sister datasource OR its parent?

(0) ShareShare
ReportReport
Posted on by TimothyOtt3

I am attempting to join a query datasource to Either its parent OR its sister, but am not getting the desired results.

I overrode the init method of the query and added a range to the subject datasource with a query expression after the super() similar to this:

datasourceRange.value('((whsLoadLine.inventTransId == salesLine.inventTransId) || (whsLoadLine.inventTransId == kitSalesLine.inventTransId))');

SalesLine = Parent DS

    kitSalesLine = Child of SalesLine

    whsLoadLine = Child of SalesLine

The issue is that it is not returning whsLoadLine records for the kitSalesLines.

At this point, my assumption is that this just isn't possible.   I can't make the whsLoadLine a child of the KitSalesLine because it would change the formatting of the XML.  This is an aif document query.

  • Martin Dráb Profile Picture
    Martin Dráb 231,409 Most Valuable Professional on at
    RE: How to join query datasource to EITHER its sister datasource OR its parent?

    I did use similar conditional joins in query objects. But not it AOT queries used by AIF document services.

    I don't know if I've ever did *exactly* the same thing as yourself, therefore I can't give you a definite answer to whether it is possible without actually trying it.

    You can catch the actual T-SQL code by either the profiler or extended events.

  • TimothyOtt3 Profile Picture
    TimothyOtt3 on at
    RE: How to join query datasource to EITHER its sister datasource OR its parent?

    Here is the query and how I attempted to make the join in the init method.  You can see when I test the query that the join is showing in SQL.  I dont have the output here, but it does return loadLine records for the Parent (salesLine) but not for the Sister (kitLine).  Thanks.

    ParentSisterJoin.jpg

  • TimothyOtt3 Profile Picture
    TimothyOtt3 on at
    RE: How to join query datasource to EITHER its sister datasource OR its parent?

    Hi Martin,

    Thanks for the Reply.  I guess there is a fine line between being concise and providing enough details to a question.

    I did debug and check the SQL, which appeared fine to me.  I used each datasource.toString() which showed me the joins for each DB, but I was not able to get the raw sql.  Is there a good method for this? I attempted to use sql profiler, but I could not find this query hitting the DB.

    I did use a query expression with QuiryBuildDataSource.name(), but was just trying to simplify my question by writing it out.

    Yes, the code gets executed.  In fact, it returns the first part of the join, meaning that it does return whsLoadLine records for the Parent (salesLine), but not the sister (kitLines).

    I think the question is simple.  Can you join to both a sister database OR a parent database using a query object?

  • MuthukumaranAX Profile Picture
    MuthukumaranAX 2,901 on at
    RE: How to join query datasource to EITHER its sister datasource OR its parent?

    can you confirm what join type you have used b/w these data sources?

    thanks 

  • Martin Dráb Profile Picture
    Martin Dráb 231,409 Most Valuable Professional on at
    RE: How to join query datasource to EITHER its sister datasource OR its parent?

    You're skipping an important step in debugging. You write X++ code generating some SQL code and then you check what data is returned by this SQL code. But you don't have any idea whether your X++ code generated the right query and if not, what's wrong with it.

    Also, you're hard-coding data source names in your expressions, but are you sure they're correct? Maybe they're named something like whsLoadLine_1. A safer approach is using QueryBuildDataSource.name().

    By the way, have you verified that your code gets executed at all?

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

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

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans