Relationship, Data Validation, Serial Numbers

[Himself]

I am making an inventory database / work queue system. The work orders are entered by a user, schedualed by the help Desk and addressed by a Tech who resolves the Work order. These three people with three different privilege sets come together to create one complete work order record. Which is related back to the inventory item creating a growing history of service. The Idea of using one table for the work order only lets me use auto enter features and validations for the first person entering data, the rest of the fields cant use any auto enter (date based or user based) and I can check off the cant be empty box. I know I can script those into the other two users fields but I would rather not.

My other thought was to use separate tables for each user and link them on work order number (auto enter serial number). This doesnt seem to be working. When the first user creates a new work order it doesnt create, using the same serial number, a related record in the tables of the other two users.

I realize that I have no Idea how to pass field contents to another table. Any method that I have tried has been unsuccessful.

So the two questions are:

How do you pass field contects from one table to another?

How can you create related records in other tables by creating a record in separate table with a link on an auto enter serial number field?

[Himself]

Ok so I figured out just how stupid I am. For the part were I wanted the different tables linked and pass the same work order number so that I could use the field validations at each users interface. I just needed to enter something into a related field which triggered the new record and copied the same serial number to the new table. It makes perfect sense in hinesight. I was just expecting a new record in each table as soon as I created one in the first table.

[bcooney]

I'm not sure, but it does sound like your understanding of how to structure your database tables and relationships is way off. Why not tell us the tables you have and how you have them related, and I'd be happy to see if you have the basic data model correct.

[Himself]

Well I will try to explain it as best I can.

I have an inventory table, for now we will say it is flat file. The unique Id is InventoryID.

I have a need to track the work history of the items in the inventory. So enters the Work Queue.

At this point what I have working is the goto person for a particular building submits work orders for items that are broken. This starts the creation of a Work order. Right now the Goto person hense forth called "Sysop" has a table SysopWO: which has

WO# work order number

InvID: Inventory ID linked back to Inventory table

location:

problem:

etc:

once the Sysop submits the workorder it becomes visible to the helpDesk operator who scheduals the work to be done at a particular time by a particular Technician.

HelpDeskWO: Table name

WO# work order number linked to SysopWO:

Date schedualed:

Technitian:

etc:

once the Help Desk Operator submits the schedual the technician or technicians can now see the work order. To complete the work order the techs go through thier table.

TechWO: Table Name

WO# Linked to SysopWO:

Resolution:

Completion Date time:

Etc:

That is the basic table structure that I am using. There are countless Validation tables and a many to many relationship back to Inventory but that is in a nut shell the problem I was having. At first I thought It wasnt working because when the sysop opened a work order there was no associated record in the other two tables. this was only the case because I did try to enter any data into the other two tables. Once I tried to enter "Schedual Date" the WO# copied over from SysopWO:.

I know this method has two extra instances of WO# were using one table wouldnt but this allows me to cater field validations to the particular users so I dont have to go crazy with script validations.

If this isnt the easiest or most efficient way I would still love to hear other ways. I am still learning the FM stuff.