2 Replies Latest reply on May 10, 2011 10:45 AM by margotjacqz

    importing sub tables into exisiting database

    margotjacqz

      Title

      importing sub tables into exisiting database

      Post

      I have a database file that includes a table for people and linked subtables for work history and education.  People has a unique field - PID - generated serial number; Ed and Hist records are linked to PID; new Ed and Hist records are created through a portal on a People layout.

      information on People is being collected from different sources; one such file is similarly structured as above. I can sort out the the main table fields, but am not clear on how best to handle the linked records.  In importing the people I did think to add a field for and keep the orginal UniqueID, so incoming related records might get connected, but then I want to clear up the redundancies.

      In briefest form, the critical bits:

      Target database has Table=People including it's own <PID> unique field,  and <ImportID>, from the source records. 

                             and  Table=History, with a <PID_fk> for each job record (with its own ui)

      Source database has Table=People including unique field <ImportID>

                             and   Table=History with an <ImportID_fk> for each job record (with it's own ui)

      I think my question is What is best way to get ImportID lined up with and then replaced by the existing PID. On the theory I don't want two keys for the same purpose in a join.

      i considered reusing the Imported ID as the PID, but that seems wrong. not only does it disturb my sense of order to have different serial id series, I am guessing it won't work to just rerun the numbering later once all the data is in. <s>. 

      I could envision importing as a new table and then doing a look up / import from one table to the next (how)?  but that seems a long way round. So throwing it out there for thoughts on processes.

      tnx

        • 1. Re: importing sub tables into exisiting database
          philmodjunk

          You can use two sets of relationships for this.

          Use one based on the importID field to temporarily establish a link. Then update the PID_fk field after import so that you can then rely on the internally generated Primary key in your People table to keep your records properly related.

          Just after Import, the imported records form the current found set.

          You can then use Replace field contents and this "import only" relationship based on the ImportID numbers to copy the value of PID from People to the PID_fk field in History.

          Using additional table occurrences you can not only have separate and different relationships between the same two tables, but if you use the Anchor Bouy method, you can have a layout just for importing your history records which is based on a table occurrence in Manage | Database | Relationships that links to People by the ImportID fields. You can then use this layout for importing the data followed by a replace field contents action to add the missing PID number to PID_fk.

          Your regularly used layouts need not refer to this same table occurrence and they can then utilize the PID based relationships.

          • 2. Re: importing sub tables into exisiting database
            margotjacqz

            lovely. thnaks phil as always. I needed the push to see a bit of redundancy might aid clarity and using "temporary" layouts for looking at data.

            Nothing you can do I suppose about the fact that in the old file the UI's were not in fact unique in about a dozen cases. <s> fortuantely not many and the temp layouts help make the handwork easy.