Inventory tracking for multiple Locations - Poll!

[Cassetti]

We are a parts company, we handle used high end storage equipment.

My boss built the database in FMP7 2 years ago with minimal experience in FMP.

We have currently 2 locations for inventory: Warehouse, and Main Building.

There are two tables for inventory adjustments (moving from one to the other, and for orders)

We have expanded into the UK with a new office that has a parts depot located in Manchester. We will have other warehouses as we continue to expand.

I am thinking that the inventory adjustments should all be tracked in one table.

(If we take 2 units from the warehouse and bring them here, instead of going to the warehouse tab, creating a record for -2 from there, and then +2 in the main building tab, we would do both on the same table: -2 to warehouse, +2 to building inventory, reference being moved from warehouse.)

What do you think, he says we should have separate tables.

Thanks in advance!

[Stuart Taylor]

This seems a little over simplistic to me... you may just have to re-evaluate your database schema.

You should maybe have a few databases to diplay the data in a number of different ways.

Part_Description

Part_Item

Location

Location_Log

The Part Items that you would have would take their description from the Part_Description Table then you can see all of those parts and there current location.

The Part Items may contain the current location of the part you are tracking and as it moves the last location could be added to the location log.

The locations database would contain all possible locations ... This would allow you to see all parts currently in that location and all parts that have ever been in that location.

best

Stuart

ps you could also have a Sites database linked to the Locations database. etc...

Edited November 7, 2006 by Guest

[Genx]

Not exactly the point of the seperation model section... but either way, I'm with Stuart, you shouldn't really just be using numbers to count units... You could do everything with the fields he mentions above, two simple relationships and a couple of sum or count equations...

I'd put this whole topic somewhere else, but to be honest, at this point i'm not sure where to move it..

[Søren Dyhr]

Not exactly the point of the seperation model section.

You're right that the discourse deals with data and interface splitting, but that's a horizontal cut ...but each could have an interface table that via a one2one relation, deals with a common datafile without ever being able to intermingle data entered from the respective interfaces enforced by differently designed id pre/post fixing - but seen from a statistical view is all data placed in one file ...I have stumbled over this in another thread.

So it's actually a derivate of the separation model where the interface table has records as well, although it's only the recordID though. Some freshing issues exsists though with the aggregate functions that needs the layout changes as event to trig.

I have to admit that I'm as puzzeled as you, when I was confronted with it the other forum where it came to my knowledge, but what it does is that you can do it without embracing conditional privileges on record level fully ...making the concept look like a silly "...me too" exercise made by Filemaker Inc.

I would guess the approach dates back to the different record locking mechanisms found up until the release of fm7 where a user possessed a record fully no matter if he/she left his desktop for a coffee break, or worked the data in the record.

--sd