QueryFilter with multiple values

Finance and Operations/AX
1

Hello. I am trying to add a filter to an existing query that should work like a
IN (‘value 1’, ‘value 2’)
in SQL query.
The code that I currently have is:

SysQuery::findOrCreateQueryFilter(queryName, queryName.dataSourceTable(tableNum(tableName)), fieldStr(tableName, fieldName)).value(queryValue(value1));

This works fine for a single value, but as I mentioned, I need it to retrieve records where this fieldName equals to value1 or value2. In the Microsoft documentation, it’s written that the values should be separated by comma, but I think I have tried all the possible options, Visual Studio always showed a syntax error. And I wasn’t able to find any example. Can anyone help?

2

I think the right approach is adding a second filter for the same field.
The documentation you’ve mentioned is for users, not for X++.

3

Thanks for your answer, @MartinDrab , but I don’t think you’re right this time. Adding another filter on the same field simply overwrote the first added filter. I expected it to add another value so that the final WHERE clause looks like this:
WHERE FieldName = ‘value 1’ AND FieldName = ‘value 2’
Which doesn’t make sense, because it wouldn’t retrieve anything. The correct syntax (which I found meanwhile) would be something like this:
SysQuery::findOrCreateQueryFilter(queryName, queryName.dataSourceTable(tableNum(tableName)), fieldStr(tableName, fieldName)).value(queryValue(queryRangeConcat(value1, value2)));
I haven’t tried, but I think it would work the following way as well:
SysQuery::findOrCreateQueryFilter(queryName, queryName.dataSourceTable(tableNum(tableName)), fieldStr(tableName, fieldName)).value(queryValue(value1 + ‘,’ + value2));

4

Let’s test your statement that adding a second filter overwrites the previous filter. Here is a simple test code:

Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(InventTable));

query.addQueryFilter(qbds, fieldStr(InventTable, ItemId)).value(queryValue('A'));
query.addQueryFilter(qbds, fieldStr(InventTable, ItemId)).value(queryValue('B'));

info(query.toString());

And this is the result:

SELECT * FROM InventTable(InventTable_1)
WHERE ((InventTable(InventTable_1).ItemId = N'A')
OR (InventTable(InventTable_1).ItemId = N'B'))

As you can see, there are two filters - one for A and one for B. Therefore this approach does work correctly.
Also, your expectation that the system would use a wrong operator (AND) was incorrect. The actual operator is OR, as it should be.