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: 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
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.
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.