x Want to participate? Join the group to interact
Sage CRM Team
Related
Recommended

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

2 minute read time.

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 "[lastname],[firstname]". 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.

  • 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.

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

  • 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

Generic Content
Generic Content

*Community Hub is the new name for Sage City