Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

Recommended Posts

Posted

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.

Posted

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. 

 

 

Posted

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.