11 Replies Latest reply on Jul 23, 2014 3:55 AM by Nehme

    Import from one solution to another

    Nehme

      Hello,

       

      I am using Filemaker Pro Advanced 13 and I was wondering if there is any possibility for updating a solution content.

       

      The problem is the following:

      I have a user who works on a solution in his polyclinic and at home.

      So, if he works on the solution at home, he needs to add his work on the solution that is in the clinic (without the use of Filemaker Server).

       

      I tried to use the following script:

      Import --> Update matching records in found set (matching the serial numbers)

       

      This works very well in case there is one user.

      But the problem is that there are multiple users in the clinic. Those are also adding records.

      And I am ending with 2 records having the same serial Nb (in the clinic and home versions) (and the info will be mixed up)

       

      Any help?

        • 1. Re: Import from one solution to another
          Mike_Mitchell

          The simplest answer is not to use serial IDs, but rather to use UUIDs as key fields. You can generate them by way of the Get ( UUID ) function.

           

          You can further automate the process by using synchronization, either by rolling your own process or using one of the commercial tools available, such as GoZync, MirrorSync, or EasySync.

           

          HTH

           

          Mike

          • 2. Re: Import from one solution to another
            Nehme

            Thanks a lot for this tip: This will prevent mixing 2 new records (in the parent table).

             

            But there will be another problem:

            If a new record is generated in the clinic, and another generated at home, we will end up with 2 records having the same serial Nb after synchronizing.

            And this will end up with mixing daughter tables results (that are relying on serial numbers for filtering and showing data among different records).

             

            Is there any solution?

            And does the commercial tools deal with this problem?

             

            Thank you.

            • 3. Re: Import from one solution to another
              Mike_Mitchell

              That's why you use UUIDs and NOT serial numbers. In any table. Serial numbers are created in serial order (1, 2, 3, etc.). So you can and usually will get duplicates when you're working in multiple copies on multiple devices. But UUIDs are unique. So no matter what device they're created on, you don't get duplicates.

               

              You'll have to go back and convert all your key fields from serial numbers to UUIDs. Assign it in the parent records first, then use the same UUID on all the appropriate child records. (It's usually best to create a second field to hold the new ID temporarily on the parent record, assign that UUID to all the child records, then go back and change the real key field to the new UUID on the parent record.) The Replace Field Contents command is useful here.

               

              Once you convert your existing data, change the key field in the parent record from an auto-enter serial number to an auto-enter calculation using Get ( UUID ). From that point forward, all new records will get a new, unique identifier, whether it's on the main database or on one of the mobile devices.

               

              Mike

              • 4. Re: Import from one solution to another
                Nehme

                This seems perfect.

                I will try it.

                 

                But what I will try to do is simply change the serial from : auto-enter serial to : auto-enter calculation using Get (UUID).

                So, all the previous records will rely on the previous serial, and the new ones will reply on the Get (UUID) [since the user is starting to use the solution at home from now on].

                This should work, shouldn't it?

                 

                But there is no possibility to get same UUID twice?

                What is the theoretical probability of this (especially that some records are created on 2 "different" solutions?

                • 5. Re: Import from one solution to another
                  Mike_Mitchell

                  Assuming you've cleaned up all previous instances of duplicate keys, then yes, that would be okay. (I personally would prefer not to do it that way, simply for consistency's sake, but if you can work with it, there's nothing technically wrong with it.)

                   

                  In theory, it's technically possible to have a duplicate UUID, but the whole point of a UUID is that it be "unique", so the likelihood is extremely small. The scenarios in which it could happen are generally based on automated functions that create large numbers of records in a short period of time, which don't apply to normal user interaction.

                  • 6. Re: Import from one solution to another
                    Nehme

                    I will do this method because I find the other one more complicated and there is a possibility of mistake.

                     

                    I want to thank you very much for your help.

                    You were extremely helpful.

                    • 7. Re: Import from one solution to another
                      Stephen Huston

                      And be sure you have made a full back of your entire solution before updating key fields in your live copy, as any error during the updating of Keys could make it extremely difficult to ever rematch the parent-child records if the key changes are not done in the correct order.

                      1. The parent records need a new field, populated with the new Primary Key while the old Keys are still in use.
                      2. Check that all parent records have a value populated in the new pKey field.
                      3. The child records need a new field created for the new Foreign Key, and to have that field value captured via the old relationship.
                      4. Check that all child records now contain a new fKey value.
                      5. Then the old relationships can be updated to use the new Keys.

                      If you break the existing relationships before the Key values are updated in both tables, your backup copies will probably have to be copied again to get things working.

                      • 8. Re: Import from one solution to another
                        Mike_Mitchell

                        Yes, thank you, Stephen. I forgot to mention the backup part (DOH!).   

                        • 9. Re: Import from one solution to another
                          keywords

                          Re "what I will try to do is simply change the serial from : auto-enter serial to : auto-enter calculation using Get (UUID)"

                           

                          Another potential issue is that UUIDs are text, which will necessitate a change to schema if your existing serials are number values (i.e. a Number field, not simply numerals in a Text field). If your existing serial field is already Text, then you can just switch the entry method and carry on. If it is Number, you will have to change the field type to Text, and then also change any matching foreign key fields. That should work smoothly enough, but you will need to check carefully to ensure it doesn't trip you up.

                          • 10. Re: Import from one solution to another
                            Nehme

                            Thank you Stephen for your help

                            • 11. Re: Import from one solution to another
                              Nehme

                              Yes indeed, I paid attention for this issue.

                              Furthermore, serial numbers I usually use are text, because I try to have unique serial numbers even between different tables, so I put some text (pointing to the corresponding table) in the serial number.

                               

                              Thank you very much for your help