My standard practice when pasting in data to a sheet is to paste, look for columns that might be affected by Excel’s reformatting, format those columns to text (or date), and re-paste the data using values only. When I forget to do that, I almost invariably regret it.
If you’re pasting into Excel, you should set the whole sheet to text before doing anything. Epicor ignores Excel’s data types. So take that variable out of the equation.
Hi John,
One caveat to that is vlookups. They don’t work when the field is text and then worksheet needs a tweaking. I suspect I’m not the only one needing to add a column, format it general, and then do an = to whatever it is that I actually need to use for doing lookups to get it back to behaving for me.
I do what you’re saying (whole worksheet) when I know I’m not running any calcs, but if running calcs, then I like to just format problem columns…
Nancy
The DMT works great! I have been using it in Pilot for the last few days. I am using Bill of Operations to delete and import operations for parts that I have exported from my BAQ. My BAQ filters for only parts that have an open job. We will go back at a later time and update parts without open jobs.
In this case, I have a total of about 400 parts to update, containing about 3700 operations, and about 600 materials. First, I use the Bill of Materials to delete the materials from the parts. Next, I use the Bill of Operations to delete all the operations from the parts. Now, I pull my BAQ export in to Excel and correct the various columns that need fixing. This is mainly using the old resource group ID to lookup the new operation ID.
I did the complete test in pilot for all 400 parts, and it took about 10 minutes to delete the materials, 45 minutes to delete the operations, 1 hour to import the operations, and another 15 minutes to import the materials. Not bad!
So now I am looking forward again to the mountain of open jobs that are formatted the old way. Some of these old jobs have been open for years, with no end in sight. I think the only way to move forward is to close these old-style jobs and recreate them with the new part masters, and relink their demand. Folks that have been here longer than me have balked at this idea, with various reasons that I can’t recall. I think there is an issue with finance, and some reports.
What is the best way to move ahead? We want all of our jobs to utilize the new operations that I setup so that our capacity and load can be reviewed. Since our old jobs are all setup with a single infinite resource group, our scheduling will never work right until I get these open jobs fixed.
If you want a DMT black belt make queries that start with DMT-xxx and format the headers to match your DMT template. Then all you need to do is right click and go to excel.
By the way, not sure if you know this but if you name an excel file part.xlsx you can just drag and drop it into the DMT top bar and it will automagically pull the correct template for you.
For the 2nd degree black belt look into scripting DMT. You can use a batch file to use DMT to pull your DMT Query to excel as a spreadsheet, then the next batch file line can import the result back into the system. And you can place it as a windows system task on the task agent server. Slick!
Changing jobs on the fly with DMT is a recipe for disaster, consider you will have to remove and re-issue all materials. Maybe do it on jobs with completed qty = 0 but I wouldn’t touch that one with a ten foot pole, just let them bleed out over time.
Just curious… if you really need new part masters, or just new revisions for those existing part(s)?
Otherwise, my first impulse would be to do what you suggested - close the old jobs - create new jobs and relink, etc…
To be safe I’d usually fully simulate this for at least a few part demands in a test system. And consult/verify with accounting. (since I’ve been burned by job costs in the past… a pain to fix after the fact).
Hi Nate,
I think if the problem is desire to use nifty new operations on old jobs, then I’d add the new ops to existing jobs and check old ops as complete. You probably could send out your job op data in a BAQ and then fix it up with new operations and import. I’d test first!
Nancy
I think once a job is open and released, you can’t add or edit the operations. Right?
I am actually updating existing part/revs. Maybe my terminology is off, but I consider a part/rev to be the part master. You can have multiple part masters (multiple revs). Where some can be approved and some not approved.
Hi Nate,
We unrelease and unengineer and then add ops and materials many times for mfg parts that don’t have a standard method.
Nancy
I would recommend bringing in someone from finance. It’s great to fix everything for manufacturing but they don’t want to be dinged for odd efficiencies or variances.
Depending on company config?
That’s true and it’s a pretty sketchy box to clear because unreleasing and un-engineering a job is the only way out of the box to put a job on ‘hold’. So make sure you have full buy-in before checking that box!
Wow… this is the EXACT example of one of the things you should avoid in defining a part number system in my Best Practices document Part Numbering Standards
Having an E in the middle of a number can be very problematic.
Such a great read! Thanks for putting it together! I wish folks here had read this document before they started making part numbers! Thankfully I haven’t seen any issues with exporting to Excel, yet…
30 years late on this for us Tim! I couldn’t even get our company in a new system to not use state abbreviations in some warehouse and part ids… then we moved our WV plant to OH. Ah well, what can you do?