AnsweredAssumed Answered

Script to determine if new customer already exists

Question asked by besmith1ca on Oct 6, 2014
Latest reply on Oct 6, 2014 by philmodjunk


Script to determine if new customer already exists


Does anyone know how to build a process (Script and Layout) that does the following?    

Here's a link to an excel sheet on dropbox that contains more details including sample data and data model:                                                                                                                              

If you have an interest in helping or have questions, let me know.  If someone can set it up for me, I can adapt it and maintain it going forward.  I just am not smart enough to create it from scratch. :)

Here's my process description (also included in the dropbox file referenced in link above):


Find Customer records that are new (New Records) and have not yet been reviewed for possible match with existing customer records (Existing Records)

Begin Loop - For each New Record, do the following:                 

            Find previously Existing Records (identified as Pending_Review_For_Poss_Match_Flag <> "Y") that are possible matches to the New Record.  Use each of these criteria:

                        First_Name and Last_Name match New Record                            

                        First three characters of First_Name and first three characters of Last_Name match New Record

                        Email or Phone or Shipping_Street_1 alone match New Record 

                        (may add another criteria if I can think of one)                                      

            If no possible matches are found                                                               


                        Display the following message to user and wait for their acknowledgement (user clicks "OK" button or something)

                                    Popup message:                                                                   

                                    No matches were found for this customer:                                   
                                                First_Name Last_Name                                             
                                                Shipping_Street_1, Shipping_Street_2                                
                                                Shiping_City, Shipping_State Shipping_Zip            
                                    Customer will be added as a new customer.                     OK      
                        Delete contents of the Pending_Review_For_Poss_Match_Flag field in the New Record.


                        Present the New Record and the Possible Matches to the user for review.

                        Allow user to either (1) select a single Match Record from the list of Possible Matches or (2) indicate that none of the Possible Matches are matches.

                        If user confirms that one of the Possible Matches are a match              


                                    Update any fields on the Existing Record with any data that is different as found on the New Record.

                                    Apply the Customer_ID from the Existing Record to the Order record that was related to the New Record.

                                    Re-Lookup the Lookup_Loyalty_Club_Member_Flag in related Orders and Line_Items_From_Orders records.

                                    Delete the New Record (that contains Pending_Review_Flag = "Y")


                                    Delete contents of the Pending_Review_For_Poss_Match_Flag field in the New Record.



End Loop