Does anyone know of a way to reference a table 2 times in the same BAQ? I’m trying to export data from Vantage 8 t Epicor 10 WITHOUT having to do other calculations, configurations, deletions in Microsoft excel before trying to load in Epicor 10.
What’s doing is going through all Active Parts, approved revisions and then exporting their bills of materials from Vantage 8 and bringing them into Epicor 10. The problem is that (in some cases) we have inactive parts on active Bills of materials. I would like to build this extra check into my export BAQ so that any mtl part number that is also marked as inactive will also be excluded from the export.
In order to do this, I would need to reference the part master table 2 times in the same BAQ. Once for the master part number (checking for active parts) and then linking back to the part master table using the mtl part number from the BOM and then checking again to see if that part may be marked as inactive too.
I know I can accomplish this using Excel and then filtering records out - but I’m trying to avoid that kind of editing at all costs (unless there is not other way).
Any ideas?
Please advise.
Thank you.
Jeff Henslee
M-B Companies Inc.
Since this seems to be a one off thing, could you copy the Part table (just the Company, PartNum, and Inactive fields) to a UD table, then use the UD table as the second instance of the Part table?
You’d just want to make sure the UD table is updated prior to running the final BAQ for export. If you want to get tricky, make a BPM that copies the Part to UD prior to the BAQ running in the BAQ Export.
Jeff,
What I did was bring the BOM records and part records into an access DB. I have the Part table in my export query to first filter all inactive parent BOM’s out. I then alias the part table to the BOM table on Part >> Part Mtl, run a calculated field to find all inactive Mtl parts on a BOM, Change the Mtl Part Number to “P-Inactive” and append the old Inactive Mtl part number to the Mfg comments field with any comments that may have been associated with the original inactive parts.
Jeff - as one who recent jumped from V8 to E10 (via DMT), some things I strongly suggest you double check (as they all bit me in the ass):
The UOM setup. Make sure you specify the UOM Class and Inv UOM whenever importing parts. They are a bitch to change after the fact.
Don’t import Inactive parts unless you really, really need to. And if you do, don’t set/update their costs, as this will create ParTrans, making the parts forever un-deletable.
Line numbers (OrderLine, OrderRelNum, POLine, etc…). The DMT doesn’t respect them. If you had an order in V8 with lines 1-5, 7-9 (because line 6 was deleted), the DMT import will make the new order details as lines 1-8 (new 1 is old 1, … new 6 is old 7, new 7 is old 8, …)
MtlSeq on BOMs are also not respected. if you had an V8 BOM with MtlSeq’s 10,20,25,30, they’d be imported as 10,20,30,40
CustNum isn’t respected when importing Customers. The CustNum you provide is ignured and the CustNum is sequentially set. All other imports that specify the Customer, should use the CustID and not the CustNum from V8.
Sorry if this was a thread hijacking. Maybe a new “V8 to E10 via DMT” thread is in order, or even a new sub-section.
(edited for clarity)
That’s basically what I wound up doing. I exported the part number and the inactive field to a UD Table and then cross referenced that table with the mtl part number and made sure that this was an active part. Thanks