3

I'm wondering whether its possible to use a trigger to find the most frequently recurring value in a related list, and then populate a field on the parent object with that value. And if so, what trigger structure might best achieve this?

for example, the list of values in the custom field of related list records might be:

list relatedList = dog, cat, bird, bird, bird, cat, chicken, dog, horse, bird, dog, bird

I want the trigger to iterate through this list, and find the most frequently recurring value. So.... 'dog' occurs 3 times 'cat' occurs 2 times 'bird' occurs 5 times 'chicken' occurs 1 times 'horse' occurs 1 times

So.....'bird' occurs most frequently, therefore I want to populate the parent object field 'frequent_item__c' with 'bird'

I'm thinking the trigger structure might be something like...

//iterate through the list
//for each item in the list, count the number of identical items
//find the item with the highest frequency count
//populate the parent object field with the item

Am I on the right track?? I'm fairly new to Apex development with a few triggers and test classes under my belt, but feel that this one is a bit out of reach. Any assistance appreciated.

Thanks,

1
  • 2
    You're on the right track however you can probably accomplish this without much code, leveraging an aggregate SOQL query which uses COUNT() and GROUP BY to let the database do the work for you. – Mark Pond Nov 28 '14 at 4:10
4

This could get a bit sketch if the numbers of records are large, but if they're managable within limits then some aggregate SOQL could be of use here.

For example to get all the counts for the various related records (I assume you're grouping them by some kind of field, like Type__c:

select count(Id) from Child__c where Parent__c = : someId group by <<field to count>>

This gets more tricky when dealing with multiple parents, but you can group by those too:

select count(Id), Parent__c from Child__c where Parent__c in : <<setOfParents>> group by Parent__c, <<field to count>>

Then it'll just be a case of looping through your results and finding the highest number of children for each parent, something like:

Map<String, Integer> parentToHighest = new Map<String, Integer>();
Map<String, String> parentToValue = new Map<String, String>();

for(AggregateResult ar : [ select count(Id) num, Type__c, Parent__c from Child__c where Parent__c in : trigger.new.KeySet() group by Parent__c, Type__c])
{
    Integer num = (Integer)ar.get('num');
    String name = (String)ar.get('Parent__c');
    String type = (String)ar.get('Type__c');

    if(parentToHighest.get(name) == null || parentToHighest.get(name) < num)
    {
        parentToHighest.put(name, num);
        parentToValue.put(name, type);
    }
}

Now, if you need it you'll have a map of parent id -> count of highest child, and also a map of parent -> type of child which you can use to populate the appropriate parent field.

You'll need to work out what you want to do when two children types have the same count!

1
  • Thanks guys I'm going to take a look at both of those methods this weekend and see what I can come up with. Thanks for your help. – Greg J Nov 29 '14 at 5:44

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.