Multiple Find Criteria and Blanks

[CatLady03]

I have a need to find records that meet specific criteria, but I want to include those records where any one of those criteria could be blank. How do I arrange the find requests to include blanks in any of the specified fields, as well as those that meet all specified criteria?

I have been told that I'm a beginner or novice user rather than intermediate as I thought. I just don't know how to change it in my profile.

Thanks!!!

[Genx]

Don't listen to what people say :...

In the mean time, the only way i could see of doing this is a fairly boring and annoying find script, but your user will have to push a button to search... Now assuming you have a set group of fields that you want to return even if they are blank we simply do the following in a script:

--Notes--

The fields called "allowedtobeblank" 1 through N are simply your fields that can be blank.

If there are certain fields that you want to never be blank, you would set some global fields prior to creating a new record in the script. Then you would simply set those fields in each of the new requests created equal to your stored global values. If this is going over your head, explain what you want a bit more clearly, and i'll try simplify it a bit more.

If[get(WindowMode) = 1)]

New Record / Request

SetField[allowedToBeBlank1 ; "="]

New Record / Request

SetField[allowedToBeBlank2 ; "="]

.

.

.

New Record / Request

SetField[allowedToBeBlankN ; "="]

Perform Find[]

Else

Show Custom Dialogue ["Error" ; "You aren't in find mode..."]

End If

~Genx

Edited March 21, 2006 by Guest
Script Error

[CatLady03]

Thanks so much for your response!

First, let me say that this was, indeed, a little over my head, although I think I know where you're going with it. I do feel that I need to provide you with a bit more insight into what I'm looking for. To do that, I'll explain how my database works.

The nature of the database is stock screening. We have a universe of stock symbols that we watch. The database is used to screen the data it contains. There is no data entry into this database. It is all fed through import files from Excel on a weekly basis. The data comes from various sources, is formatted for import, and then done so (imported) through scripts.

Not all sources have data for all symbols in our universe, so they are sometimes left blank. What I want to do is include symbols that meet some of the criteria even if others are blank. So if there are three fields in my search and the criteria is met in two, while the third is blank, I still want the record because the criteria in the two fields were met. A stock shouldn't be excluded just because there was no data in one of the fields.

I hope this provides a little more perspective. Please let me know if I need to provide screen shots of my searches or anything else you may need...whatever will be helpful.

Thanks again!

[Genx]

Ok, my question for you is this... A) what is the minimum criteria you require matched... um, i didn't get far past A hehe.

If no-one beats me to it, ill post you a sample file some time tommorow in between lectures hehe.

~Genx

[Genx]

In, the mean time, in case im not understanding you properly, could you post a sample solution for me, or else simply recreate a small portion of it with a few entries in there so i know whats going on... A database sample is worth at least 500 words

~Genx

[CatLady03]

OK, in the attached sample, the first company meets all of the criteria (shown as Default Settings to the right of the respective fields). The second company should be included as well because it meets two of the criteria, even though the third field is blank. I need to know how to include all records that meet all of the criteria, and those that might have blanks in some of the fields used for the "find".

Does this make sense now...??

Thanks!!

Sample_Data.pdf

[Genx]

Ok, I am going to make an assumption for this script, that you want to search over only 2 fields:

VT Status

AI Rating

I only picked them because they were shortest .

Now, we have to make 2 new text fields with global storage (1 for each field we want to search over). I'll call mine gVT Status and gAI Rating.

Now, to allow users to search over our fields we generate a new layout and put our fields gVT Status and gAI Rating on the layout (or just put them somwhere on the current one under a mini heading called search or something).

Anyway, now we generate the script to do our search for us, matching only 1 criteria.

----------------------------------------------

Enter Find Mode[]

#Create a new request and set a field for each global

Set Field[Table::VT Status , Table::gVT Status]

New Record/Request

Set Field[Table::AI Rating , Table::gAI Rating]

Perform Find[]

#Clear Fields

Set Field[Table::gAI Rating , "" ]

Set Field[Table::gVT Status , "" ]

Commit Record[]

-----------------------------------------------

# are just comments. If you want to add more searchable fields, just create an extra global text field for each one, and an extra request and set field step aswell.

Create a button somewhere under your searchable fields called search or something, and attach your script to it.

Hope this helped.

~Genx

[Genx]

P.s. I was scripting from an fm 7 / 8 perspective, there may be a few script step names etc that will vary in 6. (I tried to use comma instead of semi colon as a seperator, and i hope i did it right.. Oh well.

Good luck any way

~Genx

[CatLady03]

Hi Genx,

Thanks for your response. I don't really understand what your script is doing, though.

I'm going to assume that you picked the fields you used arbitrarily and they have nothing to do with my find fields...I simply substitute the field names you used with the real ones.

I believe that you're creating gTEXT fields just to store the contents of the find for the specified field, right?

What I don't understand is what the search criteria is for the gTEXT fields created. I need to understand the logic here. If you can provide me with further insight as to what your script actually does, that will help immensely.

There's another problem, though...the "Commit Record[]" command doesn't exist in Version 6...at least it's not listed as a command in the script writer. Is there something I can use/do instead?

Thanks for the help...

[Genx]

The commit record doesnt really do anything so you don't have to worry about it... Now, as for what my script is doing:

The global fields are simply fields that store your find criteria... One global field for each field you want to perform your search over. They simply store the data temporarily so that it can be called by the script.

Now, in filemaker, multiple find requests can be submitted in one go. I.e. If you had a street directory, and wanted to return the records for 2 different sets of streets you would do the following:

1) Enter Find mode

2) Insert "Leana Court" in to the street name field.

3) Create a new request (just a new record but in find mode)

4) Insert "Brian Crescent" in to the street name field.

5) Click the find button.

What this does is returns the records that have the street name "Leana Court" or the street name "Brian Crescent".

All my script is doing is automating this, so your user doesnt have to enter find mode, enter the first field VT Status, create a new record, enter the AI Rating etc.

I.e. My script just enter's find mode, sets VT Status equal to the global gVT Status that we created, creates a new request, sets AI Rating equal to the global gAI Rating we created and performs the find, in exactly the same way as the street directory example above.

Any more questions, or still not clear, just yell.

~Genx

[CatLady03]

Well, actually, I already understood that much, because I understand how finds work and about entering multiple criteria in a given field. What I'm trying to understand is how this deals with the blanks. How is searching the global fields any different than searching the actual fields? I don't see where your script handles any blank fields. That's the part that's a mystery.

Sorry to be so thick...

Maybe I can put this another way...if I have three fields that I'm searching on, each has it's own set of criteria. It would look like this:

Field 1: criteria or blank

AND

Field 2: criteria or blank

AND

Field 3: criteria or blank

I simply don't know how to write this in a script. I shows these fields and the specific and exact search criteria next for each in the file I attached to a previous post.

I hope this makes more sense...

D

Edited March 27, 2006 by Guest
To further describe my problem.

[Genx]

We are not searching the global fields!

Lol, thats the funniest thing i've heard in a while... anyway, point is, all we are using the global fields for is a TEMPORARY location to store our FIND CRITERIA.

Once again, all my script is doing is pulling the temporary data from the globals, and substitutes it into the appropriate fields...

I.e.

User enters data into global fields

Script executed

Find mode entered

Data transfered from global 1 to actual field 1

New Request created

Data transfered from global 2 to actual field 2

New Request created

Data transfered from global 3 to actual field 3

Find performed...

WE ARE NOT SEARCHING OVER THE GLOBALS... just using them to store our criteria temporarily.

Hope THIS makes more sense

~Genx

[CatLady03]

Guess that really shows that I'm a novice...

I'm sorry, though. Your explanation didn't clear anything up. I just don't understand why I need global fields. Where in your script do you deal with the blanks?? I just don't see that in your script.

I wish I knew a more clear way to explain myself but, obviously, I'm not having any luck. I thought my example would clear up my objective, at least, but I don't see where/how you addressed it.

By the way...laughing is not very polite...there's no need to make me feel stupid about this. I'm just ignorant of all the intricasies of the software...sorry.

[comment]

Field 1: criteria or blank

AND

Field 2: criteria or blank

AND

Field 3: criteria or blank

To put it simply (I have no idea what Genx is after, either), that translates into 8 separate requests that need to be generated in Find mode:

REQUEST 1

Field 1: criteria

AND

Field 2: criteria

AND

Field 3: criteria

REQUEST 2

Field 1: blank

AND

Field 2: criteria

AND

Field 3: criteria

REQUEST 3

Field 1: criteria

AND

Field 2: blank

AND

Field 3: criteria

REQUEST 4

Field 1: criteria

AND

Field 2: criteria

AND

Field 3: blank

REQUEST 5

Field 1: blank

AND

Field 2: blank

AND

Field 3: criteria

REQUEST 6

Field 1: criteria

AND

Field 2: blank

AND

Field 3: blank

REQUEST 7

Field 1: blank

AND

Field 2: criteria

AND

Field 3: blank

REQUEST 8

Field 1: blank

AND

Field 2: blank

AND

Field 3: blank

The script steps required to generate the requests:

Enter Find Mode [ ]

#REQUEST 1

Set Field [ Field 1 ; criteria ]

Set Field [ Field 2 ; criteria ]

Set Field [ Field 3 ; criteria ]

#REQUEST 2

New Record/Request [ ]

Set Field [ Field 1 ; "==" ]

Set Field [ Field 2 ; criteria ]

Set Field [ Field 3 ; criteria ]

... and so on ...

Perform Find [ ]

[LaRetta]

Hi Dog Girl,

I know I'm coming into this thread a bit late but I can't help but ask ... what if a company fails ALL criteria by having blanks in ALL the criteria fields? You don't want them do you? Then in essence, every company that you want should pass at least ONE of your criteria tests?

Each test (it appears from my perspective) should return true in at least one field (searched) which indicates one find request for each criteria; just as most Finds require. Am I missing something here? In fact it seems you shouldn't worry about the empty fields at all if a company passes anywhere ...

I'm intrigued by this thread and would be most willing ... I think there may be alternate approaches if we can get clarity on what you need. I read the entire thread and still don't fully understand your needs. Can you sum up what would determine whether a company passes or fails if a field (or fields) are empty? Can you give me percentages or numbers? One? Two? Out of how many criteria? Is the number of criteria always three?

LaRetta

[CatLady03]

Thanks for taking the time to "interject," LaRetta. I appreciate a different voice on this.

I see where you're coming from and here's the explanation I have. I have eight different screening panels (layouts), if you will, where I search the records for certain criteria. The search fields are different on each of these panels. The sample file I attached to a previous post shows one of these panels and the three fields that I search on...the default settings indicate the exact search criteria used for this particular panel.

As it turns out, most of the panels can include a record even when all of the search fields are blank or meet ALL of the criteria. There are two panels where we do not want the blanks, so I'll simply leave them alone. This could change, but I can take care of the other panels if I know how to do the first one. The sample I attached is one of the panels where all three fields can be blank or meet all of the criteria.

If I need to provide you with other screen shots or whatever to further explain myself, I'll be happy to. I'm just not sure what it is that's not being understood. Please keep in mind that I'm still using Version 6.

Thanks so much for your time!

Debbie

[Oldfogey]

Debbie,

Use Comment's script. It looks tedious but once you're over the RSI, it will be great. It is also readily adaptable for the three situations you describe.

You will have to provide global fields for the users to enter their find criteria.

You might like to think about setting up a calculation field along the lines of

CALC_FLD -> (Fld1 = G_Fld1 or IsEmpty(Fld1)) AND

(Fld2 = G_Fld2 or IsEmpty(Fld2)) AND [ditto for Fld3]

Fld1/2/3 are your fields, G_Fld1/2/3 are the global fields the user enters data in.

This will evaluate to 1 if your criteria are met and 0 if not. Then a very simple search is all that is needed - just find on CALC_FLD = 1.

You also might like to think about a drop down list in the globals using value list based on the relevant fields. This might make things easier for the users to enter criteria.

[LaRetta]

Hi Debbie,

“I have 18 different screening panels”

Create a script for each panel.

“The search fields are different on each panel.”

Store the fields to search within each Panel script itself.

“the default settings indicate the exact search criteria used for this particular panel.”

You can pre-fill with the default search criteria (as I've done in the sample) or even leave it open for a User to change the criteria. You can remove this option if you don't want a User to change the values. Just remove the Pause.

“most of the panels can include a record even when all of the search fields are blank or meet ALL of the criteria.”

All of the search fields are blank or meet ALL of the criteria. One find request, listing every criteria and one find request with each of the criteria blank.

“where all three fields can be blank or meet all of the criteria.”

Does the attached file not do as you request? You can duplicate the script, modify your find, [replace] the find within the script and rename the script to the next panel. You don't need global fields or calculations at all...

blanks.zip

[CatLady03]

Hi LaRetta,

I have responded to your responses in [color:red]red below...

Hi Debbie,

[color:blue]“I have 8 different screening panels”

Create a script for each panel.

[color:red]This is already done...I just didn't mention it.

[color:blue]“The search fields are different on each panel.”

Store the fields to search within each Panel script itself.

[color:red]I already have scripts in place that contain the search criteria. The users can either run the scripts that produce reports based on the predetermined search criteria or they can approach it using the panel I sampled that contains the "reminders" of what the automated reports use as criteria and do their own searches.

[color:blue]“the default settings indicate the exact search criteria used for this particular panel.”

You can pre-fill with the default search criteria (as I've done in the sample) or even leave it open for a User to change the criteria. You can remove this option if you don't want a User to change the values. Just remove the Pause.

[color:red]As stated above, there are preset panels that have the criteria pre-filled. The users never see these panels...they only see the resultant reports.

[color:blue]“most of the panels can include a record even when all of the search fields are blank or meet ALL of the criteria.”

All of the search fields are blank or meet ALL of the criteria. One find request, listing every criteria and one find request with each of the criteria blank.

[color:blue]“where all three fields can be blank or meet all of the criteria.”

Does the attached file not do as you request? You can duplicate the script, modify your find, [replace] the find within the script and rename the script to the next panel. You don't need global fields or calculations at all...

[color:red]I can't tell you how glad I am to hear that I don't need global fields or calculations. It does look like what you did works, so I just need to apply it to my application. I do have a really dumb question, though. I have never used the Set Field command, so I'm not familiar with the syntax. I managed to specify the field (duh), but I can't get it to look like yours. I don't know how to specify the "=" in Version 6. Am I missing something really easy here?? When it brings up the calculation window and I type in "=", it comes back with ""="", and doesn't do what is intended...no records with blanks in the field I specified are being included. Help!

Thank you so much, LaRetta. I appreciate your input VERY much and will let you know how it goes.

Debbie

[LaRetta]

Hi Debbie,

"I don't know how to specify the "=" in Version 6."

I used 6.0v3 to create this test file. I assume you already have the script in place? You need to manually create a find with ONLY your default lines... Enter Find Mode [Restore, Pause ]. When you leave, select REPLACE. The blanks should NOT be stored as part of that find criteria. The script will then add the second request and the Set Field[] steps will set each criteria (while script is running) with [color:blue]"=" and that is exactly how you type it in the Set Field[] calculation box.

LaRetta

[Genx]

How is:

...I want to include those records where any one of those criteria could be blank.

the same as:

...where all three fields can be blank or meet all of the criteria

... i've been writing my scripts on the assumption that your find simply wanted to retrieve records that matched one or more of your criteria...

Oh well, looks like you guys have got it handled, ill um, take my leave , g'luck.

~Genx

[LaRetta]

i've been writing my scripts on the assumption that your find simply wanted to retrieve records that matched one or more of your criteria...

Hi Genx, yes I understand. And that was my initial conclusion when reading the thread. And it STILL may be required here. After my fifth read-through, I realized one thing ... regardless of what was needed, there was probably a simpler way. Because, in performing thousands of finds within the last three years, I have NEVER needed such a complex find script. That doesn't mean it wasn't needed here but it threw a red flag. My questioning wasn't because you guys weren't providing the answers. It was because I saw the dichotomy in the discussion, ie, whether a record is found because it meets one, two or three criteria would be moot - it meets any criteria or it doesn't - or ALL are blank.

REQUEST

Field 1: criteria

New request

Field 2: criteria

New request

Field 3: criteria

New request

all three fields with "="

... would produce the same results as having a record match TWO or THREE criteria. This was what I thought was needed when I first posted. I adjusted it to all criteria in one request (if ALL criteria must be met) but that is a minor tweak. If Debbie wants records if ANY criteria matches, the above will do just that. The blanks were adding a convolution which could instead be ignored (except for the ALL blank Request).

LaRetta

[comment]

Interesting point. But the question if the blanks can be ignored can only be answered by the OP. Certainly,

Field 1: criteria or blank

AND

Field 2: criteria or blank

AND

Field 3: criteria or blank

is NOT the same as:

Field 1: criteria or any value

AND

Field 2: criteria or any value

AND

Field 3: criteria or any value

[CatLady03]

[color:blue]

[color:green]

[color:red]This is an interesting statement to me because in my initial post, I stated that I wanted to meet ALL criteria.

[color:green]

[color:red]I don't believe that meeting all criteria is a moot point...it is the point. It must meet all of the criteria, but I also want to know if there are blanks. I do have new information here, though. I now know that only one field in the sample provided needs to look for blanks in addition to ALL OTHER CRITERIA...SES Signal.

[color:green]

[color:red]I need ALL criteria to match...not ANY. Hopefully, there is no question on that point now...

I really do appreciate everyone who has posted to this question. Your input is invaluable to me. I just keep learning with each new post. I hope I'm not annoying anyone with all my questions, but I need to really understand what everyone is saying so I can fix my problem.

Debbie

[CatLady03]

Thanks for all your help, Genx. I really do appreciate your contributions...

Debbie

[CatLady03]

I assume you already have the script in place? You need to manually create a find with ONLY your default lines...Enter Find Mode [Restore, Pause ]. When you leave, select REPLACE.

[color:blue]I do, indeed, have the script in place with the search criteria stored...I did the find manually (searched for all three criteria) and then Replaced the find in the script when I left it.

The blanks should NOT be stored as part of that find criteria.

[color:blue]I haven't tried searching for the blanks yet, only the predetermined search criteria.

The script will then add the second request and the Set Field[] steps will set each criteria (while script is running) with [color:blue]"=" and that is exactly how you type it in the Set Field[] calculation box.

[color:blue]I finally got the "=" to work by doing a manual find and doing a REPLACE in the script!

This is what I did: I entered all three of my search criteria and then did Ctrl-N and entered the first two search criteria and put an = sign in the third and performed the find. This produced what I'm looking for! Once I ran this manual search, I went into my script and REPLACED the find. I have attached a file containing my script after doing this so you can see what it looks like.

Finally!!

Debbie

SSR_Screener_Script.pdf

[LaRetta]

Well I'm not going to argue what you requested in your first post, because it all is a matter of interpretation. My point was that we were all confused ... and obviously still are. I was simply pointing out MY confusion on your request and why I thought it best to pin it down ... which still isn't done, in my opinion.

BTW, when you say ALL criteria blank, your example script won't give you that ... you only have ONE Set Field [] for SES (for blank) ... not constraining to also the other two criteria fields being blank as well. But you're quite bright and I can tell you're on a roll now and I'm tickled you have it worked out.

No problem Michael, we can agree to disagree.

L

[CatLady03]

[color:blue]

BTW, when you say ALL criteria blank, your example script won't give you that ... you only have ONE Set Field [] for SES (for blank) ... not constraining to also the other two criteria fields being blank as well. But you're quite bright and I can tell you're on a roll now and I'm tickled you have it worked out.

Thanks, LaRetta. I very proud of myself for having figured this out. Sometimes we just can't see the forest for the trees, huh?

As for having only the one Set Field[], I mentioned in Post#200235 (my response to one of your posts) that I had new information and would only need to look for blanks in one field after all. That's why only SES Signal has the "=" in it.

Thanks for your patience with this. I'm sorry it was so hard for me to express what I actually needed.

Take care!

Debbie

:yourock: