Daily Learnings: Tue, Jan 02, 2024
The differences between friends cannot but reinforce their friendship. — Mao Zedong
Notes on Querying Household Info in FSC
I had a request come up today for a simple Excel export of all Person Accounts with their related Household information. Unfortunately, given that the data model of Person Accounts <> Household Account records in FSC needs to support a Person Account potentially being associated with multiple Households, it’s not as straightforward as a simple field lookup. I had to go about it through some more complex SOQL.
General Notes
- The
AccountContactRelationobject is what stores the primary information about Person Accounts and their related Households- This is critical to understand, and where I had a key learning today. I assumed that it was the
FinServ__AccountAccountRelation__cobject, but I was mistaken
- This is critical to understand, and where I had a key learning today. I assumed that it was the
SOQL Used
Retrieve all AccountContactRelation records that include Contacts related to Households, and whose Contacts are Person Accounts.
SELECT Id, Account.Id, Account.Name, Contact.Id, Contact.Name, Contact.Account.Id
FROM AccountContactRelation
WHERE Account.RecordType.Name = 'Household'
AND Contact.IsPersonAccount = TRUE
This gave me all Households, with some needed information there, and all Contact records associated with them. I was also able to pull in the Person Account record’s ID through traversing through Contact.Account.Id.