How can I define multiple OR clauses in a QueryBuildRange in AX?

Finance and Operations/AX Developer Forum
1

Hello, I’m creating a query in AX where I need to define multiple OR clauses for a single field; in SQL would be something like:

…AND (field1 IN(value1, value2, value3) OR field1 IS NULL OR field1 = ‘’)

This is what I’ve tried in AX: (parameter1 is a string in this format: ‘a, b, c’)

QueryBuildRange.value(strFmt("((field1 == ‘%1’) || (field1 == %2) || (field1 == %3))", parameter1, SysQuery::valueEmptyString, null));

But it does not work! could you help me out? I would highly appreciate it, thank you!

2

Hi,

I had the same problem and i solved it with the following method :

str fieldListRange(FieldName _fieldName, str _value)

{

container c;

int countNumber;

str rangeValue;

c = str2con(_value,

“,”);

for (countNumber = 1; countNumber <= conLen(c); countNumber++)

{

if(rangeValue)

{

rangeValue +=

" || ";

}

rangeValue +=

“(” + _fieldName + " = “” + strLRTrim(any2str(conPeek(c, countNumber))) + “”)";

return rangeValue;

}

And in your queryBuildRange :

QueryBuildRange.value(strFmt("(%1) || (field1 = %2)", fieldListRange(field1, parameter1), SysQuery::valueEmptyString()));

It’s not beautiful but it’s working. If not try to replace field1 by fieldstr(TableId, field1)

QueryBuildRange.value(strFmt("(%1) || (%2 = %3)", fieldListRange(fieldStr(YourTableId, field1), parameter1), fieldStr(YourTableId, field1), SysQuery::valueEmptyString()));

On axaptapedia they said : http://www.axaptapedia.com/index.php/Expressions_in_query_ranges

"Whilst in standard queries you can specify “AA*” or “A,B,C” as criteria, and they will be parsed by Axapta and sent through correctly to the database, these will simply be passed directly through when using the Query Expressions engine. As a result, they will not return the expected results. On a related noted, the use of ‘like’ is not supported, so there is no way to use wildcards in any form. "

I hope this help.