Jump to content

Prevent import of duplicate records


This topic is 3209 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by eos
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 3209 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.