Value List from Calculation Field

[Matt Malyschko]

Hi,

I have a table called Customers, holding fields 'Customer ID', 'First Name', 'Last Name', 'Full Name'. Full Name = First Name & " " & Last Name.

I want a value list that will allow me to view the full names, and select a value based on this. How is this doable? I've tried many solutions I found around the place, but nothing seems to work.

Any help would be greatly appreciated.

[LaRetta]

Full Name should be calculation. It will be indexable so it can be used in a value list. Create a value list call Customer Names. Base it upon selecting the CustomerID in the left pane and the FullName in the right pane. Then below specify 'all values' and 'show values from second field'. Attach pop-up to your field for selection. :-)

[Matt Malyschko]

Full Name should be calculation. It will be indexable so it can be used in a value list. Create a value list call Customer Names. Base it upon selecting the CustomerID in the left pane and the FullName in the right pane. Then below specify 'all values' and 'show values from second field'. Attach pop-up to your field for selection. :-)

OK this works.

Say I have a field that comes from a separate table, eg: Sales Reps. In which each Customer also now has a 'Sales Rep ID' attached to it. I now wish to display the value of Sales Rep Name & " - " & Full Name in my value list.

This then tells me it cannot be referenced and does not work in the value list.

[LaRetta]

I would suggest that you instead consider a conditional value list. User would select the sales rep in the first pop-up and the second pop-up would only show the customers for that rep for selection. You can search here for conditional value list. Look for examples by Comment two-tier self-clearing.

Sorry, on iPad so I'm a bit limited at the moment. Others can assist or I'll provide more info later when I get the chance. :-)

[Matt Malyschko]

Hmmm, this is not really what I'm looking for. I understand how to make conditional value lists, but that's not really what I want. It's not to select the sale's rep, it's just to display both values, in the same field.

[LaRetta]

Then you must use regular text field for the Rep's name in Customers. Still use their ID for relationships though. Also, the Rep Name must be forced to update if you change the customer's SalesRepID. Handle that by using an auto-enter calculation (replace existing value) with something like:

Let ( trigger = SalesRepID ; salesReps::RepName )

This rep name would then be concatenated in your calc.

[Matt Malyschko]

Sweet! This is great!