This might be pretty basic but I can't get anything on top of my head right now.

We need to query all the accounts (or AccountContactRelation which is fine as well), which has only one inactive AccountContactRelation and doesn't have any active relation. What is the effective approach of doing it? Code and some additional field is also acceptable in this case.

  • Does the retrieval need to be possible with just SOQL? It is not clear if code can be part of the retrieval, or just part of the population of any additional fields. – Adrian Larson Aug 16 '19 at 1:04
  • @AdrianLarson It is okay to use anything - code, new formula fields, data loader or even reports. We just need to fix the data. I stated in the question code and some additional fields is acceptable in my question though. – Lance Shi Aug 16 '19 at 1:11

The following should work.

Set<Id> accountIdsWithOneInactive = new Map<Id, SObject>([
    SELECT AccountId Id FROM AccountContactRelation
    WHERE IsActive = false
    GROUP BY AccountId HAVING count(Id) = 1
List<Account> records = [
    SELECT Id FROM Account WHERE Id IN :accountIdsWithOneInactive AND Id NOT IN (
        SELECT AccountId FROM AccountContactRelation WHERE IsActive = true

I don't think you can get it down to one step unless you do an Inactive_Relation_Count__c rollup, which you could implement using various packaged rollup helpers already on the market (I typically favor DLRS).

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