I have two tables, labels and items. each table has a field for keywords
Labels and their keywords are predefined and will not change. Items will have more added with many variations of keywords.
I want the suggested label field in the item table to show the label that shares the most keywords with the item.
The closest i’ve gotten is what is linked below. basically im filtering every list of label keywords with the keywords of each item then counting how many match and choosing which has the most matches. i’ve kind of got the counting working, but the selecting breaks as soon as an item has keywords matching those of more than one label.
One way that I went about this is by counting how many times each keyword appears in each item’s text and then adding the total number found for each label. Then matching with the label with the maximum keywords found.
You can see the screenshot for the Found Keywords column below. I used the withName() formula for readability, these are not necessary.
It first is searching Each Label (3 items: red, green, blue) then for each label will go through each key word for the current label and countif the current word in the text is equal to the keyword.
For item 1, the first list of numbers is [2,0,1,0] meaning it counted 2 "a"s, 1 “c”, and 0 “b” or "d"s in the Item 1 text. This adds to a sum of 3 matching keywords which is the maximum of any of the labels thus Red is chosen.
