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

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Rodolfo Recalde on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
@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

Was this reply helpful? Yes No
Anton Venter 20,345 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No