Do you have fields that identify the size and color of each item? Is the Product ID for each combination of size and color unique?
In any case, you shouldn't need additional tables here. It's just a matter of constructing a summary report that groups your entries by size and color to give you the proper "break down".
I have one repeating field for color and one for size...this is because up until now, there has been no interest in tracking inventory, so we just needed a place to type in specifications for the order pickers. The product ID is not unique, and my boss is unwilling to make a unique ID for each color/size combination.
How are you recording "inventory changes for each product"? Do those changes record the color/size?
It's possible to set up a "log" that has a separate entry for each change in inventory. If you set this up to also record the color and size, you can get the report you want here. Depending on your business model, you may already have a table used as the line items table for invoicing that can be adapated to this purpose.
In my Product Detail layout there is a portal to the Inventory Changes table. This where I enter quantity and the type of change, but size and color is not recorded. I would be happy to record the inventory breakdown here, or somewhere else if necessary. I was initially envisioning entering the breakdown into a table of sorts.
However we do this, I need the sales made to be reflected in my inventory, of course...including the breakdown of things sold. I need it to function so that when I sell four of product ID 51802 I can have that on one line on the invoice, and can also indicate that two of them are size small and two of them are size large...if that makes sense.
Yes that makes sense, but you will need a way to record the size and color of items for each inventory change. Then you can create a summary report that groups your entries first by Item, then by color and size to display the needed sub totals.
I don't see any way to do this, unless you include fields for size and color in your inventory change table and record each addition and removal as a separate entry for each size and color combination.
It'd look something like this:
Date Item Size Color Qtyin QtyOut Balance
12/13/10 1234 Large Red 4 34
12/13/10 1234 Med Blue 2 36
(and sub totals for each color/size combination are possible also.)
So is what youre saying that I just need to add a color and size field to Inventory Changes, and then use those fields to record the breakdown of product? and that should translate over to my invoices fine when I'm entering a sale?
I'm identifying a basic way to distinguish between adding 5 large red items to your inventory from adding 5 medium blue items. That has to be recorded in some fashion before you can get started setting this up.
I don't know what effect this will have on your invoices as I don't know what design you've selected for the rest of your database.
A typical Invoicing system in FileMaker centers on these three tables and relationships:
Invoices ----< LineItems >----- Products
Where each item purchased on an invoice is listed in a portal to LineItems on the invoice layout while pricing and other info is looked up from Products each time the user enters a new item into the portal.
If you have that structure, I would think you'd need some method to specify size and color in the LineItems table so you that you can document this at the time an item is sold.
The LineItems table can then be further enhanced to serve as a table that tracks all inventory changes. Inventory changes due to sales are already recorded here, so you'd just need to add the needed fields and an additional layout for recording other inventory changes such as recieving shipments, return to vendor, shrinkage, etc.
My database is set up the way you described exactly, except that it also has the inventory changes table. This table has a relationship with the products table.
That can be made to work, but you will need to update it with sales driven inventory changes either sale by sale or by totaling up all the sales since last update (say every day at Close of Business), and making a batch update of your changes table.
I'm suggesting a simpler structure where your inventory changes are all recorded in LineItems. This assumes that you want to remove each sold item from inventory at the time of sale. Non sale inventory changes can be recorded on a different layout that is also based on LineItems. Since these changes won't have a value in the key field that links a line item record to an invoice record, the non-sale changes won't appear on any invoice.
A list view style ledger can be set up to log each such inventory change and running total summary fields can be set up to tell you the exact amount on hand that you have of each item. Separate item numbers for each color and size would make this easier, but I think you can work around that as long as you record size and color with each inventory change in fields defined for that purpose.
You the man, PhilModJunk. I'm going to give this a go. I'm sure I'll be back with insanely dumb questions...