Tracking changes to BOM.

[MaxMiller]

I'm doing a database that tracks customer projects and orders.

The Header table contains all the Customer and Project details like customer name, contact details, project leader, etc.

A second table BOM contains the customer orders for a particular project.

I need a way to track changes or revisions to the BOM. It happens when a customer makes changes on their orders like quantities, etc.

Any ideas? Thanks in advance.

 

[CJ_London]

There are a number of ways this can be done. Which way depends on what you require.

If you normally require the latest view, then you have a duplicate of the (current) table as a history and for each update event insert the new or revised record into it. The difference between the 'current' table and the 'history table is that the autonumber field in the history table is a number type (long, duplicates OK) rather than an autonumber type (effectively a family key to link bact to the autonumber field in your current table) and has an additional autonumber primary key. Recommend you also have a timestamp field in the history table so you know when the record was updated (with defaut=now()) and a userid field (if there isn't one in the current table) so you know who did the update.

Another thing that can be useful is to include an 'action' field so you know what the user was doing. This is only really required if you cleardown the history table - otherwise the assumption is an insert is the earliest record

It does mean you have a duplicate current record but overall performance is not reduced

tbl1Current
PK
Name
Address

tbl1History
PK
TimeStamp
UserID
Action - insert, update, delete, view
FK (contains PK from tblCurrent)
Name
Address

One of my clients also updates the history table everytime someone views the record so they can be sure they know what the user was looking at and it also acts as an activity report for the record.

Other methods are to just use the history table in place of the current table - with the latest record being the current view but this can be slower if there are high volumes of records

Disadvantage of both of the above is that you have to determine what has changed from what to what by inspection (which could be a query)

So the final option is to have a history log which is updated (perhaps multiple times) on each insert

typically the history log will have the following structure

TblHistoryLog
PK
TimeStamp
UserID
TableName
FieldName
OldValue
NewValue

 

[MaxMiller]

Thanks CJ!
You answered one of my other table requirement which is an audit table.

Here's a quick overview of what I need:

Header Table
ProjectID
CustomerName
ContactDetails
ProjectDetails

BOM Table
ProjectID (Linked to Header table)
Line (An autonumber field to give me a reference for each Item the customer orders)
Item
Quantity

Sample:
Header
Proj001
International Shoe Exchange
+1-800-1-800-8000
Internal sales order

BOM
Proj001
1
Buckle
20pcs

Proj001
2
Buttons
10pcs

Proj001
3
Laces
5pcs

Now, let's say the customer changes their mind and makes changes to their BOM.

New BOM
Proj001
1
Buckle
20pcs

Proj001
2
Buttons
20pcs

Proj001
3
Flaps
2pcs

I need a way to compare the changes made to the initial BOM like in a side by side table or something of that nature.

I hope I'm not confusing anyone.

 

[CJ_London]

Side by side can be done but has potential issues which makes it more complicated - for example what if the customer changes the order more than once, do you want 3 side by side views?

Also would you show

Line    original item    orginal quantity    Orig Input Date    new item    new quantity New InputDate
2        Buttons         10pcs                 1/1/2013             Buttons      20pcs           1/2/2013
3        Laces            5pcs                  1/1/2013             Flaps          2pcs             1/2/2013

or

Line InputDate Item         Quantity
1     1/1/2013  Buttons    10pcs
1     1/2/2013  Buttons      20pcs
2     1/1/2013  Laces       5pcs
2     1/2/2103  Flaps        2pcs

Because in reality, when the order is changed from laces to flaps, they have cancelled the first item and ordered a new one

 

[MaxMiller]

Thanks again CJ!

The side by side views just makes it easier to spot the changes in the customer's order. What I'm thinking with the side by side view is that the left side is the initial order details.

On the right would be a drop down list of revisions. Let's say the customer revised the order 5 times. The left view displays the initial order. On the right is an to choose which revision you would like to compare the initial order to. So let's say I choose 5th on the drop down option, the right side view would then show the fifth and last revision of the order.

Does this make any sense or is there a simpler way of doing this.

 

[MaxMiller]

CJ, if I go with this implementation:

Line InputDate Item         Quantity
1     1/1/2013  Buttons    10pcs
1     1/2/2013  Buttons      20pcs
2     1/1/2013  Laces       5pcs
2     1/2/2103  Flaps        2pcs

Is there a way where I can show the changes made in a side by side view?

 

[CJ_London]

All things are doable, but what you are asking for is quite complex so to provide detailed help you will need to be very clear on field names etc - you will need a table structure as per my original suggestion for the BOM Table (i.e. need a separate BOM History table.)

You would show the bom table to the left and the bom history table to the right, each in their own subform. One problem will be in syncing them if there are more rows than can appear in the subform.

In what context do you need to see the revisions? - it might be easier just to see the current view and have a combo box which lists the history and defaults to the original view - could also be highlighted in some way so if it is different from the current view the user knows there is a history.

Would strongly recommend that you only allow changes to quantity. If the customer wants to change from buttons to laces, the user should change button quantity to 0 and add a new line for laces. If you don't and there are lots of changes, tracking it in a 'digestible' way will become very difficult.

e.g. Line 1 starts off with 20 buttons, changes to 10 laces, then changes again to 5 buttonholes.

 

[MaxMiller]

I need to digest this over the weekend. Let me see what I can come up with. Thanks for the help CJ!