Inventory Management (2 Viewers)

[tmyers]

That is true Pat. See, I overthink and overcomplicate

 

[LarryE]

Just based on what you have said so far, here is a start anyway. You can use it as a beginning if you wish.

 

[Pat Hartman]

So, we forget about pallets

 

[tmyers]

I was finally sent the Excel that is in use.
It is being very user unfriendly currently, so all I could manage was a snip.

The current file has 3 tabs as we have 3 different locations currently (were moving and everything is going to be consolidated into one giant warehouse). They color code rows for quick identification and such and there are 6 people that actively use this and there has been numerous update conflicts and instances of corruption even though it looks so simple.

 

[jdraw]

Can you step back and look at the big picture?
I see JOB as an identifier in your speadsheet snip, but no reference to same in the thread until now???
Perhaps the model(from LarryE) needs an update and then vetting with some sample data.

 

[mike60smart]

I was finally sent the Excel that is in use.
It is being very user unfriendly currently, so all I could manage was a snip.

View attachment 101788

The current file has 3 tabs as we have 3 different locations currently (were moving and everything is going to be consolidated into one giant warehouse). They color code rows for quick identification and such and there are 6 people that actively use this and there has been numerous update conflicts and instances of corruption even though it looks so simple.

Can you explain in more detail the "Description" Column?

 

[LarryE]

I was finally sent the Excel that is in use.
It is being very user unfriendly currently, so all I could manage was a snip.

View attachment 101788

The current file has 3 tabs as we have 3 different locations currently (were moving and everything is going to be consolidated into one giant warehouse). They color code rows for quick identification and such and there are 6 people that actively use this and there has been numerous update conflicts and instances of corruption even though it looks so simple.

Can you attach the EXCEL file so I can download it? I may be able to import data from it. Also:

1. What is the heading of the first column?
2. What does the Color column mean? The color of what?
3. You have Job Names. Do you have the potential for multiple Job Names for any Customer?
4. Is Location the warehouse location of the pallet?

 

[LarryE]

So you have:

1. Multiple Customers
2. Which may have multiple materials
3. Which may be stored in one or more of multiple warehouses
4. Which may have multiple storage locations within each warehouse
5. Which may have multiple pallets of material at each storage location

Is this scenario correct?

 

[Pat Hartman]

I know you haven't mentioned this but it seems that you would want to double click on an item in the order database and have it copied over here, maybe with a question about quantity. i.e. all or part. That minimizes the data entry and goes a long way toward ensuring correctness. Or, even manage the whole thing in the order entry application.

I'm going to guess that you don't have any way to modify the order entry application also which makes all the data entry manual

 

[Pat Hartman]

If Pallets can contain multiple types of material, the Pallets table would need to be between Customer and Material rather than at the end of the line. Also, I'm not sure we are using pallets any more.

 

[jdraw]

@tmyers

Can you describe your intended use of Pallet, Item, Material... after receiving some advice in the forum? It is important to your database structure as Pat has said in #30.

 

[LarryE]

If Pallets can contain multiple types of material, the Pallets table would need to be between Customer and Material rather than at the end of the line. Also, I'm not sure we are using pallets any more.

You're right. I think he should just use warehouse location. As of right now he said there are 3 warehouses, so we also need to identify them somehow, maybe by address location or name.

And is Job Name the same as Customer Name?

 

[tmyers]

I ended up getting busy near the end of the day so sorry for the late reply!

Attached is a trimmed down version so it plays a little nicer (it kept locking up for me). Here are the details:
Row in the context of this file is what I am referring to as Aisle.
Location is what I am calling bay, row and shelf. I just chose to break it down a little more whereas they combined it into one code.
Color is simply the color of the paper label they taped to the material for easy identification (they might have some sort of color coding system with that, but if they do, I am not aware of it)
Customer and job name are self explanatory.
The description is simply what is on that pallet, so in this context, the pallet is a container of those listed items. Sometimes it has multiple of a single item, other times it can have a bunch of various items and it looks like they don't keep track of master cartons, just the overall quantity of an item on said pallet.

Can you attach the EXCEL file so I can download it? I may be able to import data from it. Also:

1. What is the heading of the first column?
2. What does the Color column mean? The color of what?
3. You have Job Names. Do you have the potential for multiple Job Names for any Customer?
4. Is Location the warehouse location of the pallet?

Answers to 1 and 2 are above. For 3, yes. A single customers can have many jobs, but a job only belongs to a single customer. For 4, the location is as I stated above, it is the location within the given aisle (which in this excel file, they refer to as row).

So you have:

1. Multiple Customers
2. Which may have multiple materials
3. Which may be stored in one or more of multiple warehouses
4. Which may have multiple storage locations within each warehouse
5. Which may have multiple pallets of material at each storage location

Is this scenario correct?

1. Correct
2. Correct
3. Incorrect, currently we do have multiple locations but everything is being consolidated into one warehouse (I deleted out the other tabs in the file so it ran smoother and to not cause confusion). So for intent of this particular app, multiple warehouses is not needed.
4 & 5. Customer may have material on multiple pallets in multiple locations within a single warehouse.

I know you haven't mentioned this but it seems that you would want to double click on an item in the order database and have it copied over here, maybe with a question about quantity. i.e. all or part. That minimizes the data entry and goes a long way toward ensuring correctness. Or, even manage the whole thing in the order entry application.

I'm going to guess that you don't have any way to modify the order entry application also which makes all the data entry manual

I can export excel/csv files from our primary system, but more or less yes, the entry will sadly be manual.

If Pallets can contain multiple types of material, the Pallets table would need to be between Customer and Material rather than at the end of the line. Also, I'm not sure we are using pallets any more.

I am not entirely sure either lol. I always just thought of a pallet as a container of multiple items, whether all of the same item or different types of items. I guess we would not need pallet as the location itself would technically be the "container". However, how would you handle items that take up 2 spaces? So if a row in a bay has technically 2 spots, one item could take up both due to size.

Hopefully I answered everyone's questions!

 

[mike60smart]

Hi
In the master file I have entered details of the Customer Names for the first 3 rows.

Is my example correct or would all of the Rows have different Customer Names?

 

[tmyers]

Hi
In the master file I have entered details of the Customer Names for the first 3 rows.

Is my example correct or would all of the Rows have different Customer Names?

How you entered it is correct

 

[jdraw]

@tmyers
Just some thoughts for consideration.

I think you have to flesh out more details of what you described in post #1. Also, minty offered some suggesrions in post #6.
Seems you have some activities related to
- acquisition of material and recording of details of that material
-identification of available locations (row,aisle,shelf) for such material
-assignment of locations for material (whether pallet/item)
-recording details material, who, what, when (with links to Customer, Job/Project..)
Then, you have the transactions related to
-withdrawal of materials (need to determine the granularity (item/group/pallet..to be used) will involve material/qty/who/time...
-whether the withdrawal can be picked from existing materials for this Customer/Project (sufficient qty?)
-whether the withdrawal exhausts the existing qty of the material
-at some point you will be faced with a physical stock taking to confirm holdings

Can materials be "transferred between customer projects"?
Can materials be transferred between Customers?

What interactivity of personnel with the database system/application is envisioned?
What exactly is the significance of the color scheme? Will it be replaced with some screens/forms...?
As minty suggested, a simple bar code mechanism may support more automation, if that is part of the goal of a system upgrade (move away from Excel).

You can mock up some sample database and review with the sponsor of the project to help with the analysis required.

 

[mike60smart]

How you entered it is correct

OK Thanks and in the Description Column does the following "(10)-F4A" equate to

Qty x 10 of Item F4A

 

[tmyers]

OK Thanks and in the Description Column does the following "(10)-F4A" equate to

Qty x 10 of Item F4A

Correct.

 

[mike60smart]

How you entered it is correct

So Customer A received Jobname "Orthopedic One" which is made up of 2 Pallets

 

[tmyers]

So Customer A received Jobname "Orthopedic One" which is made up of 2 Pallets

Correct. Customer A has job Orthopedic One and that job can have material in multiple locations.