Sizes from one worksheet in choices component of other worksheet

I have a worksheet with available sizes from a product. The column is named sizes and it is in this format: S,M,L.

I have a form setup (different worksheet named form), where I want customers to make a selection from the choice component. So they would be able to choose either S, M or L and submit that choice in the form.

So far I have no clue how to get the available sizes data into the choice component of the form.
Tried with relations but somehow that does not work here.
Any guidance or help is appreciated!

Assuming you have a products sheet, a sizes sheet, and the form sheet, correct? It sounds like you have a multiple relation from the products sheet to the sizes sheet? You should only need a form button on the product details page. Open the form and add a choice component. For the data source of the choice component, set it to the relation you created to link the product sheet to the size sheet.

no, just a product sheet and a form sheet.
The sizes are in a column in the product sheet (coming from an xml feed).

How many different unique combinations of sizes would you have? (Ex. ‘S,M,L’; ‘S,L’; ‘M,L’; etc.)

we have about 100 sizes, so that would be an impossible venture to make all combinations from them.
Is there no way to display a field value from a differtent worksheet, or does this just only work through relations?

I’ll have to think about this. To be clear, the sizes are listed in a single column for each product with a comma delimiter between each size?

Yes, correct.

I was talking about this in a thread like 2 weeks ago with the same problem. It would be nice if we can split that to multiple columns in an array column format, then make that array column the choice value, but obviously that does not work as of now.

@ThinhDinh Yeah, that’s kind of what I want to double check on when I get to a computer. I’m wondering if some weird split/transpose/unique or query formula could give us a list in a new sheet that could be linked to with a relation to build the list of sizes for the choice component.

@applemooz Can you share a small sample of your data? Screenshot should be fine.

1 Like

If we have each combination of product ID and size choice in a row then it would work, but it will eat up a lot of rows.

If Karl still wants to go that way, then I think after splitting & trimming the choices into multiple columns in the original sheet, we can transform that to a new sheet with multiple lines by something like.

={FILTER({ProductID Column; Size 1 Column}, Size 1 Column <>"");FILTER({ProductID Column; Size 2 Column}, Size 2 Column <>"")}

It might work stacking it on each other like this, an Array literal problem can come up, but that’s my best bet now.

Is this enough for you?

Yep, that will be good. I’ll play around later today.

1 Like

@ThinhDinh Maybe you have an idea on this. I have a formula that works to take a product in one column and a comma delimited list in another column and build a list of sizes for each product in separate rows, but I’m running into issues when splitting cells that have numbers for each size. It seems like the split doesn’t work. I think it’s because the result of the split is a number, but I don’t understand why. It happens when it runs into the row that contains “31,32,33”. Thinking that maybe the comma with a number was causing an issue, I did a substitute to convert commas to pipes, but that didn’t seem to help.

image

I shared a sheet here.

Hi Jeff, it’s a bit strange I can’t get the “text” options to be splitted out (as in one size, L, M etc.), the original formula can only return the numbers.

Ultimately, I had to make it a 2-step solution, first getting everything including the rows with empty Col2, then a filter to filter out those rows. Query did not work.

Yeah, I don’t know why the formula is acting the way it is.

I just had another thought though. Figure out the max number of sizes a product could have. Let’s say 10. Create 10 columns in an array format (Size 1, Size 2, Size 3, etc.). In the first array column create an array formula with Split to split the sizes on comma into each array column for each product. Create a new sheet that lists each possible unique size on a row. Maybe a this could be done with a formula automatically or just manually entered.

Now on the products sheet in glide, create a relation that links the array size column to the size column in the new sizes sheet. This should give you only the matching sizes for the product that can be used in a choice component.

I haven’t tested this and I will have limited computer access over the next week, so I don’t know if we would run into similar issues from my first idea, but it should result in a lot less rows used to build the choices.

1 Like

Thanks for the idea, I will test it later.

1 Like

Feel free to use the same sheet.

1 Like

Thanks so much for looking into this guys!

So I managed to get the sizes in an array column on the product sheet.
I did this with help of parabola.io, I used it to split the comma separated values in different columns as you suggested (size1,size2,size3 etc.)

How would I go from here in displaying this sizes array on my product page ?
I can only see the different sizes to pick from (size1 or size2 or size3 etc.)

And after that I would need the choice option in my form, how would I make that relation?

This will not work because as of now you can’t use an array column for your choice value.

Here, we have solved it.

1 Like