Our team would like a report created that pulls the last 3 closed jobs for each active part. Although, there may only be 2 closed jobs for a part.
This means going through the Part table and for each part find the last 3 closed jobs from the Job tables while being able to get all these results into the final report.
I’ve only been able to do this for one part at a time. I’m not sure how to loop through all active parts and grab the last 3 jobs in a BAQ.
So, for a simple example the final report could have values like this:
Part A - Job 3
Part A - Job 2
Part A - Job 1
Part B - Job 2
Part B - Job 1
Part C - Job 3
Part C - Job 2
Part C - Job 1
Looking for help from the BAQ geniuses. Any feedback or ideas are appreciated!
Whenever I run the BAQ for all active parts, and set the subquery to use the Top 3 option, nothing returns. I’m sorting on descending Job Num.
When I set a parameter to search for a specific part on the main query, and have the Top 3 option set on the subquery, I get the last 3 jobs. This is one part, not all parts.
When I turn off Top 3, and look for all active parts, I get all job numbers returned in order.
So, I think something is missing in the BAQ when Top 3 is being used when grabbing all active parts, but I’m not sure what.
Ah, I get what you’re saying. My understanding from the user is they want to see the last three jobs. I don’t think they want it by close date. But, that’s a great question, so I’ll have to double check with the user.
Either way, whether by close date or by job number, I don’t think the query is going to work they way I’m currently seeing it. Seeing how you picture it will be helpful.
Ok, I thought of an easier way, thanks to @Banderson 's presentatation @ Insights 2023.
@Banderson It’s making an impact, technical difficulties be damned!
Anyway, you’ll have two subqueries.
SubQuery 1, Top Level:
will include SubQuery 2
Criteria: Calculated_LastClosedRank <=3
Display Fields:
Part_PartNum
JobHead_JobNum
JobHead_ClosedDate
SubQuery 2, Inner SubQuery
Table Part, JobHead
Linked on PartNum
Part Criteria -> Inactive = false
JobHead -> JobClosed = true
Display Fields:
Part_PartNum
JobHead_JobNum
JobHead_ClosedDate
Calculated_LastClosedRank
Calculated_LastClosedRank is an int field with the formula:
ROW_NUMBER() over (PARTITION BY Part.PartNum ORDER BY JobHead.ClosedDate DESC)
Couldn’t find a time field for close, you’d probably have to link into PartTran or something if you need
the exact time.
Thanks, Kevin. This is great! I’ll set this up.
I’m going to have to get better trained on BAQ’s for this type of query.
I kept thinking this might need two queries, but didn’t know how. I’m missing your fancy calculation.
I’ve never used Partition By, but I had been wondering about this function, too.
I have, but I’ve always had a little trouble with it. I keep mentioning @Banderson, so I hope
he doesn’t get annoyed, but he did a presentation on it at Insights, and explained it really well.
I went from making it work, to understanding it, so he obviously did a good job.
I’m pretty sure he plans to do a summary for the forum so everyone here can benefit.
At least I hope he will.
Keep tuned for my presentation to be posted. I didn’t get to a lot of stuff that I wanted to due to the technical difficulties, but I plan on recording something to re-iterate what I did get to and expand on what I wasn’t able to get to. I just need to weekend to decompress.
Looks like it’s working, Kevin! I have to do some tweaking for all the fields the user wants. But, we should be good to go. Thanks, again!! Much appreciated.
Any feedback or ideas are appreciated!
