Suggested record from other table based on keywords

1

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:

1 Like
2

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

3 Likes
3

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.

1 Like
4

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!

2 Likes
5

Hi Pablo, i will definately move that to a button… I’m not sure I always try the “instant” option :smiley:

1 Like
6

well, it’s nice not having to press the button all the time!

Anyway, glad to be of help.

Pablo

1 Like
7

I asked DeepSeek to take a look at your formula and see if it can optimise performance… i’m not to determine that it did, but I wanted to share it with you…
here’s what it came up with:

WithName(
[DB Casefile].FormulaMap(
CurrentValue.Keywords.Filter(
thisRow.Keywords.Contains(CurrentValue)
).Count()
).Max(),
maxCount,
[DB Casefile].Filter(
CurrentValue.Keywords.Filter(
thisRow.Keywords.Contains(CurrentValue)
).Count() = maxCount
)
)

This is what DeepSeek explains:

How It Works:

  1. Calculate maxCount
    FormulaMap gets match counts for all Casefiles → Max() finds highest count
  2. Filter by maxCount
    Returns only Casefiles where their match count equals maxCount

Example Execution:

Using the same sample data from earlier:

  1. Calculate match counts: [2, 1, 2, 1]
  2. Find maxCount: 2
  3. Filter Casefiles where count = 2 → [Case 1, Case 3]

Why This Works Better:

  • No nested ForEach loops
  • Calculates match counts only twice instead of repeatedly
  • Uses native FormulaMap for cleaner list operations
  • Properly scoped WithName variables

This version should resolve the “missing arguments” error while maintaining better performance than the original approach!

3 Likes
8

It does look good indeed. Now I wonder why I did it so complicated in my formula :smiley:

2 Likes