Creating a list of references from a text column

[Jack_Beaudoin]

Good morning,

I am wondering if anyone has a solution or suggestion to accomplish the following. In a table, I have a column of text in which I occasionally reference other rows such as @rowname. I would like to have a column that extracts all such references into a list. Ideally, I’d love to see Coda create a formula or column type that would do this automatically, but it is admittedly a niche use case…

So alternatively, I could imagine a formula that would take the text column and parse the text to create such a list. I love Coda’s formulas, but I’m not great with algorithms and code and I can’t quite figure out how to do this. Is there a way to identify such a reference as an object? Or would one have to treat each reference as a string of text?

The two conceptual difficulties for me are (1) defining the reference, because they can contain names of varying length and number of words; and (2) pulling multiple references from a text column (maybe use formulamap here?). I’d like to create an approach that could be applied to any available reference, whether it is a row, a table, a page, etc.

Finally — although I’d prefer not to — I would be willing to use a special character to “close” each reference, for example, using a “<” as in @rowname is quite long < .

Thanks for any input or suggestions you might have.

Jack

[Scott_Collier-Weir]

I think @Agile_Dynamics did this once, didn’t you?

[Pch]

I think there’s a way using the hidden/experimental RegexExtract()
… which seems to be the easiest way to do this.

References.Name.ForEach(
  RegexExtract(thisRow.Text,CurrentValue)
)

I used 2 tables in the sample below (one where the references are extracted and a second to store the references) so this might need some adjustments but all it does is it takes the text values in the whole field Names in my table References and ForEach() of these Names (stored as CurrentValue), I ask RegexExtract() to extract the value from the text in thisRow.Text

This is just an idea …
(And I really wish RegexExtract() would be made public at some point)

[Scott_Collier-Weir]

Oh wow. That’s brilliant. And really cool

[Pch]

Thank you @Scott_Collier-Weir !

[Agile_Dynamics]

RegexExtract() may be hidden, but it is reliable. I have hundreds of instances of it in a great many documents and it has not caused any problems.

BUT…

be aware that RegexExtract() as you use it here, will respond to ‘special’ characters in ways that you may not expect!
* ? [ ] { } ( ) ^ and . all have specific meanings and there is a syntax that governs their use.

SO…

as long as your reference texts just contain alpha-numerics and spaces - you will be fine

but if it contains any of the characters mentioned above - then it will most likely fail to do what you expect - which is to find and extract the text.

ALTERNATIVE

Use the Split() formula to split-up your text where there is an @ symbol and then use Slice(2) to just take the 2nd and subsequent items and loop through the rows of the References table for each of them looking for a StartsWith() match. but that is a lot more complex.

Max

[Pch]

I’m definitively not a RegEx expert @Agile_Dynamics ! (Still learning )

Thanks for your helpful input !

I’ve tried the Split() by @ method but I couldn’t make it work for some reasons …

Would you mind, if you have a little bit of time to spare to share an example ?
Maybe I just missed something (which is more than possible )

Another way, also involving a hidden/experimental formula (_Merge()) would be to expose the “raw data” in the text field using something like :

thisRow.Text._Merge() + ""

And then, with the help of ParseJSON(), retrieve the name of the reference within the text :

thisRow.[Text Raw data (JSON)].ParseJSON("$..name")

(I chose the name of the reference here but it could be something else)

I know it’s not a pretty way to do this though …

And I’m pretty sure @Paul_Danyliuk explained how this all work somewhere or @Christiaan_Huizer on his blog maybe ?

[Jack_Beaudoin]

Thanks so much. In your first suggestion, how did you create the “References (as ref)” column? I can recreate the “References (as text)” column…

Jack

[Pch]

I just added a Relation field by clicking on the + at the end of the table and in the menu which pops up when the field is added, somewhere near the bottom, you should find the brand new Relation type of field (as since yesterday, Lookup fields officially became Relation fields).

Then I simply selected the table References

And in Relation options:

I clicked on Add formula :

Which opened the formula editor where I’ve added the “all-in-one” formula just below :

References.Name.ForEach(
  RegexExtract(thisRow.Text,CurrentValue).WithName(Ref,
    References.Filter(Name.Contains(Ref) AND Name.IsNotBlank()).First()
  )
).Filter(CurrentValue.IsNotBlank())

It extracts each reference from the text (like in the field References (as text)) which is then stored within the value called Ref using WithName() and then, using Filter() it retrieves the appropriate row in the table References by comparing the CurrentValue.Name to the value Ref (and this is done for each reference in References.Name)

But If you already have the “References (as text)” you can re-use those values in the Relation field too (if it’s easier for you) :
In that case, the formula needed in the Relation field would look like this :

References.Filter(
  Name.Contains(thisRow.[References (as text)]) 
  AND Name.IsNotBlank()
  )

Hope this helps

[Jack_Beaudoin]

Helps a lot!

I simplified it by self-referencing the main table (in your example Table), and it is working like a charm. For anyone else who stumbles on to this thread, two things to note:

1. If you use a common word in the “Name” column, you will get unexpected references whenever you use the word in the text column of other rows.
2. As Max pointed out, “RegexExtract() as you use it here, will respond to ‘special’ characters in ways that you may not expect!” I had a couple of references ending in question marks which nullified the regex match.