2 Replies Latest reply on Sep 21, 2012 9:06 AM by philmodjunk

    Multiple Lookups, Help Please !



      Multiple Lookups, Help Please !


           Hi All, Manged to get a good looking service report going with lots of little tricks but need help on the last part of the form, which has been bugging me for weeks and cant find an answer.

           I have three columns -  Qty, Items,  Cost.

           this is then repeated ten times. for ten separate items.

           What I'm trying to achieve is some form of look up. from another table.

           Click on the Items box and that in turn produces a drop down list of parts from a separate table ( Parts Table ). When the part is selected it automatically puts in the price in the cost column. ( Cost will come from the Parts Table )

           I assume I should be using the look up command in someway, but don't know how to implement it.

           Any help much appreciated.





        • 1. Re: Multiple Lookups, Help Please !

               I have a very similar issue, so I'm going to jump on this post so as not to create two similar posts.  I'm trying to auto-populate fields for "room information" from a separate table once the user inputs the 'Building Name' and 'Room Number'.  Those two elements should be sufficient to find a unique record that already exists in a static, master-room-list table (and yes, it's already a related table).  Once the user selects a building and room I'd like FileMaker to run a script that finds the record and automatically fills in the rest of the blanks (fields) based on the matching data from that record.  I essentially need to do a "lookup" or "find" with a 2-field criteria, not just a single field.

               Any input on which scripting commands to use would be greatly appreciated.  I've gone about as far as trial and error will take me with no success.  Thanks.

          • 2. Re: Multiple Lookups, Help Please !

                 Sorry Atkins, but your post isn't as similar an issue as you might think. I suggest you make a separate post.

                 @Dave Dave,

                 The basic problem you have is in the structure of your table. You have a structure that will make look ups very difficult to do as you will need to set up 10 different relationships to 10 different occurrences of the same look up table.

                 I suggest that you remove the 10 separate sets of fields and replace it with a related table displayed in a portal. Then a single relationship from this new table to your lookup table can be used with looked up value auto-enter settings to look up information from the look up table.

                 Here's a very simple demo file created by Comment in a different forum that illustrates an invoicing system like yours with this structure and the needed lookup settings to copy a unit price from a table of products: http://fmforums.com/forum/showpost.php?post/309136/

                 Be sure to extract the file from the zip archive before you open it and if you are using FileMaker 12, open the file by launching Filemaker first, then use Open from the file menu to generate a converted copy of the file that you can then examine to see how it is done.