4

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.

Improve this question
1
  • You can use SOQL query with Group by and Having clause
    – Santanu Boral
    Jul 23, 2016 at 16:26

2 Answers 2

Reset to default
7

Query on Contact using Aggregate function Count()

SELECT AccountId, count(Id)
FROM Contact
GROUP BY AccountId
HAVING count(Id) = 1
Improve this answer
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, 2016 at 11:50
2

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

Improve this answer
3
  • 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, 2016 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, 2016 at 12:28
  • Ya but i cannot do it because i have more that 76K records.
    – sam
    Jul 25, 2016 at 13:40

You must log in to answer this question.

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