3 Replies Latest reply on Sep 13, 2012 2:32 PM by philmodjunk

    table access in a script



      table access in a script


           I've read help and another manual but still cannot figure this out.  I think I need a trigger to run on commit of a record in a table after data entry which contains fields that can be compared in another table that has a many-to-many relation to the first.  The trigger script needs to look up stuff in one of the  tables to make a join table entry.  So,  I have a field to search in "the other table" for which I have the content I need to find the record with the primary key that is to be the foreign key in the join table.  So,  how do I write a script to search (do a find on) a table for a record and then access fields in the found record?

           (I hope that was clear?)

        • 1. Re: table access in a script

               Technically, you never directly specify a table in FileMaker though it will look and feel as though you do. What you do instead, is select a layout--which refers to a table occurrence (one of the "boxes" in manage | database | relationships) that in turn refers to a table. This is true for manual interaction with your database and also for scripts. FileMaker can make this all a bit confusing because when you add a new table to your database, FileMaker automatically creates a table occurrence linked to that table, a new layout linked to that new table occurrence and names them both exactly the same as your new table.

               What you do in your script is something akin to this:

               Freeze Window
               go to layout [select a layout here that specifies an occurrence of the table you want in Layout Setup | Show Records From]
               //do the stuff you need to do with this table such as performing your find
               Go to layout [original layout]

               Often, you also need to put data into a variable--such as the data you want to use in performing your find, just before you switch layouts as the change in "context" may make the data in fields of the first layout's table unavailable or references to those fields may actually refer to data in a different record of that table. (This is controlled by the relationships to the new layout's table occurrence.)

               Also, beware of any script triggers on either layout that might be "tripped" by such a layout change. OnLayoutEnter, OnLayoutExit, OnRecordLoad, OnObjectEnter, OnObjectExit are all triggers that can be tripped by such a script.

               Note that this is not the only way to access data in that other table. You may not need to perform any find at all if you are able to set up a relationship between your original layout's table occurrence and an occurrence of the second table that matches directly to the record with the data that you need. In such cases, you can simply refer to fields from that related table occurrence to get the data you need.

               To learn more about Table Occurrences and how they affect the function of your databases, see this thread: Tutorial: What are Table Occurrences?

          • 2. Re: table access in a script

                 So the "do the stuff..." would include

                 1. find the record with key info I need,

                 2. save that info in a script variable,

                 3. go to the layout for the join table,

                 4. add a new record for the new join,

                 5. return to the original with a go to layout, but what about to the same tab in the original display the user was working with?

                 Would not the scenario in your last paragraph be associated with a one-to-one relationship (I also have one of those in my DB).  I really do have a many-to-many relation with which I must deal so I have created a join table relating the primary keys of the two many-to-many tables.

            • 3. Re: table access in a script

                        what about to the same tab in the original display the user was working with?

                   If you give the tab panel an object name, the script step Go to Object may be used to bring that tab panel to the front. (Use the Name box at the top of the Inspector's position tab to give a layout object an object name.) And there are ways to detect what tab panel is in the front when the script starts up and to then save its object name in a variable should that be necessary. (This part is many times simpler in FileMaker 12 than in older versions.)


                        Would not the scenario in your last paragraph be associated with a one-to-one relationship

                   but of course, but you are not limited to a single relationship between two tables. By creating additional occurrences of the same table, it may be possible to establish an additional, one to one relationship matching to the record that holds the data that you need. Please note that this may or may not be possible in your case.