6 Replies Latest reply on Oct 28, 2009 5:46 PM by kart

    Setting a "trigger" for lookups



      Setting a "trigger" for lookups


      I have upgraded from version 2 to 7. In 2, lookups were easy : I could specify the trigger field in one record (eg invoice number) to automatically import data into various other fields from a record with the same invoice number in a different file. How do I do that in this more sophisticated version? I.e. enter an invoice number and have all of the other information dragged over? I've connected all of the target fields between the two files in the relationships table but how do I set the one critical trigger field to link specific records?

        • 1. Re: Setting a "trigger" for lookups

          Unlike Filemaker 2, you have two options here and it's important to understand the differences:


          Option 1: Looked Up Data (what FMP 2 and 7 can both do)

          In this option, Data from the related table is physically copied into the parent table. If changes occurr in the related table, those changes will not appear in the parent table unless you specifically trigger a "relookup" to once again copy the data over. This is good for things like invoiced unit prices so that changes to a table listing unit prices won't change the price data in past invoices.


          Option 2: Display Data from related table

          In this option, the current values from the related table are displayed. If a change is made to a value in the related table, the change automatically appears in all records in the Parent table that link to that specific related record. No Relookups are needed to update.


          Assuming Option 1 is what you want:

          Let's call your two tables Invoice and Parent. The two tables are related via a field called InvoiceNumber. In Manage | Database | Relationships you should have:


          Invoice::InvoiceNumber = Parent::InvoiceNumber


          For each field in Parent that you want to see data copied over from a matching Invoice record...

          Go to Manage | Database | Fields

          Select the Parent table

          Double Click the field and select the Auto-enter tab

          Click the Looked Up Value check box.

          In the Look up from related table drop down, select Invoice.

          Click the matching invoice field in the list below to highlight it.

          Click OK twice to dismiss the dialogs.


          After you have done this for each such field in Parent, click OK to return to your layouts.

          Select or create a layout with the InvoiceNumber field and each of the Lookup fields from the Parent table

          Select or Enter an invoice number and you should see any values from a matching invoice table appear in the Look up fields.

          • 2. Re: Setting a "trigger" for lookups
               Thanks. I began to try to follow your instructions (yes, option 1 is what I'm after) but have only one table as an option when I go to "relationship". I begin to suspect the problem is that in FP2 my parent and target were called files, which in FP7 have been converted to different databases...does this sound right? That being the case, can I do a lookup from a different database, or do I have to create a new target document in the parent database, or can I import a separate database (previously a "file" in FP7)as a "table"? I am hazy on the difference between "file" and "table" and "database" in FP 7- as I can find "Create new Database" under "File", but not "Create new File" - and the relationship window suggests one file can have numerous tables.
            • 3. Re: Setting a "trigger" for lookups

              In FP2, you had a single file with a single table. Table=file=database.


              In fp7 and later versions, you can place mutliple tables in a single file. Table = File = database is no longer true in every case.


              You can establish a link to tables both in the current file or in external files. I skipped from FMP5.5 to FMP 10, so am not sure if FMP 10 is different from FMP 7 in this regard, but here's how you would do it in FMP 10:


              In Manage | Database | Relationship...


              Add a new table occurrence box by clicking the bottom far left button.

              From the Data source drop down in the dialog that pops up, select "Add Filemaker Data source".

              Find your second file and click open to select it for the new TO box.

              Now link your two tables by dragging from one field in one TO box to a field in the other TO box.

              Now you can click the fields tab and set up looked up auto-enter settings for whichever fields you need to do this.

              • 4. Re: Setting a "trigger" for lookups

                I previously had the two tables with their various fields linked in | Relationships but deleted and started again. Still no luck.


                Specifically- I have a database called "Paintings Receipts" with the painting's title, date painted, dimensions and media, and a database called "Paintings schedule" in which I list a painting's title,date painted, dimensions, media and much more. I want the Paintings Receipt to pull date painted, dimensions and media across from "Paintings Schedule" when I type in the painting's title.


                Under Paintings Receipts | Manage | Database | Relationships I have both tables, Paintings Schedule and Paintings Receipt. Painting title, Date painted, Dimensions and Media are linked. 

                In Painting Receipts | Manage | Database | Fields I have chosen the Lookup option for each field except Painting Title which is not a lookup as I will be typing it in. Under lookup  "Options..." I chose "Looked up value". Under  " Specify " I chose:


                Starting with table  "Painting Receipts"

                Lookup from related table "Paintings schedule"

                I selected the equivalent field in Paintings schedule for each field in Paintings receipt that I want copied over from Paintings schedule.  "Don't copy contents if empty" is ticked.


                When I create a new invoice and type in the title of the painting, nothing happens.


                Am I missing a step...ie the one that in PF2 would have said ...  If "painting title" in "Paintings schedule" = "painting title" in "paintings receipt" then nominated fields in "paintings receipt" will lookup and be inserted from nominated fields in "paintings schedule" ?



                • 5. Re: Setting a "trigger" for lookups

                  You don't have the correct relationship set up. You want to link the two tables by the fields that correctly identify which records match to each other not the values being looked up.


                  Don't do this:

                  "Under Paintings Receipts | Manage | Database | Relationships I have both tables, Paintings Schedule and Paintings Receipt. Painting title, Date painted, Dimensions and Media are linked."


                  Instead, since you "...want the Paintings Receipt to pull date painted, dimensions and media across from "Paintings Schedule" when [you] type in the painting's title." Link the two tables by Painting Title not Media and dimensions. Then your lookups will work.


                  Frankly, I don't see the value in copying this data over from the other table. If these are art works, the media and dimensions data doesn't sound like data that where you would have one set of Media and Dimensions data in one Painting receipt record for a given painting and a different set of media and dimensions for the same painting in a different receipts record.


                  I could be wrong here, but I don't think you need to use a look up at all. You could just place the media and dimensions fields from the painting schedule table on your Painting Reciepts layout--which is quicker/easier to set up in the first place.

                  • 6. Re: Setting a "trigger" for lookups

                    You are right re the nature of the information and the alternative method being appropriate and simpler, but once started on this and having tried and failed to understand it via "Help" I wanted to learn how to do this nonetheless.


                    Thank you for your help - it is now working!