Check for duplicates/Prevent Duplicates

[aprescott]

Thanks, Gregg. I'll give it a whirl.

[WebDesignShop]

I know you already have a solution for this, but in case you, or anyone else, is wondering about an alternate method (a bit less programming and what there is can all be done in javascript):

1. You have your list with a search form
2. in the search form is all the fields you would want to validate
3. When they are entering a new record they actually enter the data into the search fields
4. Then you have a custom submit button which does a search and determines the number of records returned (easily done in javascript)
5. if a record already exists then it pulls that one up for editing
6. if not it creates a new record and inserts the search form data into the fields

Just a thought. Happy coding.

[CharlesParker]

There is always the idea that you could have a separate component that can perform a merge of possible duplicates, marking them as NOT a duplicate if needed. Might be a bit more work though in the thought process to create but will certainly give users more control over their data.
Show both records in separate lists. Correct one then delete the other could be a simply idea - all manual data entry ensuring the code doesn't miss something - or - overwrite something unintentionally.

[CharlesParker]

For the most part in my application, every once in a awhile there will be a duplicate customer. Often the phone number is the same, so when entering it a drop down of selections based on a lookup can help confirm a possible. I also have another drop down that you can do a last name check. I leave it up to the data entry person to check on their own work with the ability to simply pick from an existing customer if they find a match during entry.

[aprescott]

Another resurrected post from me... can you tell I'm nearing a deadline?

Here is a portion of my code:

duplookup = sql_lookup_multi(cn,"records","last_name = :last and (first_name in (:first,:spouse) or spouse in (:first,:spouse))","first_name,last_name,spouse,address",argsUpdate)
dim first as c
dim last as c
dim spouse as c
dim address as c
first = duplookup.first_name
last = duplookup.last_name
spouse = duplookup.spouse
address = duplookup.address

if dupCount >= 1

js = <<%str%
var title = 'Duplicate Found';
var html = '<p>There is a record with this name already. Do you want to enter it anyway?</p>';
var type = 'yn';
var onClose = function (btn) {
if (btn == 'yes') {
// save their work then close the window
{dialog.object}.runAction("saveAndClose");
} else if (btn == 'no') {
// close the window
{dialog.object}.runAction("close");
} else {
// cancel - do nothing
}
};
A5.msgBox.buttons.yes = {html: 'Yes', className: 'msgbtnyes'};
A5.msgBox.buttons.no = {html: 'No', className: 'msgbtnno'};

A5.msgBox.show(title, html, type, onClose);

%str%

Is there a way to call the variables (first, last, spouse, address) in the message box? So the user is shown "There is a record with this name already. (NAME, ADDRESS) Do you want to enter it anyway?"

I'm not sure how to use variables in the <<%str% format.

[madtowng]

The strtrani() function is your bestest friend for that.
Format the string the way you want, putting the desired
elements between <>. example : js = strtran(js,"<first>",duplookup.first_name)

[aprescott]

madtowng , once again you're saving me a lot of headaches. Thanks! Works perfectly.