Skip to content


Is it possible to utilize aggregate functions with a CRM query?

Kevin Collins asked 5 years ago
We have a custom entity that has multiple rows with the same item but with different dates. 
I’m trying to mark all items with an older date as a historical record.
Is that possible?
Patrick Roth Staff answered 5 years ago
Not sure about this one.
Ideally you would use this table as the source and so would have the id of the row.
Then using the same table as the destination, you can update it back again since you know specifically what row you are updating.
But if you (most likely) saying “of the X rows, one of the dates is the “latest” date.  I don’t know what that date is but that one is the open record and the rest are historical”.  Then that’s going to be trickier.
I’m not sure in the CRM Query if we can get the aggregate (max) record.  We could use a Grouped On map in SC – but then you would just get the max but don’t know the detail records anymore.  So I don’t know how you’d then update just the not-current crm records w/o the record id of each.
What would work is if you do the crm query as discussed and then send the results to a SQL table with the field information that you need.  The item, date, and the record id maybe is all you need.
Now you write that to a SQL table and write a query to then find the item/date max and restrict those out of the query and just return the rest.
Now use a run map to the 2nd map to use the SQL query as the source and then your table as the destination.  Since you’ve found the records you need, you’d just update that data set.

If you would like to submit an answer or comment, please sign in to the eOne portal.