Combining an AND and an OR search

[mgerney]

I need to do a search that requires a Date Range, a field called Status, and then I need to have 3 other fields be OR fields. For example, I want to input the date as a range, have a default status field (set to ACTIVE). that's the AND condition.

Then I want to look at several other fields: If either of 2 of them are NO, then the row should be included. If a 3rd field is NO, then I need to check a 4th field to see if it is YES or NO. If it is NO, and the first 2 fields are YES, this row should not appear in the result. If it is YES, and the 3rd field is NO, it should appear in the result. If all the fields are YES, the row should not appear. Here are some examples:

Name Date Status FieldC FieldA FieldS FieldI

X In range Active NO NO NO NO Should appear

Y In range Active NO NO NO YES Should appear

Z In range Active YES NO NO NO Should appear

A In range Active NO YES NO NO Should appear

B In range Active YES YES NO YES Should appear

C In range Active YES YES NO NO Should NOT

D In range Active YES YES YES YES Should NOT

So If it is in the date range, and active, and field C or A are a NO, it should appear. If field C and A are a Yes, it should appear only if field I is a Yes and field S is a no.

Can this be done? I could write it as a CASE statement in Access, but don't know Javascript (if that is what would do it) well enough. Can anyone help? Thanks.

[bahar_vm]

Unfortunately at this time it is not possible to have a combination of AND OR query to the table, but it is added to the feature request lists for further considerations.

Best,

Bahar M.

[telly]

Hi @mgerney,

For the Date Range, you may add new criteria and for the other, you may use AND and OR logical operator on the DataPage or View:
- https://howto.caspio.com/tech-tips-and-articles/range-search-finding-data-between-two-numbers-or-dates/
- https://howto.caspio.com/datapages/reports/search-and-filter-comparison-types/