Prevent import of duplicate records

[Stu412]

My import routine is nearly complete, thanks for everybody on here who's helped with all my questions!  I have one other question to ask.

I need to ensure that the data being imported doesn't already exist in the system otherwise the values would double up and chaos would be caused.  I'm looking to use several criteria to check this and if no records are returned as a result of the check, carry on with the import.  The criteria are:

CustomerID (Number) AND

DataStatus (Draft or Final) AND

Date (Date)

I have each of these already stored in separate variables as a result of previous steps in my import script and I'm looking to use those variables in the check routine.

If a record (or actually a group of records) are returned as a result of this combination, I will allow the user to abort the import via a custom message.

My import routine is pretty set now:  Import to temp, update, import to permanent.  This suits the users!  

What would be the least invasive way to put the check stage (presumably some sort of find?) into the routine just prior to import to permanent?

I'm a little stuck at this part - please forgive the slightly open question!

Thanks

 

[comment]

If you define a text field to auto-enter a calculated value =

CustomerID & "|" & DataStatus & "|" & Date

and validate this field as 'Unique, Validate always', then duplicate records will not be imported.

 

Alternatively, you could define a relationship between the two tables, matching on all three fields, and omit the temp records that do have a related record in the permanent table before doing the final import. Note that this does not prevent importing duplicates that exist in the temp table only.

 

I could not understand this part:

I have each of these already stored in separate variables as a result of previous steps in my import script and I'm looking to use those variables in the check routine.

​

[Justin Close]

Stu,

I would suggest an ExecuteSQL check that simply counts the number of records returned that match the criteria you provide.  This will act as a flag that zero records matched, or more than zero matched, and you can show the user your abort dialog (or not).  Then you don't have any extra TOs/relations or fields or calculations you need to do.

It would look something like this:

ExecuteSQL (
"SELECT COUNT ( * ) FROM "TableA" WHERE "CustomerID" = ? AND "DataStatus" = ? AND "Date" = ?" ;
"" ; "" ; $CustID ; $Status ; $Date
)

You need to be careful about quoting your field names; I would recommend using some kind of robust-coding technique for the fields, e.g. a custom function such as GTN() or GFN().  In your case 'Date' is a reserved SQL keyword and definitely needs quoted to be used in the ESQL statement.  GFN() helps do that for you, but also prevents errors if a field name gets changed.

 

-- Justin

[eos]

Stu,
I would suggest an ExecuteSQL check that simply
[…]
You need to be careful […].

​Besides other considerations, that longish disclaimer you (simply have to) add is why I've become hesitant in suggesting ExecuteSQL().

Next thing you know, you have two issues instead of one. 

Edited June 12, 2015 by eos

[Justin Close]

Well, if you get in the habit of using an external function (as I do) and then those caveats all become irrelevant.  Here is what my own version of that same statement would be, but using a single 'Select' field to make it more evident:

ExecuteSQL (
"SELECT " & GFN ( TableA::FieldABC) &
" FROM " & GTN( TableA::FieldABC) & 
" WHERE " & GFN ( TableA::CustomerID) & " = ? AND " & 
            GFN ( TableA::DataStatus ) & " = ? AND " &
            GFN ( TableA::Date ) & " = ?" ;
"" ; "" ; $CustID ; $Status ; $Date
)

It's not a difficult habit to get into and it saves you from various headaches of using ESQL itself, as well as providing robustness to your code so things don't break.  You have to put the GTN() and GFN() custom functions into your solution, but that's a couple of copy and paste and you're done.

-- Justin

[eos]

Well, if you get in the habit […]​

I hope you didn't write all that on account of me … 

So “You have to put the GTN() and GFN() custom functions into your solution” to “(save) you from various headaches of using ESQL itself” is not a disclaimer?

[Justin Close]

It's a disclaimer, yes. 

But it isn't a problem to use with the right habits.