Overview
If you need to filter an OptionSet/Choice column base on the text values within the OptionSet, North52's advanced business rules engine for Microsoft Dynamics 365 / Dataverse can easily help you achieve this.
Using two option sets, Continents and Countries, we show how to filter Countries using the Continent text value. In our example below the Country list contains the continent name within the value e.g. Canada (North America). When the user selects a Continent, North52 takes the selected text value and filters all the countries with that continent in the name.
North52 Decision Suite Solution
The North52 Decision Suite solution works like this:
- A ClientSide - Perform Action Formula is set up on the Account table:
- The Source Properties are set to Onload and Continents
- The first sheet retrieves the Continent-Countries OptionSet values as an entity collection
- The second sheet takes the selected Continent and loops over all Continent-Countries matching each one that contains the selected Continent and builds up a list of matching OptionSet values
- The third sheet then filters the Continent-Countries OptionSet using the matched list and the SetPicklistValues() function
Formula
To create this Formula:
- Open the North52 App
- Navigate to Business Process Activities > Formulas
- Create a new formula, setting the following values in the Formula Guide:
- Source Entity set to Account
- Set Formula Type to ClientSide - Perform Action
- Select the Decision Table editor
- Change the Name of the formula to Account - ClientSide - Filter Countries based on Continent
- Click Save
Sheet 1. Get OptionSet Data
- Double click the sheet name and enter 1. Get OptionSet Data
- Right-click column A and select Insert > Insert Inline Calculation
- Delete the Condition and Action columns
- Select cell A4 and from the Functions list click on GetEntityMetadata
- Replace the placeholders with 'account', 'Attributes.n52demo_ContinentsCountries.OptionSet.Options'
- Note the second parameter OptionSet reference needs to be the Schema Name not the Logical Name
Sheet 2. Build Filtered OptionSet Data
- Insert a new Decision Table sheet: right-click on the first sheet and select Insert > Insert Decision Table
- Double-click the sheet name and enter 2. Build Filtered OptionSet Data
- Right-click column A and select Insert > ForEachRecord
- In cell A2 enter Loop on OptionSets
- We need to expose row 3 to set the variable holding the OptionSet collection to loop over - press F4 or right-click and select Operations > Toggle Advanced Mode
- Select cell A3, and expand the cell editor by clicking the + button
- Click the Source menu and then the refresh button
- Expand Decision Table Calculations > 1. Get OptionSet Data then CRTL-click on Get All OptionSet Values
- Close the cell editor
- In cell B2 enter Check label name
- In cell B3 enter TRUE
- Select cell B4
- From the Functions tab, find and select Contains
- Replace the placeholder text 'inputstring' with CurrentRecord('Label') (this is the value of the Label for the OptionSet meta data)
- Delete the placeholder text 'stringtofind', then from the Source tab, expand Source and select Continents {Name} (this will be the selected Continent from the form)
- Delete the Action column
- Right-click in column C and select Insert > Insert SetVarConcat Calculation (*-)
- In cell C2 enter Filtered List
- In cell C4 enter ToString(CurrentRecord('Value')) + ',' (for each matched row the OptionSet value e.g. 903840000 followed by a comma will be added to the variable)
- We can hide row 3 now, press F4 or right-click and select Operations > Toggle Advanced Mode
Sheet 3. Set MultiSelect Picklist
- Insert a new Decision Table sheet: right-click on the first sheet and select Insert > Insert Decision Table
- Double-click the sheet name and enter 3. Set MultiSelect Picklist
- Ensure the sheet is set to ClientSide: right-click on the sheet and select Set Sheet Options > Use ClientSide
- Select cell A2, click the Source menu and then the refresh button
- Expand Decision Table Calculations > 2. Build Filtered OptionSet Data then click on Filtered List
- In cell A4 enter !='?' (a question mark will be returned in the Filtered List if no matches are found)
- Right-click column B and select Insert > Insert Inline Calculation
- From the Functions tab, find and select ReplaceLastOccurrence
- Delete the placeholder text 'inputstring', then from the Source tab expand Decision Table Calculations > 2. Build Filtered OptionSet Data then click on Filtered List
- Replace the placeholder text 'stringtobereplaced' with a comma ','
- Replace the placeholder text 'replacementstring' with an empty string ''
- In cell C2 enter Filter OptionSet
- Select cell C3, and expand the cell editor by clicking the + button
- From the Functions tab, find and select SetPicklistValues
- Delete the placeholder text 'field', then from the Source tab expand Source then CTRL-click on Continent-Countries {Value}
- Replace the placeholder text 'emptyvalue' with true
- Delete the placeholder text 'optionsetvalue', 'optionsetvalue', then from the Source tab expand Decision Table Calculations > 3. Set MultiSelect Picklist then click on Cleaned OptionSet Data
- Right-click column D and select Insert > Action
- Select cell D2, then from the Source tab expand Source then click on Continent-Countries {Value}
- Select cell D4, then from the Functions tab find EnableFields and CRTL-click EnableFields
- Select cell D5, then from the Functions tab find DisableFields and CRTL-click DisableFields
- Right-click column E and select Insert > Action
- Select cell E2, then from the Source tab expand Source then click on Continent-Countries {Value}
- Select cell E5, then from the Functions tab find ClearFields and CRTL-click ClearFields
