Overview

In this business scenario, we have three multi-select option set (picklist) fields on the Contact entity: Continent, Country and City. 

To improve user experience, we require that the Country option set to be filtered by the Continent of the Contact. We also require the City option set to be filtered by Country chosen. 

Example: If a Contact's Continent is set to Europe, we wish to only see European countries like Ireland or Germany in the Country field. If we were to select Europe and North America in the Continent option set, we want to show countries like Ireland, Germany, USA and Canada. When the User chooses a Country like Ireland, the Cities field will be filtered to include Cork and Dublin. 

We can do this using the SetPicklistValue function with Decision Tables.

Note: The 3 multi-select fields here are custom - Continents, Country and Cities. 
We also created 7 tabs on our Contact form - 'Africa','Antarctica','Asia','Europe','North America','Oceania','South America'

North52 Decision Suite

The North52 BPA solution works like this:

• Two Formulas are set up on the Contact entity
• The first formula will fire whenever the Continent field is changed
  • It will filter the Country field to show only countries from the Continents chosen
  • It will also show the tabs for each of the selected Continents
• The second formula will fire whenever the Country field is changed
  • It will filter the City field to show only countries from the Country chosen

Set up Formulas

First Formula - Filter Countries by Continents

• Navigate to Formulas
• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Contact
  • Set Formula Type to Clientside - Perform Action
  • Select the Decision Table editor
  • Change the Name of the formula to MultiSelect Optionset Demo : Filter Countries by Continent
  • Click Create
• Expand Source & Target
  • For Source Property, select onLoad and Continent
    • Note: you can multi-select by holding down the Ctrl key when selecting
• Click into the Decision Table
  • Right-click column C and select Insert > Insert Action 
  • Right-click column A and select Insert > Insert Comment
  • In cell A2 type Africa
  • In cell B2 type Picklist Continent
  • Right-click and select Operation > Toggle Advanced Mode
    • In cell B3, type TRUE
  • Select cell C4 
    • Find the HideTabs function and shift-click to open the function wizard
      • Ignore the Friendly Name as we will insert the function directly into the cell
      • Select the Tab(s) you would like to hide – in our case Africa,Antarctica,Asia,Europe,North America,Oceania & South America
        • Note: you will need to create these tabs for it to work in your system
          
      • Select Generate
  • Select cell B5 then go to the Functions tab
    • Find and shift click the function Contains
    • Leave Friendly Name blank
    • For Input string, go to the Source tab and find and select the custom field Continent
    • For String to find, expand Continent and select Africa 
      • Note your values may differ in your system
        
  • Select cell C5 then select the Function tab
  • Find the ShowTabs function and shift-click to open the function wizard
    • Ignore the Friendly Name as we will insert the function directly into the cell
    • Select the Tab you would like to show – in our case Africa
      • Note: you will need to create these tabs for it to work in your system
        
    • Select Generate
  • In cell D5 enter SetVarConcat('ListofCountries', 'XXXXXX, YYYYYYY,' )
    • Highlight XXXXXX and go to the Source tab
    • Find the custom field Country and expand it
    • Select one of the countries in Africa
    • Highlight YYYYYYY and go to the Source tab
    • In the same expanded Continent menu, select another of the countries in Africa
      • It should look like this but with different numbers SetVarConcat('ListofCountries', '217890000, 217890001,' )
  • Follow the pattern for the other continents as show in the screen capture below
  • In cell A16 (or the last row of the formula), enter the text Set picklist using List
  • In cell D12 (or the last row of the formula), enter the function SetPicklistValues('north52_country', 'false', Replace(Trim(GetVar('ListofCountries'),','),' ',''))
  • Click Save
  • Click N52 Commands > Publish Formula

Second Second Formula - Filter Cities by Countries

• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Contact
  • Set Formula Type to Clientside - Perform Action
  • Select the Decision Table editor
  • Change the Name of the formula to MultiSelect Optionset Demo : Filter Cities by Country
  • Click Create
• Expand Source & Target
  • For Source Property, select onLoad and Country
    • Note: you can multi-select by holding down the Ctrl key when selecting
• Click into the Decision Table
  • Right-click column C and select Insert > Insert Action 
  • Right-click column A and select Insert > Insert Comment
  • In cell A2 enter Argentina
  • In cell B2 enter Picklist Country
  • Right-click and select Operation > Toggle Advanced Mode
    • In cell B3 enter TRUE
  • Select cell B4 then go to the Functions tab
  • Find and shift-click the function Contains
    • Leave Friendly name blank
    • For Input string, go to the Source tab and find and select the custom field Country
    • For String to find, expand Country and select Argentina 
      • Note your values may differ in your system
        
    • Click Generate
  • In cell D2 enter Build List of Cities
  • In cell D5 enter SetVarConcat('ListofCities', 'XXXXXX, YYYYYYY,' )
    • Highlight XXXXXX and go to the Source tab
    • Find the custom field City and expand it
    • Select one of the cities in Argentina
    • Highlight YYYYYYY and go to the Source tab
    • In the same expanded Cities menu, select another of the cities in Argentina
      • It should look like this but with different numbers SetVarConcat('ListofCities', ' 217890018,  217890019,' )
  • Follow the pattern for the other countries as show in the screen capture below 
  • In cell A16 (or the last row of the formula) enter Set picklist using List
  • In cell C16 (or the last row of the formula) enter the function SetPicklistValues('north52_city', 'false', Replace(Trim(GetVar('ListofCities'),','),' ',''))
• Click Save
• Click N52 Commands > Publish Formula

Test

To test these two formulas:

• Make sure all three fields - Continents, Country & Cities - and the tabs referenced in formula one are on a Contact form
• Then open a record in that form and try changing values in either the Continent or the Country
• When you change a value in the Continents multi-select field:
  • The Continent tabs appear/disappear
  • The Countries field should also become filtered

• When you change a value in the Countries multi-select field:
  • The Cities field should become filtered