Microsoft Dynamics AX (Archived)

And Not in AOT

(0) Share

Report

Posted on by lukbel

282

Hi all

Running AX 2012 R3 CU9

I have run into an edge case in my query creation. I'm looking to filter off a number of records, based on the value of two of their fields. One is a journal number, one is a string value which may be blank. I'm looking to filter out the blanks, but only when the journal number matches some criteria

Normally in a basic SQL database I would do this with SQL query:

select * from Table1

where (other criteria here)

and not (field1 = "" && field2 Like "___*");

However, I can't work out how to get the AOT syntax to do this. The closest I've gotten is to know that I can wrap Like in a !() to recreate "not like" but apparently that can't be applied to string values.

I don't know if AX has some kind of workaround for this?

Thanks very much in advance for your help

Cheers

Luke

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: And Not in AOT

Hi lukbel,

It should be possible using expression, please refer for details www.axaptapedia.com/Expressions_in_query_ranges

Was this reply helpful? Yes No
lukbel 282 on at

Like (0)

Report

RE: And Not in AOT

Hi ievgen,

I was already looking at this page and can't find this particular case. I don't think it's actually mentioned. Could you pls specify?

Also, to bear in mind I am creating this as a view, so as far as I know it should be done with the built-in AOT filters rather than with X++ code

Thanks

Luke

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report

RE: And Not in AOT

"not (A and B)" should be equal to "((not A) or (not B))", try this range in your query range value

((FIeld1!= "") || !(field2 Like "___*")))

Was this reply helpful? Yes No
lukbel 282 on at

Like (0)

Report

RE: And Not in AOT

Hi

Had already tried this: it excludes values where Field1 = "" regardless of the value of field2, and vice versa.

"not (A and B)" is only equal to ((not A) or (not B)) where A and B only ever = that value you're looking for

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report
RE: And Not in AOT

So I have this dummy table

That's my query range:

((FIeld1!= "") || !(field2 Like "A*")))

That's my job:

Query q = new Query(queryStr(Query3)); QueryRun qr = new QueryRun(q); Table2 t2; while (qr.next()) { t2 = qr.get(tableNum(Table2)); info(strFmt("'%1' '%2'",t2.Field1, t2.Field2)); }

That's result:

You saying that row with "", "BB" will be excluded because Field1 = "", but it is not, what am I doing wrong?

Was this reply helpful? Yes No
lukbel 282 on at

Like (0)

Report

RE: And Not in AOT

I stand corrected!

Sorry - I promise I tried it, and again after you left your reply. But trying it now, it seems to be working.

I eat my words, aha

Was this reply helpful? Yes No