Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Show only records in a Found Set in a Menu Field

Posted on 2004-10-25
Medium Priority
Last Modified: 2010-04-27
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
Question by:RICHARDH
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 28

Accepted Solution

lesouef earned 400 total points
ID: 12403685
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:
http://extranet.valblor.com/~lesouef/tt.fp5 or
http://extranet.valblor.com/~lesouef/tt.fp7 depending on which one you use.
LVL 19

Expert Comment

ID: 12404177
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.
LVL 19

Expert Comment

ID: 12404209
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... ;)

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Expert Comment

ID: 12408351
LVL 28

Expert Comment

ID: 12414082
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...
LVL 19

Expert Comment

ID: 12415176
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.

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question