Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Create a View Using "OR" & "AND"

(0) ShareShare
ReportReport
Posted on by 135

Hello

I am trying to create a View on the Customer Grid where I check 4 custom fields.

If 1 or more of the 4 fields are 0, I want to see them in the grid to be displayed,

(DLVOPEN=0) works perfectly in my custom view query.

But I need to check if any of the the other 3 fields are = 0, i.e.  something like the following : 

(DLVOPEN=0 OR DLVCLOSE=0 OR OPERATIONOPEN=0 OR OPERATIONCLOSE=0)

Is there a syntax I can use that will provide this?

Note: functional answers only thank you!

Paul

  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    It should work for different fields as well.

    queryBuildRange.value(strFmt('((%1.%2 == 0) || ((%1.%3 == 0) || (%1.%4 == 0)) || (%1.%5 == 0)))',
        query.dataSourceTable(tableNum(CustTable)).name(), // CustTable %1
        fieldStr(CustTable, DlvOpen), //  %2
        fieldStr(CustTable, DlvClose), // %3
        fieldStr(CustTable, OperationOpen), // %4
        fieldStr(CustTable, OperationClose), // %5
        

    Note: I haven't compiled code, written for reference purpose.

  • Paul V Profile Picture
    Paul V 135 on at
    RE: Create a View Using "OR" & "AND"

    Hello.

    That would work if I was using the same field, but

    I'm using 4 separate fields and need to view the customer grid if 1 or more of these 4 fields are zero

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    It can't be done by SQl scripts, you need customization in x++.  Please check below article.

    https://wp.me/p8wel1-1I

  • Verified answer
    Anton Venter Profile Picture
    Anton Venter 19,493 Super User 2025 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    The comma works for a single field but because the OR is required on different fields, a customisation is definitely required.

    learn.microsoft.com/.../using-expressions-in-query-ranges

    Google "Expressions in query ranges" for more information.

  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    Hi, if you are using advanced filter, you can just give a comma for each value which is nothing but a or condition.

  • Paul V Profile Picture
    Paul V 135 on at
    RE: Create a View Using "OR" & "AND"

    I've tried via advanced sort filter.  If it can be done I need the syntax..  maybe via sql statement?  Again I need the syntac

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    Hi Paul,

    As you mentioned only functional answers, Check whether you can achieve this through "Advance filter or sort option" on the form.

    Thanks,

    Girish S.

  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Create a View Using "OR" & "AND"

    Hi, I don't think it can be done without customization but happy to get corrected here. You are trying to filter on multiple fields if any any field have value zero then display record.

    You can create AND condition without customization to check if all fields are zero then display but it can be OR condition from front end.

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans