0

I am given a list which has 1000 accounts. I am asked to find opportunities between stage0-5 that are present on these 1000 accounts. I should differentiate these 1000 accounts into 2 ways.

1 If Account has opportunity which is in stage 0-5, it should be seperated from the account that has no opportunity or opportunity that is in stage 6 or above. I need to differentiate the 1000 accounts into 2 lists so that we know which account has opportunity and which doesn't.

Your reply would be really of great help. Thanks

  • 3
    How are you trying to find them? Reports? SOQL? We need more information about the tools you have available and what you're tried so far. – ca_peterson May 28 '15 at 5:32
  • I am trying to use vlookup in excell. If you know any better ways let me know. – George May 28 '15 at 6:03
0

Set AccIDs=new Set()// fill this set with account ids in your list List=[Select id from Account where Id IN (select AccountId from opportunity where stage='0' || stage='1'....... && AccountId IN AccIDs)];//this will result into parent accounts of opp having stages 0-5.

Now remove the obtained account IDs from the list of Ids you have to get the the ones having no opportunity or opportunity that is in stage 6 or above.

This will help you,just you need to modify or construct your query.throw kudos if you like my answer !

0

This may work:

  1. Loop over all the Opportunities where Stage is between 0-5 and capture their AccountId.
  2. Use Aggregate SOQl to count the number of Opportunities each Account is having. Something like this:

    [SELECT AccountId AcctId, Count(id) OppCount FROM Opportunity WHERE AccountId in: acctIds GROUP BY AccountId]

  3. Now, you can loop over the result to differentiate account where count = 0 and count != 0.

Please try and lemme know if it works.

  • I was trying to use VLookup in Excell, do you think that would work.? – George May 28 '15 at 6:02
  • That may work but i would suggest you to use apex. – the_phantom May 28 '15 at 15:41
0

I believe you could use roll up summary fields to the accounts, and than make a report on the accounts filtered on the roll up summary results ?

Rather than receiving 1000 accounts, I'd ask for the selection criteria of these accounts, so that you can include these in your reports too.

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.