I have requirement like we have existing opportunities are there.

For some accounts we have multiple opportunities are there.

And we had a requirement like some accounts have first opportunity had "Closed Lost" and reengage the same account and "Closed" the deal.So, they are created second opportunity as "Closed won".

And i am trying to update a field in account called ReWon in account level when any account meets above criteria

So we need filter accounts like first opportunity as "Closed Lost" and second opportunity as "Closed Won" based on created date.

But,for existing accounts i am unable to differentiate with in soql query.

My soql query would be

[Select id,name,AccountId,CloseDate,Type,StageName,account.Opportunity_Count__c from opportunity where Type='New Business' And Stagename='Closed Lost' ORDER BY CreatedDate];

Please let me know

  • when you said existing customers are you going to create a new field IsSecondOppClosed? and you are trying to update existing customers with field you'll filter on? – Shanker Paudel Mar 14 '17 at 8:01
  • Yes you are right i had created a field Rewon in account level and updating yes if it is reenaged and closed.@ShankerPaudel – Pavan tej Mar 14 '17 at 8:20
  • You can't get that level of detail with a single SOQL, you would need to write logic in apex to filter out the records. – Raul Mar 14 '17 at 8:41
  • Ya i tried but i'm new to apex but i'm not able to differentiate second one in list of opportunities but thanks i keep working on this. – Pavan tej Mar 14 '17 at 8:51

You could actually do that with 3 fields on the Account:

  1. "Closed Date Lost": Roll-up summary on Opportunities, Roll-up Type "Min", Field to aggregate "Close Date", only records that meet the criterion "Stage equals Closed Lost". Keep this field invisible.

  2. Very similar, but "Closed Date Won". Keep this field invisible as well.

  3. "Reengaged Won": Formula Field, Type "Text", Advanced Formula: IF(OR(ISNULL(Closed_Date_Lost__c), ISNULL(Closed_Date_Won__c)),"No", If(Closed_Date_Lost__c < Closed_Date_Won__c,"Yes","No"))

So, in Closed Date Lost you record the earliest lost Opportunity, in Closed Date Won the same for a won opportunity. In the formula there is first a check whether you actually have a won and a lost opportunity and then the comparison whether the won opportunity came after the lost one.

No Apex needed, Rollup-Summaries are great for some problems.

| 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.