2 Replies Latest reply on Jan 20, 2012 8:30 AM by philmodjunk

    More versatile lookups



      More versatile lookups


      I am new to FileMaker and when looking up values from another file, such as customers, I need to see more than two fields.  Am I doing it wrong or is there a 3rd party function that can be used.  What I need to do is look up a customer from the customers file but since I have many hundreds of them, I need to be flexible on searching for the correct one, selecting it and returning the data to the entry screen.  What would be the proper way to accomplish this.  Any help would be appreciated.



        • 1. Re: More versatile lookups

          The proper way to accomplish this is to use a Primary Key field in your Customer database usually named as something like __kp_Customer. This would be a serial number field that no user would ever see.

          Then for example if you generated Sales Order for that customer it would populate a _kf_Customer number field (again that no user would ever see) in the Sales Order table that contains the __kp_Customer from your Customer table

          Between these two fields your relationship would be based and susequently all lookup data.


          Hope this helps


          • 2. Re: More versatile lookups

            You can define calculation fields that combine data from more than one field and use that combined data for "field 2" in your value list. Thus, you might combine name and address, for example in a caclulation field so that you can then see both in your value list.

            You can also use portals in combinations with scripts to search for data using a variety of partial text matching schemes. You then select a value for your current record by clicking a button in the portal. (Often, the fields in the portal are configured as that button to click so that clicking the portal row performs this script.) The portal approach gives you the flexibility to display data from as many fields are as relevant to your search and to sorth them in whatever order makes the most sense for you data base.

            Here's a demo file that illustrates several "enhanced value list" approaches to this issue: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html