Order Status Display (1 Viewer)

[ChrisMore]

Hi all,

I'm seeking some advice on a good way of implementing an order status field for my Shipments table. This field will be displayed under the shipments section of the orders form to quickly show the user what stage of the process the order is currently in. I would like this to be a field in the Shipments table because my intention is to use the status field in queries.

I initially thought the statuses could be determined based on which fields have data entered in the Shipments table. This seems plausible because the fields in this table are populated in a constant sequence as the order progresses, e.g 'Planned' status if the 'Planned Production Date' field has a date, moving onto 'Transport Booked' status when the 'Date Transport Booked' field has a date. In other words, transport wouldn't be booked until there is a planned production date entered. The status field would be controlled by VBA to select the appropriate status title.

However, I feel this system could be easily broken if data is not entered or accidently entered out of sequence, so is there a better way of achieving this or does my idea have potential?

Thank you for your assistance.
Chris

 

[Gasman]

I would have a table that holds the statuses and the order they are implemented.
Then when a status is changed, I would check if the previous status has been set (by it's date being entered), if not give warning message and prevent the change.?

That way if you ever get another mid status requirement, it is just a new record in the table.

If that did not work, then I'd be back here asking for a better way.

 

[ChrisMore]

I would have a table that holds the statuses and the order they are implemented.
Then when a status is changed, I would check if the previous status has been set (by it's date being entered), if not give warning message and prevent the change.?

That way if you ever get another mid status requirement, it is just a new record in the table.

If that did not work, then I'd be back here asking for a better way.

Hi Gasman,

The warning messages sound like a good solution.

I'm not sure what you mean by "That way if you ever get another mid status requirement, it is just a new record in the table."

 

[Gasman]

I'm not sure what you mean by "That way if you ever get another mid status requirement, it is just a new record in the table."

Lets say for simplicity you have 3 statuses, Started Worked,Completed

So you code a function or just hard code to check that Started date is set before you can set the Worked date, and the same with Completed.
Then you have a new status Postponed, which should be inserted before Worked and after Started.
You would need to rewrite the function to accommodate that new status.

If you have a structure like I am proposing, then all it means is adding a new Status record, and changing the order in the table for checking.?

Bear in mind however, that I am not an expert, so they experts will likely have other/better ways, but that is how I would have approached it, had I had to do that when I was working.

 

[mike60smart]

Hi Gasman,

The warning messages sound like a good solution.

I'm not sure what you mean by "That way if you ever get another mid status requirement, it is just a new record in the table."

Hi Chris

I agree with Gasman

You need a related table to the Orders table

tblOrderStatus
-OrderStatusID - PK - Autonumber
-OrderID - FK (Linked to the related PK from tblOrders)
-StatusID - Number (Linked to PK from tblStatus)
-StatusDate - DateTime

Then if you need to add a different Status this can be achieved by using the NotInList Event to add a new Status to the tblStatus)

 

[jdraw]

Agree with info provided. If you need to know more info --who updated the status etc, then that could be included in your logic and tblOrderStatus design.

 

[ChrisMore]

@Gasman @mike60smart @jdraw thanks for this, I like the table structure in terms of normalization. My initial idea lacked structure which is why I thought I'd better speak to you guys about a better solution. As my idea was more simple (which is great for my limited Access experience) I could picture how I was going to implement it, however for this structured (and in my head more complicated) approach I am struggling to understand how the StatusID field in tblOrderStatus will get updated based on specific changes in the orders form. I assume this would be via code but I wouldn't know where to start...

 

[Gasman]

I would have thought it would just be a combo that retrieves values from the status table?
As you amend the combo entry, it updates the status field for the product in the ProductStatus table which holds a FK from Product and a FK from Status. You could either add all the relevant status records to that table when you start a new order and save it, or add as the status changes.?

In the AfterUpdate event of the combo you can set whatever else you want set.?
So if you update that combo and the value is now 4, then look for a value 1 less in the table and that it has a date for that record.
If not found, then prevent that amendment and warn the user.
You could even look for the last status with a date and then add the next. Up to you.?

I generally look at the overall picture, then break it down to smaller segments and work out a way to implement each of those.?

 

[jdraw]

Do you have a data model that shows your tables and relationships?
Do you have a list of statuses and the conditions/attributes for each? You'll have to know what makes something at status1 vs status2...
You seem more concerned with how rather than what at the moment.
See this stump the model link for ideas.
Is it 1 Order to 1 Shipment or can you have partial deliveries/shipments?
Would be helpful if you posted whatever you have for context.

Just saw gasman's post and agree with general approach. If the statuses are not necessarily sequential, you can adjust his proposed logic (check previous status...) accordingly.

 

[ChrisMore]

@Gasman I had it in mind that the status would automatically update so that's why I thought tblOrderStatus would be updated using code.

@jdraw I sometimes walk myself into projects at work where I think of an idea but then wonder how am I actually going to create it. This status idea has grown into a bigger project than I thought it was going to be. I like learning Access though and I learn a lot off everyone on this forum so I embrace the challenge but tend to over think everything before actually doing it.

I have attached a screenshot of my database relationships. I currently have the Order_Status table linked to my Customer_Orders table but I need to link it to my Shipments table instead as there can be multiple dispatches/split deliveries. Note, I haven't implemented the table structure that mike60smart suggested yet.

I have a list of statuses and the conditions for them are based on the data in the Shipments table for each shipment number. Statuses are:

Direct Delivery - when [Transport Mode] = "Direct Delivery" and all other fields are null
Awaiting Planning - when [Delivery Date] is not null and [Planned Production Date] is null
Planned - when [Delivery Date] and [Planned Production Date] is not null
Transport Booked - when [Delivery Date], [Planned Production Date] and [Date Transport Booked] is not null
Production Complete - when [Planned Production Date], [Date Transport Booked] and [Production Complete Date] is not null
Awaiting Collection - when [Production Complete Date] is not null, [Transport Mode] = "Collection" and [Despatch Date] = <Date()
Shipped - when [Production Complete Date] and [Date Shipped] is not null
Invoiced - when [Invoice Date] is not null

As I said before the conditions seem flimsy and easily broken but with the logic that gasman suggested I now think it could work.

 

[mike60smart]

@Gasman I had it in mind that the status would automatically update so that's why I thought tblOrderStatus would be updated using code.

@jdraw I sometimes walk myself into projects at work where I think of an idea but then wonder how am I actually going to create it. This status idea has grown into a bigger project than I thought it was going to be. I like learning Access though and I learn a lot off everyone on this forum so I embrace the challenge but tend to over think everything before actually doing it.

I have attached a screenshot of my database relationships. I currently have the Order_Status table linked to my Customer_Orders table but I need to link it to my Shipments table instead as there can be multiple dispatches/split deliveries. Note, I haven't implemented the table structure that mike60smart suggested yet.

I have a list of statuses and the conditions for them are based on the data in the Shipments table for each shipment number. Statuses are:

Direct Delivery - when [Transport Mode] = "Direct Delivery" and all other fields are null
Awaiting Planning - when [Delivery Date] is not null and [Planned Production Date] is null
Planned - when [Delivery Date] and [Planned Production Date] is not null
Transport Booked - when [Delivery Date], [Planned Production Date] and [Date Transport Booked] is not null
Production Complete - when [Planned Production Date], [Date Transport Booked] and [Production Complete Date] is not null
Awaiting Collection - when [Production Complete Date] is not null, [Transport Mode] = "Collection" and [Despatch Date] = <Date()
Shipped - when [Production Complete Date] and [Date Shipped] is not null
Invoiced - when [Invoice Date] is not null

As I said before the conditions seem flimsy and easily broken but with the logic that gasman suggested I now think it could work.

Hi Chris
Looking at your ER Diagram I am a little concerned with how you are linking related records.

Your table Products has a PK of Product_Code - Is this an Autonumber or a Text Data Type?
In the related table Suppliers Parts you have a FK of Product_Code - Is this a Number Datatype?
In your table Staff you have the PK as Staff_Initials - Is this just a Text Field?
In your Customers table you have the PK as Customers_Ref - Is this a Text DataType?

 

[ChrisMore]

Hi Chris
Looking at your ER Diagram I am a little concerned with how you are linking related records.

Your table Products has a PK of Product_Code - Is this an Autonumber or a Text Data Type?
In the related table Suppliers Parts you have a FK of Product_Code - Is this a Number Datatype?
In your table Staff you have the PK as Staff_Initials - Is this just a Text Field?
In your Customers table you have the PK as Customers_Ref - Is this a Text DataType?

Hi, Product_Code is a number data type (I realized I should have had this as an autonumber later into the development of the database but it is working as a number without any issues)
Suppliers Parts FK Product_Code is a Number Datatype
Staff_Initials is a Text Field
Customers_Ref is a Text DataType

 

[mike60smart]

Hi, Product_Code is a number data type (I realized I should have had this as an autonumber later into the development of the database but it is working as a number without any issues)
Suppliers Parts FK Product_Code is a Number Datatype
Staff_Initials is a Text Field
Customers_Ref is a Text DataType

Hi Chris

It might be working but it is the wrong way to create relationships. I would recommend that you change all PK so that they are Autonumbers and then in any related tables have the FK as a Number Data Type.
It is best to bite the bullet now and fix the issues before you are too far down the road.

 

[ChrisMore]

Hi Chris

It might be working but it is the wrong way to create relationships. I would recommend that you change all PK so that they are Autonumbers and then in any related tables have the FK as a Number Data Type.
It is best to bite the bullet now and fix the issues before you are too far down the road.

Thanks for the advice and will remember for the future but I fear I might be too far down the road as the database has been split and is currently live in my workplace. The Product_Code field is used everyday for inventory purposes so this I why I decided not to change it after realizing it should have been autonumber. I really don't want to potentially ruin what I've got working. I can change the other fields you picked out though.

What issues do you foresee with these incorrect datatypes?

 

[jdraw]

Chris,
I wasn't aware from your posts that this was an operational system.
Too many bad designs are the result of "I've already spent so much time on this, I can't change the design now".
The essence of this is -I'm too busy chopping wood to sharpen the axe.
Anyway, good luck with your project.

If the live system is simply an interim system and you plan to review, redesign, test and implement an upgraded version, I recommend you give readers a description of your requirements and build a model that you can test in order to get a blueprint for the revision(s).

When you go live, it should be with more confidence than

"I now think it could work".

 

[ChrisMore]

@jdraw This database isn't interim, it's more of an expanding database as my company realize the benefits of it compared to having everything on Excel. I understand that I should be using autonumbered ID fields instead of number or text datatypes but I would appreciate some guidance to understand why it's such a bad design to have number instead of autonumber e.g. on the Product_Code field as Mike pointed out. My database has over 10,000 records using this design so what can I expect to go wrong? I'm happy to change it but I don't have the confidence to do this effectively when my company is currently relying on this database as the sole inventory system.

P.S my thread has now gone off topic from my original question so I would still appreciate some help with the statuses and a reply to my previous post regarding this.

Thanks for everyone's help.

 

[jdraw]

Chris,
To me, your original question was concerned with design options. Typically alternative approaches are considered, reviewed, tested and evaluated. Perhaps I misunderstood that you have a production, operational system, but there was no explicit info to suggest it. And some responses seem related to basic design issues/considerations.

Whether you use autonumbers or any other approach that assigns unique values to your PKs is your choice. Experience suggests that autonumbers (or sequences in other DBMS) is a recommended route, but not an absolute in every situation. The fundamental issue is that every table has a PK and the PK is unique.(see rdbmsprinciples).

As for your Status, like any validation/verification, you identify definitive conditions that make a specific value(status) acceptable. If conditions are met, then you accept/assign that value as meeting your criteria. If the conditions are not met, then you raise an info/error flag identifying what/why the validation failed. This same approach works for spelling errors, missing data, null values etc. Just as you wouldn't accept a complete mailing address without a City, you wouldn't promote a record from one status to another, if it didn't meet your criteria.

As for approach for a potential redesign to incorporate a status concept into your existing application, identify the conditions, create a dataflow diagram or similar logic diagram to show a) the validation criteria for each status and b) the events where a status may be changed. Mock up a prototype and test the logic and related validation procedures. Once you have determined and vetted your approach, make the changes to your application in your maintenance/pre-production version and test. Once the testing has met all your acceptance testing criteria, move your new version to production.

Good luck with your project.

You may get more ideas from the Similar Threads at the bottom of the page. This one seems related.
Perhaps workflow info could apply.
For more info on control systems and feedback.

 

[RogerCooper]

I would not expect a Product_Code to be a number or an autonumber field. The normal purpose of a Product_Code is to provide the ability to create summary information. I would expect it to be something descriptive about the group of products, not an arbitrary code. You have fields called "Product_Code" and "Product_Type_Code". You may need to code products multiple ways, but you should make the difference clear. It seems that "Product_Code" relates only to things you buy so it should be called something making that clear like "Commodity_Code" or "Material_Code". The "Product_Type_Code" seems to relate to sold products, you could call it a "Sales_Code" or something like that.