(If this isn't in the right forum, pleae feel free to move it or tell me to repost).

I'm in the midst of converting/re-writing a point-of-sale system (university computer store) for FM7. My boss threw me a challenge and I'm stumped on if it's possible and if so, how to implement it.

Currently, the system looks something like this:

INVOICES (invoice number) -----> LINE ITEMS (item ID) ------> INVENTORY (inventory ID).

The inventory file isn't really named properly as it doesn't track in-stock inventory; rather, it tracks what items were assigned to which purchase order. A invoice can have many line items and those line items can have many "inventory" records (usually one, but it could be more; for example, I could have an invoice with qty 1 of part number 123456, assigned to PO 1234. Tomorrow, I could change the quantity to 2. A new record would be created in the Inventory file for that second item. Each of those inventory records has a unique ID (V000001 and V000002 for example).

As I mentioned, the inventory file doesn't actually track in-stock status. What my boss asked me to think about is having every single item on the invoice assigned a unique identifier (so, if I had qty 4 of part number 123456, I would get 4 records in my inventory table (V000001, V000002, V000003, V000004). Items ordered for stock would be the same way. What I think he wants is to be able to track a discreet part as it travels through our system (for example, stock order is placed, order arrives and is put on the shelf, item is put on invoice 1234, etc).

I could probably achieve this with some scripting but I'm wondering if there's something simple I could do relationship-wise. I'm open to anything at this point as I'm still thinking about what tables I need in our rewrite.

Thanks!

Before you do anything, I think you need to find out exactly what your boss wants to do with this system. There's no point setting something up and then finding out that it isn't exactly what was required and then having to start all over again. I've gone through this before, and it's always been a situation where the boss didn't really know what he wanted, and so he made up a bunch of ill-conceived requirements.

Now, having said that, if you want to track individual items, then you need to physically identify them and use the same identifier in your inventory file. There's no point having a big box full of identical widgets with no way to distinguish them, and then try to track abstract ID numbers in the inventory file. This means that you have to develop at least a basic inventory tracking file that assigns a serial number to each individual item when it's received, prints out a label for it that you can attach to the item and then continues to track it until it leaves the store. I'm not sure why your boss would want to track items individually rather than simply keep a record for each product type and a quantity on hand. However, I can understand there could be legitimate reasons for it. But, it's best to find out the real reasons behind why he wants this.