Getting the 2nd column from a Value List
I have a global parameters table which I'm using to select criteria for a Find in order to select a set of records to include in a report. One of the fields in the global table is a Category Code which uses a Value List defined to list the 2nd field (name) from the Category table. User chooses a Category from the value list and the Category Code is selected for inclusion in the Find. The Find gets the correct records, ie. only those with the chosen Category Code. So far, so good.
But, what I also want is to get the Category Name when the user selects a Category, so that I can use it in the title of the report, eg. "Listing Items for category xxxx". Seems a simple requirement - the Category Name is obviously related to the Category Code and it is also displayed in the value list. But I cannot find any way to do this; I cannot see any function to either get the value of the second column (Category Name) from the value list or to lookup the Category Name from the Category table using the Category Code. I have tried being tricky and creating a relationship between the global table and the Category table but that does not work either.
I am quite new to FileMaker. In Microsoft Access, this simple, common requirement is addressed through an inbuilt function which allows you to get values from the second (or third, etc) column of a value list (for the chosen value). In Access I could also use a database lookup function to get the name using the code. But I cannot find any equivalent functions in FileMaker.
My data is in a MySQL database so I guess I could write a bit of Applescript to query the MySQL database to get the Category Name using the Code selected from the value list, but that seems like ridiculous overkill for such a simple requirement.
What am I missing / doing wrong?