Array Column as options in a choice component?

Spent a couple of days trying to find an elegant solution to this. This post touches on a similar challenge but I couldn’t transfer the workaround to my setup.

I have a Product sheet (simplified below) where vendors have multiple products/sizes/price points. In a previous version, a form button component was linked to an “Order Form” sheet, separate from Products. Then, Orders were sent to an Orders Received sheet. Current version needs to use just one sheet for Products and another for orders received, nothing more. This reduces complexity for Admins uploading/editing products.

Looking to create a shopping cart experience with an “add to cart” Form Button on each product page on a vendor’s profile page. Brain-twister is how to pull multiple (three) Size/Price columns into one choice component. Advanced formulas are not my forte but I’ve played around with Transpose and Query (new to).

Step 1: Query columns from Product sheet (Product, Vendor, Size&Price_A Size&Price_B, Size&Price_C)

Step 2: Transpose. Would render the sheet endless.

Right now, the choice component is pointing to Size&Price_A. It pulls the entire column on any product — I have yet to crack filtering by Vendor.

Finally, not sure if this is relevant, but since I have Users (buyers), Admins (Sellers), and an extensive product catalog. To pre-empt any hiccups/mess, I assigned a Row ID to Vendor, Product, and User sheets.

What I desperately want to avoid is multiple rows for each product to account for different size/price combinations.

Tips/tricks/magic?

I abandoned my solution (which is the only one I can think of here) since it required a separate sheet, and formulas that essentially duplicated a row of info from another sheet four times. Very inefficient.

Bummer, I was hoping you’d say you’ve fine-tuned it and it works brilliantly!

The only thing I can think of that might be a bit more efficient than what I did would be to create a blank sheet and create an arrayformula in the first column that gives you exactly 3 times the number of IDs as there are in the sheet with your Size/Price columns—pretty simply formula using counta and row().

Then in glide use math columns and if > then columns to essentially stack the three Size/Price columns from that other sheet into one column in that new sheet. That new sheet will always have the appropriate number of rows to handle that data, since your ID formula is giving you that in Sheets.

It’s not an elegant solution. It still means that you have to chip into your Glide Row Quota just to create a Choice Display. But it does seem that the 25k row limit isn’t actually a limit but rather a point when Glide starts hitting bottlenecks. And in some cases people have been able to get far more than 25k rows because the rows themselves didn’t have much data. A sheet with just one column of ID numbers doesn’t seem particularly hefty.

I’m not sure this would work but how about creating an array column with columns named “Size&Price 1”, “Size&Price 2”,…

Then use that one as the choice values for the product?

Update: It’s not working here :cry:

1 Like

I was just about to say… that was the first thing I tried :slight_smile:

1 Like

Thanks John. I have to chew on this for a bit. Because of the nature of the app, it won’t take long to reach 50k as it is and I’m expecting having to regularly transfer data to clear rows.

Oh don’t get me wrong it is a pretty terrible option, but in lieu of a feature that bundles two rows for display choice, it’s probably the lightest thing avail rn.

Array columns for Choice Display would be the simplest solution to this and something I feel like Glide could handle. Add it to the Feature Request app, and you might be pleasantly surprised that it comes sooner than you think.

2 Likes

I have just added one but if I’m not wrong it is subjected to review from Glide team before available for voting.

3 Likes

They’ll add it. It’s a pretty obvious one. They usually take a few days to add new request for voting.

2 Likes

Thank you for adding that. I thought the feature request app wasn’t live anymore, Is this the right link?

Yeah it’s there.

Here’s what Mark said a while ago:

I’ve wanted this too, thanks for submitting the request!

1 Like

looks like it’s whitelisted emails only.

I’ll give it a shot. Thanks so much!

Hi @Mark …this really needs to be a thing. Array columns as choice components. 3 of my current apps hinge on this feature. Any chance it’s in the pipeline?

2 Likes

We have solved it here.

2 Likes

:clinking_glasses: @ThinhDinh & @Jeff_Hager. Nice work and beautiful timing. Thanks for the ping!

2 Likes

Looked at this again today. For my particular use case which doesn’t utilize a standardized and finite range of sizes, it’s close but not quite. It’s users who enter their own product/size/price info. Need a solution that doesn’t rely on a secondary size array sheet.

1 Like