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

Title

Script to determine if new customer already exists

Post

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:  https://www.dropbox.com/s/ku9879faspe9pmi/Process%20-%20New%20Customer%20Records%20Review%20For%20Match.xlsx?dl=0                                                                                                                              

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                                                               

            Then                                                                                                              

                        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            
                                                Email                                                                         
                                                Phone                                                                        
                                    Customer will be added as a new customer.                     OK      
                                                                                                                                   
                        Delete contents of the Pending_Review_For_Poss_Match_Flag field in the New Record.

            Else                                                                                                                

                        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              

                        Then                                                                                                  

                                    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")

                        Else                                                                                                    

                                    Delete contents of the Pending_Review_For_Poss_Match_Flag field in the New Record.

                        EndIf                                                                                                 

            EndIf                                                                                                             

End Loop                   

Outcomes