Query Stringing "finds" together

[daveny]

Tried to create a find type script stringing together 5 different conditions for 5 different fields together. While using 2 conditions or running separate finds one by one gives me correct answers, trying to write a script for all 5 togetherI get wrong result - sure it is my script writing (dis)abilty. As an example

Have a 6 field database

1st field = Customer Number

need a script that will give me a listing of all the customers that meet following conditions

FIELD(Text) 2 = contains the letter "Y" or is left blank

FIELD(Text) 3 = Contains the letters "N" or "P" or is left blank

FIELD(Text) 4 = Contains anything but the Letter "Y" (or left blank)

FIELD(Number)5 = Contains Any number > 0

FIELD(Text)6 = contains the Letter "Y" only

Would love to see a script that I can use as a learning tool

Thanx

[mr_vodka]

Enter Find Mode []

Set Field [field1; CustomerNumber]

Set Field [field6; "Y"]

Set Field [field5; > 0]

Perform Find []

Enter Find Mode []

Set Field [field4; "Y"]

Omit Record []

New Record/Request

Set Field [field4; "="]

Constrain Found Set []

Enter Find Mode []

Set Field [field3; "N"]

New Record/Request

Set Field [field3; "P"]

New Record/Request

Set Field [field3; "="]

Constrain Found Set []

Enter Find Mode []

Set Field [field2; "Y"]

New Record/Request

Set Field [field2; "="]

Constrain Found Set []

The criteria for the Constrain found set steps could be stored within the step and hence you could use Constrain Found Set [Restore].

Another way would be to create a calc that matches all your criteria and then just do a find for 1.

Field1 = CustomerNumber AND

( Field2 = "Y" or IsEmpty (Field2)) AND

( Field3 = "N" or Field3 = "P" or IsEmpty (Field2)) AND

( Field4 ≠ "Y" or IsEmpty (Field4)) AND

( Field5 > 0 ) AND

( Field6 = "Y" )

BTW, I am assuming that when you say the phrase "Contains the lettter Y" etc, you mean that the field value is "Y" not that the letter Y could reside anywhere in the field.

Also you may want to test this since its off the top of my head and the syntax hasnt been checked.

[daveny]

Thank you for your reply. However I don't think I made myself clear asking the question. I will try again.

Each record does not have to match all the selection criteria that I listed. Matching any one of those criteria may cause it to used or omitted in the final report. Let me try to explain

I have a Layout Called Customer Status.I select that Layout, and I want to test Field 2 and see if its value matches "Y" or "NR" or "P" or is blank then those records I want to use.

Then taking that "survived" records list I want to test Field 3 and if its value is "Y" I want to omit those records from my list, giving me a new "survivors" list.

Taking that list yet again and if Field 4 > 0 then I want to omit those records as well, creating a new list.

Taking that same list and if Field 5 values are not "Y" or Blank I want use those records, giving me a final "survivors" list which I want to print.

Hope that this is clearer, and perhaps you could reply with suggestions

Thanx

[mr_vodka]

Ok I may still be missing what you are trying to do but here.

Go to Layout ["Customer Status"]

// Gives you list one

Enter Find Mode []

Set Field [field2; "Y"]

New Record/Request

Set Field [field2; "NR"]

New Record/Request

Set Field [field2; "P"]

Perform Find []

// Gives you survivor list one

Enter Find Mode []

Set Field [field3; "Y"]

Omit Record []

Constrain Found Set []

// Gives you survivor list two (reduced list of survivor list one)

Enter Find Mode []

Set Field [field4; "≤" & 0]

Constrain Found Set []

// Gives you survivor list three (reduced list of survivor list two)

Enter Find Mode []

Set Field [field5; "="]

Omit Record []

New Record/Request

Set Field [field5; "Y"]

Omit Record []

Constrain Found Set []

[daveny]

Putting this aside for a few days I tried anew - with a simpler approach

I wrote a script basically saying - Check all records

If Field 1 = N; omit the record (reducing the database size by those records)

then check again and

If Field 2 = Y omit those records - thus reducing the database again

then check again and

If Field 3 = Y omit those records as well - thus reducing the database again

and finally if

If Field 4 = or is > 0 omit those records also

Using only the first three steps,all goes well until I add step 4 then I just get the selection made by the last step.

Below is my script

Go to Layout ["Customer Status"]

Enter Find Mode []

Set Field [Field 1; "=N"]

Omit Record

Constrain Found Set []

Enter Find Mode []

Set Field [Field 2; "Y"]

Omit Record

Constrain Found Set []

Enter Find Mode []

Set Field [Field 3; "Y"]

Omit Record

Constrain Found Set []

Enter Find Mode []

Set Field [Field 4; ">0"] (Field is a Number)

Perform Find []

Omit Record

Constrain Found Set []

****

If I remove "Perform Find []" I need to click 4 times to get a wrong result of no records

If I run just the portion with Field 4 - I get a correct answer

Can anyone point me where I messed up

Thank you

[daveny]

Can't believe no one can point out my error - it must be really that obvious and easy....too bad I don't see it

[comment]

I am not sure I understand your purpose well enough, but some things do stand out:

1. Your first process is Constrain Found Set []. This means you start with any arbitrary found set you might have at the moment and progress from there. Your first process should be Perform Find[], and then - if necessary - constrain and expand the results.

2. In the last part, you use the Perform Find[] step. This cancels everything you have done up to this point.

3. Immediately after Perform Find[], you are in Browse mode - therefore, the following steps:

Omit Record

Constrain Found Set []

don't make much sense: the first step omits the record that happens to be the current record, and the second one probably doesn't do anything.

I don't think you need so many finds/constrains - IIUC, you could do this all with 4 requests and one find:

Go to Layout ["Customer Status"]

Enter Find Mode []

# 1

Set Field [Field 1; "=N"]

Omit Record

# 2

New Record/Request

Set Field [Field 2; "Y"]

Omit Record

# 3

New Record/Request

Set Field [Field 3; "Y"]

Omit Record

# 4

New Record/Request

Set Field [Field 4; ">0"]

#

Perform Find []

[daveny]

Thank you for the reply - To Explain Further

Each step in the progression is supposed to shed records -

So if in the first step Field 1 has "N" in 20 records a database of 100 records is now left with 80

The next request asks the leftover database of the 80 records, how many have a "Y " in field 2 and if for argument there are 5 records with that then those 5 records are shed as well and the next request is to a database of the leftover75 records and so on..

PS By shedding I mean only for the purpose of the "found set" report not actually losing them from the database

Edited April 16, 2008 by Guest

[comment]

I believe the script I have outlined above will do exactly what you describe. Your way, though more elaborate, would also work - provided you fix the errors I have pointed out:

Go to Layout ["Customer Status"]

Enter Find Mode []

# 1

Set Field [Field 1; "=N"]

Omit Record

Perform Find []

# 2

Enter Find Mode []

Set Field [Field 2; "Y"]

Omit Record

Constrain Found Set []

# 3

Enter Find Mode []

Set Field [Field 3; "Y"]

Omit Record

Constrain Found Set []

# 4

Enter Find Mode []

Set Field [Field 4; ">0"]

Omit Record

Constrain Found Set []