Revising order detail lines with between Lot number

[adhoustonj]

Hello AWF,
Scratching my head with this one. We receive a scheduling file that we import into our database to build the kits on the order, and sometimes the next schedule update will be a revision that modifies the orders. The problem is when there are deviations requested to the part kits, and I will try to explain below.
These are imported where OrderNo/StartLot/EndLot is null to not bring in duplicate orders.

1st schedule:

Schedule	Row	AssyDate	PartNum	Color	Qty	OrderNo	StartLot	EndLot
021_C7.xlsm	1​	10/18/2023​	kit1	White	19​	10182​	701301​	701319​
021_C7.xlsm	2​	10/18/2023​	kit2	Green	50​	10182​	701320​	701369​
021_C7.xlsm	3​	10/18/2023​	kit3	Gray	11​	10182​	701370​	701380​
021_C7.xlsm	4​	10/19/2023​	kit3	Gray	10​	10182​	701381​	701390​
021_C7.xlsm	5​	10/19/2023​	kit4	Carbon	10​	10182​	701391​	701400​

2nd schedule:

Schedule	Row	Assy Date	PartNum	Color	Qty	OrderNo	StartLot	EndLot	Note
021_C8.xlsm	1​	10/18/2023​	kit1	White	19​	10182​	701301​	701319​
021_C8.xlsm	2​	10/18/2023​	kit5	Yellow	21​	10182​	701320​	701340​	changed from part2(Green) and split from one order line of 50
021_C8.xlsm	3​	10/18/2023​	kit5	Yellow	29​	10182​	701341​	701369​	changed from part2(Green) and split from one order line of 50
021_C8.xlsm	4​	10/18/2023​	kit3	Gray	11​	10182​	701370​	701380​
021_C8.xlsm	5​	10/19/2023​	kit3	Gray	10​	10182​	701381​	701390​
021_C8.xlsm	6​	10/19/2023​	kit4	Carbon	10​	10182​	701391​	701400​

so really I need to go back and match on something to hit on schedule1.row2 -- and essentially cancel the order/say 0 required quantity to filled.
As these part kits are color essential, so for the 50pcs in schedule1, if I had built 49 for that orderLine, now none of those 49 are good, I need 50 for the different part kit.

Source.Name	Row	Assy Date	PartNum	Color	Qty	OrderNo	StartLot	EndLot	Note
021_C9.xlsm	1​	10/18/2023​	kit1	WHITE	19​	10182​	701301​	701319​
021_C9.xlsm	2​	10/18/2023​	kit5	Yellow	21​	10182​	701320​	701340​
021_C9.xlsm	3​	10/18/2023​	kit5	Yellow	29​	10182​	701341​	701369​
021_C9.xlsm	4​	10/18/2023​	kit5	Yellow	11​	10182​	701370​	701380​	changed from part3(Gray) to part5(Yellow)
021_C9.xlsm	5​	10/19/2023​	kit5	Yellow	10​	10182​	701381​	701390​	changed from part3(Gray) to part5(Yellow)
021_C9.xlsm	6​	10/19/2023​	kit4	Carbon	10​	10182​	701391​	701400​

Now the same thing - but this isn't a line split, it is a color change, so I really need to cancel schedule3.row4 & row5 - and insert the rows again, or update the existing rows with the new color/part kits.


I'm thinking about the inventory allocation to these orders as well.. I was going to do it at the orderLine level, but I am starting to rethink.
My biggest concern is missing an order that is changed, split, and building the wrong thing, or not importing the order at all.

The import process only checks if OrderNo/StartLot/EndLot is null when joining to the OrderLines table.. I can use OrderNo/StartLot WHILE EndLot <> EndLot, and OrderNo/EndLot WHILE StartLot<> StartLot, but I feel like something is going to slip through the cracks.

Any suggestions on how you would approach this?

 

[plog]

I don't really grasp what you are doing. I can't see what constitutes a match betwen your 3 sets of data. But I do know how you can communicate it very effectively:

Show me the data you hope to end with after you process the 3 sets of data above. So, using the existing data, do the processing manually and show me what should be the end product.

 

[adhoustonj]

Ultimately this is what my order table would look like as I imported the original schedule that was sent out, and then processed the #2 & 3 schedule revisions sent out afterwards.

I added an additional 'QtyRequired' column to indicate order no longer needed as it has changed. This could also be represented by a 'cancel' flag.

Sequence	AssyDate	PartNum	Color	Qty	QtyRequired	OrderNo	StartLot	EndLot
1​	10/18/2023​	kit1	White	19​	19​	10182​	701301​	701319​
2​	10/18/2023​	kit2	Green	50​	0​	10182​	701320​	701369​
2​	10/18/2023​	kit5	Yellow	21​	21​	10182​	701320​	701340​
2​	10/18/2023​	kit5	Yellow	29​	29​	10182​	701341​	701369​
3​	10/18/2023​	kit3	Gray	11​	0​	10182​	701370​	701380​
3​	10/18/2023​	kit5	Yellow	11​	11​	10182​	701370​	701380​
4​	10/19/2023​	kit3	Gray	10​	0​	10182​	701381​	701390​
4​	10/19/2023​	kit5	Yellow	10​	10​	10182​	701381​	701390​
5​	10/19/2023​	kit4	Carbon	10​	10​	10182​	701391​	701400​

 

[plog]

Excellent, that explains a lot. Let's see if I my understanding matches your reality:

You will get a series of files for OrderNo/PartNum/Color permutations that you need to use to add and update your main table. The first file you simply import straight into your main table because that's the starting data and you set every records QtyRequired field equal to whatever is in the Qty field you receive. Then, if you get another file for that OrderNo you do these things with it:

1. If you find a record with an OrderNo/PartNum/Color not in your table, you add it, setting QtyRequired equal to Qty.
2. If you find a record with an OrderNo/PartNum/Color in your table, you can delete the old record and replace it with the new one, setting QtyRequired field to Qty.
3. Last, you find records in your existing table that don't have a corresponding OrderNo/PartNum/Color in the new file. In those cases, you set the existing records QtyRequired field to 0.

How close am I? From what you have given me, the really important fields in matching prior data is OrderNo/PartNum/Color. The fields-- Row, AssyDate, and Lot aren't part of the matching algorithm, right?

 

[adhoustonj]

You are spot on @plog - I was going to say you might be, but figured I'd go ahead and step through it. Thank you for your help.

You've changed my perspective here - I'm over here wanting to protect the precious data we have parsed, imported and cleaned, Thy Lord forbid something should happeneth to it!! And.... With the rules you quickly propose, I just delete everything every time.. At least with each revision, as they all have the same ordersNos for the most part, but... Next schedule it will be different orderNos so it is likely the problem goes out the window, bullet dodged.

I'm going to set this up on my import procedure tomorrow and cycle through historical data. Again, thank you.

My next step is determining whether setting this up as a 'forever' running inventory calculation based off of each scheduling order that comes across, and then just logging all parts we produce, and determining net need, filling orders in priority --- vs ---- an order received, then filled, allocated, closed/invoiced, etc...

I would prefer the 1st - was doubting it could be implemented smoothly, and was leaning to the 2nd.. but i think this just sealed the deal.

One of my only concerns - Is this is a multi user system and could potentially have that order pulled up looking at the quantity required of a part to build, someone updates this as the new schedule comes along, and order gets deleted from others persons screen while open. Unless I can silo it off in another staging table or something first. Maybe the order person can only view partno/required by day, and then at orderNo/partNo level in the other view.. Hmm.

 

[plog]

Awesome, glad I helped. Sometimes you just need someone who is a blank slant to look at it from an unknowledgeable perspective.

 

[jdraw]

I share your concerns with

One of my only concerns - Is this is a multi user system and could potentially have that order pulled up looking at the quantity required of a part to build, someone updates this as the new schedule comes along, and order gets deleted from others persons screen while open. Unless I can silo it off in another staging table or something first. Maybe the order person can only view partno/required by day, and then at orderNo/partNo level in the other view.. Hmm.

Something has to be the authority. There needs to be a process--whether automated or manual -- that represents the current facts. If a change is made there, then you need procedures in place to have those facts presented to those dealing with/needing the current "facts". You really can't have multiple versions of "Order" where different versions have different current facts. Sorry if this is a "little too soap boxy", but since you recognize a potential problem area, now is the time to address it or bring it to the attention of management.

 

[adhoustonj]

do i just run a timer on the screen? I mean, me personally - i do not know best practice here.

and i dont know if there necessarly is ‘one’

i mean sure, i could put whatever query or data behind the form on a timer refresh..

But if someone has a live view of a screen that they need to plan daily production schedules off of, do i just kick everyone off before the update? I hope that isnt the only solution before the final plan is signed off on for the day.

or maybe if anyone has one of the ‘culprit’ forms open that could taint their view, i have a hidden form open at all times that checks every 30 seconds if the updates are running, it would either close/alert/requery their current form if it needed.

i’d take anyones suggestions over any idea that i could come up with.