4 Replies Latest reply on Dec 13, 2012 3:35 AM by brian.curran

    Importing old data from Access and matching it to an existing ID in FM

    brian.curran

      Title

      Importing old data from Access and matching it to an existing ID in FM

      Post

           Hi,
           I have just imported approx. 4,000 records from an old Access database into a table called "Incidents", the old ID field format is "A.1230" so I created a Temp field with a "Middle ( OldID ; 3 ; 3 )" calculation to extract the "123" value.

           In an existing FM table called "SiteDetails", we have a unique SiteNumber field with the same format as the '123' value. I want to use this 'match' to tie the records together temporarily so that I can copy/retrieve the PK value from a related table called "Sites", how would I go about this?

           I'm not sure whether this will cause a problem or not but some of the imported records wont' have a match as their Site PK won't exist in the new FM database (old customers etc.)

           Thanks
           Brian.

        • 1. Re: Importing old data from Access and matching it to an existing ID in FM
          philmodjunk

               Shouldn't be a problem. Maker sure that both your temp field and your Site PK fields are the same data type.

               Presumably, you have an FK field that matches to Site PK. If the table into which you imported your records is this same table where you have your other data, you can use Replace field contents with this same calculation to create a matching value in the FK field. If you have imported your data into a different table, you can then import from this table into your existing table of site data and map your temp field to the Fk field.

               Once you have these records where they need to be, you can perform a find for all records that do not match to an existing site record and then you can either manually work with this found set to create matching records in the Site table or you can set up a script that loops through the records and creates these records.

          • 2. Re: Importing old data from Access and matching it to an existing ID in FM
            brian.curran

                 Not sure I get all of the above, as we have three tables:

                 Site::__kpSiteID   =   SiteDetails::_kfSiteID (The first record in both tables have an ID of "1")

                 The SiteDetails table has a field called SiteNumber which is a unique 3-digit number. The first record with its ID of "1" has a SiteNumber of "107"

                 The Incidents table (which is a new, separate table) has a calc field called SiteNumber too which has the corresponding "107" value.
                 This table also has a blank _kfSiteID field which I want to populate with a "1"

                 The ID fields and the SiteNumber fields are all 'Text' data type.

                 Does the above make a difference to your advice above? I can't work it out :-/

            • 3. Re: Importing old data from Access and matching it to an existing ID in FM
              philmodjunk

                   The key thing is that by creating multiple occurrences of your tables in Manage | database | Relationships, you can have multiple relationships between the same two tables. One relationship can match by SiteID and one can match by SiteNumber. Then you use the relationship based on SiteNumber and a Replace Field Contents operation to copy the needed SiteID field into your incidents table.

                   Your Relationships:

                   SiteDetailsBySN::SiteNumber = Incidents::SiteNumber

                   SiteDetails::_kfSiteID = Incidents::_kfSiteID (or you can link Set to incidents by site ID, or both...)

                   To create a new occurrence of an existing occurrence, click it, then click the duplicate button (two green plus signs). You can then double click the new occurrence to open a dialog where you can rename it if you want a better name than the original name followed by a number.

                   From your Incidents layout, you can perform a find to find all records where _kfSiteId is empty, then click/tab into this field and use Replace Field contents with this calculation: SiteDetailsBySN::_kfSIteID to copy the correct ID number over from SiteDetails.

                   If "table occurrence" is a new concept, you may find this tutorial enlightening: Tutorial: What are Table Occurrences?

              • 4. Re: Importing old data from Access and matching it to an existing ID in FM
                brian.curran

                     Excellent advice Phil, many thanks...