Create a View Using "OR" & "AND"

(0) Share

Report

Posted on by Paul V

166

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

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Suggested answer

Mohit Rampal 12,573 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

GirishS 27,843 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Paul V 166 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Anton Venter 21,110 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Mohit Rampal 12,573 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

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

Was this reply helpful? Yes No
Paul V 166 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,573 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No