1

So I've read the following:

And I cannot get this to work for anything. The field I am trying to aggregate is Time_Spent_minutes__c from our IT Request ticketing system. Time_Spent_minutes__c is a formula field that gives the total amount of time spent on a single request between to date/time fields as minutes. I'm trying to collect all of these within a given time range (say a week), and then average them out into a single field in the Summary_Reporting__c object. I cannot for the life of me figure out why all I get returned is a blank field value.

I would appreciate some advice. Thanks!

//Attempt 3
Trigger VS_Summarizer on Summary_Reporting__c (after insert, after update)
{   
List<Summary_Reporting__c> toUpdate = new List<Summary_Reporting__c>();
for(Summary_Reporting__c sumrep : Trigger.new)
{
    if(sumrep.Object_to_Summarize__c == null && sumrep.Value__c == null)
    {
        Decimal avgAmount = (Decimal)[SELECT AVG(Time_Spent_minutes__c)aver
                                            FROM IT_Request__c
                                            WHERE Date_Closed__c >= : sumrep.Period_Start__c and Date_Closed__c <= : sumrep.Period_End__c group by Date_Closed__c][0].get('aver');

        sumrep.Value__c = avgAmount;
        toUpdate.add(sumrep);
    }
    update toUpdate;
  }

}

Below is a bit more convoluted, but it's where I was originally. Then I worked my way to just trying to make it work with a single trigger (the above).

//Attempt 2

Trigger VS_Summarizer on Summary_Reporting__c (after insert, after update)
{
summarizerLibrary sl = new summarizerLibrary(); 
for(Summary_Reporting__c sumrep : Trigger.new)
  {
      sl.averagerOfITReqs(sumrep)
  }



public class VS_Summarizer_library

{
public void averagerOfITReqs(Summary_Reporting__c sumrep)
{
    if(sumrep.Object_to_Summarize__c == null && sumrep.Value__c == null)
    {
        if(sumrep.Type__c == 'AVG')
        {
            AggregateResult[] groupedResults = [SELECT AVG(Time_Spent_minutes__c)aver
                                                    FROM IT_Request__c
                                                    WHERE Date_Closed__c <= : sumrep.Period_Start__c and Date_Closed__c >= : sumrep.Period_End__c group by Date_Closed__c];
            double avg = double.valueOf(groupedResults[0].get('Time_Spent_minutes__c'));

            sumrep.avgValue__c = avg;

Decimal avgAmount = (Decimal)[SELECT AVG(Time_Spent_minutes__c)aver
                                            FROM IT_Request__c
                                            WHERE Date_Closed__c <= : sumrep.Period_Start__c and Date_Closed__c >= : sumrep.Period_End__c group by Date_Closed__c][0].get('aver');
            sumrep.Test__c = avgAmount;
        }
        update sumrep;
    }
  }
}

Resolution notes:

I implemented the suggestions from the answer, and also thought hard about Adrian's question. Once I made the tweaks and put the date ranges back to an appropriate date range it immediately started working.

Thanks for the help!

  • Are you 100% certain there are records between your start and end dates? – Adrian Larson Jul 26 '16 at 1:18
  • @AdrianLarson Well, I feel stupid... but it turns out our sandbox has not been refreshed in a long time, and I was doing a date range of this week. I stared at your question for a moment, thought hard about it, and changed the date ranges to two months ago and it's working now. Thank you for pointing that out. – Genko Jul 26 '16 at 1:38
  • I've definitely made mistakes like that before. Most of us have. I added an answer to this effect, since your soql is well formed and this issue doesn't appear to have anything to do with your syntax. :) – Adrian Larson Jul 26 '16 at 1:44
1

I would like to suggest two things,

  1. Make sure that the record you are inserting must have all fields values populated which are used in the formula field.
  2. Condition specified in WHERE clause retrieves correct records.

Also try using .get('expr0') instead of .get('aver').

| improve this answer | |
  • The new record in this custom object is actually querying pre-existing records in a different object, and is supposed to return the averaged out amount of time to a single field in this object. The formula field on the other object is automated and is populated on every record I'm querying. – Genko Jul 25 '16 at 23:15
  • Trt using .get(expr0) instead of.get('aver'). – Ishwar Mete Jul 25 '16 at 23:17
  • Ohh its get('expr0') not get(expr0) – Ishwar Mete Jul 25 '16 at 23:32
  • 1
    @Genko you could have also aliased the result, like select avg(field) aver from.... Aliases make it so you don't have to remember expr0, expr1... – sfdcfox Jul 25 '16 at 23:57
  • Unfortunately those didn't resolve the issue either. I updated using an alias, and still returning no value. If I a test query in Dev Console SELECT AVG(Time_Spent_minutes__c) FROM IT_Request__c] it does populate an average for every record, so I know the basic part of the query should work. But even with my where statements, I get no errors from the DevConsole using them with Anonymous Execution. I feel really stuck x.x; – Genko Jul 26 '16 at 0:19
2

Make sure there is actually data matching your filters. If you are looking at a filter like:

WHERE Date_Closed__c >= :s.Period_Start__c and Date_Closed__c <= :s.Period_End__c

you will only get data if there are records between those two values.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.