6

On Account object I have Custom field: Support_end_date

I have a custom object called: license. On license I have a custom field called: license_end_date. Each license has a parent object: Account (in a lookup relation NOT Master-detail so rollup summary is not an option)

it can happen that an account has several licenses. And here is where my problem starts: - Account can have support_end_date of X - every license has its' own end_date

I need to Update the database that Account will have the latest end_date from it's licenses (e.g. account suport_end_date will be the same has the license with the latest date)

  • It seems that rollup summary fields are only available with Master-Detail relationships. An idea request has been submitted on this, but no update yet.

  • apparently I need that same functionality.

Thanks

7

Two possibilities:

  1. Change the Account to be a Master-Detail field on the License and then create the Account's support_end_date field as a roll-up summary field that is set to the maximum license_end_date of the child Licenses. It is important that you change the field to Master Detail because the roll-up summary won't be available for what you are trying to do on a Lookup.
  2. Create a trigger on the License Object that simply updates the Account's support_end_date when the License record's license_end_date is greater than the Account's support_end_date. This would be a bit trickier too, when you start to think about having to handle deletes as well.

Clearly #1 is easier and if it doesn't make sense for a License to exist on its own without a parent Account then you probably want to make it a Master-Detail anyway.

If you cannot use #1 and need to update the current data you can either do it manually or through code. Perhaps you could create a report to assist or data export to CSVs and manipulate with Excel to get a data load of Account objects.

For code, you could write something like the following or if you have a lot of records look into using Batch Apex with your solution.

for (AggregateResult[] licenses: [    
    Select Max(License_End_Date__c)  maxDate, Account__c acctId
    From License__c
    Group by Account__c]) {

    List<Account> accts = new List<Account>();
    for (AggregateResult ar : licesnses) {
          Id acctId = (Id) ar.get('acctId');
          if (acctId != null) {
              Date maxDate = (Date) ar.get('maxDate');
              Account a = new Account(
                  Id = acctId,
                  Support_End_Date__c = maxDate
              ); 
             accts.add(a);
        }
    }

    System.debug('about to update accounts with max dates, Accounts=' + accts);
    update accts;
    System.debug('successfully updated accounts with max dates, Accounts=' + accts);
}
  • Thanks, #1 can not happen, as licenses can exist on their own. #2 is fine, but I need a solution for the current data as well. – Saariko Aug 19 '12 at 6:39
1

You can do this with a roll-up summary field on account that grabs the maximum support_end_date of the licenses.

  • The original question says that is not available due to the relationship not being Master-Detail; please provide instructions for a regular lookup if you know of a workaround. – Mike Chale Aug 19 '12 at 12:36
  • I just didn't read the question carefully enough. Peter Knolle's answer is the correct one. – Greg Grinberg Aug 19 '12 at 13:48

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.