4 Replies Latest reply on Mar 22, 2011 7:09 PM by MarlenMadsen

    Lookup drop down or popup new window layout list



      Lookup drop down or popup new window layout list



      I'm new to FM but not to relational Database former experiance has been Clarion. 

      I'm Creating a DB simular to the included invoice app but would like to know if it is practical to use a Product name (text field) as my primary Key ID rather than product_ID, as it won't be at all practical to try to remember ID's with a product table of 3000 records, better yet would like to open an other window list to select the product to link with the invoice item line portal. 

      My question is can you create a file relation using text fields rather than num fields and work? I find it odd that I can't limit the size of the text fields to better define the link.

      Has any body have an example app of performing a look up using a second window and how I can trigger it from the portal line and then select item, close lookup window, and add record with related data to the portal.


        • 1. Re: Lookup drop down or popup new window layout list

          I'd strongly advise you to use an auto-entered serial value as your PK. You should never have to remember, or interact in any way, with The PK. It should be invisible to the user (including the developer) if the design is correct. If the user must select a PK value, using value lists with a second field also shown and "Show only second field" should be used.


          • 2. Re: Lookup drop down or popup new window layout list

            Yes I agree as I do hide PK from user but my question remains as in the invoice example when entering product items to the invoice the drop down list comes from the value list using the ID's to establish the link and to inport the product information in the item line portal of the invoice.

            What I would like to do is have the user type the product name maybe even in a dummy field then do a find in the related product table in a new window for user to select the correct product, then have it linked to the invoice. An ID drop down list just  won't cut it. as shown in example.

            Can I do this rather than as in my 1st post please look at the invoice sample and you will see what I mean.

            Thank you for any help

            • 3. Re: Lookup drop down or popup new window layout list

              There are several different widgets you can build into a FileMaker solution to make text based searches with Primary Key values entered as ultimate result of the search.

              One method is to use an auto-complete enabled drop down of your product descriptions with scripted support that handles multiple match results and which then enters the selected item's ID number where needed.

              Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

              Another method is to set up a filtered portal with a filter expression that matches text in the portal table's field to a a search field above it. The fields in the portal can be set up as a button that enter's the PK of the selected item into your line items portal. As the user types in more and more text, the listed items in the portal filter to smaller and smaller lists of matching records. With this method, you'd need to use the OnObjectModify script trigger to run this script to force the portal filter to update with each key stroke:

              Commit Record
              Refresh Window [flush cached join results]

              • 4. Re: Lookup drop down or popup new window layout list

                Thank you so much, as your example was right on. Havn't had time to try it but the scripting makes sence I also found that I can open a window of the products in list mode and drag and drop but then I will have the have the ID's field exposed.

                I'm loving FM more and more.