Jump to content

Value list based on calculation that contains related data


Recommended Posts

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

 

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.