Views:

Overview

In this KB article, we will show how to filter and sort picklists/option sets. 

In our example we will change the order of an Account's shipping method to be alphabetical whenever a User is in the security role Sales Manager

North52 Decision Suite

The North52 Decision Suite solution works like this:

  • A client-side Formula is set up the Account entity that fires on the onLoad event
    • This formula will check the current User's security roles
      • If the User has the security role Sales Manager the Address 1 shipping method will be ordered alphabetically

Formula

  • 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
    • Click Create
  • Change the Name of the formula to Order a Option set/Picklist depending on a User's role
  • Expand Source & Target
  • Under Source & Property select the onLoad event of the form you want this formula to fire on
  • Copy and paste the following formula: 
    Iftrue(IsUserInSecurityRoles(WhoAmI(), 'Sales Manager') = true, 

  xCacheLoadLocal('Address1_ShippingMethodCode_OptionSet', GetEntityMetadata('account', 'Attributes.Address1_ShippingMethodCode.OptionSet.Options')),
  xCacheFilterLocal('Address1_ShippingMethodCode_OptionSet', '', 'Label ASC'),

  ForEachRecord(    GetVar('Address1_ShippingMethodCode_OptionSet'),

    SetVarConcat('Ordered_Address1_ShippingMethodCode_OptionSet_Values',  ToString(CurrentRecord('Value'))+',')

  ),

  SetPicklistValues('address1_shippingmethodcode', true,TrimEnd(GetVar('Ordered_Address1_ShippingMethodCode_OptionSet_Values'),',')) 

)
  • Click Save

Testing 

Open up an Account as a User with and without the security role Sales Manager, you will see the ordering of the shipping method change. 

Note: you will need to open the Account record using the form you selected earlier. 

Did you know?

North52 Decision Suite helps streamline complex business processes

North52 Decision Suite is a cloud-based or on-premise decision management/decision engine platform for the Dynamics 365 and Power Platform that uses data, analytics and business rules to automate decisions.

Companies use North52 to streamline their processes, improve the quality of the decisions inside their Dynamics 365 systems, and shorten the time it takes to adjust to business and competitive changes.

Learn more about the North52 Decision Suite

Keywords: xCache, Decision Table, SetPicklistValues, ToString, iftrue, ForEachRecord, CurrentRecord, IsUserInSecurityRoles, WhoAmI, GetVar, SetVar, SetVarConcat, xCacheFilterLocal, xCacheLoadLocal, TrimEnd, GetEntityMetadata, Formula Manager