Duplicate Record Check

1. On change of Client Last Name search database for potential duplicates based on the Last Name value -I'VE DONE THIS AND IT IS WORKING
   
2. IF potential duplicates found, display a list of those duplicates with their First and Last Name and address so the user can determine if one of the entries is the original client record - THIS PART I AM LOOKING AT Runner.displayPopup(() as a solution
   
3. IF one of the entries in the list displayed to user is the client they want to work with, allow them to select it and open that record in Edit mode.
   Using the field Client Before/Server/Client After events I can pass the entered value from the client_LastName field to the server, perform a query on it and determine if there are records that may be duplicates. My question is does anyone have a suggestion on how to handle the prompt/selection for #3 above? I've searched and not found any references for that.
   Any suggestions would be greatly appreciated!
   Thanks as always,
   
   Randy
   

Hey Randy,
Would you be willing to share the code that you have for step 1 and 2? I have a few ideas, but I would like to see what you're working with first to see if I can get it to work.
Thanks

David

Sure thing David! Here's my current code breakdown:

1. client_LastName field On Blur event
   SERVER SECTION
   
   $rs=DB::Query("select client_FirstName, client_LastName, client_Email, client_Address1, client_City, Client_State, Client_Zip from CLIENTS WHERE client_LastName ='".$params['client_LastName']."'" );
   while ( $masterdata=$rs->fetchAssoc()){
   
   $result["clientFirstName"]=$masterdata['client_FirstName'];
   
   $result["clientLastName"]=$masterdata['client_LastName'];
   
   $result["clientEmail"]=$masterdata['client_Email'];
   
   $result["clientAdd1"]=$masterdata['client_Address1'];
   
   $result["clientCity"]=$masterdata['client_City'];
   
   $result["clientState"]=$masterdata['client_State'];
   
   $result["clientZip"]=$masterdata['client_Zip'];
   }
   
2. I am thinking that it would make sense to pass the $results from client_LASTNAME SERVER SECTION in #1 above to the AFTER CLIENT SECTION. I've created a custom client list page "CLIENTDUPECHECK" that I want to display with the results of the lookup in #1 above if it contains any records.
   
3. The user will look at the list and if they see the client already exist, I want them to be able to select that record from the list page popup from #2 and open the EDIT page for that Client record.
   Thanks for taking the time to look at it David.
   Best,
   
   Randy

1. client_LastName field On Blur event
   SERVER SECTION
   
   $rs=DB::Query("select client_FirstName, client_LastName, client_Email, client_Address1, client_City, Client_State, Client_Zip from CLIENTS WHERE client_LastName ='".$params['client_LastName']."'" );
   while ( $masterdata=$rs->fetchAssoc()){
   
   $result["clientFirstName"]=$masterdata['client_FirstName'];
   
   $result["clientLastName"]=$masterdata['client_LastName'];
   
   $result["clientEmail"]=$masterdata['client_Email'];
   
   $result["clientAdd1"]=$masterdata['client_Address1'];
   
   $result["clientCity"]=$masterdata['client_City'];
   
   $result["clientState"]=$masterdata['client_State'];
   
   $result["clientZip"]=$masterdata['client_Zip'];
   }
   
2. I am thinking that it would make sense to pass the $results from client_LASTNAME SERVER SECTION in #1 above to the AFTER CLIENT SECTION. I've created a custom client list page "CLIENTDUPECHECK" that I want to display with the results of the lookup in #1 above if it contains any records.
   
3. The user will look at the list and if they see the client already exist, I want them to be able to select that record from the list page popup from #2 and open the EDIT page for that Client record.
   Thanks for taking the time to look at it David.
   Best,
   
   Randy
   

No problem. I'll play with it the next day or two to see what I can come up with.

Thanks David, I look forward to seeing what you come up with!

Just wondered if you had considered doing it the other way around. Force the user to enter the name and search for the name, display a list of all the records found, user then has the option to ADD a new client or to update an existing client. There is still the possibility of a duplicate, which has to be checked for and the user given the option to either continue with the ADD. The advantage I see to doing it this way is that the user doesn't have to enter all the data before being informed its a duplicate.

For what it's worth...
I would not use the Address unless you're already using a standardized address routine and even that has caused problems for me.
Someone could enter:

1. 456-D MyStreet St.
   
2. 456D mystreet Street
   
3. 456 Mystreet St
   
   Unit D
   etc.
   None of which are a duplicate.

Hey Thamsytader: The last and first names are actually the first thing it needs to search on after being entered to see if there is a potential match. If not then it checks the address fields as they are entered for potential matches. So it needs to do both because we have people with different last names at same address or new client at old clients address etc. We photograph a job which has literally hundreds of orders all needing to be entered as quickly and accurately as possible. We have been doing this for over 10 years so a large portion of the people are in our systems after a job UNLESS it is a new sports league/dance studio etc. In that case there are many clients who are not going to be already in the system.
Our old system prompted after each of those sets of fields were entered with a list of potential duplicates.

I've worked a little on it, but couldn't get your code to work on my "test" area. I'm still trying a few different ways.

Thanks for taking a look at it David!

Going back to your original post your 3 steps are almost identical to the process I use in my application, apart from the starting point. My users are forced to enter the last name either fully or partially and either a page of potential matches is displayed OR nothing is displayed. I have a custom ADD button which is initially disabled until the search has been performed, this is what forces the user to perform the search. I've included my code here - it may be of some assistance.
In the Before Display event of the List Page (which is where the user would attempt to ADD a new client) I have this code

Then in the Client After event for the custom Add New button I have:

Once the search has been performed the user can choose to use an existing clients record - duplicate avoided, they just edit the appropriate one displayed on the list page using standard PHPR functionality.
If the user insists on adding a new client the Add New button is now enabled and displays the add page.
With the addresses I had to make a very tight format and control the entry of data. So the user has to define the accomodation type (House,flat, hostel, hotel) the page then dynamically adjusts the fields displayed and the field labels, I had to use regular expressions to control the format of the house or flat numbers (allowing for 1A, 2B etc). I should add at this point that I am in the UK so use post codes rather than ZIP codes, which again required regular expressions to control the data entry. On that point does an inital duplicate address check using ZIP code help?
If my user then enters a new client the following code is used to check for a duplicate (because it could still be a duplicate). Note a return of false will display a message back to the user, the user could change some of the data when warned they could create a duplicate, so we have to check for that too. The function Keyfieldschanged checks if any of the key fields have changed and the fumction RecordExists checks for an existing record. They are functions because the code became unreadable with them embedded in the IF structure. I had to use session variables to retain information from the previous server execution of the code.
Sanitise_input($values); // Function that cleans up the input - correct case, remove excess spaces etc

Address_format($values); // Function that takes the address data entered and correctly populates the database fields

Hope you find a workable solution.