When working with reference fields, it is really important to understand how the display value for the reference field works. The display value for a reference field is what the end user actually sees when they search in a reference field to produce an autocomplete drop down and what they can see when a reference field gets populated. Information about reference fields and reference field display values can be found here.
Some questions about display values that I hear very often are “How do I show information from more than one field for the display value?” or “Can I have different display values on 2 reference fields that reference the same table?”. The limiting factor when answering these questions is that you’re limited to a single searchable display value per referenced table. What this means is that EVERY reference field that references a particular table needs to use that table’s display value so you cannot mix and match between reference fields. There are a couple of options that can be used to provide a bit more information to the user in these scenarios however.
The way I typically approach this type of requirement is to create a new calculated field that is a composite of the fields that I want to search on. Then I make that field my display value for that table. A good example of this is the ‘Name’ field on the user table. What you see is the full name of a user. If you look at the dictionary entry for that field however, you’ll see that it is actually a calculated field that pulls together the values from the First name, Last Name, and Middle Name fields.
Once this is done (and I’ve updated all of the records in the table so that my calculation gets stored on each record) I can make this field my display field for the table and change how searches are performed on references to that table. By using a ‘Contains’ type of search instead of the default ‘Starts with’ type of search against the display value for a table I can effectively search on multiple terms in the same field since I’ve included them in the same display value string. This article explains how to set up a ‘Contains’ autocomplete search for a particular table.
Another useful option for reference fields is to display additional attributes in the autocomplete drop-down. These attributes aren’t searchable and they won’t show up as the display value when the field gets populated, but they do make it very simple to display more than just the display value in an autocomplete.