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

[Ankur Bawiskar · Accepted solution]

@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.

[Ankur Bawiskar]

@CCZMAX1 

Thank you for marking my response as helpful.

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

[CCZMAX1]

Hi Ankur, I used a lookup select box and this worked.  However, the performance on the form is too slow. It takes mins of 6 seconds to load the modal to add a row and then about another 3 seconds to select a location.  There is over 20,000 records in this table.  So I may have to go back to a reference variable and update the variable after the form is submitted.

[Ankur Bawiskar]

@CCZMAX1 

yes it's recommended to have lookup select box variable only for limited records.