Relationship structure question #1

[VaTechTigger]

Hello. Fist post. Just learning how to work with filemaker by taking some tutorials on VTC this past month. Very neat stuff and Im excited to learn it. I am a Biochemist so this is new for me...

That being said I am making a database to track inventory in my lab. As reagent comes it needs to be tracked, as well as when it is used in testing. Reagent can be uniquely identified by its barcode. There can however be many different lot numbers of each type of reagent.

For state regulations I need to have a record of when reagents arrive, their lot and expiration dates. I also need to be document when reagents are used and by whom. So for example 100 kits of Reagent A come in. Lot # 124 on 7/1/10. On 7/2/10 User XX uses one kit of Reagent A, lot #124. 99 kits of Reagent A, lot 124 remain.

I made an INVENTORY_LINES table, that is fed from an INVENTORY_ENTRY table. (There is also an INVENTORY_DEPLETION table for when stuff is used, havent gotten as far as setting this up yet) I did this so that the ENTRY table can create the records in the LINES table and the entries couldn't be removed or altered, and they could be reported on so the inspector could see dates of arrivals, usage and to keep running totals of the reagent.

So my basic question is, does this make sense? Or am I using 5 tables for something that one could accomplish?

*Extra Info*

(The REAGENT table is there to provide the corresponding Reagent Name and Manufacturer for when a user would scan the barcode. The DATE LOOKUP table occurrence is there to auto enter an expiration date when a user types in a Lot# for a reagent that has been previously entered into the database.)

[VaTechTigger]

Ok, so my thinking has changed a bit. Seems maybe one table would be fine for the data entry. I would add another field that had a variable for whether it is adding inventory or removing it. It would be set based on what layout the user does it from.

So field status would be a calculation..

Case (

Get ( LayoutName ) = "INVENTORY_Form"; "Added";

Get ( LayoutName ) = "INVENTORY_Use"; "Removed"

)

Then I can base the summary calculations on this field, whether it adds or subtracts, etc.

Edited July 2, 2010 by Guest