I hope this isn’t too easy of a question for all of you but i need to create a BAQ that will list how many jobs we’ve made in Epicor using a specific part number. My idea was to create a BAQ that would show the partNum in column A, Our inhouse Part Num (custom field) in column B, and then the number of jobs containing this part number in column C. We need to reorganize our tooling storage and we want to move lesser used tools somewhere else and replace them with frequent fliers. I was trying to use Jobnum, Count and part.partnum but i don’t think im using it correctly. Which field would i need to perform the count function? Or is there a system report i can use instead?
Hi Joe,
It looks like you are on E10. Just create a calculated field and use the aggregate function Count with any field from your result set (part or custom part). You will need to group the part and custom part fields.
1 Like
Ok i understand the part about using the calculated field with count but how do i associate the calculated field ( Count(Part.Partnum) with the JobNum? I need it to tell me how many jobs exist for that one part? I know i can use the job manager for such a report but this would be a list of ALL of our parts and how many jobs either exist or have existed before.
Hi Joe,
In your BAQ, use the job Head table and join it to the part table to get your custom part. Then use the count calculated field.
1 Like
Joe, sounds like you need some help with basic BAQ writing. Can you paste some screen shots of what you have done so far so we can get an idea of what you have so far and what you need help with? I’ll be happy to coach you through up to what I know (which, from your description I should be able to do) but I don’t want to describe everything from start to finish if you are close and only need one small piece.
1 Like
Here are a couple screenshots. This report works as expected, but this morning i noticed one minor problem. It does not show a zero value for “Number of Jobs” and i know we have some parts in the system that have never had a job number (old unused Part #'s). Can i change up my calculated field to include 0 values as well?
You have to change the relationship type on the bottom right to a right join. I usually prefer to make sure that the tables are ordered in a way that I can use left joins as it’s easier to understand.
2 Likes
That seems to work!!!
I am still getting accustomed to the deeper tricks of BAQ’s so i appreciate the walk-though everyone.
The report is now working as we need it to. Thank you BBrowning, Banderson, and Pmarques very much for taking the time to help me.
I was going to tell you about the join relationships, but Pedro beat me to it. Glad you got it working.
1 Like