I have a value list that is the IDs of a given record of a "colors" table, such as the following:
The value list is called "Colors" and pulls from the ID column but shows the secondary "Colors" field, so its values are "1, 2, 3, 4, 5" but when you choose items from a pop-up menu they are "Red", "Green", "Blue", etc. I did this so the value list would be dynamic and editable, but of course any field that uses this list will receive the numerical ID value. This is perfect for the uses of the database, since it allows me to change the text in the "Color" field and have this be reflected throughout the database.
I specifically use this value list as a checkbox set for various records. When doing this, the value of the field that uses this is a concatenated list of the ID numbers separated by carriage returns.
However, there are times such as when printing a layout, where I would like the color name to be printed. Is there a way to have FileMaker convert the values to a formatted list for easy printing? In essence, I have a field that shows the entire checkbox set for me to select color values. For a selection of colors this would be "1 2 4" (assuming spaces here are carriage returns), but I would like it to display "Red, Green, Magenta" (separated by commas) when I print it.
Is this type of conversion possible for a single field? I cannot find a way in scripting to parse the list of IDs and then lookup their corresponding color values to concatenate and print. Is this possible in any way?