1 Reply Latest reply on Oct 6, 2014 10:50 AM by philmodjunk

    Script to determine if new customer already exists

    besmith1ca

      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