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 Accounts' shipping method to be alphabetical whenever a User is in the security role Sales Manager

North52 BPA Solution

The North52 BPA 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


  • 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


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 helps Government (federal and state) departments with complex rules in their Dynamics 365 systems

Public sector organizations frequently deal with large user bases and complex rules and routings. The North52 Decision Suite helps government orgnizations with Dynamics 365 manage processes like:

  • Case Management - task checklists, routing, prioritization and approval
  • Eligibility checks for applications/enrollment
  • Licensing administration

Learn more about the North52 Decision Suite