Views:

Overview

In this business scenario we have a custom entity which stores Application data that a User provides. 

When a User submits this Application, we want to match the details given in the Application with any existing Contact. The match rules are as follows,

  • Match on email address 
  • Match on phone number
  • Match on firstname, lastname, date of birth

If there is no match to any of the above rules we will create a new Contact. Finally the lookup field of type contact will be set on the  Application.

Note: In this KB article we use a custom entity called Application that has the custom fields Email address, Phone number, First name, Last name, Birthday  and a lookup to the Contact entity. We also have a custom field called Birthday. on the Contact entity.

North52 BPA Solution

The North52 BPA solution works like this:

  • A formula is created that will trigger whenever an Application record is created or whenever the following fields are updated:
    • Email address, Phone number, First name, Last name and Birthday 
  • When triggered, the formula will perform 3 searches to find a corresponding Contact. It performs these in descending order, stopping the searches if one is found.
    • First it will look for a Contact with an email address that matches the one in the Application
      • If it finds one, it will associate that Contact with that Application 
    • Second, it will look for a Contact with a phone number that matches the one in the Application
      • If it finds one, it will associate that Contact with that Application
    • Third, it will look for a Contact with a First Name, Last Name and Birthday. that matches those in the Application
      • If it finds one, it will associate that Contact with that Application
  • If the formula finds no matching Contact, it will create a new Contact using the fields given in the Application and associate it with the Application

Formula Setup 

  • Navigate to Settings > N52 Formula
  • Create a new formula, setting the following values in the Formula Guide:
    • Source Entity set to Application 
    • Set Formula Type to Save - Perform Action
    • Select the Decision Table editor and click Create
  • Change the Name of the formula to Find or Create Contact for Application
  • Expand Source & Target 
    • Ctrl-click the following fields:
      • Email address, Phone number, First name, Last name and Birthday 
  • Select cell A2 on the Decision Table editor
  • Right-click and select Insert > Insert Decision Table
    • Repeat this 3 times
  • Name the 3 new Decision Table sheets by double clicking the sheet name:
    • Match Contact if Exists
    • No Contact found : Create Contact
    • Update Application with new Contact

Sheet 1: Match Contact if Exists

  • Right-click and select Hit Policy > Exist all Decision Tables on First Match
  • Right-click in column A or B and select Insert > Insert Condition
  • Right-click again and select Insert > Insert Condition
  • Select cell A2
    • Go to the Function tab and search for FindValue
    • Shift click it to to open its wizard
      • For Friendly Name, enter Match Email
      • For Entity Name select Contact
      • For Where Attribute select Email Address
      • For Equals Value:
        • Click on Email
        • Expand Source
        • Go to the Source Tab
        • For Select Attribute, select Contact
        • Click Generate
  • Select cell A4 
    • Go to the Function tab and search for Contains Data
    • Ctrl-click the function ContainsData
  • Select cell B2
    • Go to the Function tab and search for FindValue
    • Shift-click it to to open its wizard
      • For Friendly Name, enter Match Phone Number
      • For Entity Name select Contact
      • For Where Attribute select Business Phone
      • For Equals Value:
        • Go to the Source Tab
        • Expand Source
        • Click on Phone Number
      • For Select Attribute, select Contact
      • Click Generate
  • Select cell B5
    • Go to the Function tab and search for Contains Data
    • Ctrl-click the function ContainsData
  • Select cell C2 
    • Go to the Function tab and search for FindValue
    • Shift-click it to to open its wizard
    • For Friendly Name, enter Match First Name, Last Name and Birthday.For Entity Name select Contact
    • For Where Attribute, enter SetFindAnd('firstname','lastname','birthdate')
    • For Equals Value, enter SetFindAnd([north52_application.north52_firstname],[north52_application.north52_lastname],[north52_application.north52_dob]
      • All these fields are custom fields
    • For Select Attribute, select Contact
    • Click Generate
  • Select cell C6
    • Go to the Function tab and search for Contains Data
    • Ctrl-click the function ContainsData
  • Click Save

Sheet 2: No Contact found : Create Contact

  • Right-click and tick Set Sheet Options > Use Create Record
  • Select cell A2
    • Right-click and select Delete > Delete Column
  • Select Columns A - D
    • Right click and select Insert > Insert Action 
  • Select cell A2 again
    • Go to Explore and expand Entites > Contact 
    • Find and select Email
  • Select cell A4 
    • Go to the Source tab and select Email address
  • Select cell B2 
    • Go to Explore and select Business Phone
  • Select cell B4
    • Go to the Source tab and select Phone Number
  • Select cell C2 
    • Go to Explore and select First Name
  • Select cell C4
    • Go to the Source tab and select First Name
  • Select cell D2 
    • Go to Explore and select Last Name
  • Select cell D4
    • Go to the Source tab and select Last Name
  • Select cell E2 
    • Go to Explore and select Birthday.
  • Select cell E4
    • Go to the Source tab and select D.O.B.
  • Click Save

Sheet 3: Update Application with new Contact

  • Select cell A2
  • Right-click Delete > Delete Column
  • Select cell A2 again
    • Go to the Source tab 
    • Expand Source and select Contact {Value}
  • Select cell A4
    • Collapse Source and expand Decision Table Calculations > No Contact found : Create Contact
    • Select No Contact found : Create Contact-Row-0004
  • Click Save

Test

  • Create an Application record that has a matching Email address, a matching Phone Number or a matching First Name, Last Name and D.O.B combination with a Contact record.
    • The Application will be associated with the matched Contact
  • The second test is to create an Application without matching fields
    • A Contact will be created and associated to the Application