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.
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.
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.
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.
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.
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.
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
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.
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.
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.