It would be nice if filemaker value lists supported more than two columns like some competing products do.
You can define a calculation field that combines your text into a single field. Define a value list with this expression:
RoomNumber & ", " & BuildingName & ", " & StreetAddress
Make this calculation field your column 2 field.
Note: If some of the above fields might be blank in some cases, you can use If functions to keep the extra commas from appearing if you wish.
Thanks for the quick response.
I'm almost there now.
For my value list I'm using two tables.
for the first field I specify Location : idlocation
For the second field I added a calculation field 'option text' to location table and specified (from the context of location):
room_number & Building::name & Building::street_address
this doesn't work get '....will not work because field 'option text' cannot be indexed. ...'
If I instead make a calculation field in Building using name and street_address this will work.
However, I need the room_number from one table and the other fields from Building.
Can't see where my logic is wrong.
If I try just room_number is the calculation, this fails.
I get MySQL error: ...syntax to use near FROM SIS.location where idlocation IS NOT NULL ORDER BY 2, 1'
I must have an issue with the schema. Been a long week. But if you can see something I'm missing on the FM side I be greatful to know.
I'm not sure where MySQL comes in to this, that's a detail left out of your first post.
In filemaker, you can't combine values from two related tables, because the result, by definition, is unstored. Value lists that draw values from fields in a table, use the field's index to build the value list. Since unstored fields don't have an index, you can't use the result for a value list.
Two options--neither will do exactly what you prefer here, but they can get the job done.
1) Define a lookup field in one of the two tables that uses looked up values to copy the data from the related record. You now have all the values in one table so your calculation will work. Of course, you've now denormalized your data (you have duplicate copies of the info in more than one place) and you'll have to manage any updates to the looked up values so that the copies are alos updated. This may be something you can manage with a script trigger.
2) look into using conditional value lists to reduce the number of values in your value list at any one time. In your case, you might have a conditional value list that only displays data from the building table for a given location. Since all the values in the list are from the same location, you no longer need those fields in the value list itself.
Thanks for all your help. I got a clearer idea of how FM handles values list.
I found an alternative solution, which was to create my aggregated data in a MySQL view (I'm using an external database), and then use the field from the view to populate the value list.