Inventory Relationships

[Peter Bloeme]

I have developed a basic solution for "customers," with related "invoicing," "products" and "line items." They all are connected through Customer ID, Invoice ID and/or Product ID and work great.

The invoices are typical in that they provide for a part number (which looks up information from the product table), along with product name, quantity, price, discount, line total and multiple product entry (through a portal to the line items table). This allows me to create product and invoice sales reports through the line items table.

My question is, what is the recommended way to handle inventory where it will keep a "product on hand" field accurate and does that field belong in products? I would like the file to be able to automatically deduct or add inventory into this field depending on whether a customer orders or returns products. I would also like to manually be able to add or deduct from this field to maintain its accuracy (as inventory is received or counted). Does it have to do with a calculation, look up or related file?

TIA

Edited December 1, 2005 by Guest

[Ender]

Hi Peter,

This is a good question that hasn't been visited in a while.

Presumably, you have a process to order, build, or assemble new items that are added to the available inventory. You might use a couple additional tables to hold these "Orders" and their line items. Then you could have your Product file compute what's available:

Qty on Hand (calculation, number result) = Sum(Order_Line::Qty) - Sum(Line_Item::Qty)

This works for the basic case, but over time, physical inventory may not match this difference between what was ordered and what was sold (things get lost, stolen, or broken.) So there should be some way to either account for the discrepancies, or manually enter the physical inventory. Another issue that can be problematic: when the line items have hundreds of thousand of records, those Sum() functions are going to get pretty slow. And finally, keeping the records from two years ago in the active file may not be necessary, so the method chosen should allow for records to be purged, with the balance Qty on Hand used as the starting point.

So taking these issues into account, my suggestion is to use the structure in the attached ER diagram, but then have the Qty on Hand field be a regular number field that gets updated via scripts. Whenever an Invoice is processed or an Order adds items to inventory, a script would need to loop through all the line items for that Invoice/Order and update the Qty on Hand by the Qty used for that line:

Set Field [ Product::Qty on Hand ; Product::Qty on Hand - Line_Item::Qty ]

and

Set Field [ Product::Qty on Hand ; Product::Qty on Hand + Order_Line::Qty ]

The issue you have to be careful of with this method is record locking in multi-user environments. The Product record must be commited right away after updating. Also a test might check that the Product is not locked prior to attempting the Set field[], and some method for dealing with the locked record should be considered. Maybe you just don't update the Qty on Hand in locked records until the end of the day or week, when a maintenance script runs through all the products to refresh the Qty.

You also asked about dealing with returns, these could simply be the Invoice with additional negative Qty line items added, or you might use another TO of Line Items filtered to only show returns, where a calc sees that they are returns and negates the Qty that the Sum() function uses (in both cases, the original line items are kept intact and additional line items for the return are added.) Or you might use a separate Return table linked to Line Items (probably the same Line Item table as for Invoices.) An approriate Qty on Hand update script would be run for these as well.

[comment]

I agree. I am wondering though, if combining the best of the two methods wouldn't be possible:

Instead of running a script after every product transaction, I would run a script after every product count. The script would make all previous product transactions (LineItems and OrderLines) unrelated to Products. So QtyOnHand would be a calculation of

last product count + order lines since last product count - line items since last product count.

This is just an idea I've been toying with. Someone more experienced with large-scale files should comment on its feasibility.

[Siri]

I have an a sophisticated inventory system(in some ways but not filemaker) it only updates on hand quantities after a count. It is a complicated system as it is like a production system where all the products have recipes the ingredient counts are updated by small quantities like for shots of rum when a daquiri is sold.

I don't recommend waiting for a count to update the on hand unless you have a theoretical quantity available at all times.

This is a users PoV as it seems to reduce the usefulness of the system if there is not always an on hand number available for comparison.

Siri

[comment]

I don't recommend waiting for a count to update the on hand unless you have a theoretical quantity available at all times.

It seems to me that

last product count + order lines since last product count - line items since last product count

qualifies as a "theoretical quantity", don't you think? That sums up all that is known about the quantity - until the next count.

[LaRetta]

This appears to be a beautiful way to 'bump' the balance forward. I've been considering such ideas for a few months now but couldn't solidify the logic. I think you just did it for me, Michael. Unrelating is the key!!

This would restrict the stress and size but provide accurate totals. I'll see how it plays out in the Beast. :wink2:

[Ender]

Makes sense to me.

If I'm correctly inferring the relationships that this would need, you would have to have a stored date field in the Line Items and Order Lines tables to get the date range part of the relationship to work. But this bit of additional overhead seems like a reasonable compromise to avoid the additional scripting and record locking issues with my original suggestion. Then just use a daily or weekly script to loop through and update the stored count and a Last Update date of the Products.

[Siri]

I didn't originally see the key of your idea (unrelating only after counting). This seems like a novel idea. I'm not sure if unrelating to the product table is a good idea but you could create a separate fk in another table and change that foreign key to do it (thats just an instinctive opinion). There would be less consequences for the products that way and you'd still be able to display product price history etc.

I have to think much more about the design of this part of the database before I'd be able to offer an opinion re:filemaker. I only know how my current pc/restaurant system works.

Edited December 2, 2005 by Guest

[Siri]

I was posting before reading the replys of the others and I see more what you are talking about (unrelating). You could take the difference between the 2 figures to post to losses or whichever.

For me, I think there maybe some other factors but might be possible to work through.

Thanks for that flash of an idea.

Siri.

[comment]

Actually, what I had in mind is replacing a field in line items (thus "unrelating" them).

But now that I think of it, this can probably done without a script, and with equijoins only. Let's say our basic TOG looks like this:

Invoices - LineItems - Products - ProductCounts

ProductCounts is sorted by date, descending.

LineItems has a LastCountID field, that looks up from ProductCounts::ProductCountID.

Products has a calculated field cLastCountID = ProductCounts::ProductCountID.

Now we can add a TO of LineItems:

Products::cLastCountID = LineItems2::LastCountID

and calculate cOnHandQuantity (in Products) =

ProductCounts::Quantity - Sum ( LineItems2::Quantity )

When another count is entered in ProductCounts, the subsequent LineItems will automatically pick up the new ProductCountID, and so will Products. So the calculation always refers to the last count of the product, and only to line items created AFTER the last count.

[Søren Dyhr]

LineItems has a LastCountID field, that looks up from ProductCounts::ProductCountID.

Products has a calculated field cLastCountID = ProductCounts::ProductCountID.

This is pure Luca di Pacioli, a.k.a. http://www.filemakerpros.com/LULAST.zip

...but it gets rotten in a multiuser invironment when someone while typing changes their mind and uses an almost complimentary component instead ...after already been entering something.

The idea though is splendid, if you in a similar manner counter post ...or maybe could committing the record be made to behave in a desired manner???

--sd

[Ender]

Looks pretty good, comment. :waytogo:

[comment]

I am not sure what you mean - on both your points:

If someone enters bad data, then there is bad data in the system. I don't think I can do anything about that (other than some validation and a mechanism of "are you sure" before committing).

My point here is that the "core" data, i.e. the transactions themselves, are left untouched. Thus you always have a perfect trail, and if necessary, can go back and reconstruct inventory movements from the beginning of time. The entire mechanism of grouping transactions into "pages" is external to the core.

If you go with Ender's original notion of using a date comparison join, you could even delete count records, and it will self-adjust. In such case, no lookups are necessary* (and you could enter any date and get the corresponding inventory level).

What do you mean by "in a similar manner counter post"?

---

(*) except the date in line items. That could be a problem, if the date of an invoice or an order is changed AFTER entering the line items...

Edited December 2, 2005 by Guest

[Søren Dyhr]

If you go with Ender's original notion of using a date comparison join, you could even delete count records, and it will self-adjust. In such case, no lookups are necessary* (and you could enter any date and get the corresponding inventory level).

Yes I do now remember doing something like that, and yes I didn't get the finer points in what you were writing! I apologize!

--sd

[Peter Bloeme]

I'm glad I was able to get all you FileMaker geeks (and I mean that as a positive as I am a newbie geek) pondering...And, thanks for all your responses.

I had assumed there was a straight forward answer to my, how to handle inventory, question, but apparently not.

Anyway, it sounds like "Comment's" suggestion might be the most workable. Now, as far as this newbie goes, how would I actually impliment this solution? I know about the different tables: Invoices, LineItems, Products and ProductCounts. But, I'm only seeing one field in ProductCounts (a product id field). I'm assuming this is the relational field.

So, can someone simplify this a bit more for me? I've read "Comment's" comments many times and, while I'm visualizing it conceptually, I still don't have the grasp enought to work it out.

TIA

Edited December 2, 2005 by Guest
I referenced the wrong person.

[Ender]

I don't think Soren offered a solution. Perhaps you can clarify which implementation you were interested in.

[Søren Dyhr]

No I didn't! Comment (Michael) did!

--sd

[Peter Bloeme]

Did I say Newbie???-)

My apology, the one idea which seemed like a workable solution was by "Comment." Unless, you all think it won't work...

I was looking for a name to reference and must have scrolled too far.

TIA

________________

Actually, what I had in mind is replacing a field in line items (thus "unrelating" them).

But now that I think of it, this can probably done without a script, and with equijoins only. Let's say our basic TOG looks like this:

Invoices - LineItems - Products - ProductCounts

ProductCounts is sorted by date, descending.

LineItems has a LastCountID field, that looks up from ProductCounts::ProductCountID.

Products has a calculated field cLastCountID = ProductCounts::ProductCountID.

Now we can add a TO of LineItems:

Products::cLastCountID = LineItems2::LastCountID

and calculate cOnHandQuantity (in Products) =

ProductCounts::Quantity - Sum ( LineItems2::Quantity )

When another count is entered in ProductCounts, the subsequent LineItems will automatically pick up the new ProductCountID, and so will Products. So the calculation always refers to the last count of the product, and only to line items created AFTER the last count.

____________________________________________

[comment]

Well, for starters, ProductCounts would certainly have a Quantity field - that is where you would input the actual count of the product. A date field would also be useful, I think.

From this point on, the answer to pretty much everything is "it depends...". I haven't yet worked out all the details for myself, and there are plenty of options, again depending on the circumstances of the actual implementation.

Just as an example, you could choose to update ProductCounts even without an actual product count (when the amount of transactions gets too large to handle). That would have to be done by a script, writing the "theoretical quantity on hand" into ProductCounts.

Then there's the question of how to handle discrepancies. A relationship to the previous ProductCount would probably be required for this.

And so on... there are plenty of details here that can only be worked out in the context of an actual implementation.

[Ginxy33]

I am also needing to be able to do this. My biggest concern right now though is how to get my “products” to hook up with my “line Items”.

My product table has a unique number key (ProductID) . My problem comes with this. My Product SKU is made up of "Product::MilID" and "InventoryItemCode" (which is constructed of ColorCode+SizeCode+GarmentCode) and this is the unique identifier that I am using to order more of that product (InventoryDetails table)

Got that? OK, here's more. In my LineItems table a product is ordered just by the "InventoryItemCode" not the complete SKU. The LineItems do not care what actual MillID is chosen. There is a boolean flag that is set to yes or no that chooses the preferred MillID for the "InventoryItemCode" so we will know what our preferred brand of clothing is when it's time to order more. If the preferred is not available then we order from what ever else is available.

Since the LineItems table does not get the entire SKU, only the InventoryItemCode portion, how then can I relation to the Product/ProductDetail table? If I do LineItem::InventoryItemCode to ProductDetail::InventoryItemCode I am not getting all the information to get the Product::MillID.

Can someone help me?

[Ender]

I didn't see your query here until today (it's usually better to start a new topic for your own specific issues.)

I guess I don't understand why your Product::InventoryItemCode isn't the same as the LineItem::InventoryItemCode.

Are there more than one Product records with the same InventoryItemCode (but different MillIDs)?

[Ginxy33]

Yes, Exactly. We have several different Mills of the same type of product. Example:

We can get

Small White T-Shirts

in Mills

1.Hanes

2.Jerzee

3.Gildan

with the prefered Mill being the Gildan

The only thing they need to know when they enter the work order is that the order is for Small White T-Shirts

Maybe I don't need it to hook up to the Products table? I am just not aware of any other way to keep track of QtyOnHand but to do it through the Products table.

[Ender]

I'd suggest keeping the Product information separate from the Mill information by adding a Product-Mill table (or if the Mill is just an attribute of where the product comes from, add "Mill" as a field in whatever table you use to track the replenishing of inventory.) This way, each record in Product is unique to the InventoryItemCode.

When it comes time to pull or commit the Products from the Mills, use a script to set which mill the Line Items are comming from (it may be possible to have this as an auto-enter calc, but I'm not sure.)

[Ginxy33]

I had a feeling I was going to have to do it with a script. I have the mill in a seprate table, I may not have named them well but, the Mill table is "Product" and the "ProductDetails" is made up of the size color and style of that mill.

I am stumped on how to create a script to insert the prefered mill and were to insert it. I guess I would insert the mill in the "LinesItem" table in order to join to the "ProductDetails" table right? I tryed that once before and couldn't figure out how to do a script to make it choose a flagged(Prefered mill) item.

Could you give me an idea of how to pull this off?

Remember, there is a picture of my TOG in this post for reference. Thank you so much.

[Ender]

Well, this is what I had in mind. You could place a Mill_Product portal in a layout based on LineItem, and sort the portal by Preferred?, so that the Preferred Mill_Product records are first. Then have the script check each successive line in the portal to see if the there's a Mill_Product records with enough Product on hand to accomodate the Qty needed in the LineItem.

Now that I'm thinking about it though, I might I'd try adding another TO of Mill_Product that's filtered by Qty, so that only those with enough Qty on hand show. If there are none, you'd then need to decide how to deal with it. Either splitting the order for that line among multiple Mills, or putting a hold on that line.

Anyway, once you decide which Mill to ship from, that would be filled in on the LineItem record, in the MillShippedFrom field.

We haven't even gotten to the whole issue of keeping the Qty on Hand updated, the major point of the original discussion of this thread. I'm thinking this will be a little more complex in your case, since each Product_Mill will keep a separate Qty on Hand.

[Ginxy33]

WOW, YOUR AWESOME!! You have given me a lot to think about and a realy neat way to go about doing it. I will digest this information and play around with it for a while. I am sure it's exactly what I need. Thank you so much, I was soooo stuck!

Gina

[philfox]

I have altered the Business Tracker to handle my music instrument repair business and it works GREAT except that I can't get it to update my Products,Qty on Hand after I Invoice a part. I've tried the method that is reccomended herein to no avail. This one funtion is the only thing keeping my solution off line. Can anyone reccomend someone that would perform this last tweek? I've spent a year and a half of my "spare" time getting this far and I openly admit that the beast has won. I give up. My sanity is worth paying someone else to weild the final blow.

[Ender]

It's not clear which solution you've tried (this thread offers several.) If you want free advice, you'll need to provide more detail. If you're trying to hire someone, you should post the details in the Services Wanted forum.