Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I have a child object to my contact called Rating. On this object we have ratings for products and overall ratings. I need a field on the contact called NPS_Rating updated with the newest overall rating from the Rating object.

I have tried many times to write this but with the 2 filters ("overall" rating and newest date) I am finding it very difficult, any help would be appreciated.

share|improve this question

5 Answers 5

Unfortunately, if roll up summary fields aren't an option you will need to either create a trigger or create a batch class to run on a schedule as other users have advised. I do prefer creating triggers just because you get instant updates (and it's always fun to get around the governor limits). I can give you a few tips below:

  1. Make sure you handle all scenarios - 'insert', 'update', 'delete', 'undelete'

  2. Start with creating the 'after insert' and 'after undelete' logic first (Note: 'after undelete' logic will be the same as 'after insert'). This will be the easiest bit to write as essentially you just need to check for a creation of the 'overall' rating.

  3. You can do this with a single query (plus child query) '[Select Id (Select RatingType_c, RatingValue_c From Ratings_r Where RatingType_c = 'overall' Order by RatingDate__c Desc limit 1) From Contact Where Id in :contactIds'. The variable 'contactIds' will be all contacts with a new/updated/deleted/undeleted rating record

  4. Make sure that for 'after update' you check for a change in fields, especially the Contact lookup as you will need to run the contact query above for the old contact Id and new contact Id (so that both contacts are updated).

Essentially, I haven't written any code in this post as someone has already given you the basis. Hopefully, with my tips above you have somewhere to start. Happy to provide more pointers if you get stuck :)

share|improve this answer

It's quite possible that the easiest way to achieve this would be to do it asynchronously using a scheduled batch job, it may be possible in a trigger but a batch could be less smart about what it's doing and as such would be easier to write, read and maintain.

Your batch could run on all overall rating records created in the last hour say, filter out multiples for each contact (keeping the latest for each of course!), and then update the related contacts accordingly if the rating for each is more recent than the current one.

The logic would be nice an simple compared to a trigger and you'd be far less likely to have to worry about governor limits. Note that you'd need a lookup field on the contact to the current rating object to make the date checking way easier.

I'd also suggest adding an optional switch member variable that you could use when running code manually to find all ratings created after date x, so that you can initialise the system to begin with.

Should you need to ensure that records are up to date immediately after being modified by a user (and you don't care about bulk data loads) you could write a trigger that uses the same logic (rip it out to another class to use in the batch & trigger!) if trigger.new.size() == 1. This sounds hacky but it's a nice way to distinguish between a bulk load/update vs. a user using the system as expected.

share|improve this answer

This can be performed in a trigger and does not need to be performed in a batch.

Your NPS_Rating field on Contact (or another field of your choosing) should be a lookup to the Rating object, this will give you easy access to the record which you're comparing against. The trigger defined on the Rating object will compare the values of each trigger record to the record in the lookup on the related Contact.

If the current trigger record's 'Rating Type' is 'overall' and the date is more recent than the related Rating record noted in the NPS Rating field, update the lookup on the Contact to the ID of the trigger record (since it is of the correct type and more recent).

For display purposes, your Contact record could then utilize formula fields to display relevant information from this related record.

share|improve this answer

Can you do this with a roll-up summary field on Contacts?

share|improve this answer
    
I cannot do it with roll-up fields, I soooo wish I could! –  Merry Jan 7 '13 at 6:17

Basically you can use Trigger to do this. Can you post your code, or give more specific conditions, for example, what is the relation of your two filters,....

You can try the code below,

trigger UpdateContact on Rating__c (after insert, after update) {

Set<Id> RatingId = new Set<Id>(); 

for (Rating__c rating : Trigger.new) {
    if(rating.RatingType__c != null && rating.RatingType__c.equals('overall')) {
        RatingId.add(rating.Id);
    }
} 

Set<Id> ContactId = new Set<Id>();
for(Rating__c rating1:[Select ContactChild__c from Rating__c Where Id IN:RatingId ]) {
    ContactId.add(rating1.ContactChild__c);
}


List<Contact> contactforupdate = new List<Contact>();

for(Contact c:[Select Id, NPS_Rating__c , 
 (Select RatingType__c, RatingValue__c From Ratings__r Where RatingType__c = 'overall' Order by RatingDate__c Desc limit 1 ) 
 from Contact where Id IN:ContactId ]) {
    Rating__c Rating = c.Ratings__r;        
    String RatingValue = Rating.RatingValue__c;
    System.debug(' ------------ ' + RatingValue);
    //if(c.NPS_Rating__c != null && c.NPS_Rating__c.equals(RatingValue)) {
        c.NPS_Rating__c = RatingValue;
        contactforupdate.add( c );
    //}
}
update contactforupdate;    

}

share|improve this answer
    
ON the ratings object I have three fields that someone fills out when adding a rating to a contact. One of them is the Rating Type, which is a picklist, one is the date, and the other is a Rating, which is also a picklist. I need the trigger to filter out ratings with a "type" of 'overall' and I need it to only return the most recent rating. (For instance, there could be 5 'overall' ratings that come from the last 3 years, I need the most recent). The field on the contact just needs to have the 'rating' number. –  Merry Jan 7 '13 at 6:17
    
Thanks! I have updated the code with my filed names but I get this error when I save. "Error: Compile Error: Didn't understand relationship 'Rating__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 19 column 15" –  Merry Jan 7 '13 at 17:20
    
When you query from the Parent-to-Child, you should use Child Relationship Name, but not the API name of the Child object. Open the detail page of your custom object Rating, click the field of relation to Contact object. Find the value of Child Relationship Name, in my case is 'Ratings'. Then plus __r to the name, finally, in the SOQL query, it is '...... From Ratings__r'. –  Jia Hu Jan 8 '13 at 1:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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