3

I want to pull all the parent records which have only 1 child record either using a query or report or any other way. For example all the accounts with only 1 contact record. I don't want to create roll-up summary field.

  • You can use SOQL query with Group by and Having clause – Santanu Boral Jul 23 '16 at 16:26
7

Query on Contact using Aggregate function Count()

SELECT AccountId, count(Id)
FROM Contact
GROUP BY AccountId
HAVING count(Id) = 1
  • Thanks for the response. I want to filter the results with more conditions like country code and is Primary(roll up summary field) but i am not able to do it due to below error:"field 'Is_Primary__c' can not be grouped in a query call". My query "SELECT AccountId,Account.Country_vod__r.Name,Count(id) FROM Contact group by Accountid,Account.Country_vod__r.Name,Account.Is_Primary__c having count(id)=1 and Account.Country_vod__r.Name='CA' and Account.Is_Primary__c=0 – sam Jul 25 '16 at 11:50
1

Hi @sam you can do it in reports also with out creating a Roll-up-summary field.For this you need to create a report with accounts & contacts.

1.For this report you need to use Cross filter like Acccounts with contacts

2.You need to change to summary report with grouping as Account name.

3.You need to create a formula based on RowCount.

For reference:

enter image description here

You can do it in either query also as ishwar answer.using count with aggregate list.

I hope it solves your problem

  • Thanks for your suggestions. But this report will pull accounts with more than 1 contact so it will not work in my case. Please let me know if there are any other ways to achieve it. Thanks. – sam Jul 25 '16 at 11:43
  • Ya but you cant filter the formula but if you can display in ascending order based on formula field. @sam – Pavan tej Jul 25 '16 at 12:28
  • Ya but i cannot do it because i have more that 76K records. – sam Jul 25 '16 at 13:40

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.