So we have different products that are boxed up for orders months in advance and every week staff add what was boxed writing down SN/LOT, BOX SIZE, Weight.
The band aid solution currently providing us a schedule is a BAQ that Comtec made for us when we first signed up and it is better than the canned reports like Scheduled Shipments or Sales Order Backlog Report. I have attached a screenshot below:
But Comtec just added the BOX and Weight Field to this without it being a field I can add the data every week from what the production floor boxes off, so I take this and Copy to Excel then format it, add in the weeks data then vlookup from the previous week because there is no paste update in excel that I am aware up, then custom sort Customer Name then Need By and also Concat(Order-Line-Rel) and add a column for SN/LOT
The a manually added data into this new spreadsheet has to be the right font size and other preferences so people can see whats shipping this week, whats already boxed and what orders are open that need to be boxed. Or Else lots of complaints. So I utilized Macros for most of the formatting operations but didn’t have a lot of success filtering and custom sorting with macros.
Here a screenshot of what the end spreadsheet shows.
So any luck with how I might be able to eliminate the need to Copy to excel from the BAQ. Have any of you utilized Staging in the Customer Shipment Entry? To keep track of all the boxes we write down SO#-LN-REL, CPN, C PO#
Each shipment is only one box? or there could be more… also, is your dashboard an updatable one?
And have you tried to add the extra field for the SN/LOT in the BAQ/dashboard?
The BAQ only updates when you hit the refresh which triggers it to run again. It would be nice to have it update on its own every hour or some interval, and yes adding the extra field for SN/LOT would save a step in excel though before I do I want to see if I should start over or add User Define Fields which I have no experience with.
So if I understand you correctly, You are copying to Excel, for the users to write down the values of Box, weight and SN/Lot am I correct?
If this is the case, no need to redo averything, YES ud fields would be best for you and I encourage you to read along how to do that (multiple cases here or use read your documentation).
You would then modify your BAQ to make it updatable for the users to enter the info directly into your dashboard in Epicor. (again read IceTools documentation on how to do it… )
Better yet, Once you are getting the hang of it, create a small interface that could read your scan (if info into the barcode) and save all the info in your table in one click (but that is more work… ).
(for us, our labels contain the job, partID and box no. where the job acts as a Lot# )
@TaylerDISwanson
What version are you on?
If you have Advanced Material Management module you may want to consider implementing PCID, at first glance it may seem like overkill, but it might really help you without all the extra work and delays on updates to the spreadsheet. PCID can be used a lot of different ways, but in your case, it would allow your staff to ‘pick’ or move all the parts to PCID containers (boxes) and it works just like any other transaction. However you can see everything in the PCID container and it can contain mixed parts, lots, SN, etc, whatever you want. You can assign PCID to a Customer, order, by using the Fulfillment Workbench to Pick Orders by Material Queue.
This then allocates and reserves the inventory.
Now you have a link between a PCID and the Order, which has the ship by date. Then a BAQ that would show the earliest Sales Order Ship By Date in the PCID would give you a ship schedule.
Then when it’s time to ship you just need to enter the PCID number into Customer Shipment entry and it loads all the contents into the Pack.
It’s really easy to get lost in PCID since the training guides tend to explain it when it’s used for everything, instead of just picking\packing\shipping. The only other hurdle would be PCID labeling, Epicor includes BarTender labels for printing PCID’s that you generate but that can be a challenge to work out.
I know this isn’t a direct answer to your question, but what you are trying to accomplish sounds like PCID is a good fit.