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

[svirkar420]

Hi @CCZMAX1 , Can you share some screenshots on how have you configured the reference variable. This will help me understand more.

If this solution helped you Please Mark this solution as accepted and helpful as it will be helpful for other users as well.
Best Regards.
Saurabh V.

[CCZMAX1]

Hi Saurabh, thank you for your reply.  The reference variable is very basic.  

[RaghavSh]

@CCZMAX1 I believe the order of your your tables in database view is same, try changing it to 100, 200 etc and then see if you are still getting the same issue.

Please mark the answer correct/helpful accordingly.

[CCZMAX1]

Thanks Raghav, the database view currently looks like this but if I swaps the order around the location variable is not populated with the location.