Suggested record from other table based on keywords

Hi,

I’m trying to find a way to suggest casefiles that can be linked to attachments based on the attachment name.

Each attachment has some keywords extracted
Each casefile also has some keywords extracted

I’ve created a column “Matching casefiles #2” that iterates through all the records from the casefiles… and tries to find matches between the keywords from the DB Casefile and the keywords from the current attachment.

It gives me for each db casefile that exists a list of true’s and false’s (match keywords).

[DB Casefile].ForEach(Keywords.ForEach(Contains(CurrentValue,thisRow.Keywords)))

But now i want to be able to have 2 options:

  • either retain only the casefile with the maximum number of matching keywords
  • or, in case of multiple matching casefiles (same max hits): the list of those casefiles.

I’ve been looking around to find a solution, but i’m kind a on a dead end here.
I would appreciate any help!

I shared my doc:

Hi @Reinout_Decock ,

I just created a new column named ‘Matching casefiles #3’.

I’m not sure if there are more efficient formulas that can achieve your requirements, but this is a very expensive one. If the number of items increases even only moderately, the document will probably become very slow.

If that’s the case you should consider moving the logic to a button or automation and run it only when needed. For example, when a ‘Casefile’ is modified.

[DB Casefile].ForEach(
  List(
    CurrentValue,
    CurrentValue.Keywords.Filter(
      thisRow.Keywords.Contains(CurrentValue)
    ).Count()
  )
).WithName(
  lst, 
  /*List of lists with all the Casefiles and the number of matches with the current attachment*/
  lst.ForEach(CurrentValue.Last()).Max().WithName(
    max,/*Max number of matches per Casefile*/
    lst.Filter(CurrentValue.Last()=max).ForEach(CurrentValue.First())
    /*List with number of matches equal to max*/
  )
)

Hope this helps,

Pablo

2 Likes

Thank you Pablo!

Normally, i would expect up to 125 max concurrent case files (active ones).
Concerning attachments… i would expect up to 50 max.

Hard to tell if it will be an issue or not, depends on many factors. Just try it out and keep in mind the option of moving the logic to a button if it gets too slow!