3 Replies Latest reply on Sep 30, 2013 4:24 PM by Mike_Mitchell

    change table for instance yet match fields?

    njem

      I have two tables that should have been combined all along. I can fix that if I can get this straight.

       

      I can import all data from tbl1 into tbl2. I can make sure all the field names names match between the two (before the import). Then I could find each instance of tbl1 and tell it to instead switch to sourcing from tbl2. That way a complex set of instances and relationships and such doesn't need to be disrupted. It almost works but layouts that show fields from one of these changed instances gets the fields mixed up. I can see that there is some relationship to field order. If tbl1 had Name in position 4 and tbl2 has Address in position 4 then a display field that should be Name shows Address instead. I tried just manually moving field order around to match but it didn't help. (That is, went to a backup before any of these changes, moved the fields first, imported, then change the source table for the instance.)

       

      I suspect under the hood FM is keeping its own table of what field numbers go with what fields. That's probably how it can keep track of, for instance, chaning the name of a field in the data half of a data-separation set up and yet fields in the UI half don't get confused by that. FM is tracking the fields by some Field ID# under the hood or something.

       

      But once they're given that number on creation then moving them around in order doesn't change it. Is this what FM is doing? Is there a way to see FM's own table under the hood? Is there a way to change the source table for an instance and have it find the right fields from the new source?

       

      Thanks,

      Tom

        • 1. Re: change table for instance yet match fields?
          BruceHerbach

          Hi Tom,

           

          FileMaker does keep it's own internal method of tracking everything.  Thats why you can rename a field and have it automaticly update in all scripts and fields where it is used.  If two files are connected then renaming a field in one file and any scripts or fields in the second file will update. 

           

          if you are going to import the data you can manually match up the fields on the import screen and have the data pull into the second file.  You won't have to rename all of the fields.

           

          HTH

          • 2. Re: change table for instance yet match fields?
            njem

            Bruce,

             

            Thanks but I have the import fine. I want to get rid of tbl1 because it's data should have been in tbl2 all along. (It's a non-profit and separate lists of donors and volunteers were set up when they should have been one table with a field to indicate donor or volunteer or both.) I get the data into tbl2 just fine. But now on the relationship graph I have many instances of tbl1 and complex relationships, and layouts that use these instances, and reports that subtotal on fields in these instances etc. I will have to find everyone of those things and redo them to point to instances of tbl2 now, and likely introduce mistakes. OR...if you edit the details of an instance in the relationship graph you can change what table that instance gets its data from. If I could do that to each instance of tbl1, getting data from tbl2 instead, I'm done, nothing disturbed, no bugs introduced. The thing is FM's internal way of tracking which field is which leads to fields being mismatched.

             

            Do you know if there's a way to get to FM's internal tracking so I can fix it?

             

            To illustrate:

            I make two identical tbls:

            Name

            Street

            City

             

            In that case I can change the sourcce table an instance uses and everything comes out right. But if I originally made one of those tbls with different field order

            City

            Street

            Name

             

            Then when I change the source table for an instance, places on a layout that used to show Instance::Name now show Instance::City. Even if I go in and manully correct the field order first and then change the instance source table it fails in the same way. FM's db of fields in a user table gets in the way. If I could edit FM's internal field tracking I could fix this.

             

            I imangine in enterprise size DBs that grow and change there must be times when tables need to be combined or other reasons where changing the source table that feeds an instance must be needed. I can't be the only one to ever need to do this. I'm hoping there's a solution out there.

             

            Thanks,

            Tom

            • 3. Re: change table for instance yet match fields?
              Mike_Mitchell

              No, there's no way to get to the internal IDs for the objects. You'll have to change the objects to their new identities on a case-by-case basis.

               

              Mike