Calculated Fields when reference not working

[swathi Gadde]

Hello,

I have created a calculated field on the custom table called NameID(as i need in some format like Name(ID)) by using two fields Name and id. The data is available in the new field NameID and set display to true. So on the record producer i created a reference field which refers to the custom table. So that the NameID field should show up for the user to select. but when i open the record producer in service portal and select the  variable which refers to the custom table. It shows no record found. how can i fix this?

Thank you.

[Brad Bowman]

What you've shown and described is the recent selections, which there wouldn't be if you haven't populated any yet.  If you type *e or whatever, does the typeahead search return the correct results?  If you click the reference search (magnifying glass) icon, do you see a (correct) list of records?  If you select one of the records, does it display the NameID field correctly to represent the record that was selected?

[swathi Gadde]

Hi,

It shows No match found so I cannot select anything. The typeahead search nor the magnifying glass  doesnt show anything. But the data is available in the table.

[pavani_paluri · Accepted solution]

Hi @swathi Gadde ,

The field you made (NameID) is a calculated field. Service Portal reference lookups don’t work well with calculated fields because they aren’t stored in the database.
Reference fields only search on the display field of the table. If that display field is calculated, the search usually fails.
That’s why you see “No records found.”

Create a normal string field on your custom table (for example: u_name_id).
Fill it automatically with the value you want (Name(ID)) using a simple business rule:
Whenever a record is created or updated, copy Name + '(' + ID + ')' into u_name_id.
Set this new field as the display field in the table dictionary.
Now, when you use a reference variable in your record producer, the dropdown will show the combined value correctly.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for

Kind Regards,

Pavani P

[swathi Gadde]

Thank you.