Dynamic SOQL + Person Accounts = 😡

A story in overcoming challenges working with Person Accounts and dynamic SOQL

Update: A tweet from Charles T led me to update this article. See the end of this story for an explanation on how I now overcome the challenge of tackling Person Accounts for the timeline Lightning Web Component. Thanks Charles!

The Requirement and Original Solution

The original requirement seemed simple — show an interactive timeline for Accounts and Contacts and allow an administrator to decide which child records to plot by specifying their object name and relationship.

SELECT Id, (SELECT <FIELDS> FROM <RELATIONSHIP-1>),(SELECT <FIELDS> FROM <RELATIONSHIP-2>),(SELECT <FIELDS> FROM <RELATIONSHIP-3>) FROM <PARENT> WHERE Id :=recordId
SELECT Id, (SELECT Subject, ActivityDate FROM Tasks),(SELECT Subject, ActivityDate FROM Events),(SELECT Subject, CreatedDate FROM Cases),(SELECT Subject, CreatedDate FROM WorkOrders) FROM Contact  WHERE Id :=recordId

The Problem

I had successfully used the approach above to plot child records of Account, Contact, Lead, Opportunity and Case. The problem arose when I tried to plot child records to Person Accounts.

The Digression

Before we talk about what caused the issue it’s important to get a baseline understanding of Person Accounts first.

Custom fields created on the Contact object appear on the Account object with a __pc suffix standard field keep the same name of are prefixed with ‘Person’

Standard Relationships

Tasks can be related to an Account — via WhatId and/or the Contact — via WhoId. Again, for Person Accounts, Salesforce tries to simplify things by creating a version of the Contact relationship on the Account object. In the example below the standard relationship between a Task and a Contact — called Tasks — becomes PersonTasks on the Account object.

Standard contact relationships are available on the Account object using the ‘Person’ prefix

Custom Relationships

Custom object relationships are similar. The naming convention varies. Consider the example below for a custom object Contact_Addresses__c that has a master detail relationship to Contact. Just like standard objects a copy is available directly on the Account

Custom contact relationships are available on the Account object using the ‘__pr’ suffix
SELECT Id, (SELECT Subject, ActivityDate FROM Tasks), (SELECT Subject, ActivityDate FROM PersonTasks), (SELECT Subject, CreatedDate FROM Cases),(SELECT Subject, CreatedDate FROM PersonCases),(SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id :=accountId

The Problem Restated

The above digression through Person Accounts seems to fully support one database query for related child records. I was excited until I tried it and failed.

//Execute a query on a Person Account using standard relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Subject, ActivityDate FROM PersonTasks) FROM Account WHERE Id =:accountId
List<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact task related records are returned
for (Sobject each : listOfRecords) {
for (SObject eachCh : (List<SObject>)each.getSObjects('PersonTasks')) { //This errors
System.Debug('Woo hoo it worked!');
}
}
System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Tasks
//Execute a query on a Person Account using custom relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id =:accountId
List<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact Address relationship records are returned
for (Sobject each : listOfRecords) {
for (SObject eachCh : (List<SObject>)each.getSObjects('Contact_Addresses__pr')) { //This errors
System.Debug('Woo hoo it worked!');
}
}
System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Contact_Addresses__r

The New Solution

Largely thanks to this tweet from Charles T I have updated my solution.

//Execute a query on a Person Account using standard relationship from the Account object
String queryPersonAccount = SELECT Id, (SELECT Id, Name FROM Contact_Addresses__pr) FROM Account WHERE Id =:accountId
List<SObject> listOfRecords = Database.query( queryPersonAccount );
//This works. The Contact Address relationship records are returned
for (Sobject each : listOfRecords) { //This is key. I no longer rely on getting each SObject
Map<String, Object> fieldValues = each.getPopulatedFieldsAsMap();
for (String fieldName : fieldValues.keySet()) { if (fieldValues.get(fieldName) instanceof List<sObject>) {
List<sObject> myCollection = (List<sObject>) fieldValues.get(fieldName);
for (Sobject childObject : myCollection) { //Now simply get the field you need
childObject.get(<field>);
}
}
}
}

Summary

Designing for Person Account usage can throw you a few curve balls. I’ve learnt to test more thoroughly.

Cloud architect || Problem solver || Interested in solving unique challenges using different cloud service providers || All opinions are mine.

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store