5 Replies Latest reply on Mar 13, 2014 6:31 AM by mperley

    Inventory w/ Varying Colors and Sizes



      Inventory w/ Varying Colors and Sizes


           I'm developing a sales order write-up solution for our company and struggling with the right approach to adding line items. We process sales for a variety of inventory items, from simple items (just a SKU and qty) to complex ones (one SKU, but different colors and sizes). For these complex items, I'm trying to avoid having to add a line for each unique combination of size and color, and would prefer to simply select the SKU and then be able to add different size and qty combinations. The different size and qty combinations would sum to the total qty on the main line item.

           Is this type of thing possible with FileMaker? Because the items we order don't always have different size and color combinations I would prefer to not have to add this type of information for every line, but instead be able to add this as "extra" information as needed. Just curious if anyone else has done this type of thing or if I need to completely alter how I'm approaching the problem.

           Thanks in advance for the help.

        • 1. Re: Inventory w/ Varying Colors and Sizes

               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:





          • 3. Re: Inventory w/ Varying Colors and Sizes

                 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?

                 Thanks again!

            • 4. Re: Inventory w/ Varying Colors and Sizes

                   Hi Michael. 

                   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:

                   - Products
                   To store all products with a productId.

                   - Sizes
                   With a productIdFk and then the size info.

                   - Colors
                   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.



              • 5. Re: Inventory w/ Varying Colors and Sizes

                     Thanks, Guy -- I really appreciate it. I think I get the gist of what you're talking about and will start working on this.