OpenEdge Card-On-File Setup

This process will outline the steps needed to update teh RMS database with the card-on-file Alias' from the Admin Panel. This should be billable due to the data work that goes into the process.

Steps for OpenEdge
NOTE: For testing purposes, it will be a good idea to add a new test customer to the clients Admin Panel and assign a card-on-file. You will use this record for testing before you perform your mass INSERT statement later in this document.

• Request that the eComm Alias' be copied to the Retail account.
  • For best results, you want to do this very close in time to the execution of the RMS update outlined later in this document

Steps on our end

• Submit request to Jon Trafton for extract of the following information:
  
  • Customer EmailAddress
  • PayerData (Token/Alias)
  • Last4 of Credit Card
  • 4 digit Expiration Date (ie: 0918 NOTE: Excel may automatically truncate the leading 0, this needs to be in 4 digit format, so be prepared to CONCAT 0 for this column)
  • First Digit of Credit Card
    
• Obtain a database backup of the customer, and extract the following information from the Customer table:
  
  • ID
  • EmailAddress
    
• Get both sets of data onto an Excel document for duplicate removal, match data retrieve from the eComm PayerData extract to the RMS Customer table using EmailAddress as the matching key
  • Duplicate Removal: Highlight the EmailAddress column > Go to Data > Remove Duplicates > Expand Selection > Only Check the Column for EmailAddress > Hit OK
  • Match on EmailAddress: Highlight the longer EmailAddress column > Right-click > Define Name.. > Perform VLOOKUP in new column > Delete rows where output = #N/A
    
  • Copy all columns to a single sheet based on your VLOOKUP
    
• Concatenating PayerData/Alias' with XAW. In this example, our PayerData column is Column F
  *** This process is important, as this is what triggers the Card-on-file to skip over the swipe/insert step during the transaction. ***
  • Create a new column, lets call it column G and enter the text value: XAW
  • Create a new column, lets call it Column H
  • In column H, type =CONCAT(G2,F2)
  • Drag this column all the way down to your last entry
    

Preparing your mass INSERT statement

For this part of the tutorial, I will provide an example of how you work above should appear now, and how to utilize the previous setup to build your INSERT statement.

Below is an image depicting the spreadsheet, we will use this example for the insert statement that follows:

Based on the example above, the cell for your INSERT statement will look like this:

="INSERT INTO NWT_ManagedPayerData (CustomerID, PayerData, Last4, Expiration, FirstDigits, FirstDigit, PaymentProcessorType) VALUES ("&A2&",'"&G2&"','"&D2&"','"&E2&"','','"&F2&"','1');"

If done this way, you should now be able to drag this cell down the spreadsheet to encompass all of your cell work from the previous steps. The finished product should look something like this:

You should now highlight and copy these cells. You will use this to paste into the SQL Management Studio to run the query.
NOTE: Prior to running this query, it will be best practice to run a single INSERT statement with your test record to ensure the desired result is achieved.