Conduct a find, with multiple exclusions?

[Jhestler]

Hi All,

I need to conduct a found set of records that includes only 1 or 2 values (out of 10 or more) within a given field, excluding all other records.

For example, I'd ideally like a script that would transport the user to a find screen, where the user would be presented with a paused find layout that features only one field: the Salesperson field.

Once the user populates the Salesperson field and presses the continue button, I'd like the database to perform an automatic find of all the records related to that salesperson, but excluding all records except those that reflect a Project Status of "In Development" or "Closed."

I've gotten everything down up until the find.

I don't want the user to have to conduct multiple finds, and would rather the database do everything seamlessly and in the background.

Funny how I see no "not equal" value in filemaker's find mode; surprising!

Can you help?

Thank you!

J

[comment]

Try:

Go to Layout [ Find Screen ]

Enter Find Mode [ Pause ]

Set Field [ Project Status ; "In Development" ]

Duplicate Record

Set Field [ Project Status ; "Closed" ]

Perform Find []

Edited August 26, 2010 by Guest
misunderstood the requirement

[Jhestler]

Whoa!

How many times can I chain the Set Field/Duplicate Record mechanism before it becomes ineffective/cumbersome?

For example, let's say I want it to return 8 out of 10 values in a given field? Sincere apologies, I should've specified. I tried chaining more steps, and the report brought in everything instead...

Thanks, Comment! (good to see you, my friend!)

[comment]

How many times can I chain the Set Field/Duplicate Record mechanism

I haven't tested the limits, but I'd say quite a few - 8 is certainly not a problem.

But for 8 out of 10, it would be more efficient to exclude the remaining two, e.g.

Go to Layout [ Find Screen ]

Enter Find Mode [ Pause ]

New Record/Request

Set Field [ Project Status ; "In Development" ]

Omit Record

New Record/Request

Set Field [ Project Status ; "Closed" ]

Omit Record

Perform Find []

[Jhestler]

That's certainly more efficient, and it returned the proper result, except for one glitch:

It seems that if the value to be omitted contains a word that is contained in another value, then records containing both values are omitted.

For example, if I omit Project Status which is "Executed," then those records with a Project Status of "Partially Executed" are also omitted.

Any way around that? That seems the last issue!

[comment]

This issue is common to all Filemaker finds, not specific to the thread's topic. The default find is for "words that begin with ...". To change the search to exact value, set the field to "==Executed".

Sometimes these situations are better handled by using Go to Related Record[] to create a found set, instead of performing a find.

[Jhestler]

Comment, once again I owe you a virtual Guinness. Thank you! I'll look into the related record means of accomplishing this as well; forest for the trees... very creative suggestion!

[comment]

With all these virtual drinks, I am in danger of becoming a virtualcoholic...

http://fmforums.com/forum/showtopic.php?tid/214752/post/364114/#364114

[Jhestler]

"You brilliant bastard!"

I've totally been where he's at, lol; you're quite amazing, my friend!