Hi,
can i use OR with Not Like in a query range?
OR with like works...
(AccountNum LIKE "005*") || (Name LIKE "SGBD*")
...but how change to Not Like
Regards
Michael
*This post is locked for comments
Maybe it is too late. But I just found the similar issue today in AX 4.0.
Since Like and Not Like is not supported,my solution is :not use OR and "not like" explicitly as follows.
CustTable custTable;
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbds = query.addDataSource(custTable.TableId);
//QueryBuildRange qbr = SysQuery::findOrCreateRange(qbds, fieldNum(CustTable, AccountNum));
qbds.addRange(fieldnum(custTable, AccountNum)).value('C904029*');
qbds.addRange(fieldnum(custTable, AccountNum)).value('!C*');
//info(range);
info(qbds.toString());
queryRun = new QueryRun(query);
while (queryRun.next())
{
custTable = queryRun.get(tableNum(CustTable));
info(custTable.AccountNum);
}
The info message is : SELECT * FROM CustTable WHERE ((AccountNum LIKE N'C904029*') OR (NOT (AccountNum LIKE N'C*')))
Replacing the values with fixed data is correct instead of using the SysQuery, does essentially the same.
I am afraid your observation is correct and the syntax is not supported in AX 2009 probably.
You could try to raise a ticket with Microsoft to address the issue, mainstream support is still active until next year:
Hi Vilmos,
in AX 2009 i have no SysQuery::valueLikeAfter.
I think it only add '*' after the string ?
str range = strFmt('((AccountNum LIKE "%1") || (!(AccountNum LIKE "%2")))', 'C904029*', 'C*');
Then i got following as range ((AccountNum LIKE "C904029*") || (!(AccountNum LIKE "C*"))).
This is the same syntax ievgen posted before.
When i run the job, i got nearly the same error, with info all customers.
So, if it works in AX 2012 with my range line, the answer here is:
OR + NOT LIKE in QueryBuildRange don't work in AX 2009.
Ievgen's syntax is correct. I do not have AX 2009 in front of me, but are you sure you have CustTable.Name field? In AX 2012 it is no longer there.
The below code works perfectly fine in AX 2012 as per your requirements. It correctly returns all my customers that start with that specific C-account, and also return all customers which do not start with the C character.
If it does not work for you like this, then it might be a limitation of AX 2009. But first of all I would double-check that Name field.
static void WIK_LikeNotLike(Args _args) { CustTable custTable; Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbds = query.addDataSource(custTable.TableId); QueryBuildRange qbr = SysQuery::findOrCreateRange(qbds, fieldNum(CustTable, AccountNum)); str range = strFmt('((AccountNum LIKE "%1") || (!(AccountNum LIKE "%2")))',
SysQuery::valueLikeAfter('C904029'),
SysQuery::valueLikeAfter('C')); qbr.value(range); info(range); info(qbds.toString()); queryRun = new QueryRun(query); while (queryRun.next()) { custTable = queryRun.get(tableNum(CustTable)); info(custTable.AccountNum); } }
Sorrry but i don't found an example for QueryBuildRange with OR + NOT LIKE
I found many examples before, but nothing with OR + NOT LIKE:
If you have an example syntax, please publish it here.
Please look into the link that i provided. It provides the example of code.
How is the syntax for OR + NOT LIKE in x++ QueryBuildRange.
It's no difference, if i use the syntax in advance filter, or in x++ QueryBuildRange.
Hi,
I believe the answers above is not for query dialog in advance filter. Please try to use the expresion in x++ by select statement or QueryBuildRange.
You can look into this link for example:
Hi Ievgen,
i try it, and got following error.
Same as all my trys before
Hi Michael Niemeyer,
Try this:
((AccountNum LIKE "005*") || (!(Name LIKE "SGBD*")))
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156