Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / And Not in AOT
Microsoft Dynamics AX (Archived)

And Not in AOT

(0) ShareShare
ReportReport
Posted on by lukbel 280

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

  • lukbel Profile Picture
    lukbel 280 on at
    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

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: And Not in AOT

    So I have this dummy table

    58332.2.png

    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:

    58332.2.png

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

  • lukbel Profile Picture
    lukbel 280 on at
    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

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    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 "___*")))

  • lukbel Profile Picture
    lukbel 280 on at
    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

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: And Not in AOT

    Hi lukbel,

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans