I think you should focus your search on the keywords "Conditional Value List".
That could help you out.
I would imagine that you have one product then with multiple size and colour options in a related table.
Or do you simply make multiple product records for the same product with the same productId but different size and colour specs?
I have a few video's on Conditional Value Lists on my youtube channel:
Thanks, Guy and David, I hadn't thought of using a conditional value list there, although I am familiar with them and have made use of them in this same solution. I guess what I'm having a hard time wrapping my head around is how they would actually be used in practice (adding a line item, picking a t-shirt SKU, and then adding sizing and color subcomponents of that line). For example, I may have a line item that looks like this:
Product Description: Qty : Price per : Price Ext
(Line 1) Mens Crew T-Shirt : 150 : 2.50 : $375.00
S - 50
M - 50
L - 50
(Line 2) Mens Crew T-Shirt : 50 : 3.50 : $175
XL - 50
Because we order so many different items from different suppliers it is not (at this point) a goal of ours to have every item in our database ahead of time so we can select them, we just need the ability to create the size (and sometimes color) breakout of the main line item as detailed below. Although this example has the sizing broken out vertically, other industry software has that done horizontally like so:
S : M : L
50 : 50 : 50
Seeing that makes me think that a repeating field may be an option? I had originally planned on adding these line items via a portal on the Sales Order record -- would adding sizing and such with conditional value lists eliminate that as an option?
I'm sorry for not replying sooner. But I was crazy busy :)
So what you want to do is create line items and then create the size and color on the fly.
I would create conditional value lists so you can see the colors and sizes that are already available for that product. And then have a button that runs a script to create a new size for that product or a new color.
So I think you need a few tables:
To store all products with a productId.
With a productIdFk and then the size info.
Just the same as the Sizes table with a productIdFk field and the size info.
Both of those last tables must also have their own ID field.
Then in your line items table you select a product. Then you get the sizes and colors for that product from a conditional value list.
If the color you want is not in there you create a new one.
The script needs to start from a button in that portal row.
It takes the ProductId as a variable.
Goes to the sizes table and cretes a new record and enters the productId.
Goes back to the original layout, and back to that line item (you can use a loop and the size Id to find the correct portal row. )
And then you can enter the size right there into the field.
Aother option is that you enter the size in a temp field. Then you look it up to see if that product already has that size for that product. And if not, creates a new one.
Anyway it requires a little bit of scripting. But nothing to hard.
Let me know if you are still having problems.
Thanks, Guy -- I really appreciate it. I think I get the gist of what you're talking about and will start working on this.