How to add "AND' condition in query range?

Finance and Operations/AX Developer Forum
1

image
image1289×521 17.6 KB

How to add "AND’ condition in query range?
code snippet:
FormDataSource PurchLine_ds = formRun.dataSource(formDataSourceStr(PurchTable, PurchLine)) as FormDataSource;
QueryBuildDataSource qbds = PurchLine_ds.query().dataSourceTable(tableNum(PurchLine));
qbds.addRange(fieldNum(PurchLine, PurchStatus)).value(SysQuery::valueNot(enum2str(PurchStatus::Invoiced)));
qbds.addRange(fieldNum(PurchLine, GSID)).value(SysQuery::value(’’));

Required output: invoiced data with Null value and open order lines

2

Let me format your code, so it’s easier to understand. I’ll also simplify it a bit:

FormDataSource purchLine_ds = formRun.dataSource(formDataSourceStr(PurchTable, PurchLine));
QueryBuildDataSource qbds = purchLine_ds.queryBuildDataSource();

qbds.addRange(fieldNum(PurchLine, PurchStatus)).value(SysQuery::valueNot(PurchStatus::Invoiced));
qbds.addRange(fieldNum(PurchLine, GSID)).value(SysQuery::value(''));

This will use AND operator.
If you, for example, call qbds.toString(), you’ll get something like this:

SELECT * FROM PurchLine(PurchLine_1)
    WHERE ((NOT (PurchStatus = 3))) AND ((GSID = ''))

Isn’t it what you wanted?
I just think that you need SysQuery::valueEmptyString() instead of SysQuery::value(’’).

3

Thank you Martin for the quick reply, rage is acting like OR condition instead of AND condition, used the same query(as u mentioned) Line no 5 and 6 it is not displaying can you pls help attached below screenshot after executing the query

Capture.PNG
Capture.PNG959×719 174 KB

4

Your own screenshot disproves your theory too - the SQL statement contains AND operator, not OR. Also, the data corresponds to what you asked for.
You want to show only lines that have status “Open order” and no GSID. Line 5 isn’t shown because GSID isn’t empty. And line 6 isn’t shown because it doesn’t have the correct status.
If you want to see lines 5 and 6, you seem to want the opposite of what you’re claiming. It seems that you actually want lines that either has “Open order” status OR they have no GSID.