I have 2 tables a Room table and I have a value list table that uses enumerations (enum) as the key and a name field.
I have value lists based on the enum but displaying the name field (unrelated to the room table).
So as would be expected the enum is stored in the Room::category field that uses the value list.
How would one concatenate the name field of that value list in the room table where the value list is used.
ValueList::enum actual data= 206
ValueList::name displayed data = Bathroom
Room::name date = Master
Room:category data = 206
Do not know where to start all I get is Master 206 when I concatenate.
The result I am looking for is "Master Bathroom"
Thanks in advance for any guidance.
A couple of ways.
So what you are selecting in the value list is what you refer to as enum, but in FileMaker lingo you'd call it a primary key, it's a numerical identifier pointing to a specific record of your value list table - in this case Bathroom.
So to display "Master Bathroom", you're going to need to obtain the name field value from the ValueList table, where the enum field value is 206.
1. You could create a relationship. On the relationship graph, you want to add a relationship between "Room" and "ValueList". The predicate in the relationship will be ValueList::enum = Room::category
What this gives you is when 206 is the value in Room::Category, you can look through the relationship to the matching ValueList record. This gives you the ability to then use the name field in a calculation back on the Room table. For example if you created a calculation field on the Room table named "Display", and it was:
name & if ( not isempty ( ValueList::name ) ; " " & ValueList::name ; "" )
This is adding onto the end of your Room name field value, the corresponding value lists name field value (but only if there exists one through the relationship, we don't want to add a blank space onto the end of the name unnecessarily).
2. ExecuteSQL
You could also write an ExecuteSQL query to obtain this value, this means you do not require to use a relationship:
Let (
~name = ExecuteSQL ( "SELECT \"name\" FROM \"ValueList\" WHERE \"enum\" = ?" ; "" ; "" ; category )
;
name & if ( not isempty ( ~name ) ; " " & ~name ; "" )
)
Note I just escape everything in the ExecuteSQL to be safe, but you probably don't need to.
Good luck.