8 Replies Latest reply on May 11, 2014 3:52 PM by wimdecorte

    Changing the data source without breaking realtionships

    PeterWindle

      Hello my fellow filemakers...

       

       

      I have currently built a solution (one file containing multiple tables) which has a table within it for tracking event dates/times. (let's call this FILE A for now)

       

      I have a second file (let's call it FILE B), containing just one table, which is identical to the event table within FILE A.

       

      I'd like to change the relationships that exist in FILE A from one table to point to the table in the external FILE B.

       

      If I just simply change the External File references to point to the external file B, it breaks all the relationship links (despite the fact that all the fields are identical in FILE A's table as they are in FILE B's table.)

       

      So, my question is simple, is there a way of changing the external file references whilst not breaking the relationships?

       

      The only other alternative would be to copy/paste all the layouts/scripts from one file to the other.. either way, it's a painful process... anyone know of a better way?

        • 1. Re: Changing the data source without breaking realtionships
          wimdecorte

          Yes, the table in the new file has to have the same underlying table iD as the original file, and all the fields in the table need to have the same underlying hidden field ID as the original fields.

           

          You can get the table ID and field IDs by using ExecuteSQL() against the FM metatables FileMaker_Tables and FileMaker_Fields

           

          Under the hood, FM does not line-up the relationships by name but by the field IDs used.  Which is how you can safely rename fields without breaking relationships.

           

          Say that in your original table you have 2 fields:

           

          Field1 - underlying ID  = 1

          Field2 - underlying ID = 5

          (this indicates that at some point you had 3 more fields with IDs 2 through 4 but you deleted them

           

          In your new table you actually have to create 5 fields to make sure the relevant new fields have the right IDs:

           

          Field1

          dummy1

          dummy2

          dummy3

          Field2

          • 2. Re: Changing the data source without breaking realtionships
            PeterWindle

            wimdecorte wrote:

             

            Yes, the table in the new file has to have the same underlying table iD as the original file, and all the fields in the table need to have the same underlying hidden field ID as the original fields.

            Ok, here's the thing... I doubt that the table id's are the same. Why? Because although the tables are the same, I copied and pasted the fields from the original table to the new file (with one table).

             

            Am I totally going down a rocky path with no brakes?

            Is the Table ID modifiable ?

             

            Also, my knowledge of SQL is zero, so I'd be lost trying to get anywhere with that...

            • 3. Re: Changing the data source without breaking realtionships
              wimdecorte

              Pasting fields would not have anything to do with the table ID.  If the table was the first one created in each file then they will have the same ID.  Or did you mean that you pasted the table from one file to the other?

               

              If so, as long as the field IDs match up, you would still have to visit each TO and point it to the new table but the rest of the relationship will magically "snap" in place with the right fields matched up.

               

              You don't have to know much SQL: just do

               

              ExecuteSQL( "SELECT * FROM FileMaker_Fields" ; "" ; "" )

               

              That will give you too much (all fields from all TOs), but at lease you can work with the result.

              With a little more SQL you can add a WHERE to ask for just the fields from your intended table.

               

              And besides; ask here and we'll help.  That's why we are here.

              • 4. Re: Changing the data source without breaking realtionships
                PeterWindle

                wimdecorte wrote:

                 

                Pasting fields would not have anything to do with the table ID.  If the table was the first one created in each file then they will have the same ID.  Or did you mean that you pasted the table from one file to the other?

                 

                There's just one thing... in the file with multiple tables, it was NOT the first table created. In the other file, it's the only table, therefore, it will be the first table... any other suggestions?

                • 5. Re: Changing the data source without breaking realtionships
                  wimdecorte

                  wimdecorte wrote:

                   

                    Or did you mean that you pasted the table from one file to the other?

                   

                  If so, as long as the field IDs match up, you would still have to visit each TO and point it to the new table but the rest of the relationship will magically "snap" in place with the right fields matched up.

                   

                  I gave this suggestion earlier...

                  • 6. Re: Changing the data source without breaking realtionships
                    PeterWindle

                     

                      Or did you mean that you pasted the table from one file to the other?

                     

                    If so, as long as the field IDs match up, you would still have to visit each TO and point it to the new table but the rest of the relationship will magically "snap" in place with the right fields matched up.

                     

                    Well, there was some pasting done between the original and the "other" file, however, some changes have been made to the "other" file. When I change the External file reference from either of the files to the table in the other file, it breaks all relationships.

                    • 7. Re: Changing the data source without breaking realtionships
                      gdurniak

                      You could also make a copy of File A,  call it File B,  and point the external data source there

                       

                      then all the hidden ID's will match

                       

                      greg

                       

                       

                      > I'd like to change the relationships that exist in FILE A from one table to point to the table in the external FILE B

                      • 8. Re: Changing the data source without breaking realtionships
                        wimdecorte

                        PeterWindle wrote:

                        Well, there was some pasting done between the original and the "other" file, however, some changes have been made to the "other" file. When I change the External file reference from either of the files to the table in the other file, it breaks all relationships.

                         

                        Sure, it breaks the relationship.  And I've explained why.

                        But when you have the field ID's correct then all you need to do is repoint the TO to the newly pasted table and the fields for the relationship will correct again.

                         

                        From what you are describing you have to take out the pasted table, and create one with the correct field IDs for this to work.  If you have made too many customozations already then this is something to remember for next time.  It's a huge time-saver if you get it right.