Catalog item reference variable shows sys_id instead of display name after submit

[CCZMAX1]

Hi, I created a database view that links the OOB cmn_location table with an m2m table I created. The m2m table holds the relationship between a location in cmn_location and a record in a custom table.  All these tables are in the Global scope and set to be accessible from all scopes.

I created a MRVS (in Global) with a reference variable that points to the database view table then added the MRVS to my record producer in a custom scope. When I add a row to the MRVS, the location variable correctly shows the location display name. But after submitting the form, the location variable shows the sys_id instead of the display name in workspace, and it’s empty in classic. If I open the row preview in either workspace or classic, the location variable is empty and says, no results found if I try to select one. The odd thing is, if I add a new row directly in workspace or classic, it adds and displays fine. I just seems to have an issue when submitted via a record producer.  The record producer is NOT in global.

Does anyone know what might be causing this?

Many thanks,
Max

Classic

Workspace

Added via the workspace/classic

[RaghavSh]

https://www.servicenow.com/community/developer-forum/datab-view-how-to-get-display-value-of-a-sys-id... 

Check this, its a similar solution

[kaushal_snow]

Hi @CCZMAX1 ,

It's typically because the field on the target table is defined as a string (or non reference) type, so ServiceNow stores whatever value is passed (sys_id) and can’t resolve it to a display name later,  the solution is to change that field’s dictionary type to reference pointing to your location table so it can resolve display values.

If changing the field type isn’t possible (because of data constraints or scope boundaries), another approach is in your record producer script or post insert business rule to explicitly fetch the display value and populate a display name field (text) or reassign the reference field pointing to the record by sys_id so it resolves correctly when viewed....

If you found my response helpful, please mark it as ‘Accept as Solution’ and ‘Helpful’. This helps other community members find the right answer more easily and supports the community.

[CCZMAX1]

Thanks Kaushal.  Yes, the field that holds the location name is a string in cmn_location.  I will see if I can set this with a script prior or post submission.

Max

[CCZMAX1]

The challenge I now have is that whilst it displays the sysid to the location when the form is submitted a script within the record producer returns the  values starting with __ENC__  for the location instead of the sysid. 

I can set another variable on the MRVS to auto populate with the sysid of the location variable by dot walking but the sysid will show in in the row as hidden only hides from the pop up when adding a row.

[Ankur Bawiskar]

@CCZMAX1 

did you try using Lookup Select box variable on that table and see if it removes the sysId?

Then Set Lookup label as the Name field from location table

Note: ensure Name field is added as View field under the Location View table

If my response helped please mark it correct and close the thread so that it benefits future readers.