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.