• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
  • 3
  • 3
1 Solution
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.
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... ;)

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now