function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
mjohnson-TICmjohnson-TIC 

AggregateResult Method on Custom or Manipulated Object List?

Hi All,

 

I was wondering if anyone might have any idea as to possibly aggregate a manipulated list of an object without actually updating any values of the records returned. Here is the easiest example I can think of to help try to explain what I am doing.

 

I retrieve and manipulate a list of accounts.

 

for(Account a: [Select Name, Last_Year_Sales__c, CreatedDate from Account LIMIT 20]){

if(a.CreatedDate >= datetime.now().adddays(-100)){
 a.Name = 'New';

}else{

a.Name = 'Old';

}

 

Now I would like to aggregate the SUM of Last_Year_Sales__c and group by the manipulated value of Name without actually updating the name of these accounts in the database. Is there a way to use AggregateResult to summarize this list of retrieved data without actually querying the Account object in the database? It would be very nice to aggregate custom or manipulated lists of data generated in Apex.

Best Answer chosen by Admin (Salesforce Developers) 
Damien_Damien_

Yea,  I addressed that just not very detailed.  Thats why I also added This is possible if you want to loop through all of the data.  You could pass a field into the parameter of a method to make this more dynamic to determine what field you would be grouping by.

 

1) Create SUM method

2) add parameters for 'grouping', 'list', 'field'

3) put your loop here in your method.  You loop can basically create a map that uses your 'grouping' for its key.  You can then use the map 'get' for the associated key and add your value according to whatever is already here.

 

Does this method make sense?

All Answers

Damien_Damien_

You can get the info with 2 separate aggregate queries.

 

SELECT SUM(Last_Year_Sales__c) from Account WHERE CreatedDate >= dateTime.today().addDays(-100)]

SELECT SUM(Last_Year_Sales__c) from Account WHERE CreatedDate < dateTime.today().addDays(-100)]

 

(My syntax might be off, but this should give the general idea.)  You would still need to be querying accounts in order to aggregate this information.  You wouldn't need the group by Name with this since its already in 2 separate lists.

 

It would be very nice to aggregate custom or manipulated lists of data generated in Apex.

This is possible if you want to loop through all of the data.  You could pass a field into the parameter of a method to make this more dynamic to determine what field you would be grouping by.

mjohnson-TICmjohnson-TIC

Unfortunatly while similar in concept, programatically the issue I am facing is a bit more complex where the dynamic value assigned to a field in the object is based off the mapped value returned  from an unrelated object list. My hope was to use aggregate methods such as SUM, AVG etc on a list generated in Apex rather than returned from a query to the database.

 

I think for this particular case I may be able to loop through my mapped list for every unique value and run a dynamic database.query filtered by each value within the map - ultimately returning  a list of aggregateresult. What I don't like about this though is the need to run a separate select statement for each aggregateresult returned rather than grouping by a value assigned dynamically. 

Damien_Damien_

Yea,  I addressed that just not very detailed.  Thats why I also added This is possible if you want to loop through all of the data.  You could pass a field into the parameter of a method to make this more dynamic to determine what field you would be grouping by.

 

1) Create SUM method

2) add parameters for 'grouping', 'list', 'field'

3) put your loop here in your method.  You loop can basically create a map that uses your 'grouping' for its key.  You can then use the map 'get' for the associated key and add your value according to whatever is already here.

 

Does this method make sense?

This was selected as the best answer
mjohnson-TICmjohnson-TIC

The field itself is not what needs to be dynamic, the value in the field needs to be determined dynamically without running an actual update to the records. I'll give my actual example so that there is some context behind this.

 

I have a custom object "Country__c" and have a record for every country in the world (record name) and a custom text field "Sales_Region__c" to determine the sales region. 

 

I have a custom object "Forecast__c" which we use to forecast products, which has a lookup to Account. Right now there is no relation between the Country__c object and Forecast__c or Account - the only way to obtain this relationship is creating a map of the Country__c object and using get methods based on the BillingCountry listed on the Account (note, in this example both country name, and abbreviation are put into the map due to inconstency of BillingCountry entry which is a problem in its own).

 

The challenge is to get a summary of the Forecast__c amount field (CY_Fcst__c) grouped by the Sales_Region__c mapped value returned from the related Account BillingCountry.

Damien_Damien_

Yes, my solution covers this example.  My example just allows for being more dynamic if you wanted to do the same work on different fields later on or even different objects and different fields.