Searching arrays for whole words

Panorama X
1

Hi All,

I am a new user of Panorama X and I am very excited about this great product. Still in the learning process, I have the following issue: I am trying to search pieces of text (in a database “Documents New” and a field “Words”) for all occurrences of “product tags” that are in the database “Global Tag List” and the field “ATag” - but only whole words, not partial ones. I am trying to use the following formulas:

arraybuild(", ",
    "Global Tag List",
    {ATag},
    {Type="Product" and searchanycase(fieldvalue("Documents New",Words),ATag)>0})

This formula works well but includes also partial matches (e.g. the product tag “Rice” pops up although it is only a partial match of the word “Price”).

I then attempted to include a regular expression for getting whole words only, having found the idea when recording a search procedure like this:

«Words» regexmatch "\b"+fieldvalue("Global Tag List",ATag)+"\b"

This works fine for individual records/tags. I then tried to integrate this regex approach into my overall formula like this:

arraybuild(", ",
    "Global Tag List",
    {ATag},
    {Type=“Product” and searchanycase(fieldvalue("Documents New",Words),ATag) and fieldvalue("Documents New",Words) regexmatch "\b"+fieldvalue("Global Tag List",ATag)+"\b"}).

However, this formula, while being accepted in the Formula Workshop (no “red” mistakes) does not produce any result (nothing in the results box). Where is my mistake? Or, more generally, how can I limit this kind of search (searching in one text array for tags of another array) to whole words only?

Thanks for any suggestions and help.

2

Try replacing this with:

arraysearch(fieldvalue("Documents New",Words),ATag,1," ")>0

This will only match full words and will work as long as you are never searching for items that already contain a space.

3

I was having trouble understanding what your goal was, so I reformatted your formulas to make them a bit easier to read (I can do that since I have admin priviledges). However, I’m afraid I still don’t understand what you are trying to do. Maybe if you would post screenshots of what the Documents New and Global Tag List databases it would be more clear what you are going for.

A minor note, your first formula uses the searchanycase( funtion. The way you have used it:

searchanycase(text,phrase)>0

is exactly equivalent to:

text contains phrase

so this part of your formula could be:

fieldvalue("Documents New",Words) contains ATag

Now it sounds like what you really want is this:

fieldvalue("Documents New",Words) containsword ATag

Unfortunately, Panorama doesn’t have a containsword operator. You were on the right track with your idea of using regular expressionns, but the correct regular expression you need is:

fieldvalue("Documents New",Words) regexmatch "\b"+regexliteral(ATag")+"\b"

The regexliteral( function may not be needed, but if your ATag field ever contains special characters like . or * it will make sure they are handled properly by the regular expression. Here’s the help page for that function.

Here’s a tip – to refresh memory on how to use regular expressions I opened a new procedure, started the recorder, then recorded myself searching using the Contains Word option in the Find/Select dialog. Panorama wrote the correct procedure for me!

I hope all this is of some help!

While I was writing this Gary responded, his answer is also good, except for the limitation on items that themselves contain spaces.

4

Thanks for the quick response, Jim and Gary. Jim, as you correctly guessed, I was looking for a “containsword” operator. I will give both of your approaches a try.

If I have still trouble with the formulas, I will follow Jim’s suggestion and provide more detail, including some screenshots.

In any case, thanks for the quick help.

5

FYI, if a “containsword” operator was ever implemented, it would be using regexmatch and regexliteral( as I described above. In fact, that’s such an excellent idea i have added it to the list of proposals in the issue tracker, it would be pretty trivial to do I think.

6

Jim, your formula worked and finally solved my problem. This is the way I am using it now:

arraybuild(", “,“Global Tag List”,{ATag},{Type=“Product” and fieldvalue(“Documents New”,Words) contains ATag and fieldvalue(“Documents New”,Words) regexmatch “\b”+regexliteral(ATag)+”\b"})

Gary, as you said your formula works for terms that do not contain spaces, but I have a lot of those so I am using Jim’s formula.

Again, thanks to both for your help.