Changing the sort order of an SSA (Search Select Advanced) field

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Changing the sort order of an SSA (Search Select Advanced) field

  • Comments 9
  • Likes

I was recently asked how to change the view order of the drop down in a SSA field (Advanced Search Select/Search Select Advanced).

The sorting is always on the view field for the SSA field in alphabetical order.  You cannot change that.  But you can change which field is used for the view field. 

How SSA fields behave is different for each entity.  There are four translations for each entity that tell CRM how to create SSA fields.  These translations all have a code that matches the name of entity and a Caption Family Type of ‘Tags’.  The family is different for each bit of information, here is what they mean:
  • ss_entities – this translation tells CRM that the entity is available for use in an SSA field.  Each of the translations should be set to the entity name
  • ss_searchtables – this translation tells CRM which table or view to use to get the information for the SSA field.
  • ss_idfields – this translation tells CRM which field in the table/view defined in ss_searchtables to use as the value/id field for the SSA field.
  • ss_viewfields – this translation tells CRM which field is used as the display field for the SSA

Note that the display field defined in the ss_viewfields translation is different from the view fields defined in the SSA field meta-data, the view fields in the meta data refer to which fields should be displayed in the drop down box.  The display field is what is shown in the SSA field when a value is selected.  For example on the Person entity this defaults to pers_fullname.

CRM always sorts the values in the SSA drop down by the ss_viewfield in ascending alphabetical order.To see examples of how these translations are populated go to Administration -> Customisation -> Translations and search for a caption family of “ss_” and a Caption Code of “person”.  You will see the following:

As an example of how you can use the above information to change the sort order of an SSA field drop down, I will show you how to change the Person SSA field to be sorted by last name instead of first name which is the system default.

First we need to create a new field in the vSearchListPerson that is in the format of “<<last name>>, <<first name>>”.  We will call this pers_fullname2.  We will create it on the existing vSearchListPerson view by adding in the following:
RTRIM(ISNULL(Pers_LastName, '')) + ', ' + RTRIM(ISNULL(Pers_FirstName, '')) AS Pers_FullName2
Next we update the SS_ViewFields for the Person entity to have pers_fullname2 instead of pers_fullname.Now we refresh the metadata. 

Now SSA fields will show people in the format of “<<lastname>>, <<firstname>>” and it will be sorted by this, so last name first.

Note: to complete this customization you probably would also want to change the view fields on the entity field’s meta data to use pers_fullname2 instead of pers_fullname so that the format is consistent between what is shown in the field and in the selection drop down.
Comments
  • Hi Jack,

    I’ve tried to change the fild cmli_comm_personid on the screen CommWebPicker as you have described here.

    If I just change the vsearchlistperson from …

    “RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName”… to

    “RTRIM(ISNULL(Pers_LastName, '')) + ', ' + RTRIM(ISNULL(Pers_FirstName, '')) AS Pers_FullName”

    and refresh the Metadata,  the SSA field will be displayed as expected in the format “<<lastname>>, <<firstname>>” and ordered by last name.

    If I ad the part RTRIM(ISNULL(Pers_LastName, '')) + ', ' + RTRIM(ISNULL(Pers_FirstName, '')) AS Pers_FullName2 to vsearchlistperson and change ss_viewfiels to Pers_FullName2 the SSA field will be ordered by the last name but still shown in the format “<<firstname>> <<lastname>>”

    Do you have any Idea what I’ve made wrong?

    Thanks

    Waldemar

  • Hi Waldemar,

    Did you change the ss_viewfields caption to pers_fullname2 as described?

  • Hi Jack,

    yes I’ve changed all languages to pers_fullname2 for the caption ss_viewfields.

  • Hi Jack,

    I found the solution.

    Additionally to the steps, which you have described, I’ve added a new caption:

    “Caption Code: Pers_FullName2” , “Caption Family: ColNames” , UK / German Translation. Pers_FullName2”

    Now the field “Pers_FullName2” is available in the properties of the SSA field “cmli_comm_personid” and can be selected to for the screen.

    Regards

    Waldemar

  • Thanks for sharing that!

  • This was a helpful article, thank you

  • Hi Jack,

    this is good but is it possible to change the order and applying a DESC order instead of an ASC ?

    I would like to present the Case list in the CommWebPicker screen ordered by Case_RefID DESC so that the latest cases are presented first.

    Any idea or workaround to solve that problem ? (not sure if there is another field in Cases entity that would help...)

    Thanks for sharing ideas.

    Proconsult

  • Thanks for this - as valid and useful today as it was 5 years ago when it was first posted!

  • Sorry to jump in on an old post. I also had a requirement that required the opportunity SSA dropdown to be sorted by the most recent created opportunity first. I managed to do this by doing the following :

    1. Created a new view which included a field which is sorted by the opportunity created date descending

    CREATE VIEW vSummaryOpportunityWithSort AS

    SELECT

    ROW_NUMBER() OVER (ORDER BY Oppo_CreatedDate DESC) AS SortKey,

    Oppo_OpportunityId, Oppo_Description, Oppo_Note, Oppo_Stage,Oppo_PrimaryCompanyId,Oppo_PrimaryPersonId,Oppo_PrimaryAccountId

    FROM vSummaryOpportunity

    The SortKey column above could be changed to whatever you wanted to be sorted by, in my case I wanted the most recent opportunity first.

    2. Changed the SS_SearchTables and SS_ViewFields translations to look at vSummaryOpportunityWithSort and SortKey respectively. My translations looked like this :

    Caption Family, Caption Code, UK Translation

    SS_Entities, Opportunity, Opportunity

    SS_IdFields, Opportunity, Oppo_OpportunityId

    SS_RelViewFields, Opportunity, Comp_Name,oppo_Description

    SS_SearchTables, Opportunity, vSummaryOpportunityWithSort

    SS_ViewFields, Opportunity, SortKey

    Now the SSA list is displayed by the most recent created opportunity first.