Using arrayboth in a find statement

Panorama X
1

Hi

I have some calendar events that I’m importing into Panorama X using a procedure.

The events have an ID, but sometimes they may have more than one. So the id field is really a textarray that might look like any one of these examples:

aaa;bbb
ccc
ddd;eee;fff

When a new event comes along, I’d like to check if it’s already in the database - e.g if any one (or more) of its ids appears.

I wanted to do this using arrayboth:

local newItemToAdd
newItemToAdd="ccc;xxx;yyy"
find arraysize(arrayboth(A,newItemToAdd,";"),";") > 0

I was expecting that this would find the “ccc” row, above. I’ve tested the formula outside of find, and it does work (displays a 1 for the matching row). But it doesn’t work in a find statement. The error I get is:

Find Formula Error “cache( unable to access formulalocal variables”

Is there a way to use arrayboth in a find statement? Or is there a good workaround I can use?

I’m considering using looparray, but it feels a bit messy to do this, and it would be nice if there’s a better way.

CleanShot 2022-09-20 at 15.58.51@2x
CleanShot 2022-09-20 at 15.58.51@2x1426×594 86 KB

2

This worked with your sample data.

local newItemToAdd
newItemToAdd="ccc;xxx;yyy"
find arraystrip(arrayfilter(newItemToAdd,";",{?(arraycontains(A,import(),";"),import(),"")}),";")≠""
3

Excellent - that makes sense; thank you very much!

Out of interest, does anybody know why arrayboth() won’t work in “find”? It feels like that would be a simpler way if possible.

4

This appears to be a bug, I have logged it.

1 Like
5

Ah great; thanks! I don’t know how useful this is, but there is also this post which looks related:

6

Thank you, yes, I think that is the same problem.

1 Like