Finding across multiple fields again

[Chrism]

Hi,

I would like to make script to search to display all records if the following Criteria are met

Criteria1  -

Field1 = "y"

Criteria2 

Any 1 of below fields do not have a "y"

Field2

field3

field4

 

Sure its simple

Thanks

Edited September 11, 2020 by Chrism

[comment]

I wouldn't call it simple, but if you restate your requirement as "find all records where Field1 = "y" except those where all the other three fields have a "y", then you could write it as:

Enter Find Mode [ ]
Set Field [ YourTable::Field1; "y" ] 
New Record/Request
Set Field [ YourTable::Field2; "y" ] 
Set Field [ YourTable::Field3; "y" ] 
Set Field [ YourTable::Field4; "y" ] 
Omit Record
Perform Find [ ]

--
Side note: it looks like you're using "y" to indicate "yes" in a status field. It is much better to use a Number field with 1 for true, 0 or empty for false.

 

Edited September 11, 2020 by comment

[Chrism]

Hi,

Thank you, not quite, I probably didn't explain it right!

I want to find all records where field1 is "y"

and omit records if ANY of Field2, Feild3, Filed4 have a "y"

[comment]

12 minutes ago, Chrism said:

omit records if ANY of Field2, Feild3, Filed4 have a "y"

Well, then do:

Enter Find Mode [ ]
Set Field [ YourTable::Field1; "y" ] 
New Record/Request
Set Field [ YourTable::Field2; "y" ] 
Omit Record
New Record/Request
Set Field [ YourTable::Field3; "y" ] 
Omit Record
New Record/Request
Set Field [ YourTable::Field4; "y" ] 
Omit Record
Perform Find [ ]

It could probably be made simpler if we knew what those fields do contain when they do not contain "y". For example, if they are empty, you could find them by:

Enter Find Mode [ ]
Set Field [ YourTable::Field1; "y" ] 
Set Field [ YourTable::Field2; "=" ] 
Set Field [ YourTable::Field3; "=" ] 
Set Field [ YourTable::Field4; "=" ] 
Perform Find [ ]

 

[Chrism]

Thank you, you really know your stuff!

 

It a mixture, they are mainly blank is they do not have a "y", its legacy data that has been imported, no consistency unfortunately!

[comment]

8 minutes ago, Chrism said:

no consistency unfortunately!

Perhaps you should spend a moment or two making it consistent. Otherwise you will be constantly dancing around obstacles.

 

[Chrism]

We are going through the process of updating and using expiry dates instead, so the "y" will be come redundant and removed.