Searching for Categories in LMS

One of the areas where the default LMS Search Selectors lack, is in the capacity to easily view, search, and download what the parent category is.  In any hierarchical data type, it is helpful to see the rollup\down of the data.   In this blog, I’ll show you how to update the ‘Category’ Search Selector to make the Parent Category ID visible, searchable, and downloadable.

First, let’s revisit what Categories are.  While Categories have not changed in terms of functionality in many years (longer than I’ve been around), their name has.  For those that have been working in SuccessFactors Learning for a while, you may remember that present day Categories used to be referred to as “Topics”.  If you’ve been around even longer, they were known as “Subject Areas”.  So, if you see any documentation lingering around on any of these three terms, its all the same, and is very likely to still be valid!

Categories are one of the many ways in which a learner can filter (or “drill down”) on content while browsing the Library.  In the example I’ll use today, we have two Categories(“Behavioral Skills” and “Business Process Competence”) that roll up into “Professional Development”.

OK – so we understand what Categories are now.  Let’s jump into what we need to change:

1. Navigate to system Administration –> Configuration –> Search Selectors
2. Locate the Search Selector named “Category”, and click the Edit button.
3. Update the Select statement to include the Parent subject area ID by adding SUBJ_ID_PARENT to the statementBEFORE (Default):

   selectClause.mainSqlClause=PA_SUBJ.subj_id,SUBJ_DESC,STYLE_COLOR,STYLE_COLOR_CUSTOMIZED​

   AFTER:

   selectClause.mainSqlClause=PA_SUBJ.SUBJ_ID, PA_SUBJ.SUBJ_DESC, SUBJ_ID_PARENT,STYLE_COLOR,STYLE_COLOR_CUSTOMIZED​

4. Next, we need to tell our SQL Statement what table to get parent relationship from, which is the PA_SUBJ_RELATION table.  Update mainSqlFrom lines to the following:

   mainSqlFrom=PA_SUBJ LEFT JOIN PA_SUBJ_RELATION ON PA_SUBJ.SUBJ_ID = PA_SUBJ_RELATION.SUBJ_ID_CHILD
   mainSqlFromHana=PA_SUBJ INNER JOIN PA_I18N_ACTIVE_LOCALE_LABEL I18N_ACTIVE_LOCALE_LABEL ON I18N_ACTIVE_LOCALE_LABEL.locale_id = '$DATA_LOCALIZED_COLUMN$'  INNER JOIN PA_I18N_LABEL I18N_LABEL ON I18N_ACTIVE_LOCALE_LABEL.label_id = I18N_LABEL.label_id and PA_SUBJ.subj_desc = I18N_LABEL.label_id
   
   ​

5. The third part, where have to set some criteria in our “where “statement.  Find the link that is labelled (starts with) “mainSqlStatisWhere=”  and update it to this:

   mainSqlStaticWhere=(PA_SUBJ_RELATION.DIRECT_LINK='Y' OR PA_SUBJ_RELATION.DIRECT_LINK Is Null)​

6. Lastly, since all of the following lines are new (as opposed to update of the existing code), you can copy/paste these into place.  Up until now, we’ve only update the SQL to pull the data required, but there would have been no changes to the UI if we stopped here.  These lines are what make the UI elements visible to search on and download.

   selectClause.attributes.SUBJ_ID_PARENT.enabled=true
   selectClause.attributes.SUBJ_ID_PARENT.type=string
   selectClause.attributes.SUBJ_ID_PARENT.fieldName=SUBJ_ID_PARENT
   
   searchResultColumns.SUBJ_ID_PARENT.enabled=true
   searchResultColumns.SUBJ_ID_PARENT.order=3.0
   searchResultColumns.SUBJ_ID_PARENT.columnID=SUBJ_ID_PARENT
   searchResultColumns.SUBJ_ID_PARENT.attributeRefID=SUBJ_ID_PARENT
   searchResultColumns.SUBJ_ID_PARENT.labelID=label.ParentSubjectAreaID
   searchResultColumns.SUBJ_ID_PARENT.i18nFormatDataType=String
   searchResultColumns.SUBJ_ID_PARENT.width=35
   searchResultColumns.SUBJ_ID_PARENT.visibilityType=mandatory
   searchResultColumns.SUBJ_ID_PARENT.orderbyOptionRef=
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.type=view
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.enabled=true
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.order=1.0
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.url=../admin/references/body_generalref_subjectarea_view.jsp
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.params.SUBJ_ID_PARENT.enabled=true
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.params.SUBJ_ID_PARENT.order=1.0
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.params.SUBJ_ID_PARENT.name=SUBJ_ID_PARENT
   searchResultColumns.SUBJ_ID_PARENT.hrefs.view.params.SUBJ_ID_PARENT.attributeRef=SUBJ_ID_PARENT
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.type=edit
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.enabled=true
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.order=2.0
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.url=../admin/references/editSubjectArea.do
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.params.SUBJ_ID_PARENT.enabled=true
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.params.SUBJ_ID_PARENT.order=1.0
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.params.SUBJ_ID_PARENT.name=SUBJ_ID_PARENT
   searchResultColumns.SUBJ_ID_PARENT.hrefs.edit.params.SUBJ_ID_PARENT.attributeRef=SUBJ_ID_PARENT
   
   criteria.ParentID=com.plateausystems.elms.client.search.MultipleIDSearchCriterion
   criteria.ParentID.name=ParentID
   criteria.ParentID.enabled=true
   criteria.ParentID.order=6.0
   criteria.ParentID.filter=$CASE$(SUBJ_ID_PARENT) $OPERATOR$ $VALUE$
   criteria.ParentID.isCaseSensitive=true
   criteria.ParentID.i18nFormatType=string
   criteria.ParentID.criterionUI.uiType=TextCriterion
   criteria.ParentID.criterionUI.uiLabel=label.ParentSubjectAreaID
   criteria.ParentID.criterionUI.visible=true
   criteria.ParentID.criterionUI.matchOptions.OP_EXACTMATCH.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_EXACTMATCH.order=1.0
   criteria.ParentID.criterionUI.matchOptions.OP_EXACTMATCH.value=OP_EXACTMATCH
   criteria.ParentID.criterionUI.matchOptions.OP_EXACTMATCH.label=label.Exact
   criteria.ParentID.criterionUI.matchOptions.OP_ANYMATCH.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_ANYMATCH.order=2.0
   criteria.ParentID.criterionUI.matchOptions.OP_ANYMATCH.value=OP_ANYMATCH
   criteria.ParentID.criterionUI.matchOptions.OP_ANYMATCH.label=label.Any
   criteria.ParentID.criterionUI.matchOptions.OP_STARTWITH.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_STARTWITH.order=3.0
   criteria.ParentID.criterionUI.matchOptions.OP_STARTWITH.value=OP_STARTWITH
   criteria.ParentID.criterionUI.matchOptions.OP_STARTWITH.label=label.StartsWith
   criteria.ParentID.criterionUI.matchOptions.OP_CONTAINS.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_CONTAINS.order=4.0
   criteria.ParentID.criterionUI.matchOptions.OP_CONTAINS.value=OP_CONTAINS
   criteria.ParentID.criterionUI.matchOptions.OP_CONTAINS.label=label.Contains
   criteria.ParentID.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.order=5.0
   criteria.ParentID.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.value=OP_DOES_NOT_CONTAIN
   criteria.ParentID.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.label=label.DoesNotContain
   criteria.ParentID.criterionUI.matchOptions.OP_NULL.enabled=true
   criteria.ParentID.criterionUI.matchOptions.OP_NULL.order=6.0
   criteria.ParentID.criterionUI.matchOptions.OP_NULL.value=OP_NULL
   criteria.ParentID.criterionUI.matchOptions.OP_NULL.label=label.IsEmpty
   criteria.ParentID.filterOperator==
   criteria.ParentID.valueDataType=string
   criteria.ParentID.filterSelectorName=ParentID
   criteria.ParentID.visibilityType=mandatory
   criteria.ParentID.defaultUIType=TextCriterion​

Lastyl, let’s test your changes.

Go to Categories, and you should, by default, see the Parent ID field available as both a search vriteria as well as and search result (and therefore, downloadable).

Search for Parent Category

I hope you’ve found this useful!

*This code is provided without warrentee or support.  Only that it worked at the time of this article, in an environment where no other modifications to this particular search selector had been made. Please use caution when implementing this code, or any other customization to your search selectors.

Related Blogs:

Searching for NOT empty values in LMS

https://blogs.sap.com/2022/04/15/searching-for-no…ty-values-in-lms/ ‎

Implementing “Email Address” in your User Search

https://blogs.sap.com/2022/06/02/implementing-email-address-in-your-user-search/