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.