Automated Production Planner

[ChrisMore]

Hi everyone,

I am looking for some guidance on how to approach building an automated production planning system into my existing orders/stock database.

A little background on our business operations and current set up:

• We manufacture fire barrier insulation using various machines and staff to operate each machine
• We currently have a traffic light system which is controlled by the factory managers who use their knowledge and experienced to manually update the capacity of each machine based on production demands
• The traffic light system is built into a work week calendar within the database
• The day in which an order appears in the calendar is controlled by the factory managers entering a date in the Planned Production Date field

Relying on one or two staff to control our current system of course means we have issues when those staff aren't available, so it's important that the system is as automated as possible. I would like the database to calculate the next available Planned Production Date and/or calculate a viable date based on a chosen dispatch date. Ideally, the calendar will include a visual similar to a traffic light system so staff can quickly see the status of each machine. I am thinking this could be in the style of a graph, one for each working day and a line for each machine per graph. The lines can then change from green to red based on the current capacity.

The database currently has data for all the products we manufacture so I think each product will need to be categorized by type and then utilizing a junction table of sorts to list the product types and the factory resources required for each. I know we also need to record time in motion data for each product, but I am otherwise drawing a blank on how to proceed with building my production planning vision.

Any questions, please feel free to ask. I really appreciate any support I can get.

Thanks,
Chris

 

[Gasman]

Well I would be asking the factory managers how they work out capacity/dates and and anything else that can change.

Having that will set the rules on calculating dates, whether we are on green, amber of red, etc.
When you have that information, you should be able to see how to structure the DB to produce that process.

 

[ChrisMore]

Well I would be asking the factory managers how they work out capacity/dates and and anything else that can change.

Having that will set the rules on calculating dates, whether we are on green, amber of red, etc.
When you have that information, you should be able to see how to structure the DB to produce that process.

Hi Gasman, thanks for your input.

This has already been discussed and the managers have a judgement on capacity based on their experience on how many of a product can be manufactured per day. However, this judgement will not be accurate on a daily basis, so we might not be achieving our maximum production capacity. This is why the system needs to be data driven. As we are gathering the data to find this answer, it makes sense to attempt to build an automated planner into our current system using this data. The building part is what I am seeking advice on.

 

[mike60smart]

Hi Gasman, thanks for your input.

This has already been discussed and the managers have a judgement on capacity based on their experience on how many of a product can be manufactured per day. However, this judgement will not be accurate on a daily basis, so we might not be achieving our maximum production capacity. This is why the system needs to be data driven. As we are gathering the data to find this answer, it makes sense to attempt to build an automated planner into our current system using this data. The building part is what I am seeking advice on.

To do that we would need to see your current table structure.
Are you able to upload a zipped copy of the database with no confidential data?

 

[ChrisMore]

To do that we would need to see your current table structure.
Are you able to upload a zipped copy of the database with no confidential data?

the database is split, is it just the back end tables you need to see?

 

[mike60smart]

the database is split, is it just the back end tables you need to see?

Both FE & Be would help

 

[JTrilloJ]

Hi Chris:
I understand that what you need is a "Utility" to calculate machine load.
If so:
1.Do you have a "Route Map" defined for each product in your database?
2. Do you have a setup time and process time defined for each machine and product phase?
3. Do you work against orders or against stock?
There are quite a few more elements to consider, but these are just to give you an idea, in case I can help you with anything once I see your database.
Regards

 

[ChrisMore]

Hi, attached is a sample version of the database. I have only included the calendar section of the database with the traffic light system but you can see the table structure etc.. Double click on the records in the calendar report to open the order form.

@JTrilloJ
1. I'm not sure what you mean by the terminology "route map", maybe you'll get your answer from the sample database.

2. I don't have the data yet for process times. As I said in my initial post:

I think each product will need to be categorized by type and then utilizing a junction table of sorts to list the product types and the factory resources required for each. I know we also need to record time in motion data for each product

As this project is still in the planning stage I don't want to record any data until I know what direction I need to go in for it to work with my database setup. The last thing I want to do is record the data and then find out I need more after starting to build the Production Planner.

3. Again, not sure what you mean by this but you might set the answer from the sample database.