Imagine a shopping cart. I add a product from another table, set the quantity, and then select a “model” from a drop down menu (choice). The models I can select of course depend on the product I selected for the shopping cart item, eg
product “t-shirt”: models are “small”, “medium”, “large”
product “pen”: models are “red”, “green”, “blue”
All products can define models.
But how to do that in the most easy way? I thought I could let the “product manager” enter the models as a comma separated list in the product, split the list up into an array an base a choice on that. But that does not seem to work; the array column of the table is not offered as a base for the choice.
Am I doing sth wrong?
Or do I really need to go through a separate table where the models are registered for the products, e.g. with columns (product ID, model):
What I forgot to mention but made the solution quite straightforward: I’m using a custom form for adding/editing a cart item.
Based on that the solution is looking like this:
There is a PRODUCTS table to select the product for a cart item from. In the PRODUCTS table the models are entered as a comma separated list into a column “models”.
There is a ITEMS table for the cart items. In that table there is a “model” column. Not that relevant for the solution, though.
There is a ITEMS_FORM table with just 1 row to be bound to the custom form to add/enter a cart item. This table there is a relation to the PRODUCTS table. And a lookup which pulls the value from the “models” column of the selected product. This is, where the fun starts.
In the ITEMS_FORM table there is also a split text column “models_split” which separates the model labels into an array.
The solution starts with yet another table I call MODELS. It has as many rows (!) as I think there can be models of a product, eg 10.
Each row has an “index” starting from 0 (up to eg 9).
Also in the MODELS table there is a single value column “models_split_single” to select the first (and only) record from the ITEMS_FORMS table. From that - which contains the current item visible in the custom form - the “models_split” value is grabbed.
And finally there is a second column in MODELS - “model name” - which is another single value type column, but this one picks a single value from the start (!) of the “models_split_single” column. As the index the “index” column’s value is used.
That way, each row in MODELS contains a different value from the “models” column of the product selected for the current item.
The MODELS table can easily be bound to a choice component on the custom form which then stores the selected value in its “model” column which mirrors the “model” column in ITEMS (to which the ITEMS_FORM values are written using a custom action).
