All to common a request is to allow your LMS admins to search for users based on email address. You'll notice that, in the default configuration, LMS admins are unable to search for users using an email address as a criteria, despite the fact that email address is contained in the search results.
In this blog, I'll review the steps necessary to do this, and what you may need to adjust.
First, let's take a close look at the "User" Search selector. We will find this under
System Administration » Configuration » Search Selectors » User
Most of the work, in this particular example, is already done for you. Scroll down a few lines, and you'll see that the email address is already (by default) defined as an attribute, and is enabled.
Step 1: Verify that this code is already in place:
selectClause.attributes.emailAddr.enabled=true
selectClause.attributes.emailAddr.type=string
selectClause.attributes.emailAddr.fieldName=EMAIL_ADDR
Step 2: Verify (again) that this code is already in place:
The second step would have been to add the email address field to the main SQL clause, but again, you'll see that it is already there by default. (
This is a fairly long line of code, so I'm truncating it a bit for you). None the less, you should see the "PA_STUDENT.EMAIL_ADDR" field in the line somewhere.
selectClause.mainSqlClause=PA_STUDENT.STUD_ID, [...] , PA_STUDENT.EMAIL_ADDR,
Step 3: And again ... Verify that this code is already in place:
The third step would have been to define it as a search result, so that you can download it (or view in in the field chooser). Again, we find that this is already completed for you.
searchResultColumns.emailAddr.enabled=true
searchResultColumns.emailAddr.order=18.0
searchResultColumns.emailAddr.columnID=emailAddr
searchResultColumns.emailAddr.attributeRefID=emailAddr
searchResultColumns.emailAddr.labelID=label.EmailAddress
searchResultColumns.emailAddr.i18nFormatDataType=String
searchResultColumns.emailAddr.width=15
searchResultColumns.emailAddr.visibilityType=null
searchResultColumns.emailAddr.orderbyOptionRef=null
searchResultColumns.emailAddr.operators=null
In any other field that didn't previously exist at all, we would have had to define all of those from scratch.
Step 4: Add this code !
Since the above steps are already completed, we need only define the search criteria. This is the step you've all been waiting for! Copy/Paste the code segment below into you search selector. I try to put it with the other lines that start with "criteria.", and put it in numerical order. usually this is near the end.
criteria.EmailAddress=com.plateausystems.elms.client.search.MultipleIDSearchCriterion
criteria.EmailAddress.name=EmailAddress
criteria.EmailAddress.enabled=true
criteria.EmailAddress.order=77.0
criteria.EmailAddress.filter=$CASE$(email_addr) $OPERATOR$ $VALUE$
criteria.EmailAddress.isCaseSensitive=true
criteria.EmailAddress.i18nFormatType=string
criteria.EmailAddress.criterionUI.uiType=TextCriterion
criteria.EmailAddress.criterionUI.uiLabel=label.EmailAddress
criteria.EmailAddress.criterionUI.visible=true
criteria.EmailAddress.criterionUI.matchOptions.OP_EXACTMATCH.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_EXACTMATCH.order=1.0
criteria.EmailAddress.criterionUI.matchOptions.OP_EXACTMATCH.value=OP_EXACTMATCH
criteria.EmailAddress.criterionUI.matchOptions.OP_EXACTMATCH.label=label.Exact
criteria.EmailAddress.criterionUI.matchOptions.OP_ANYMATCH.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_ANYMATCH.order=2.0
criteria.EmailAddress.criterionUI.matchOptions.OP_ANYMATCH.value=OP_ANYMATCH
criteria.EmailAddress.criterionUI.matchOptions.OP_ANYMATCH.label=label.Any
criteria.EmailAddress.criterionUI.matchOptions.OP_STARTWITH.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_STARTWITH.order=3.0
criteria.EmailAddress.criterionUI.matchOptions.OP_STARTWITH.value=OP_STARTWITH
criteria.EmailAddress.criterionUI.matchOptions.OP_STARTWITH.label=label.StartsWith
criteria.EmailAddress.criterionUI.matchOptions.OP_CONTAINS.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_CONTAINS.order=4.0
criteria.EmailAddress.criterionUI.matchOptions.OP_CONTAINS.value=OP_CONTAINS
criteria.EmailAddress.criterionUI.matchOptions.OP_CONTAINS.label=label.Contains
criteria.EmailAddress.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.order=5.0
criteria.EmailAddress.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.value=OP_DOES_NOT_CONTAIN
criteria.EmailAddress.criterionUI.matchOptions.OP_DOES_NOT_CONTAIN.label=label.DoesNotContain
criteria.EmailAddress.criterionUI.matchOptions.OP_NULL.enabled=true
criteria.EmailAddress.criterionUI.matchOptions.OP_NULL.order=6.0
criteria.EmailAddress.criterionUI.matchOptions.OP_NULL.value=OP_NULL
criteria.EmailAddress.criterionUI.matchOptions.OP_NULL.label=label.IsEmpty
criteria.EmailAddress.filterOperator==
criteria.EmailAddress.valueDataType=string
criteria.EmailAddress.filterSelectorName=EmailAddress
criteria.EmailAddress.visibilityType=default
criteria.EmailAddress.defaultUIType=TextCriterion
You should be able to copy & paste the above code into the criteria section of your search selector without need for adjustments. If you've done other modifications, you may want to check line 4 (criteria.EmailAddress.order=77.0) above, and make sure that the order is the next one in sequence.
In conclusion, we've reviewed the code that already existed, and the code necessary to add email as a searchable field.
Happy searching!
UPDATE: Please be careful when updating Search Selectors. You are literally redesigning the SQL that gets run up against the LMS database. Any of the half-yearly updates could break your search selector. You may or may not even realize it happened, so if you've chosen to customize a search selector, please be in the habit of re-testing your search selectors after each release.
