2

Should be simple had it been SQL. But. I would like to get the amount field value that belongs to the most recent month available for that parent and that type.

Data:

Parent_Id__c    Type__c    Date__c       Amount__c
1               A          09/01/2016    100.00
1               A          10/01/2016    200.00
1               A          08/01/2016    50.50
1               B          09/01/2016    1000.90
1               B          07/01/2016    1100.40
1               C          05/01/2016    888.20
2               A          09/01/2016    100.40
2               A          10/01/2016    200
2               A          08/01/2016    50
2               B          09/01/2016    1000
2               B          07/01/2016    1100
3               C          05/01/2016    888

Desired Output:

1 A 200.00
1 B 1000.90
1 C 888.20
2 A 200
2 B 1000
3 C 888

What I have so far:

select Parent_Id__c, MAX(Date__c), Type__c from Expense__c 
GROUP BY Parent_Id__c, Type__c
ORDER BY Parent_Id__c, Type__c

But when I throw in Amount__c currency field in the mix, like so:

select Parent_Id__c, MAX(Date__c), Type__c, Amount__c from Expense__c 
GROUP BY Parent_Id__c, Type__c, Amount__c
ORDER BY Parent_Id__c, Type__c

...it says "field 'Amount__c' can not be grouped in a query call". Sounds like it is not possible to get what I want with pure SOQL (looked at this also). Can the experts comment/confirm please?

  • as per your desired output, use select id...from object order by Date__c. – Shravan Boddula Oct 21 '16 at 15:02
  • Won't work. I am looking for just the most recent record from each parent and type. – Lightning Evangelist Oct 21 '16 at 15:08
  • sorry...select id...from object order by Date__c,type desc. will this works – Shravan Boddula Oct 21 '16 at 15:11
  • Sorry, I believe you aren't understanding the question. Perhaps I need to rephrase, but not sure how else. I am looking for 'just the most recent record' 'from each parent and type'. Updated the Desired output in the OP. – Lightning Evangelist Oct 21 '16 at 15:17
0

No, you cannot get the specific Amount__c given the maximum Date__c for your groupings. Not in one go, anyway. I think the path of least resistance would be to scrap the aggregation and just use post-processing.

You could do a mapping of combined key to Expense__c record, but it may be easier to just use a Map<Id, Set<String>> to track observed values and store the records in a List<Expense__c instead. You could use an inner class that looks something like:

public class ExpenseSorter
{
    public List<Expense__c> mostRecentExpenses { get; private set; }
    final Map<Id, Set<String>> observedValues;
    public ExpenseSorter()
        mostRecentExpenses = new List<Expense__c>();
        observedValues = new Map<Id, Set<String>>();

        for (Expense__c record : [
            SELECT Parent_Id__c, Date__c, Type__c, Amount__c
            FROM Expense__c ORDER BY Date__c DESC Parent_Id__c
        ]){
            if (!observedValues.containsKey(record.Parent_Id__c))
                observedValues.put(record.Parent_Id__c, new Set<String>{ record.Type__c });
            else if (!observedValues.get(record.Parent_Id__c).contains(record.Type__c))
                observedValues.get(record.Parent_Id__c).add(record.Type__c);
            else
                continue;
            mostRecentExpenses.add(record);
        }
    }
}

Or you could remove the query and accept the List<Expense__c> as a constructor parameter for added flexibility. The above is just an idea to give you the general outline of how you can solve your problem.

| improve this answer | |
  • Unbelievable. This is like killing an ant with a bazooka. Miss SQL. But thanks for the tip! – Lightning Evangelist Oct 21 '16 at 16:38
  • @Kal You could reduce the number of records queried by adding more onto it, but I'm not sure that would be to your taste either. It's a platform with many limitations, but if you spend long enough on it, you learn to love it. – Adrian Larson Oct 21 '16 at 17:52

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.