Calculation w/ FilterValues showing Values from 2nd Field in a Value List

[chikanyc]

Hi, I have a Song table and a Keyword table. Let's say the Categories in the Keyword table are Instruments, Genre, Tempo.

Each category has its own field in the Song table which is populated by keywords in that Category. E.g. Instruments would have Timpani, Guitar, Harp, etc. and the user would check off the Instruments that apply. This is displayed by a checkbox from a Value List that is populated by the first field: the UID from the Keyword table, and the Description from the Keyword table. And it only shows the 2nd field.

I've been trying to set up a calculation for a field in my Songs table that would show be a list of checked off Instruments. But the calculation I keep getting only lists off values from the 1st field in my Value List (the UID) and not the 2nd field (the description). I want a list of the 2nd field...does anyone know how to do this?

As an example, this is what I've been using that only shows the first field from the Value List:

Substitute (

FilterValues ( ValueListItems ( Get ( FileName ) ; "VL_AllInstruments" ) ; Instruments ) & ¶ ;

[ "¶¶" ; "" ] ; [ ¶ ; ", " ]

)

[comment]

Use the List() function to get a list of related descriptions from the Keywords table.

[chikanyc]

Thanks comment for replying:)

I just tried inserting the List() function into my Calculation field, but it either just lists out all the UIDs (first field) in the Value List or it just gives me the UID of the checked off Values in my Value List...

- Still confused. Hmmm

Edited March 11, 2009 by Guest

[comment]

This has nothing to do with the value list. The value list as only a device for entering/displaying data in the Instruments field. Now you need a relationship to the Keywords table, based on matching the ID's entered into the Instruments field with the ID in the Keywords table. Once the records are related, you can get the list of related descriptions using List ( Keywords::Description ).

[chikanyc]

THANK YOU! Yay it worked:)