Creating a list of references from a text column

9

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

Add Relation
Add Relation2346×1052 243 KB

And in Relation options:

Relation options
Relation options2188×1296 245 KB

I clicked on Add formula :

Relation Add Formula
Relation Add Formula2188×1296 258 KB

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) :blush: :
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 :innocent:

2 Likes