10 Replies Latest reply on Jan 24, 2015 2:11 PM by KristopherKruger

    Prevent Overwriting Records on Import

    KristopherKruger

      Title

      Prevent Overwriting Records on Import

      Post

      I'm sure this question has been asked before, but I wasn't able to find an answer.

      I have an ODBC connection to a website's mySQL db.  It imports records submitted by a webpage form.  

      I have a script that successfully imports those records.  My problem is that when our "back-office" edits the records that have been submitted, the records are overwritten on the next import - reverting them back to the original info.

      How can I prevent records from being overwritten if they already exists in the database?  

      These are the settings for importing.

      ID = ID

      Info 1 > Info 1

      Info 2 > Info 2

      etc...for about 30 fields.

      Under Import Action

      Checked : "update matching records in found set

      Checked: "add remaining records as as new records"

      ---

      I've tried setting the ID field in the table to "unique" value, but that only creates duplicates upon importing.

      ---

      I've heard of proxy fields, but i have over 30 fields - that seems pretty complex.

       

        • 1. Re: Prevent Overwriting Records on Import
          philmodjunk

          You can't import directly into your table with requirements specified and not have data overwritten. That's what the "update" version is designed to do. If you don't want data overwritten, you could click to clear the symbol that indicates that data will be copied from the source field into the target field but this will leave fields blank when the imported record doesn't match and is imported as a new record.

          If you can, set up the query that is part of the ODBC import to not import records that have already been imported. Then you can just do an "add new" import of the new records.

          If that's not possible, you'll need to import into a "staging" table and do a second import that more selectively moves data into the final table--such as performing a find on the staging table that omits all records that match ID's to the target table and thus import only the new records.

          • 2. Re: Prevent Overwriting Records on Import
            KristopherKruger

            Thanks for the suggestion.  I created an import staging table where the imported records are initially imported into the db.

            But I'm not sure how to then import the records into the table I need them.  I'm creating a script, but it doesn't allow me to select a table from the database itself. 

            I can't believe a feature as basic as this for a database is not already built in... seems like a major oversight in FMP.. a simple "skip record if record exists" would be the best.

            • 3. Re: Prevent Overwriting Records on Import
              philmodjunk

              I'm creating a script, but it doesn't allow me to select a table from the database itself. 

              I don't follow that. A scripted Import records step can import records from Table A to Table B with both tables defined in the same table. So this should not be a problem. (You go through the same steps for selecting the source table for your import, you just select the file that you already have open just like you would select a different file to get to the source table.)

              You can use a relationship between the staging table and the target table that matches IDs. You perform a find to create a found set of just the records that do not have a matching ID and then Import Records can do the import.

              • 4. Re: Prevent Overwriting Records on Import
                KristopherKruger

                I was able to import from one table to the other.. now working in the right combinations to import only new records.

                These are the settings I have.

                Staging and Primary tables share a relationship with their ID fields

                I created a script to "find" ID fields that are not equal to each other ... then import the data 

                Under Import Action

                Checked : "update matching records in found set

                Checked: "add remaining records as as new records"

                Didn't work - it overwrote the data on the primary table.

                ---

                I tried changing the relationship from equal to not equal between ID's and that didn't work either - primary table data was over written by staging data.

                 

                 

                • 5. Re: Prevent Overwriting Records on Import
                  philmodjunk

                  From the layout based on the staging table:

                  Enter find mode

                  Specify an "*" in the ID field from the target table not the staging table
                  Specify the omit option

                  Perform the find

                  Then do an import records to do only an "add new records" import of records.

                  Be sure to specify the correct table occurrences in order to import the correct found set of records.

                  • 6. Re: Prevent Overwriting Records on Import
                    KristopherKruger

                    Thanks..

                    I'm not sure what you mean by "Enter find mode" .. I'm working on a script so it's automated..

                    I used the "Perform Find" function and chose  "Omit Records" the table ID field and the Operator of "*"..  I then have the import function and chose "add new records".. 

                    When ran it I get the "no records found" message - which allows me to select "modify find" or "continue".  If i choose continue the records are imported.  But if i run the script again, all of the same records are imported again - so now there are duplicates.

                     

                    • 7. Re: Prevent Overwriting Records on Import
                      philmodjunk

                      There is a script that enters find mode. Most all menu options have a script step equivalent and this is one of them.

                      You need to fix the find so that it works and does not produce this error message or you will not import the correct records.

                      My best guess is that you specified the * operator in the ID field from the staging table instead of the ID from the target table.

                      For examples of a better way to set up scripted finds, see: Scripted Find Examples

                      • 8. Re: Prevent Overwriting Records on Import
                        KristopherKruger

                        I tried manually typing in the "*" in the ID field of the Target layout (i made sure it was the correct table) ... I read the other post and not sure how to get rid of the "no records match..."

                        I think I know what it is.. the staging table isn't being filtered by the primary table... So even if I would filter the Target table with a known ID number, that number isn't being omitted on the staging table and is imported again.

                        In the relationship of the two tables - they're linked by the ID field and = as the selector. 

                        --- 

                        Update:  I was able to get the find script to work - thanks to your posting above.  Now the issue is filtering the staging table - performing the search function doesn't have any affect on the records being imported from the staging table. All of them are being treated as new and all are importing - creating duplicates.

                        • 9. Re: Prevent Overwriting Records on Import
                          philmodjunk

                          If you have this relationship:

                          TargetTable::ID = StagingTable::ID

                          Then, go to a layout based on StagingTable, enter find mode and specify the "*" wild card in the TargetTable::ID field (NOT the stagingTable::ID field), select the omit option (Omit records executed in find mode does that in scripts) and perform the find.

                          The crictical details are that your layout must specify StagingTable in Layout setup|Show Records From and then you add the TargetTable::ID field to this layout. For new records in the staging table, this field will appear blank as there will be no matching record in the target table. Previously existing records will have records in TargetTable with a matching ID and this field will not be empty, so this find should find all records in the staging table that do not have a matching record in the target table.

                          • 10. Re: Prevent Overwriting Records on Import
                            KristopherKruger

                            THANK YOU!  This is just what I needed. The complication came with the ID field of the Target Table being placed on the Staging table layout.

                            I created scripts for each process, importing data into the staging table via an ODBC connection to a website mySQL database, a search script - that follows your instructions, and an import script into the Target table that follows your info...  I then created another script to call all of these scripts in order.  I'm sure there is a much better way to do it -and I'm open to suggestions...

                            thanks again  PhilModJunk for your patients and knowledge.