Work order forms

[Jebuz]

Hi all, I have a question that's been bugging me for some time now.

Basically I have a form for work orders. I have to be able to create new
orders and have some drop down lists that the user can use to choose the
information to be used for the new work order. This info is pulled from some
tables I have already storing a basic "inventory" in which the fields data
may change.

The hard part I don't know how to do is how do I show the previous work
orders and show the data that was in the tables at the time the work order
was created. I know I need to have some "history logging" tables but how do
I get the same work order form to function 2 ways? in present and past data?
Or do I use 2 seperate forms, one for showing previous data and other for
creating new order from current data??

 

[Klatuu]

Not really all that difficult.
Instead of using the dropdowns (combo boxes) as bound controls, make them
unbound and only use them for searching for values. Create regular text
boxes to display the data in your Work Order table.
You can get the value you look up in the combo boxes into the text boxes and
thus into your table by using the After Update event of the combos.

If Not IsNull(Me.cboSomeField) Then
Me.txtSomeField = me.cboSomeField
End If

 

[Jebuz]

That part makes sense but I guess my question is more of table design than
form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
I go to create a new work order and the user selects person A, it fills in
the company they work for automatically. I click OK , submit or whatever
after I fill out the details of the work order. Sometime after person A goes
to work for a different company, and now when I look up the work order it
shows the new company for person A, not the old one. However I still need to
be able to then create a new one and have person A fill in the company with
the new one he went to work for. If that description makes any sense.

So I guess it's how do I store previous relations between 'Employees' and
'Company' and show work orders created with those relations as well as when
the user selects "new" work order it lists the current relations?

it's like the work order form has to be bound to 2 different sets of tables
depending on if the user is on a new record or not. *shrugs*

 

[Klatuu]

A work order record should contain the relationship to the employee table and
to the company table to keep historical accuracy. If Person A works for
company 1 and you create a work order, the work order record should contain
the primary key of the employee as a foreign key and the primary key of the
company as a foreign key. That work order record now reflects person A and
company 1. Person A goes to work for company 3. The new work order would
relate to person 1 (same person) and company 3 (different company). The
first work order would still show person A and company 1. It is a matter of
proper relationships.

How you are describing it makes me think you have the work order related to
a person and the person related to the company. That is why you are having
the issue.

The basic table lay out should be

Work Order
WO_ID - Autonumber Primary key
EMP_ID - Long - Foreign key to employee table
COMP_ID Long - Foreign key to company table

Employee
EMP_ID - Autonumber Primary key
COMP_ID - Long foreign key to company table

Company
COMP_ID Autonumber Primary key

 

[Jebuz]

That is actually how I have it set up now. I figured that must be the way to
store the information in the tables. Here is the catch though. Say on the
work order I want to display some of the 'employee' and 'company' fields,
like person's name, phone # and company name and phone #. This all works
great till I make a change to one or both of the tables. Say changing the
company's name or employees phone #. Now I want the work order to display
the "current" fields at the time of creation not the updated fields. I know
this means I need some type of tables to track histories of 'employees' and
'companies' right? So how do I link that into the work order as well? So the
work order table and forms work with previous data in history tables and
current data in other tables when I want new work orders made?

Do I make a history table for each 'employeehistory' and 'companyhistory'
and in those have also the current records, but when the data changes mark
the correspnding records to have a 'true' retired field?

 

[Klatuu]

Well, it seems that some of the examples given don't make complete sense.
For example, if an employee's phone number changes, what good is having an
out of date phone number?

I think I would consider doing the changes in the employee and company
tables. First, I would add a Boolean field to each table that would be used
to designate the current record. Then when I had to make a change, rather
than update the table, I would create a new "current" record and mark the
other as history. If you are using Autonumbers for your relationships, this
will work well, because no matter how many changes are made to an employee or
a company record, the work order would still point the the record the way it
was when the work order was created.

That would mean that in the forms you use for maintaining the employee and
company tables, you would need to use a query filtering on the "current"
field so you would get only the current record. It would also mean that the
forms would become more complicated because you would have to know a change
has been made, append the table with the old record data and populate a new
record for the current record.

 

[Jebuz]

Yea the 'employee' and 'company' tables were examples. I really am working
with 'sites' that have 'monitors' installed at them. Also each 'monitor' has
many 'sensors' connected to it. So I also have a table called
'monitor_configs' that shows what monitors have what sensors connected to
them.

The work order should display the site with the corresponding monitor and
it's configuration at the time of the work order, as to maintain a type of
history of configurations and work done to those configurations. The
'employee' and 'company' example is just easier to explain hehe.

But I think now I am pointed in the right way. I think whatever the
solution ends up being it will be complex no matter what ! I am going to
stick with the 'work order' table examples with foreign keys you suggested.
So it will probably involve creating some "history" tables and some complex
queries on some work order form! Thanks again for all your help, very much
appreciated!!!

 

[Klatuu]

Okay, the real world make a whole lot of sense.
As to making additional tables, that would only make it more complex. If
you use the approach I suggested, you don't need additional tables, you only
need to modify your data entry forms for the site and monitor tables.

Don't confuse how you store data with how you view it. Using the Current
Record flag I suggested and filtering on it depending on the circumstances
will take care of the rest of it.

For example, don't apply any filter at all for the work order form. That
way, the existing work orders will show the data the way it was when it was
entered.

For creating new work orders, you only need to filter any combos, etc that
are based on data in the site or monitor tables so you are getting the
current data.

Having one table with the flag in it is really no different, logically, than
a current table and a history table. The two tables are harder to manage.
Would it not be easier to set a filter than to change recordsets?

 

[Jebuz]

Yea I guess that is true, it would add to the complexity. I think I will do
as you suggested, which means I have to use an auto-number or multi-field key
and not the serial #'s of monitors for keys, since I will have multiple
entries for each monitor/site. But that sounds much easier of a fix then
adding more unneccessary tables. Thanks again!! Now just to implement that
*sigh* haha

 

[Klatuu]

You are on the right track, now.
The Autonumber is the way to go.
The only thing you will need to work out is aligning your existing data, but
that is no big deal.
Onward and Upward!