13 Replies Latest reply on Aug 3, 2016 5:01 PM by russk68

    Mirror a table

    russk68

      Hi All,

      I want to create 2 tables with 1 mirroring another. So in table 1 when I create a record and enter a value in the field, I want table 2 to automatically create a record with the same value in it's field. I would then want to add other fields that are unique to each. I'm thinking that this would be a calculation.

       

      Any help would be greatly appreciated!

        • 1. Re: Mirror a table
          Johan Hedman

          Why do you need a replica of your table?

           

          I would use a trigger onRecordSave that triggers a script that sets a Variable to unique ID from first table and then have all fields in second table have calculated result from table on, so when you create a new record in table 2 and use Set field to your Variable it will automatically set all other fields

          1 of 1 people found this helpful
          • 2. Re: Mirror a table
            nicolai

            First of all, there is nothing in FileMaker to support this natively. You are probably thinking of SQL creation triggers which can run INSERT, but there are no such triggers in FileMaker.

             

            There are some workarounds, e.g. FileMaker Forum | FMT FIleMaker Community

             

            I would advice to rethink your structure as mirroring data does not seems to be a good idea. Why would not use one table and connect to two other tables to record different information.

            1 of 1 people found this helpful
            • 3. Re: Mirror a table
              beverly

              other fields that are unique to each

               

              seems to me that you have one-to-one relationship between the two and simply setting up the relationship to Allow creation (either way) would 'trigger' the new record in the other. Of course some related field would need to be populated to make the relationship.

               

              beverly

              1 of 1 people found this helpful
              • 4. Re: Mirror a table
                coherentkris

                +1 to johan.

                why you need replica?

                • 5. Re: Mirror a table
                  russk68

                  Hi Johan,

                  After reading your reply and the others too, It sounds like I need to rethink this like Nicolai suggests.

                  Here's an example of what I want to do:

                  I need 2 layouts with identical fields.

                  Layout 1 is for data entry in column A. Column B fields turned off from Browse mode. Layout 2 is vise versa.

                  When someone makes changes in Layout 1 in column A, I want a Record Modification Count only for layout 1. I want layout 2 to have a Record Modification Count as well but only for layout 2.

                  Because my current structure has 2 layouts from the same table, I get the same Rec Mod Count in both layouts as expected. Any thoughts on how to accomplish this?

                  Thank you for replying!

                  • 6. Re: Mirror a table
                    russk68

                    Hi Beverly,

                    That was my thought as well but I couldn't figure how to get the records to create themselves.

                     

                    Thank you for your reply!

                    • 7. Re: Mirror a table
                      nicolai

                      Create two fields to count Modifications, one for each layout and populate them with a Get(RecordModificationCount) with OnRecordCommit script trigger - use different field and script on each layout

                      1 of 1 people found this helpful
                      • 8. Re: Mirror a table
                        russk68

                        I think I get it.

                        I'll give it a go!

                         

                        Thank you very much!

                        • 9. Re: Mirror a table
                          russk68

                          Hi Nicolai,

                          I created a calculation field with the Get(RecordModificationCount)

                          Does the layout setup get the script trigger or the field? I believe it's the layout.

                          I then checked the OnCommitRecord event in the layout setup. What would the actual script for that event be?

                           

                          Thanks!

                          • 10. Re: Mirror a table
                            nicolai

                            russk68

                             

                            Get(RecordModificationCount) will be triggered on both layouts for both fields.

                             

                            Create two number fields, not calculations, e.g. mod_count_1 and mod_count_2

                             

                            create two scripts, e.g. set_mod_1 and set_mod_2

                             

                            set_mod_1:

                             

                            Set Field [mod_count_1 ; mod_count_1+1 ]

                             

                             

                            set_mod_2:

                             

                             

                            Set Field [mod_count_2 ; mod_count_2+1 ]

                             

                             

                            Set the script trigger in the layout settings dialogue for the OnRecordCommit trigger

                             

                            for the first layout to set_mod_1 script

                             

                            for the second layout to set_mod_2 script

                             

                            Try to modify records in both layouts, the fields should count the modifications done on a particular layout.

                            1 of 1 people found this helpful
                            • 11. Re: Mirror a table
                              russk68

                              Works perfect!

                              Thank you for the help and your patience!

                              • 12. Re: Mirror a table
                                nicolai

                                Hey, that's what the community for!

                                 

                                Good luck

                                 

                                Nicolai

                                • 13. Re: Mirror a table
                                  russk68

                                  They're too good to be true!

                                  Why don't I see the ANSWERED button?