Views:

Scenario Overview


Many businesses import data from outside sources and they often will require either validation or clean up of said data. The below example details how any data that is imported in CRM can be put under strict validation rules or undergo any clean up process that may be required. 

This example shows how to set up a Decision Table to perform validation checks on imported data. An Example of a typical data set is below:




This example will use the following business rules:

Rule 1: 

The Renew Date column must contain Data
Rule 2:

The Amount column must contain Data
Rule 3:

The Account Type column must contain Data
Rule 4:

This import update will be matched to the existing Account entity via the Reference Number 
Rule 5:

Duplicates of this update are not allowed
Rule 6:

Only matching Account types are allowed
Rule 7:

All validation failures will create a relevant error message in the field Status Message
on the Integration Account entity
Rule 8:

If all validation passes, update a field in the Account entity with the next working day after 
the renew date - this field will be called new_nextworkingdayfromimport

N52 Formula Overview


The Formula Manager solution works like this: 

  • A custom entity called External Integration is created with the following fields:  Amount, Reference Number, Status Message, Type Number, Renew Date
  • The data is imported into this entity
  • A North52 formula is set to trigger upon the creation of an External Integration record
  • This formula will perform the validation checks and update any relevant fields

N52 Formula Manager Solution

The following set of steps outline how to create this Formula:  

  • A formula of type 'Save - Perform Action' is created
  • The Mode is set to 'Server Side'
  • The Event is set to 'Create'
  • The Source Entity is set to 'Integration Account'
  • The Source Property is set to 'All Properties'
  • Switch to the Decision Table editor by selecting Toggle Editor from the Command menu

    Decision Table

     

    Calculations Table


    The Calculations Table has 4 actions to execute as part of this Decision Table:

    • UpdateAccount - Updates the Account entity : Sets its custom field nextworkingdayfromimport to the next working day
    • FindCount - Account - Counts up how many Accounts match the criteria of Account number Reference Number
    • FindType-Account -  Brings back the value of the Description field in the Account entity where the Account number Reference number
    • GetNextWorkingDay  -  Calculates the next working day 

      Calculations 

      UpdateAccount 
      
      UpdateRecord('account',
          FindValue('account',
                     'accountnumber',
                     [new_intergration_account.new_referencenumber],
                     'accountid','?','true') , 
        SetAttribute( 'new_nextworkingdayfromimport', GetVar('GetNextWorkingDay') )
      
      )
      
      FindCount - Account
      
      FindCount('account','accountnumber',[new_intergration_account.new_referencenumber],'accountid','0','true')
      
      FindType-Account 
      
      FindValue('account',
                'accountnumber',
                [new_intergration_account.new_referencenumber],
                'description','0','true')
      
      GetNextWorkingDay
      
      GetNextWorkingDay([new_intergration_account.new_date])