Best approach to display and use Store Codes from cmn_location in Catalog variables?

Jonathan Rousse · Thursday

Hello Community,

I have a Catalog Item with:

a Reference variable to cmn_location for the Primary Store;
a List Collector on cmn_location for Additional Store Visibility.

Both variables use a dynamic Reference Qualifier to filter the available locations based on the Requested For user.

This Catalog Item is used to create user accounts in a third-party application, which requires the Store Code to be sent through an API.

The display field of cmn_location table is name, while the Store Code required by our business process and external API is stored in the custom string field u_store_code.

Using ref_ac_columns, I can display the Store Code while searching for a location. However, once a location is selected, only the location name is displayed, both before submission (on the Service Portal form) and after submission (in the approval view and Catalog Task variables).

We need to be able see the Store Code to validate the request, and the integration must also retrieve the corresponding Store Codes to send them to the external API.

I identified two possible solutions:

Change the display field of cmn_location to u_store_code, but this would affect every reference to the Location table.
Use a client-callable Script Include and Catalog Client Scripts, along with an additional read-only variable to store and display the Store Code. This works, including dynamically updating the Store Code when the user changes the selected location. However, the same requirement may apply to several Catalog Items, and I would like to avoid duplicating this logic.
1. For the List Collector, the displayed Store Codes should also update dynamically whenever locations are added or removed.

Is there a more standard or reusable approach for this use case?

Thank you.

Juhi Poddar · Thursday

Hello @Jonathan Rousse

Use a Lookup Select Box instead of the Reference variable. It lets you choose what is stored and what is displayed on the variable itself, so the table display field stays untouched.

Create the variable with the type Lookup Select Box.

Set Lookup from table to cmn_location. Set Lookup value field and Lookup label field(s) as shown in the screenshot, where I used city and name as an example. Any field can be used here, and the label accepts a comma separated list. In your case, use u_store_code as the value (or Sys ID if you prefer) and u_store_code, name as the label.

Add your existing dynamic filter in the reference qualifier, based on the Requested For variable.

Hope this helps!

Thank You!

Juhi Poddar

Ankur Bawiskar · Thursday

@Jonathan Rousse

My suggestion

Keep cmn_location.name as the display field and use ref_ac_columns to show Store Code during search.
Add read-only Store Code variables for visibility after selection, approvals, and Catalog Tasks.
Use logic to dynamically update codes for both the Reference and List Collector.
For the API, retrieve u_store_code server-side using the selected location sys_ids, rather than relying on the display variables.

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

Jonathan Rousse · Friday

Hi @Juhi Poddar , @Ankur Bawiskar

Thank you very much for your answers. The Lookup Select Box solution works perfectly for my first field, where only one store need to be selected.

For the second field, however, users need to select several stores. As far as I understand, I will therefore need to keep a List Collector and use a Script Include and Catalog Client Script to retrieve and display the Store Codes dynamically.

Please let me know if you have another suggestion. Thank you again! 🙂

Juhi Poddar · Friday

@Jonathan Rousse

You can try this:

Create a Multi Row Variable Set and attach it to the catalog item.
Add a Lookup Select Box variable in the set and configure it the same way as your first field (table, value field, label field showing the Store Code, reference qualifier).
Select the Unique option on this variable, so the same store cannot be selected twice.

Result:

Hope this helps!

Thank You!

Juhi Poddar