So I’m not well versed in query language, so forgive my ignorance but I’m so lost with the whole Group By and Aggregate function thing. I’m just trying to make a BAQ that gives me a list of all my parts and part rev data but only for the latest rev. So I assumed that I can create a calculated funct…

There are some potential issues in that PartRev.RevisionNum is a string instead of a number, but for the most part it shoulld work. If I’m reading correctly, this is what you are facing: [image] This is actually an easy fix, but it’s confusing to read. The key is to just hit ok and then click t…

Yeah, that was my thought! But the issue is that once I add PartNum to the Group By, I end up with multiple entries (2 entries) for the same part instead of it just showing a singular entry for the latest rev. :frowning:

What all fields are you displaying, just the partnum and calculated max rev and what tables are you including? You should only need the partrev table itself to get this. If you are trying to pull more information, you need to put this into a subquery to feed into the top as once you add in other f…

This is what I was looking for but couldn’t find. Great resource. I learned a bunch from it.

I have a hell of a time finding it every time I want to reference it too, and I posted the darn thing!

Unfortunately there are a TON of fields I’m bringing in from multiple tables…such as the following (and more): Non-stock VMI Part Class Lot Size Backflush Rev Track by Rev IUM PUM SUM Phantom BOM Purchase to max Track Lots Costing Method Track Serial # Run Out Inactive Primary Warehouse Primary Bi…

Thank you! Ill def take a look at this. Appreciate the resource!

You have a couple of ways to do that. One is the windowing functions filtering technique that I show in the video. The other is to make a subquery with only your selection fields (Company, part number, max(rev) etc.) that ONLY has what you need to ID the proper record. Then you bring that into you…

Group By and Aggregate functions?

Kinetic ERP

Banderson (Brandon Anderson) October 13, 2025, 4:20pm 5

You’ve got quite a bit of learning to do! What you want to do is definitely doable, but a bit hard to explain. I do have this recording that explains a bunch of different techniques that might be a good place for you to get to a better place where at least you can understand enough to be able to ask the right questions.

Topic		Replies	Views
REST API - Power Query to Extract Data Set Kinetic ERP	4	1417	August 2, 2021
More MAX PartTran.TranDate problems in BAQ Kinetic ERP baq	18	1413	July 14, 2023
Different Ways to Use Aggregate Functions Kinetic ERP e10 , baq	28	3344	September 19, 2023
BAQ - Group by function Kinetic ERP	3	378	October 29, 2024
Get latest PartRev in a BAQ Epicor ERP 10 baq	9	5359	September 9, 2020

Group By and Aggregate functions?

Related topics