Inventory and Purchase Orders ...again

[trmupstage]

Hi,

I've searched for two days to try and hunt down the answers to my questions, but I've struck out. I need to do something that I'm sure is simple, I just can't get my head around what FM7 is looking for. I'm sure that once I get past the initial understanding, I'll be able to put the rest together. Can someone point me in the right direction? I've made a number of databases over the years. Mostly simple ones. Here's the newest challenge:

I need to create a simple database. I'm using two tables: 1) Inventory 2) Purchase Orders

To make things clearer, I'm limiting the fields required to just the ones that are relevant to this.

Inventory: Item#, Initial_Qty, Qty, Cost, Description.

Purchase Orders: Item#, Qty, Cost, Description

There will be thousands of unique Item#. I had the purchase orders importing the cost and description from the inventory. It worked fine till I tried to get multiple items on a single PO. My other challenge is to have the Inventory Qty change when we order more, using the PO form. With a single instance of each thing, I did a simple calculation for the Inventory Qty: Initial_Qty + Purchase Order::Qty That worked fine ... till I needed to start adding more items to the inventory. I tried creating additional fields in the PO to handle each new purchase. Sometimes we order 50 different items on a single PO. At this point, even the look-ups don't work. I'm sure I could get that going again for a single item.

It seems like it should be very simple. I know I'm missing some fundamental things. Can anyone give me an example that I could learn from, or point me in the right direction? I really appreciate the help.

Edited October 31, 2007 by Guest

[Fitch]

As you suspected, you are missing a fundamental thing: a relational database structure. Each line of your PO should actually be a separate record in a related table. I suggest you browse through some of the templates that are included with FileMaker, and/or check out some of the downloads on FileMaker's website to start with.

[trmupstage]

Hi,

Thanks for the reply. I have looked at all the examples that came with FM7. I've read a book as well. None show an example of what I'm referring to, unless I'm blind, which may be the case.

[Søren Dyhr]

Alright here are two different approaches:

http://www.jonathanstark.com/downloads/Inventory.fp7.zip

....and:

http://www.geistinteractive.com/downloads/Transactions.zip

--sd

[Fitch]

I just revisited the templates that come with FileMaker 9, and none of the business templates I looked at include more than one table. Sorry I steered you that way.

FileMaker does have a couple of decent free downloads, one is the Business Productivity Kit, which at least has a very simplified relational structure; another is the FileMaker Business Tracker. These files are completely unlocked and open for you to study.

The basic idea is that when you have a parent record (PO) that can have many children (PO items); and you have on the other side your Inventory that can belong to many PO's; you have what is called a many-to-many relationship. And that requires a third table, usually referred to as the join table.

Your graph will at a minimum look like this:

PO -< Items >- Inventory

Each item on your PO will be a new record in the items table. The items table will lookup product and pricing info from the Inventory table. Typically you'd do your data entry from the PO and Inventory perspective, e.g. showing Items records in a portal. But for reporting and printing, it's often best to go straight to the Items table.

I would think your graph would have more in it:

Customers -< PO -< Items >- Inventory >- Vendors

Customers and Vendors might be two instances of the same table, e.g. Contacts.

I'll leave you for now with this advice: don't use an unstored calculation to track your inventory totals, it will eventually become too slow. Use a script or auto-enter calcs to keep your totals up to date.

[trmupstage]

Thanks for the links and the advice. I downloaded Business Tracker. Wow! that is very impressive. I could spend a long time learning from that. I'll keep researching this till I get it. my main job here is designing and writing software for embedded controllers. It's amazing how stupid I feel working in database design.

Thanks for the help. I'm sure I'll have a few more questions before it's all done.

One last thing: Your comment "I'll leave you for now with this advice: don't use an unstored calculation to track your inventory totals, it will eventually become too slow. Use a script or auto-enter calcs to keep your totals up to date."

Can you describe it a little so I understand what it is I should not do? I think I have the idea correct. I shouldn't set it up so that it has to recalculate the quantity on hand each time I look at the inventory. I see that would cause trouble and slow things down as the record count became high. Would you use a "recieved" button in the PO and only when that is pressed, it adjusts the inventory. Sorry for being dense on this. It's a whole new world of learning.

Edited November 1, 2007 by Guest

[Fenton]

Would you use a "recieved" button in the PO and only when that is pressed, it adjusts the inventory.

Exactly. When you "receive" a quantity of a particular item, it can be added to that item's "stock", which is a plain number field, and that particular transaction is finished. The difficulty is more with the fact that maybe you didn't receive all of the items ordered, or all of the quantity of a particular item(s). That's where it starts to get squirrely.

Sales orders are somewhat more difficult. But, as with received purchase orders, if there is a cut-off point in time, such as when "shipped," then a script can take the quantity of a particular item and subtract it from the stock.

Whether you further record those transactions in some way is another question (dilemma). The POs are fairly straightforward, the sales order items not so straightforward; especially if there are "components" or "bundles" involved (a "product" is actually composed of smaller parts, or other products). Or partial shipments.

Basically it's all simple arithmatic, adding or subtracting. But keeping track of everything that can happen makes it complex.

There is another FileMaker template, the Business Productivity Kit, which has some structure for this. It is somewhat basic however, which may be for the best. It is also separate files (which I think is due more to laziness on their part, when converting). It's bundled with a trial of FileMaker 9, but I think you can get just the BPK.

http://www.filemakertrial.com/bpk/?ovmkt=O5M3PJVLTCEPQ0GCEMJUJO4SS0

Edited November 1, 2007 by Guest
BPK

[trmupstage]

Thank you. I'll check that out. I really appreciate all the help.

[David Jondreau]

I just revisited the templates that come with FileMaker 9, and none of the business templates I looked at include more than one table.

I just want to use this post to point out again how embarassing the Templates are. I mean, really.

FileMaker does have a couple of decent free downloads, one is the Business Productivity Kit, which at least has a very simplified relational structure; another is the FileMaker Business Tracker. These files are completely unlocked and open for you to study.

Yes, but that's no excuse for the relational travesty that are the Templates.

[trmupstage]

Well, I'm still trying to figure it all out. I've downloaded every Filemaker example I can find. It's amazing that there are no simple, functional examples of multi-table databases used for inventory, invoicing and PO's. All the examples I found were either way too complicated to grasp or they simply don't function properly. After hours and hours of messing with this, I'm kind of burnt-out. I normally write code for embedded controllers. That has some logic to it's structure. I feel like an idiot working in Filemaker. Like most programming puzzles, I'll probably stumble on the answer while I'm sleeping. Or at least, that's what I'm hoping for. I don't seem to be getting anywhere while I'm awake.

[Søren Dyhr]

It's amazing that there are no simple, functional examples of multi-table databases used for inventory, invoicing and PO's.

You have to consider the vast amount of people, to whom this is thier bread and butter. There is not one single-size-fit's-all here, it's an area where things are deeply customized. Chances are taken with unstored field in some lines of business, where a transfere of such approach would be daft in other businesses due to the scales of some matters. A rental shop is very different in approach to a hardware store ... even what is considered worth tracking is different. Just count the times the suggestors in this thread uses the word "might" ....

--sd

[trmupstage]

Yes, you're right. I realize it is all customized. It would have helped me a lot if the examples that come with Filemaker were done a bit better. Even the new Inventory example it the business pack is based on relationships across different databases, instead of using multiple tables. It just got a bit frustrating putting the pieces together. I've also noticed that there are many, many ways to do the same thing. I've been studying as many examples as I can find. Everyone seems to do things a bit differently.

I was finally successful in getting the relationships working in my database. I used a portal to display line items in the Invoice layout. I was pretty happy about getting that going. Next, I need to work out a script to adjust the inventory levels. I know there are a couple ways to handle it. I'm going to go with a script to avoid doing calculations all the time. One calculation when the item has been pulled for an order is all I "think" I will need. The difficulty now is making the script work for individual line items, instead of globally to all line items.

Thanks for the help that everyone has given me.

[Fitch]

Starting with FileMaker 8 we now have variables which makes scripting much more flexible and robust. FileMaker 9 Advanced has added great improvements to the script debugging tools. Consider upgrading, you'll make at least part of your job a bit easier.

[jeh910]

It is difficult to get your head around but all items on POs and Invoices must be individual records and the PO and the Invoice are individual records. You reach this by having portals for the line items in the POs with the PO number being the key and the same idea with Invoice and the invoice number as key. What adds to the inventory is not the PO but the receiving report for the PO. You can then make the proper calculations using the related records. Receiving report line items add to the inventory and shipped invoice items subtract from the inventory. For each line item in a PO, I have 4 fields to receive the products in because vendors have been known to do partial shipments.

A company with existing inventory must have a field for beginning inventory and there must be field for adjustments to the inventory after physical inventory show differences with book inventory.

Make sure that you control access to physical inventory adjustment because people will use it like a cat box to cover up their mistakes.

I hope this is clearer than mud.

Johnny

[Søren Dyhr]

people will use it like a cat box to cover up their mistakes

So very true!

--sd

[trmupstage]

Thanks for all the helpful replies. Yesterday, I made some real advancements. I've got the relationships working well and I'm able to adjust our inventory correctly. I used scripts to adjust the inventory when either a part was used or when it was received. I put a few extra things in there to prevent people from doing stupid stuff too. The scripting thing will give us a lot more possibilities. I never jumped in to it before. I'm glad I did now.

I've ordered version 9. I got a dozen copies for the normal users and I bought advanced for myself. I'm not sure what the differences are, but I wanted to make sure I didn't run into problems by not having the right version to develop in.

[trmupstage]

It is difficult to get your head around but all items on POs and Invoices must be individual records and the PO and the Invoice are individual records. You reach this by having portals for the line items in the POs with the PO number being the key and the same idea with Invoice and the invoice number as key. What adds to the inventory is not the PO but the receiving report for the PO. You can then make the proper calculations using the related records. Receiving report line items add to the inventory and shipped invoice items subtract from the inventory. For each line item in a PO, I have 4 fields to receive the products in because vendors have been known to do partial shipments.

A company with existing inventory must have a field for beginning inventory and there must be field for adjustments to the inventory after physical inventory show differences with book inventory.

Make sure that you control access to physical inventory adjustment because people will use it like a cat box to cover up their mistakes.

I hope this is clearer than mud.

Johnny

Thanks for the tips. I ended up using a Qty Received and a Quantity Previously received field to handle backorders. When a part arrives, you type in the quantity that came in. The backorder field is calculated. Then the quantity received field is added to the previously received field and then the quantity receieved field is cleared. I adjust the inventory based on the quantity recieved field, and adjust the Backorder field by Qty - QtyPreviouslyReceived. I also added another field to show the status of each line item. When something is backordered, the status line says "partial" when we got all the parts, it says "complete". I also change the color of the Qty text, so we can tell at a glance if we are waiting for more, or if stock is low, etc. I'm really starting to like the possibilities. Today, I need to work on a script to lock the line item once all the quantity is received.