8 Replies Latest reply on Jul 9, 2010 2:24 PM by BillRyan

    Creating Records in Multiple FMP 11 Databases

    BillRyan

      Title

      Creating Records in Multiple FMP 11 Databases

      Post

      Hi, all. I have two separate databases, and I'd like to set it up so that when I enter a record in one of the databases, it automatically creates a matching record in the other database. I don't need them to be related or to share data back and forth... I'm not worried about duplicates or redundant info. I literally just want to create a matching record in the second database every time I create a new one in the first, with no further relation between the two.

      Does anyone know if this is possible? Thanks so much!

        • 1. Re: Creating Records in Multiple FMP 11 Databases
          philmodjunk

          If they aren't related, in what way are they "matching" records?

          Do you want just a new blank record in the other file/table or do you need to enter data from the new record you have just created in the first file/table?

          Even if you don't "need" them to be related, a relationship linking the two tables can be a very easy way to set this up with a script that creates the record in the other table for you.

          • 2. Re: Creating Records in Multiple FMP 11 Databases
            BillRyan

            Thanks so much for your quick response. Yes, there is some data from a few fields that I'd like to copy over to corresponding fields in the second database. What I mean by not "needing" them to be related is that after the initial creation of the record, I don't need them to stay connected and share data. Also, if a record in the second database already exists with identical data, I'm not concerned about the creation of duplicate records with fields that have the same values. I hope that makes sense! Thanks again!

            • 3. Re: Creating Records in Multiple FMP 11 Databases
              philmodjunk

              I'm assuming you have two separate files.

              Write a script that uses the Import Records step in the 2nd file to import records from the first file. If at all possible, use the Matching names option to align fields for the import, but you may have to manually map the import depending on your field names and table design.

              Write a script in the first file that isolates your new record in a found set of one record and then uses Perform Script to perform the import records script in the second file.

              Warning:
              If you can't just use "matching names" when specifying the import in the script, be very, very careful to update and remap the import in this script each time you add or remove fields from your table definitions. Otherwise, there's a nasty bug that can mess up this automated import: Data loss bug : Spontaneous and erroneous import matching of new fields in specified imports !

              If it's just a few fields, you might instead pass the values in a list as a parameter to a script in the second file that works like this:

              Set Variable [$Values; value: Get ( ScriptParameter ) ]
              New Record/Request
              Set Field [ Yourtable::Field1 ; GetValue ( $Values ; 1 ) ]
              Set Field [ Yourtable::Field2 ; GetValue ( $Values ; 2 ) ]

              and so forth...

              • 4. Re: Creating Records in Multiple FMP 11 Databases
                BillRyan

                Thanks so much for this info... this sounds great. Just so I understand correctly, do I need to write scripts for each of the files? Or can I "import" into the second file from a script in the first file?

                • 5. Re: Creating Records in Multiple FMP 11 Databases
                  philmodjunk

                  You will need two scripts. One script sets up the found set for the import and the second must run from the file that will receive the imported records. Since import can modify or add large numbers of records to your database, it's a good idea to make back up copies of both files before you test this.

                  • 6. Re: Creating Records in Multiple FMP 11 Databases
                    BillRyan

                    Okay, thanks, but if that's the case, this unfortunately doesn't really accomplish what I'd like to do... what I'm hoping to do would be something that happens automatically in the background: whenever I create a record in the first database, it automatically adds a corresponding record to the other database (which is a separate file). Is that even possible?

                    • 7. Re: Creating Records in Multiple FMP 11 Databases
                      philmodjunk

                      You can use a script trigger to perform the first script (set up the found set) and that script can use perform script to call the second one (Import the record).

                      Which script trigger is best for this may require some careful thought on how your users will be interacting with your layout. OnRecordCommit will work, but it will also generate multiple duplicate records as you can easily trigger this several times just in the process of entering or modifying data--depending on your layout settings.

                      • 8. Re: Creating Records in Multiple FMP 11 Databases
                        BillRyan

                        Okay, cool. I'll play around with the scripts and see if I can make it work. Thanks for all your help!