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 Decision Suite

The North52 Decision Suite 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