Scenario Overview

In this example, we need to enforce some validation rules whenever an Account is being updated or created.

Whenever the Account is a Preferred Customer or whenever the Credit Limit is greater than 100,000, we wish to make sure that the Address fields have been completed. 

We can do this easily by making a Decision Table that triggers off both the creation of an Account and whenever an Account's Address fields are changed. 

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • We create a formula of type Validation for the Account entity
  • Each time an Account is created, or selected Address fields are updated, the formula triggers. 
  • Our formula will check to see if the Account is a Preferred Customer or if the Credit Limit is greater than 100,000
  • If it meets one of these conditions, the Decision Table will then make sure the Address fields have data in them
  • A error message will show and display all Address fields that do not contain data.

N52 Formula Manager Steps

  • Create a new formula, setting the following values in the Formula Guide
    • Source Entity set to Account
    • Set Formula Type to Validation
      • Note that choosing this type will change Deployment Settings > Pipeline Stage to Pre-Validation (Synchronous)
    • Select the Decision Table editor
  • Change the Name of the formula to Account Conditional Address Validation
  • Expand the Source & Target section
    • Control-click to multi-select the following fields: Category, Credit Limit, Address 1: Street 1, Address 1: Street 2, Address 1: Street 3, Address 1: City, Address 1: State/Province,  Address 1: ZIP/Postal Code
  • Click Save
  • Select column B 
  • Right-click and select Delete > Delete Column
  • Highlight columns B to
  • Right-click and select Insert > Insert Condition
    • ​​​​​​​This will insert Condition columns for all the selected columns
    • Note if you have a North52 BPA version less than 562 you will need to manually add the columns one by one
  • Select column I, then right-click and select Insert > Insert SetVarConcat Calculation (*-)
  • Replace SetName with ValidationMessage
  • Right-click the sheet Hit Policy > Exit this Decision Table on First Match and uncheck the checkbox
  • Select cell A2
    • Go to the Source tab and and expand Source
    • Find and click Category (Value)
  • Select cell A4 
    • Go back to the Source tab and expand Category (Value)
    • Click on Preferred Customer
  • Select cell B2
    • Go back to the Source tab, find and click Credit Limit
  • In cell B4 and type >100000
  • Select columns A and B
    • Right-click and select Set Condition Column Options > Condition-Or
  • Select cell C2
    • Go to the Source tab and click on Address 1 : Street 1
  • Select cell C4 and then go to the Functions tab
    • Search for doesnot and ctrl+click the DoesNotContainData function
  • Select cell I4 and type 'Required Field: Address1 Street1' including the quotes
  • Repeat this for each address field in columns D, E , F, G and H like the image above
  • Click Save
Our Decision table will now check
  • If Category is set to Preferred Customer OR if the Credit Limit is greater than 100000 
  • If any of Address fields are empty
Whenever these conditions are met, the ValidationMessage is updated with the relevant message and an error message will be shown to the user.