Searching within arrays for values contained in another array

Sorry for the ambiguous title. Not sure how to succinctly summarize this.

I have a database of 6000+ orders. The orders contain various items. I want to make a selection of orders that only contain onions.

So let’s say my onion item numbers are
Onions = "7490,7500,7517"

The items on each order are stored as a and tab delimited array in a field called OriginalOrder. Contents of four sample orders in the OriginalOrder field are

   1)	7625–B	1			9.00	$9.00	2	Apple
   2)	7628–B	1			9.00	$9.00	2	Cantaloupe

   1)	7490–B	1			15.00	$15.00	2	Red Onion
   2)	7625–A	1			9.00	$9.00	2	Apple

   1)	7490–B	1			15.00	$15.00	2	Red Onion
   2)	7500–B	1			15.00	$15.00	2	White Onion

   1)	7517–A	1			15.00	$15.00	2	Yellow Onion
   2)	7517–C	1			22.00	$22.00	5	Yellow Onion

I want my selection to contain orders 3 and 4, and not orders 1 and 2. Is there a slick way to do this that doesn’t involve looping through 6000 records? Thanks.

I asked a similar question a few years ago (Seeking elegant way to make a selection based on contents of an array) and got helpful responses. I can’t see how to use arraycontains in this case though. To be clear, for each record in the database, the OriginalOrder field contains a multi-line array, where each line contains multiple elements separated by tabs, spaces, or hyphens.

So for one record in the database, which corresponds to a single order, the OriginalOrder field might contain this multi-line array:

   1)	7490–B	1			15.00	$15.00	2	Red Onion
   2)	7625–A	1			9.00	$9.00	2	Apple

Using your sample data I came up with a quick method that may or may not work for all cases.

local theItem
theItem=“Onion”
select arraystrip(arrayfilter(OriginalOrder,¶,{?(import() endswith theItem,"",import())}),¶)=""

I set the local variable to the type of item I want to select in records that only have this type of item among their array of items. The arrayfilter( removes all elements that end with theItem so only non-matching elements will remain. The arraystrip( removes all empty elements so the array will equal “” if it only contained elements that ended in theItem. If it contained other elements it will not be selected.

Works for the limited data you supplied.

Note that I just edited the code to remove the let statement that created the local variable and replaced it with the Panorama 6 method of first creating the variable and then assigning it a value. My mind is deeply entrenched in Panorama X and hard to step back into Panorama 6 at this point.

Oh yeah, sorry, my sample data here is unfortunately simplified in a way that means this solution won’t work in production. Instead of “Red Onion,” “Yellow Onion,” etc. we have variety names that don’t include the word “onion” in them.

Ok, let’s try again with something more robust.

local theItem
theItem=“7490,7500,7517”
arrayfilter theItem,theItem,",",?(seq()=1,“import() contains “+{”}+import()+{”}," or import() contains “+{”}+import()+{"})
theItem=replace(theItem,",","")
select arraystrip(arrayfilter(replace(OriginalOrder,lf(),cr()),cr(),{?(}+theItem+{,"",import())}),cr())=""

This takes your list of item numbers and expands them to the text to be used in the arrayfilter( function of the select formula. In your example the “7490,7500,7517” string is converted to:

import() contains “7490”, or import() contains “7500”, or import() contains “7517”

Then the commas are eliminated in the following line and when theItem is inserted into the select formula it will be executed as:

select arraystrip(arrayfilter(replace(OriginalOrder,lf(),cr()),cr(),{?(import() contains “7490” or import() contains “7500” or import() contains “7517”,"",import())}),cr())=""

I also made sure any line feeds were converted to carriage returns which I had to do when copying the original data which had the the cr() converted to lf() on the clipboard. You may not need this in your case however.

I’m now at home, without Panorama on my computer so I can’t try this out, but it looks like this code will select all orders that contain ANY onions? What I’m hoping for is a search that will select all orders that contain ONLY onions.

I can imagine comparing the size of the OriginalOrder array to a count of how many times an onion item number appears in the array… but I don’t know how to accomplish that in a Select statement.

No, this will only select records that contain ONLY onions. If a record contains anything else besides one of the numbers indicating an onion it will not equate to “” and therefore not be selected.

This is very clever Gary, and I think you are correct that this will work as intended. Here is revised code for lines 3 and 4 of Gary’s code which makes the logic simpler by getting rid of the seq() logic and handling every line the same, then putting the " or " glue in the replace statement.

arrayfilter theItem,theItem,",","import() contains "+quoted(import())
theItem=replace(theItem,","," or ")

Note: I haven’t tested this particular code, but I have used this technique many times.

Thank you both! I’ll give this a try when I’m back in the office next week.

Your earlier idea of using an arraycontains( test should be workable. If your tab, space and hyper formatting is consistent enough to allow parsing out an identifying element, the desired values of which you list in the array of arraycontains(. Then swap that test into Gary’s arrayfilter(. It adjusts lines that pass the test into “”. Then his arraystrip( adjusts orders whose every line passes into “”. Selecting orders that adjust to “” gives you the only onions orders or whatever other subset (apples, potatoes, etc) your desired values specified. Looking for the desired value only in the correct position also avoids false positives if that value might appear elsewhere. Theoretically Gary’s test would pass an order of 7500 turnips thinking that quantity meant White Onions.

Gary’s import() corresponds to a line of the order. If your format is no more complicated than your examples suggest your identifying element would be between the first and second tabs and before the hyphen therein:

arrayfirst(tabarray(import(),2),"-")

import()[tab(),-1][2,"-"][1,-2]

would both parse out that element. If the identifying part is always numeric and the part after the hyphen alphabetic then

striptonum(tabarray(import(),2))

would work. There are doubtless other ways to parse it including regular expressions.

Since text funnels were the shortest of these, and I’d loved them in my classic days, I’ll illustrate with them:

local theItems
theItems=“7490,7500,7517” ; Onion varieties
select arraystrip(arrayfilter(OriginalOrder,¶,{?(arraycontains(theItems, import()[tab(),-1][2,"-"][1,-2], “,”),"",import())}),¶)=""

To select only all turnip orders adjust theItems accordingly. If your formatting is fancier than shown adjust the parsing second operand of aryraycontains accordingly.

This is true and can be easily accommodated for by including the dash following the item numbers as in 7500- instead of just 7500. Using Jim’s improved code simply change the arrayfilter( to add a dash after each number:

arrayfilter theItem,theItem,",","import() contains "+quoted(import()+"-")
theItem=replace(theItem,","," or ")

This will generate this result to be used in the select formula:

import() contains "7654-" or import() contains "7500-" or import() contains "7892-"

Now there is little likelihood of confusion with another number in the line. As always, there are many ways to skin the proverbial cat in Panorama. And speaking of cats, last week I ordered Schrödinger’s cat on eBay and it arrived yesterday - but now I’m afraid to open the box! :crying_cat_face:

Since arraycontains had been mentioned but not solved as a potential method I wanted a solution using it In the forum record for future query cats.

And Gary, don’t be surprised to see S. cat outside it’s box without your opening it. Virtual cats can tunnel through walls.

I almost forget to mention the potential catch when using arraycontains: it is case sensitive so might not match everything expected. To use it to make selections in all cases use Arraycontains(Upper(Array), Upper(Value), Separator)

A belated thank you–the suggestions in this thread worked well for me.