Microsoft Dynamics AX (Archived)

How to create AX Query Range like SQL where

(0) Share

Report

Posted on by ATMA-Jen

319

Hello,

I need to create a AX query for user convenient to use.

so I need a AX query like SQL:

select PRODID , ItemId , REMAINBOMFINANCIAL ,QTYBOMCALC
from PRODBOM as PB
where REMAINBOMFINANCIAL > QTYBOMCALC

I try and no way in AX query Range like SQL where REMAINBOMFINANCIAL > QTYBOMCALC

someone Table.Field1 > Table.Fiel2

Please help and thanks in advance.

BR, Jen

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

This article explains how to use expressions in query ranges: docs.microsoft.com/.../using-expressions-in-query-ranges

Was this reply helpful? Yes No
ATMA-Jen 319 on at

Like (0)

Report

Hello Nikolaos,

I saw this article, but I still can't get the way (expression how to write).

I have 2 question:

1. I can use AX AOT Query the Value Field to expression??

or Just can use X++ to build the Query in a Job

2. I make AX Query Form or SSRS report , how to join the Job-Query??

please help & thanks in advance.

Br, Jen

Was this reply helpful? Yes No
ATMA-Jen 319 on at

Like (0)

Report

Hello,

Please tell me how to setup the AX AOT Query Range's Value field to filter the data that RemainBOMFinancial >QtyBOMCalc

thanks in advance.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

1. No, it's not possible. You can use expressions in query ranges only in x++

2. With SSRS reports you can apply the range with x++ in the data provider class, or use an x++ select statement to populate a temp table and use that temp table as the data source for your report.

Was this reply helpful? Yes No
ATMA-Jen 319 on at

Like (0)

Report

Hello Nikolaos,

I still don't know how to do ?

Have any case study to learning about this case?

thanks.

BR, Jen

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

The Microsoft article that I shared contains examples on how to add "complex" ranges in queries in x++.

If you still have difficulties, please give more details about your use case and our existing code. It helps us to help you.

Was this reply helpful? Yes No
ATMA-Jen 319 on at

Like (0)

Report

Hello Nikolaos,

My purpose is very simply.

I need put the SQL Data in AX for user just one click to use.

SQL:

select PRODID , ItemId , REMAINBOMFINANCIAL ,QTYBOMCALC

from PRODBOM as PB

where REMAINBOMFINANCIAL > QTYBOMCALC

Please tell me how to do? step by step.

I am really junior in AX.

Thanks in advance.

BR, Jen

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
Query query = new Query(); QueryBuildDataSource qbds = query.addDataSource(tableNum(ProdBOM)); qbds.addRange(fieldNum(ProdBOM, RemainBomFinancial)).value(strFmt('(%1.%2 > %1.%3)', qbds.name(), fieldStr(ProdBOM, RemainBomFinancial), fieldStr(ProdBOM, QtyBomCalc))); info(query.toString());

This prints to infolog:

Query object 10aa8230: SELECT * FROM ProdBOM(ProdBOM_1) WHERE (((ProdBOM_1.RemainBomFinancial > ProdBOM_1.QtyBomCalc)))

Was this reply helpful? Yes No
Dipen Sorathiya 555 50 on at

Like (0)

Report

hello

i create one form and give ranges in execute query method .

the issue is in my table 2 fields approver 1 and approver 2.

so I have to check that if which user runs a form that user name is either in approver 1 or approver 2 then that line is shown in form otherwise not

so my question is how can I give ranges 2 different fields of one table with same value using OR condition please suggest any idea

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

Like (0)

Report

Dipen, we're just discussing an identical requirement in How to add range on multiple field with same value.

Was this reply helpful? Yes No