Substring search on result of relatedarray( — what function to use?

1

Subject: Substring search on result of relatedarray( — what function to use?

I’m trying to create a calculated boolean field that checks whether any message records related to the current record contain a specific phrase. The relational lookup works — relatedarray( returns a concatenated text string of message content. The problem is finding the right function to test whether that string contains a substring.

Here’s the formula I’m trying to make work:

[some substring function](relatedarray(", ",{Content},"ProspectingMessages","key","MessageKey"),"<key string>") > 0

I’ve confirmed that relatedarray(", ",{Content},"ProspectingMessages","key","MessageKey") returns the correct text including the target phrase.

I tried patterncount( — unknown function error.

I tried contains as an operator — “Expression contains an operand when an operator was expected.”

What is the correct PanoramaX function or operator to test whether a text string contains a substring? And is there anything about applying it to the output of relatedarray( that requires special handling?

2

The formula for your boolean field as a text field could be:

?(relatedarray(", ",{Content},"ProspectingMessages","key","MessageKey") contains YourKeyString,"true","false")

or, if your boolean field is an integer field:

?(relatedarray(", ",{Content},"ProspectingMessages","key","MessageKey") contains YourKeyString,1,0)
3

Thank you. I’ll give that a try.