Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Problems building a qu...
Microsoft Dynamics AX (Archived)

Problems building a query with the Query-Object-Framework in AX

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi everyone,

i built a query in SQL Management Studio and now i'm trying to realize this query in a form.

This is the query:

select * from VENDPACKINGSLIPTRANS
   join INVENTTRANSORIGIN on 
       (INVENTTRANSORIGIN.INVENTTRANSID = VENDPACKINGSLIPTRANS.INVENTTRANSID)
   join INVENTTRANS on 
       (INVENTTRANS.INVENTTRANSORIGIN = INVENTTRANSORIGIN.RECID 
        and INVENTTRANS.PACKINGSLIPID = VENDPACKINGSLIPTRANS.PACKINGSLIPID
   where VENDPACKINGSLIPTRANS.QTY > 0
	and INVENTTRANS.STATUSRECEIPT = 2 
	and INVENTTRANS.STATUSISSUE = 0 
	and INVENTTRANS.INVOICEID = ''


I have problems to implement the marked link in AX.

This is the (unfinished) AX-Query:

qbds = this.queryBuildDataSource();
qbds.addRange(fieldNum(VendPackingSlipTrans, Qty)).value(SysQuery::valueNot(0));

qbds = qbds.addDataSource(tableNum(InventTransOrigin));
qbds.addLink(fieldNum(VendPackingSlipTrans, InventTransId), 
             fieldNum(InventTransOrigin, InventTransId));
qbds.joinMode(JoinMode::ExistsJoin);

qbds = qbds.addDataSource(tableNum(InventTrans));
qbds.relations(false);
qbds.clearDynalinks();
qbds.addRange(fieldNum(InventTrans, StatusReceipt))
              .value(queryValue(StatusReceipt::Received));
qbds.addRange(fieldNum(InventTrans, StatusIssue))
              .value(queryValue(StatusIssue::None));
qbds.addRange(fieldNum(InventTrans, InvoiceId)).value(queryValue(''));
qbds.addLink(fieldNum(InventTransOrigin, RecId), 
             fieldNum(InventTrans, InventTransOrigin));
//qbds.addLink(fieldNum(VendPackingSlipTrans, PackingSlipId), 
//             fieldNum(InventTrans, PackingSlipId));
qbds.joinMode(JoinMode::ExistsJoin);

The query works except for the marked fragment.  I wonder how to implement this link.

Any ideas?

Regards

Nils

*This post is locked for comments

  • Anton Venter Profile Picture
    Anton Venter 18,783 Super User 2024 Season 2 on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Hi Nils, you're welcome :-). Yes, you are right, the parenthesis is important.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Problems building a query with the Query-Object-Framework in AX

    @Anton

    The page had a solution, thank you! I'll bookmark this page.

    This way i could reference an additional datasource. The first time i forgot the parenthesis...they seem to be very important. 

    range1 = inventTrans.addRange(fieldNum(InventTrans, DataAreaId));
range1.value('(PackingSlipId == VendPackingSlipTrans.PackingSlipId)');

    Regards

    Nils

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Wow! What a weird way to thank me for helping you!

    I spent my free time explaining you how addLink() works and

    what's wrong in your code and it seems it means nothing for you, if you claim that I "don't want to help anyway".

    I guess you got offended by my statement that you maybe forgot to share some information with us. You shouldn't take it personally; I try to train all users to provide enough information.

    Consider if there isn't any tiny chance that you just get offended too easily...

    I don't have any problem to stop helping you and rather help others, more appreciative people, if you insist on it.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Martin your posts are dismissive and subliminal offensive most of the time. Please don't anwer on my posts again.

    You don't want to help anyway...

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Problems building a query with the Query-Object-Framework in AX

    If you did it, then you didn't have to ask us what's wrong, because you should already know it. And you forgot to share the information with us.

    If you failed to understand it anyway, I hope my explanation helped you to understand it now.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Hi Martin,

    of course i checked the SQL i have generated. The links didn't "translate" correct and thats why i was asking for advice here.

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Problems building a query with the Query-Object-Framework in AX

    You write X++ code generating SQL, but you don't bother to look at what you've generated. Trust me that it's extremely important - you would immediate see how your SQL differs from what you wanted to achieve.

    addLink() accepts field IDs from the current and the parent datasource - that's InventTrans a InventTransOrigin in your case. Your attempt to use fieldNum(VendPackingSlipTrans, PackingSlipId) means that you create a link to a field in InventTransOrigin that has the same fieldId as VendPackingSlipTrans.PackingSlipId (which definitely isn't what you intend). It doesn't magically use a completely different datasource.

    You should be able to achieve with the extended query syntax (if you want to do it all in code).

    PS: Note that there was no reply when I started typing this one, so it doesn't take other replies into account.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Hi Rodolfo,

    i did that already...the marked link is not "translated" properly. I think Anton Venter has the the right idea. I will try that tomorrow and let you know how it worked.

    Regards

    Nils

  • Rodolfo Recalde Profile Picture
    Rodolfo Recalde on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Hi Nils,

    Try to return query.toString() at the end of your code to compare the select statement differences in query AX and SQL Management Studio.

    I hope this helps.

    Regards,

    Rodolfo Recalde

  • Verified answer
    Anton Venter Profile Picture
    Anton Venter 18,783 Super User 2024 Season 2 on at
    RE: Problems building a query with the Query-Object-Framework in AX

    Hi Nils, you can do that with expressions in query ranges. See http://www.axaptapedia.com/Expressions_in_query_ranges.

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