Solved

Show only records in a Found Set in a Menu Field

Posted on 2004-10-25
251 Views
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.

Thanks

Richard G Hurley
0
Question by:RICHARDH
    6 Comments
     
    LVL 28

    Accepted Solution

    by:
    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.
    0
     
    LVL 19

    Expert Comment

    by:billmercer
    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.
    0
     
    LVL 19

    Expert Comment

    by:billmercer
    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... ;)

    0
     
    LVL 28

    Expert Comment

    by:lesouef
    indeed!
    0
     
    LVL 28

    Expert Comment

    by:lesouef
    bill,
    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...
    0
     
    LVL 19

    Expert Comment

    by:billmercer
    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.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    Suggested Solutions

    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…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    5 Experts available now in Live!

    Get 1:1 Help Now