advanced find

[pmusaev]

how do i perform an 'OR' in my find requests? i need to find a range of values in a certain field and another range within another, but they need to be related by an 'OR' statement. any ideas?

[-Queue-]

If you are scripting it, then use the New Record/Request script step for each group of criteria. If you are performing them manually, use Requests->Add New Request.

[pmusaev]

hey -Queue-, i did that mate... what i'm struggling with is this:

one field needs to match a certain set of criteria, as well, another field needs to match it's own set of criteria, without the two fields depending on each other. i need to say FIELD 1 is >0 AND <0 OR FIELD 2 is >0 AND <0

[pmusaev]

maybe it would be better to perform a FIND on a set of records that have already undergone the search procedure... how is this done?

[-Queue-]

Multiple find requests are not related or dependent on each other (except for Omitted ones, which is another subject). So if request 1 is FieldA > 3 and request 2 is FieldB < 1, then any records with FieldA > 3 OR FieldB < 1 will be found.

I'm not sure what you mean by 'FIELD 1 > 0 and < 0', since this is a logical contradiction and an impossibility.

[pmusaev]

because negative values are allowed, what i'm doing is finding all fields that actually contain a value (ie. greater than 0 and less than 0).

and what i'm finding is this... if request 1 is FieldA >0, request 2 is FieldA <0, request 3 is FieldB >0, and request 4 is FieldB <0 then i only get results that match all that criteria, i want results from both, so if something in FieldB does not have it's FieldA greater than or less than 0, it will not show up regardless if i want it to. make sense?

cheers!

[-Queue-]

Then you mean greater than 0 or less than 0. The 'and' was the part that didn't make sense. Why not just use 0 and Omit Record? This will find every record where Field is not 0.

Your empirical results seem to be contrary to FileMaker's Find functionality. Multiple non-omitted requests ARE equivalent to a '1 or 2 or 3' search. I assume there's something you're not telling me that would explain this discrepancy. Using Omit, however, will narrow your requests down to two: Request1--FieldA = 0 [Omit]; Request2--FieldB = 0 [Omit]. Although, if you have fields with possibly NULL values, then you'll need to add two additional requests: FieldA = "=" [Omit], FieldB = "=" [Omit].

[pmusaev]

ok, -Queue- i used your Omit advice (thankya!) but it's still not working. if criteria specified does not match across both fields, then some records are not displayed.

ie.

Request 1 - FieldA = 0 [Omit]

Request 2 - FieldB = 0 [Omit]

The results I get back are records where values in FieldA are NOT 0 AND values in FieldB are NOT ZERO as well as NOT ZERO in FieldA. In this case, no such records exist and I get no results back. Hope I am being clearer. Thank you for your patience!

[-Queue-]

Okay, I understand your problem now. Revert to using your ">0", "<0" criteria for each field, one find request per criterion. So you'll have 4 find requests:

FieldA => "<0"

FieldA => ">0"

FieldB => "<0"

FieldB => ">0"

This should work fine, and is what I was doing before your 'AND's started confusing me.

[Ugo DI LUCA]

Hey there !

Advanced Find ? Really ?

FM has a IsEmpty() which is pretty much answering this request too.

c_checknotEmpty = not isEmpty(FieldA) or not isEmpty(FieldB)

Either script a find on that field entering a "1" or show records in a portal with a relationship 1:1.

[pmusaev]

i am using version 5.5... this isn't happening for me Queue, is that the problem? cheers.

[BobWeaver]

It will work in any version 2.0 and later. Make sure that you are setting up multiple find *requests* within a single find operation, not successive find operations.

Go into find mode.

Enter the first criterion in field A: <0

Select menu item Requests>Add New Request

Enter the second criterion in field A: >0

Select menu item Requests>Add New Request

Enter the third criterion in field B: <0

Select menu item Requests>Add New Request

Enter the fourth criterion in field B: >0

Click the find button (or hit enter)

[-Queue-]

Oh sheesh, I didn't even think about the fact that multiple requests might be interpreted as multiple finds. Thanks for the elaboration on that, Bob.

[pmusaev]

thank you both, it is working great now! cheers. if you don't mind, could i ask another question? i am trying to create a group of scripts that will sort by column. i will add these scripts to buttons at the top of each column. any ideas?

[-Queue-]

Are you asking how to set it up? Manually perform your first sort, create a script with only Sort [Restore, No dialog]. Once you've closed the script, the sort is saved permanently. Then perform your next sort and create the next script. If you ever need to change the sort, perform another one, open the script, close it, and click the 'Replace' option for sort order.

Now, apply each sort script to its relevant column button, and you're done.

Does that answer your question?

[pmusaev]

YOU ARE A GOD! thank you, you're help (and all the others on this post!!!) is greatly apprecited. thank you very much! i do not understand why my search was unsuccessful at first, seems as though i had a syntax problem. thank you all once more and i wish you the best! cheers.