Inventory with history

[Yogesh Nath]

I have an inventory with Customer,Vendor,Invoice,LineItem - it all works great. But I have something additional. Please read below.

Here is the scenario. We purchase spares parts. We are a small department that looks after equipments in lecture theaters of an institution.

We keep these parts until we get request from the institution to install/replace these parts in a lecture theater.

So we want track in

- in which lecture theater a part has been installed (location field) – a drop down menu

- Who the part was issued to (name of the (employee field) who took the part from the department to install) – a drop down menu

- In which equipment in the lecture theaters the part was installed or replace (equipment field)

- What was the brand of the equipment(brand field)

- What was the model of the equipment(model field)

- What was the Asset that the part was installed/replaced (asset field)

Basically we want to track where and on what equipment the part was used.

I am thinking of creating another table called history with these fields ->history_ID (PK), location, equipment, brand, model, asset) and in my Invoice table – have an additional field called (history_ID)

Invoice -> history_ID <-History

So on the Invoice layout, I have the portal from LineItem – all as usual but also have field where the user can enter details for location, equipment, brand,model, asset- and these details go in the history table.

Does this make sense? Do you see any problems?

[normanicus]

Yes, there may be problems. With a history file you are into recurring data emanating from a single record. To handle this you need a 'History Items' table hanging off the 'History Event' (or whatever you call it) table,

Norman

[Yogesh Nath]

Thank you for your help.

But I do not know what you exaxtle mean...is it possible if you could please explain further in terms of how many more tables I need to create and how they will be linked/related and to which table...

Thank you for your help...hope you could help little bit further..

[Tim W]

I would approach the structure as below:

Work order = invoice

Tables_Relationships.pdf

[Yogesh Nath]

Thank you Tim for your help.

I hope you could answer few more of my questions on the 3 more tables you have included.

Equipment Table > why is there "Serial, Equipment_ID and Asset_ID fields"? Cant I just go with 1 field say Serial Number -set to auto-increment and use this field to link it with WO_dtl_lines table?

- also Mfg-Serial_No - is this the serial number of the equipment?

WO_Dtl_Lines > Is this sort of a lineItem table?.

I am inlcuding my complete version of the r/ship. I could also send you my filemaker file.

I sincerely hope you could help me with some bits and pieces.

Yogesh

relationship.pdf