Value list based on calculation that contains related data

[RSGC]

I have been searchind here for an answer, but probably don't know what to search for. And/or I am missing a simple solution. 

 

Three tables:

Customers

Sites

Tickets

Each customer may have one or more sites. Each Site may have one or more ticket. Most of the "action" will be creating tickets. I would like to have a single pop up on the Ticket layout that allows the user to pick the Site primary key, but displays the "Customers::Name - Sites::Name." Ideally this would looks something like:

Cust A-home Office

CustA-satelite officeA

CustB-Home Office

I thought -- no problem, add a calculated field to Sites table "Sites::CustSite" equal to Customers::Name&" - "&Sites::Name" and use that as a value list second value to display. This is not working as it will no index the calculated field. From what I can see I I can not change the storage options use to the calculation references a related field. 

Many Customers will not have more than one site. But they will all have at least one. I am open to other ways to accomplish this, but hoping to have only one selection to pick the site (and thus customer), but display the "pick list" with customers and sorted primarily by customer. Any hope?

Bob

 

[comment]

Instead of a Calculation field, you could use a Text field with auto-entered calculated value, replacing existing value. However, this will not automatically update if the customer's name is modified.

Alternatively, consider using another selection method -  e.g. selecting from a card window showing a list view of the Sites table.

 

Edited April 1, 2021 by comment

[RSGC]

No experience with cards yet, will experiment with that. Text field as well. Unlikely Customer name will update often if at all. So that is viable. Thanjs!

Bob