If you've ever used a reference field, you've probably seen that when you start typing, an auto-complete drop-down appears, to help you out. The info that appears in this auto-complete drop-down, is usually the display value for a given table (that is, the field which has the display property set to true).
There are many cases where you may want more information than that. Maybe you've got many records with similar (or even identical) display values/names. Maybe you're referencing the user table, and want to make sure you don't select the wrong "John Smith".
To enable display of more than just the display value in the auto-complete drop-down, you've got to customize the dictionary for the field you're working with, and add some attributes. Here's how:
- Open the dictionary record for the field you're working with, by right-clicking the field, and selecting "Configure Dictionary".
- On the dictionary record, find the Attributes related list, and click New.
- For the Attribute, select Reference auto completer, and for the Value, enter AJAXTableCompleter.
- Click Submit, then create a new Attribute, by clicking New again, on the Attributes related list.
- In the Attribute field, select Reference auto completer columns, and set the value to a semicolon-separated list (like "name;email;u_employee_id").
- In my example, I'm customizing a reference to the Problem table, so I'm going to use Number, State, and Opened by. However, I don't need to add the field Number to the Reference auto completer columns attribute, since it is the Display Value for the Problem table.
When you add Attributes to the related list on a dictionary entry, they are added to the sys_schema_attribute_m2m table, but are also added to the Attributes field in the Advanced view of the dictionary record (and similarly, when you add attributes to this field, they are populated in the above mentioned m2m table). This means that you can just as well populate or modify attributes using this field, as you can see below:
So the Reference auto completer property is here called ref_auto_completer. The Reference auto completer columns property also becomes ref_ac_columns. The properties are separated by a comma. The result of these attributes is this:
Now that you're clear on how these properties work, there are a couple more that you can add if you'd like:
- Setting the Reference auto completer columns search (ref_ac_columns_search) property to true allows a user to search within the reference field, by any field specified in the ref_ac_columns property.
- In my example above with the Problem field for example, if I had this property set to true, a user could enter System Administrator, and get results that were opened by the System Administrator account.
- Otherwise (when not set, or when set to false), the user can only search by the display value column on the record.
- Setting the Reference auto completer order by (ref_ac_order_by) attribute allows you to choose a field (on the referenced table) on which to sort.
- Note that you do not necessarily have to sort based on a field that is selected in the ref_ac_columns property!