how to find and count multiple things in a set?

[human]

We have a database of musical compositions, They are categorized by keywords, and the keywords are organized into categories. So for example, category 7 has vocals, and within that category there are vocal_rap, yodel, etc. Every track has an ID and there is a join table of ID's to keyword ID's, which in turn is related to a table of keyword ID's. to category ID's.

I need to first determine if any of a track's keywords are in category 7. Thats easy, I just go to the related ID in the join table, and then constrain the found set a set of keyword ID's that are in category 7. But now I further need to find if any of those found also have a keyword ID of 1 (acapella) or 2 (whistle), and I need a count of each of those ocurrences as well. The only two ways I can think to do that is to either loop through the set and check each one in turn, or to repeat the "go to related record and constrain step", each time constraining for those individual keywords. Which is better, or is there a better way?

Thanks.

[comment]

I have trouble understanding your question. I think you are describing a structure like:

Tracks -< TrackKeywords >- Keywords >- Categories
 

Given such structure, you can use the expression (evaluated from the context of a track) =

List ( Categories::CategoryID )

to get a list of ID's of all categories related to the current track, and similarly:

List ( Keywords::KeywordID )

will return a list of ID's of all related keywords.

You can use the FilterValues() function to determine the presence of a given item in the list, for example =

not IsEmpty ( FilterValues ( "2" ; List ( Keywords::KeywordID ) ) )

will return 1 (True) if ID #2 is in the list, 0 (False) otherwise. All this can be done without performing a find and without leaving the current track's record.

I did not understand the need for counting; there should be no duplicates in these lists. 

 

 

[human]

I forgot about the List command. This works great, thanks!