Dec 14, 202203:46 AM - edited Dec 14, 202208:50 AM
I'm trying to detect overlaps between my records and display data from the respective other records they're overlapping with, but am struggling yet again with creating the right regular expression for the REGEX_EXTRACT.
Creating an identifier field that helps recognize overlap (where identifier is equal = overlap).
Creating an data field that includes the identifier and additional data that I want to display.
Roll-up the data fields related to the linked record. Look up the roll-up on each record.
Apply a REGEX_EXTRACT to the roll-up to extract the data I want to display.
The innermost REGEX_REPLACE errors on the Overlap, which is a lookup of the rolled up / ARRAYJOIN(values) of the Data fields, I edited my post for clarify, I probably misplained something. After modifying the formula to Overlap&"" (for string) it gets going.
REGEX_REPLACE(Overlap&"","("& Data &")","")
Strangely, the first REGEX_REPLACE still doesn't remove Data from the string and outputs the original/unchanged Overlap.
The second REGEX_REPLACE removes the ID correctly.
I tried and it didn't work, this is where I got stuck. 😕
The "Overlap" (Data rollup) actually includes all and not just overlapping records. This is why I initially used the REGEX_EXTRACT together with Identifier to select relevant ones.
The final formula would be a combination of both, removing its own string from the roll-up, extracting all occurrences with matching IDs and then continuing to remove Identifier strings to get the desired output.