human Posted August 21, 2024 Posted August 21, 2024 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 Posted August 21, 2024 Posted August 21, 2024 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 Posted August 21, 2024 Author Posted August 21, 2024 I forgot about the List command. This works great, thanks!
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now