Importing without duplicating existing records

[Toni]

Hello

  I have a database which imports data from a csv file. This csv file is generated by a machine usually every day. But it does not delete previous data, it just generates the whole data it has on the same file (overwriting it). Obviously I don't want duplicated records in my database.

  The first solution was to actually delete the whole records and re-import them, but this wasn't the preferred solution.

  So I thought of a second solution which I would like to share for any advice.

  I created a second table, Table_B with same fields as in first table Table_A. Then added a relation between the two tables where Table_A::field_a = Table_B::field_a. Then created a calculation field in Table_B which is equal to Table_A::field_a (Table_B::field_related = Table_A::field_a). The import has two steps, first I import from csv into Table_B then I perform a find where Table_B::field_related is empty and then I import what is found in Table_B into Table_A.

  I remember I have read time ago, in this forum I believe, a similar solution with related tables but different approach. Can not find that post.

  Do you think this is a good approach or can something be done differently?

[comment]

Some more details would be useful. In general, if you want the import to update existing records, you can choose the Update matching records method when importing (see: https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fimporting-data.html%23ww1027874). Then there will be no need for elaborate post-processing of the imported data.

[Toni]

Yes comment, that can be used too, but it would still involve a find on Table_A since the update matching records works on current found set. Beside, who knows, maybe in future it will not be needed to update the imported records because of possible editing of the records.

[comment]

2 hours ago, Toni said:

it would still involve a find

Isn't your import scripted? Surely the elaborate scheme you have described in your question would have to be scripted? As would be your current solution of deleting all existing records before importing new ones So the question you should be asking yourself is what is the simplest route to the desired goal.