Best way to handle Variants of Products (Size, Color, and others) / create Sub-SKUs
I design and produce products which I sell online. I use Airtable for project management. My Airtable base has tables for PROJECTS, TASKS, PRODUCTS and more. Each Task and Each Product is assigned to a Project. I have created an interface where my supplier can get all the data and infos for the upcoming products.
In my PRODUCTS table, I use a formula to create a SKU for each product. It is a combination of the project number, the product category and the product number. My Problem: Some Products have variants (e.g. T-Shirt sizes). 5 different sizes of the same T-Shirt should all have the same SKU, just with an addition "-SM", "-MD", "-LG" etc.
When I send the info to the print company, the interface should not only show a total amount of units ordered. If there are variants, it should also show the ordered unit for each variant. For example "Small (AR0010101-SM): 11 Units", "Medium (AR0010101-MD): 23 Units".
But there are many other potential variants for products than just sizes. So I think it would be the wrong approach to add columns to the Product table for "Order Units Small", "Order Units Medium", "Order Units Large" etc. – because what if the next product has three different colors in seven different sizes? In that case, we would need columns for "Order Units Small Red", "Order Units Small Blue", "order Units Medium Red" etc. And another product might have completely different measurments than "small" or "medium" (50x70cm or 70x100cm for Posters, for example).
Another approach would be to add a new product in Airtable for each sub-variant / sub-SKU. But I think the overview will get really messy with too many products with variants. I'd rather have all the main variants in one table, and sub-variants within the records.
I tried solving it by creating a "Variant Templates" table with variant handles for sizes, colors, etc. But the problem is, when I create the Sub-SKUs with a formula from a lookup field with multiple records, it's just a list of all Sub-SKUs. And it's not clear to me how I could connect them to columns for "Order Units Variant 1", "Order Units Variant 2".
Another potential solution would be to not have the "Variant SKU Template" table, but just a "Variant SKU" table, which contains all the products that are only Variants / Sub-SKUs. But that would mean, I would have to manually create a new record for each size, color, etc. It would be much more practical, If a product just had a multiple select field that allows me to select the variants for each product. Maybe I could do it with an automation: every time I select "create variants", a script creates the new variant products in the variants table based on the sizes and/or colors I've selected in the product table. But I'm trying to avoid automations as much as possible.
Has anyone done a similar thing before?
My approach with the Variant Templates from another table. But that's not the best idea...
