LookUp based on RegexMatch?

1

I have a table of names, and another table of notes where those names are mentioned inside a text field.

In the table of names, I’d like to add a column of links to all the places where that name is mentioned, and vice versa.

Like this:

NAMES . . . . . . NOTES
John Doe . . . . Note1, Note2
Mary Smith… Note2

NOTES . . . . . .NOTE TEXT . . . . . . . . . … . . . . … . . … . . . … . . . . . … . NAMES
Note1 . . . . . .“Everybody hates John Doe.” . . . . . . . … … . . . . . … . .John Doe
Note2 . . . . . . “Yes, John Doe sucks but Mary Smith is okay.” . . . . . .John Doe, Mary Smith

I’ve been trying to use Filter and LookUp and RegexMatch in every combination I can think of, and can’t figure it out.

Anyone have any ideas?

Thanks!
– Maria

2

Assuming you call those tables Table1 and Table2, you make the NOTES column from Table1 to take this formula:

[Table2].Filter(CurrentValue.[NOTE TEXT].RegexMatch(NAMES))

3

Thanks! I was missing the “CurrentValue” part.

Didn’t know there was such a thing. Now I do!

Much appreciated!
– Maria

4

Yeah it’s a useful handle in all iterative functions… To be honest just

[Table2].Filter([NOTE TEXT].RegexMatch(NAMES))

should also work, as Coda is pretty good in identifying the scope.

image

3 Likes
5

That worked too. So it wasn’t the CurrentValue part I was missing… I could have sworn I tried this particular combo of Filter and RegexMatch. Ah well, I’ve got it now!

Thanks!
– Maria

6

… and now I can’t go the other way.

In TABLE2, I want to create a column, NAMES, which lists all the people mentioned in that note.

I’m trying to do:
=TABLE1.Filter([NOTES from TABLE2]=ThisRow)

I’ve tried using Contains, RegexMatch, CurrentValue, in various permutations and combinations, and I’m either getting no results at all, or all the names in TABLE1.

Maybe the problem is that the field NOTE from TABLE2 is a single string, and NOTES from TABLE1 is a list of strings? That’s why I tried using Contains.

I’m probably missing a simple piece of grammar here.

Thanks!
– Maria

7

This should work: [Table1].Filter([NOTES].Contains(thisRow))

8

Thanks, that worked!