How to Enable "Last Name First Name" Search in Requested For Reference Field?

[Sachithananda 1]

Hi Community,

I'm working on improving the search behavior of the standard Requested For reference field. The requirement is that users should be searchable regardless of whether the search starts with the first name or the last name. For example, for a user record named Abraham Lincoln, searches such as Abraham, Abraham Lincoln, and Lincoln should all return the correct user record. Initially, searches using the surname followed by the first name, such as Lincoln Abraham or Lincoln A, were not returning any results.

To address this, I configured the reference field with:

ref_ac_columns=user_name;first_name;last_name

This successfully enabled searching by username, first name, and last name individually. However, searches using the combined format Last Name + First Name (for example, Lincoln Abraham) still do not return any matches, even though both values exist on the user record.

Has anyone encountered this requirement before and found a way to support searching against a concatenated value such as last_name + first_name for the standard Requested For field? Is there a recommended approach using reference auto-completers, advanced reference qualifiers, search attributes, or a custom solution that would allow users to search in the Last Name First Name format without affecting the existing search functionality?

Any insights or best practices would be greatly appreciated.

Thanks in advance!

[Ankur Bawiskar · Accepted solution]

@Sachithananda 1 

It's not directly possible as Name field in sys_user stores First Name + Last Name

Workaround

1) custom string field on sys_user which stores Last Name + First Name

2) then use before insert/update BR which ensures this custom field is always in sync when either First Name OR Last Name changes

Before insert/update Business Rule on sys_user (Condition: first_name changes OR last_name changes)

(function executeRule(current, previous /*nulls when async*/) {
    var first = current.getValue('first_name') || '';
    var last = current.getValue('last_name') || '';
    current.setValue('u_reverse_name', (last + ' ' + first).trim());
})(current, previous);

3) add this new field in ref_ac_columns

ref_auto_completer=AJAXTableCompleter,ref_ac_columns=first_name;last_name;u_reverse_name;user_name,ref_ac_columns_search=true,ref_ac_display_value=true

4) Run a one-time Background Script to backfill existing active users.

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏