Show only records in a Found Set in a Menu Field

I want to be able to populate a Menu field but just use the values in a found set. At the moment it will take all the values from the named field not just the records Found.

Any suggestions as to how this can be achieved.


Richard G Hurley
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If the search criteria is simple, you can do it with a selfrelationship.
define a global field say 'tmp' where you will enter "f"
create a normal field say "found".
create a selfrelationship say 'popup_list' between tmp and "found".
create a list which use "only values from link 'popup_list'
write a script which:
goes to search layout (where you need the field to be searched + "found"
deteles all found fields
search mode, pause
execute search
replace found field with "f" in the found set.
got back to original layout.
If not clear enough, I am posted a tiny example: or depending on which one you use.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can't base a value list on a found set, because found sets can be different for different users, and value lists have to be consistent across all users. You can probably achieve the same result another way though.

The trick is to create a relationship between the tables that has the same effect as doing the find. The easiest way to do this is to make a global field in your main table, then make a relationship between the global field and the field you want to search. When you put a value in the global field, immediately the relationship will show only those records where the search field matches the global field. So typing a value in the global field has basically the same effect as doing a find.

After creating the relationship, create a value list that uses values from the field you want, check the "only related values" option, and choose the relationship you created. Now only the values from records that match your criteria will be included in your value list. If the field your value list is based on is in the same table, just create a self-join relationship.

The field you are searching for must be indexed, but this is not a problem, since value lists based on fields also require the field to be indexed.

This technique can even improve performance, because finding related records through a relationship is usually faster than doing an actual find.

The only limitation of this approach is that the set of records to be found must have something in common. You can't just arbitrarily include or exclude records as you can with a found set.
Whoops, sorry Lesouef!

I started typing my answer, then got called away, and when I got back I finished and sent it, but you had already answered in the mean time. Now I guess we're even... ;)

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

what you do is a link with fields matching exactly the same as the content of the global field, I am not sure this is what he meant; I understood he wanted to have the found set even if the search is not an exact match. If you check my example, it will work even if you search a*.
Drawback: it will be slow as I need to replace the found field everytime, so not suitable for 100000 records...
Lesouef, really your method is a more sophisticated version of mine. You included a dynamic update of the database in your search. I tend not to consider options like that because most of my tables are huge, with multiple active users. For a smaller, single-user database, your approach is definitely more powerful, but I'd be worried about performance and consistency issues in a bigger file.

The important point we both pointed out was the need to base the value list on a relationship showing only the related records, which is half the battle.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.