3

I have requirement where i need to process account which has no child but has parent and accounts which has no parent and child separately.

I tried 

select id, name,(select id, name from Accounts) from Account

But it giving me error.

Any workaround/suggestions would greatly appreciated.

Please Help thanks!

Improve this question
3
  • Related to the answer provided by Birthus, if the number of Accounts is limited, this could be done using several queries in Apex code without too much trouble. But if it is every Account in a 500,000 Account org then its triggers etc. Can you clarify the scale you need this to work at?
    – Keith C
    Feb 19, 2014 at 20:58
  • @KeithC Thanks for comment, my use case is to roll up opportunity count to the topmost account.
    – SFIntegrator
    Feb 20, 2014 at 5:59
  • @sandy - If you are looking to simply rollup a field from the child account to the parent account try out the ideas in this link: anthonyvictorio.com/salesforce/roll-up-summary-utility and add a trigger to the account when the pop count field changes on the child. Although this is title a lookup utility, it really only needs the ID so it will work when using AccountID or ParentID
    – Eric
    Jun 28, 2014 at 19:35

3 Answers 3

Reset to default
1
  1. You can create a custom field on an account called number_of_children.

  2. Write a trigger to

    • On Any Update to the account
    • trigger.new[0] = [Select Count() From Account where ParentID = :trigger.new[0].id] //You will need to bulkily this
    • also check to ensure if a parent is remove from a child (After Update)
      • if(trigger.new[0].ParentID == NULL && trigger.old[0].ParentID != null) Account a = [Select ID From Account Where ID = :trigger.old[0].ParentID]; Update a;

All of this will have to be bulkified and then will need to add a delete and after delete

Once this is done, then you can SOQL query on the number_of_children field any time you want

I would also suggest you limit this trigger to only run when a parent ID field is updated to reduce unnecessary execution of the sql statements.

Improve this answer
0

I don't think it is possible just by SOQL. You will have to use apex code to achieve your goal

Improve this answer
-1

To find accounts with no child accounts using soql, you can create a roll-up summary field on the account that is a count of the number of child records. You can then apply a filter to your query to be:

select id, name,(select id, name from Contact) from Account where rollup_summary_field0

Also, you can create a field countContacts__c to update with child records and select only that account that has ZERO childs...

 select id, name,(select id, name from Contact) from Account where  id in (select account__c from Contacts where countContacts__c = '')

Thanks.

Improve this answer
1
  • 2
    Rollup requires master detail which is: a) impossible to achieve (Account can be a master but never a child) b) you can't make a M-D on self relationship (how do you imagine inserting the 1s account in given org if he has a mandatory M-D field to fill in)
    – eyescream
    Jun 28, 2014 at 13:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .