What report can I run to show all part numbers, descriptions, supplier name and supplier part number?
There is no out-of-the-box report that shows this information.
You’d need to create a BAQ with the Part, PartPlant, Vendor, and VendPart tables (with appropriate joins). Then you could create a dashboard and/or a printed report from the results.
Ahhhh ok. Do you happen to know what “the appropriate joins” would be? I’m really new to BAQ’s and only know a little on how to create them. There is nobody here at our company that knows either.
2 Likes
Yummy. Okay, crash course on BAQs. I’m NOT trying to talk down to you, but since I have no idea where your technical knowledge lies, I’ll assume not very high. I’ll also assume since you’re on Epicor 10 that you’re using the Classic screens.
Each table in a database is essentially equivalent to a single Excel spreadsheet. The columns are fields, the rows are individual records. Every table has a column labeled “Company”, and then other fields that hold other data… and related tables have common columns that can be used to link them. For instance, the POHeader table has a field called PONum. Since a single PO can have multiple lines, there is a separate table for the lines called PODetail that also has a field in it called PONum. That way, we can join the POHeader.PONum table/field with the PODetail.PONum field and get a list of all POs with their associated PO lines.
Open the Business Activity Query program, and in the “Query ID” field, enter a name (like PartSupplier or something descriptive like that). Hit the TAB key. Answer “Yes” to the “Record not found, Add new?” message. Check the “Shared” checkbox, and enter in something in the Description field. Click the SAVE icon.
Click the “Query Builder” tab near the top of the window, and you’ll see the list of tables on the left-hand side. There is a BOATLOAD of tables in the Epicor database… but you only need to worry about 4 of them for this project. In the little white box just below where it says “Phrase Build”, type in the word “part”… and you’ll see the list of tables now has Erp.Part at the top of the list.
Double-click on Erp.Part, and it will appear in the grid area.
Now, where you typed in “part” a minute ago, change that to partplant and double-click on Erp.PartPlant to add it to the grid area as well.
You can click and drag the two green boxes to move them apart. Notice the gray diamond and lines… click on that gray diamond and look a little further down
The “Join Type” tells the query how to interpret the data. The default is “Matching rows…”, which means that ONLY show me Part records that have a linked PartPlant record. This is also called an Inner Join. The next option is “All rows from Part”, which means show all Part records whether or not they have a matching PartPlant record (also called a Left Outer Join). For now let’s not worry about the other two kinds of joins.
For your query, you want to add the Vendor and VendPart tables, and change the automatic “Matching rows” joins to “All rows from PartPlant” and “All rows from Vendor”.
With a little luck, when you finish it should look something like this:
Give it a shot and let me know!
3 Likes
Ernie - this is GOOD stuff! I am about to leave for the day but will for sure try this in the morning and let you know. Thank you so much for taking the time to put this together for me!! Have a good night and talk to you in the morning.
This query looks at Part numbers in your system in the part master file, and their associated default vendor… from there it looks at your Supplier Part List to see what is there. If the results of the query aren’t what you think they should be, don’t sweat it! We’ll figure it out.
Have a great night!
Hi Ernie - It has been a busy day! I just ran the query and it says an error and it timed out. I did the things you mention and left the gray diamond between Part and PartPlant as Matching rows. Then changed the gray diamond between PartPlant and Vendor from Matching rows to All rows from PartPlant. Then changed the gray diamond between Vendor and VendPart from matching rows to all rows from vendor. Not sure if I misunderstood what they needed to be. Please advise.
Can you post a screen shot of the BAQ grid area, showing the tables and arrows?
Ok let me try that
What is the error you got?
That means “Too much data”. The timeout means Epicor couldn’t complete the query in the allotted time.
Let’s narrow this down to just show Purchased parts. Go back to the Phrase Build showing the 4 tables, and click on the Part table. In the area below the grid, click on the “Table Criteria” tab. Click the “new” icon; use the dropdown in the Field column and select TypeCode, in the Filter Value column use the dropdown to select “specified constant”; then click on the word “specified” and type a “P” in the value field of the box that shows up. Click OK. Now your screen should look like this:
Ok did that
Try running the query now and let me know what happens.
Too much data still. Is there a particular part number that you KNOW has a supplier PN and a default supplier? If we can just pick a single part we can at least prove that the query works… then we can figure out how to filter it for the rest of them.
Back on the Query Builder page, where you have the criteria for TypeCode, change it to PartNum there and for the specified constant, change it from P to whatever your part number is.
Ok trying that now
