Scenario Overview

In this scenario we will demonstrate how to set up a Decision Table that uses the ClientSideFunction AddPreFilterLookup() to filter a lookup on an Opportunity record based on a variety of business rules.

For this article it is assumed that you have at least basic familiarity with Decision Tables and/or have read the following articles:

• Everything you need to know about Decision Table Conditions
• How to set up your Actions!

Note: We will not detail step-by-step instructions here on how to set up Conditions or Actions, please read the above articles if you need detailed configuration steps.

This company has its Sales Agents separated into 2 Positions:

• Senior Sales
• Junior Sales

Each Opportunity record has a new lookup field called Lead Sales Agent that we will apply the AddPreFilterLookup() to.

Business Rules

The following business rules must be implemented in the Decision Table :

Rule 1
If the Parent Account of the Opportunity has a credit hold applied
then no Lead Sales Agent can be assigned.

Rule 2
Only Sales Agents in the same Territory as the Parent Account can be assigned to the Opportunity

Rule 3
If the Opportunity has a Rating of Hot then only a Senior Sales agent can be assigned, 
otherwise Rules 4,5 & 6 apply.

Rule 4
If the Parent Account is a Preferred Account and the Estimated Revenue of the Opportunity is over €500,000 
then it must be assigned to a Senior Sales agent

Rule 5
If the Parent Account is a Standard Account and the Estimated Revenue of the Opportunity is 
over €1,000,000 then it must be assigned to a Senior Sales agent

Rule 6
All other Opportunities must be assigned to a Junior Sales agent.

North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

• A formula of type ClientSide - Perform Action is created on the Opportunity entity
• When the form loads the Decsion Table logic is executed and filters the Lead Sales Agent lookup view accordingly

Setup

• Create a new User lookup field called Lead Sales Agent on the Opportunity entity and add it to the form
• Add the Rating field to your Opportunity form
• Create the Senior Sales and Junior Sales Positions and assign at least two Users with these Positions
• Ensure you have created and assigned two different Territories to the sales Users and also assign a Territory to your test Account records

North52 Decision Suite Steps

Set up the Formula

• Create a new formula, setting the following values in the Formula Guide
  • Source Entity set to Opportunity
  • Set Formula Type to ClientSide - Perform Action
  • Select the Decision Table editor
  • Click the Create button
• Change the Name of the formula to Opportunity - Lead Sales Agent lookup filters
• Expand the Source & Target section and set the Source Property to Onload for the *** Opportunity Events *** form
  • Note: If there is a message in the Source Property area saying Duplicate form names exist, please change - go to your Customizations and rename the Quick Create form to Opportunity Quick Create, publish and then reload the Formula
• Click Save

Conditions

This Decision Table uses 4 conditions:

• From the Parent Account of the Opportunity it will check Category and 
  Credit Hold.
• From the Opportunity it will check the Estimated Revenue and the Rating

Create the Condition columns

• Right-click on column A and select Insert > Insert Condition from the context menu
• Repeat this 3 times so you have 4 Condition columns
• Select cell A2, then select the Parent Account Category from the Source tab: 
  • Related (N:1) > account (parentaccountid) > (Attributes) > Category {Value}
• Select cell A4, expand the Category {Value} node and click both Preferred Customer and Standard
• Select cell A5, from the Category {Value} node click Preferred Customer 
• Select cell A6, from the Category {Value} node click Standard
• Select cell B2, then select the Parent Account Credit Hold field from the Source tab: 
  • Related (N:1) > account (parentaccountid) > (Attributes) > Credit Hold {Value}
• Select cell B4, expand the Credit Hold {Value} node and click No
• Repeat the last step or copy down to cells B5, B6 and B7
• Select cell B8, expand the Credit Hold {Value} node and click Yes
• Select cell C2, then select the Opportunity Est. Revenue field from the Source tab: 
  • Source > Est. Revenue
• Leave cell C4 blank and type >500000 into cell C5
• Type >1000000 into cell C6
• Select cell D2, then select the Opportunity Rating field from the Source tab: 
  • Source > Rating {Value}
• Select cell D4, expand the Rating {Value} node and click Hot

To set defaults when there are null values in fields we can edit the dynamic field references:

• Right-click on the Decision Table and select Operations > Toggle Advanced Mode from the context menu to reveal row 3 and the Global Calculations and Global Action sheets
• Double-click cell B3 and replace the ? with false - e.g. [opportunity.parentaccountid.creditonhold.false]
• Double-click cell C3 and add .0 inside the closing ] - e.g. [opportunity.estimatedvalue.0]
• Save your formula

Global Actions

The Actions table has 2 separate but similar actions

• JuniorSales - used to filter the Lead Sales Agent lookup for Junior Sales rated agents
• SeniorSales - used to filter the Lead Sales Agent lookup for Senior Sales rated agents

Create the Actions: 

• Select the Global Action sheet 
  • If it is not shown then right-click on the Decision Table and select Operations > Toggle Advanced Mode from the context menu
• Enter Junior Sales in cell A2 under the Action Name heading
• In the corresponding Action Value row, cell B2, expand the formula editor by clicking on the + in the top right of the Decision Table canvas:
  • Select the Functions tab, type prefil into the Search Functions input box and press enter
  • Click on the AddPreFilterLookup function to add it to the canvas
  • Delete the placeholder parameter 'controlid'
  • Select the Source tab, type lead into the Search source input box and press enter
  • Expand the Source node and ctrl-click Lead Sales Agent {Value} to add it's schema name into the first parameter
  • Replace the formulashortcode placeholder text with the 3 character Short Code of your formula
  • Open the Advanced Find form and create a query to return users with a Position equal to Junior Sales and Territory equal to North America (note you can select any value as we will dynamically change these)
    • Download the Fetch XML
      
    • Edit the filter part of the Fetch-Xml and remove the yellow highlighted text. Then replace the green and blue highlighted text with 0 and 1 respectively, and finally put it all on one line:

      <filter type="and">
            <condition attribute="positionid" operator="eq" uiname="Junior Sales" uitype="position" value="{74B9734F-2F8C-E811-8147-E0071B6E9421}" />
            <condition attribute="territoryid" operator="eq" uiname="North America" uitype="territory" value="{3BCE2681-461B-E811-8135-E0071B6E4481}" />
      </filter>
      

      Becomes

      <filter type="and"><condition attribute="positionid" operator="eq" value="{0}" /> <condition attribute="territoryid" operator="eq" value="{1}" /></filter>
      

  • Delete the 'filter' placeholder parameter
  • Select the Functions tab, type stringformat into the Search functions input box and press enter
  • Click on StringFormat to add the function to the editor canvas
    • Delete the inputstring placeholder parameter and replace it with the edited filter Fetch-Xml from the step above
    • Delete the 'param[0]' placeholder
    • Select the Functions tab, type quickid into the Search functions input box and press enter
    • Shift-click the FindValueQuickId function to show the function wizard
      • Entity Name: Select Position from the search list
      • Name of Record to Find ID of: type Junior Sales
      • Click Generate
    • Delete the remaining two placeholder parameters 'param[1]','param[n]'
    • Select the Source tab, clear any previous search results and expand the Related (N:1) node
    • Expand the account (parentaccountid) node, then expand the (Attributes) node
    • Type terr into the Search source input box and press enter
    • Click on Territory (Value) to add the dynamics field reference [opportunity.parentaccountid.territoryid.?] to the formula
  • Replace entityfilter (the last placeholder parameter of the AddPreFilterLookup function) with systemuser
  • The Junior Sales Action should now look like this:

    AddPreFilterLookup('new_leadsalesagent', 
      'hM1', 
      StringFormat('<filter type="and"><condition attribute="positionid" operator="eq" value="{0}" /> <condition attribute="territoryid" operator="eq" value="{1}" /></filter>', 
        FindValueQuickId('position','Junior Sales'), 
        [opportunity.parentaccountid.territoryid.?]), 
      'systemuser')

  • Close the formula editor
• Select cell A3 of the Global Actions sheet, and enter the text Senior Sales
• Right-click on cell B2 and select Cells -> Copy  
• Right-click on cell B3 and select Cells -> Paste (this avoids any formatting issues)
  • Replace the text Junior Sales within the FindValueQuickId function with Senior Sales
  • The Senior Sales Action should now look like this:

    AddPreFilterLookup('new_leadsalesagent', 
      'hM1', 
      StringFormat('<filter type="and"><condition attribute="positionid" operator="eq" value="{0}" /> <condition attribute="territoryid" operator="eq" value="{1}" /></filter>', 
        FindValueQuickId('position','Senior Sales'), 
        [opportunity.parentaccountid.territoryid.?]), 
      'systemuser')

  • Close the formula editor

Notes:

• new_leadsalesagent is the field we apply the AddPreFilterLookup() function to
• hM1 is the specific N52 Decision Table formula Short Code
• StringFormat() function is used to build the Fetch-Xml fragment with the 2 parameters: Position {0} and Territory {1}
• systemuser is the entity filter

The Lead Sales Agent filter will now search for SystemUser records based in the same Territory as the Parent Account and then use Sales Agent Position to further filter the returned list.

Decision Table Actions

We now define the Action that needs to happen when the Conditions have been met.

• Select the Decision Table sheet
• Select cell E4
• Select the Source tab and clear any existing search
• Expand the Global Actions node
  • If nothing shows, click the Refresh button to the right of the Source search input box
• Click on the Senior Sales Global Action
• Copy cell E4 to E5 and E6
• Select cell E7
• Click on the Junior Sales Global Action
• Right-click in column F, and select Insert > Insert Action 
• Select cell F2
• Select the Source tab, type lead into the Search source input box and press enter
• Click on Lead Sales Agent (Value) from the Source node to add that field to the Action column
• Click on cell F8
• Select the Functions tab, type disable into the Search functions input box and press enter
• Ctrl-click the DisableFields function to add it to cell F8 as {{{DisableFields}}}
• Click Save
• Select Publish Formula from the N52 Commands menu to bind the formula to the Lead Sales Agent field on the Opportunity form
• We are now ready to test, your Decision Table should look like this:
  

Testing

Set up your test data so that you have at least two different users with different sales Positions and Territories and Accounts with matching Territories:

Agent 1




Agent 2




Parent Account




Opportunity record with Pre-filter in action