Table of Contents


Working with Related Records

Summary

This section covers how to work with relationships between entities, including querying related records, eager loading, creating records with relationships, managing parent-child relationships, and working with lookup and master-detail relationships.

Understanding Relationships

Magentrix supports several types of relationships between entities:

Relationship Types:

Lookup Relationships:

  • References another record by ID
  • Parent record can exist independently
  • Deleting parent does not delete children
  • Example: Contact → Account (Contact.AccountId)

Master-Detail Relationships:

  • Child record depends on parent
  • Deleting parent typically deletes children
  • Child cannot exist without parent
  • Inherits sharing from parent

Self-Referencing Relationships:

  • Record references another record of same entity
  • Example: Account → ParentAccount (Account.ParentId)

Many-to-Many Relationships:

  • Implemented through junction objects
  • Allows multiple records on both sides

Accessing Related Records

Multiple Related Records:

// Load multiple relationships
var options = new QueryOptions
{
    Includes = "Account,Owner,ReportsTo",
    EagerLoadMode = EagerLoadMode.IdAndName
};

var contacts = Database.Query<Contact>(
    f => f.IsActive == true,
    options
).ToList();

foreach (var contact in contacts)
{
    SystemInfo.Debug($"Contact: {contact.Name}");
    
    if (contact.Account != null)
    {
        SystemInfo.Debug($"  Account: {contact.Account.Name}");
    }
    
    if (contact.Owner != null)
    {
        SystemInfo.Debug($"  Owner: {contact.Owner.Name}");
    }
    
    if (contact.ReportsTo != null)
    {
        SystemInfo.Debug($"  Reports To: {contact.ReportsTo.Name}");
    }
}

Querying by Related Record Fields

Filter by Related Field:

// Find contacts at partner accounts
var contacts = Database.Query<Contact>()
    .Where(f => f.Account.Type == "Partner")
    .ToList();

// Find contacts at high-revenue accounts
var contacts = Database.Query<Contact>()
    .Where(f => f.Account.AnnualRevenue > 1000000)
    .ToList();

// Multiple related conditions
var contacts = Database.Query<Contact>()
    .Where(f => 
        f.Account.Type == "Partner" &&
        f.Account.IsActive == true &&
        f.Account.Industry == "Technology"
    )
    .ToList();

Complex Related Queries:

// Contacts at accounts owned by specific user
var contacts = Database.Query<Contact>()
    .Where(f => f.Account.OwnerId == userId)
    .ToList();

// Contacts with active accounts in specific territory
var contacts = Database.Query<Contact>()
    .Where(f => 
        f.Account.IsActive == true &&
        f.Account.Territory__c == "West" &&
        f.Account.Status__c != "Closed"
    )
    .ToList();

Self-Referencing Relationships:

// Find accounts with specific parent account
var childAccounts = Database.Query<Account>()
    .Where(f => f.ParentId == parentAccountId)
    .ToList();

// Find accounts with parent in specific industry
var accounts = Database.Query<Account>()
    .Where(f => f.Parent.Industry == "Technology")
    .ToList();

Querying Child Records

Query Children of Parent:

// Get all contacts for an account
var contacts = Database.Query<Contact>()
    .Where(f => f.AccountId == accountId)
    .ToList();

SystemInfo.Debug($"Account has {contacts.Count} contacts");

// Get all opportunities for an account
var opportunities = Database.Query<Opportunity>()
    .Where(f => f.AccountId == accountId)
    .ToList();

Query Children with Conditions:

// Get active contacts for an account
var activeContacts = Database.Query<Contact>()
    .Where(f => f.AccountId == accountId && f.IsActive == true)
    .ToList();

// Get open opportunities for an account
var openOpportunities = Database.Query<Opportunity>()
    .Where(f => 
        f.AccountId == accountId && 
        f.IsClosed == false &&
        f.Stage != "Lost"
    )
    .ToList();

// Get high-value opportunities
var highValueOpps = Database.Query<Opportunity>()
    .Where(f => 
        f.AccountId == accountId &&
        f.Amount >= 50000 &&
        f.CloseDate >= DateTime.UtcNow
    )
    .ToList();

Count Child Records:

// Count contacts for account
long contactCount = Database.Count<Contact>(f => f.AccountId == accountId);

// Count open opportunities
long openOppCount = Database.Count<Opportunity>(
    f => f.AccountId == accountId && f.IsClosed == false
);

// Check if account has any contacts
bool hasContacts = Database.Count<Contact>(f => f.AccountId == accountId) > 0;

Query Multiple Parents' Children:

// Get contacts for multiple accounts
var accountIds = new List<string> { "001...", "001...", "001..." };

var contacts = Database.Query<Contact>()
    .Where(f => accountIds.Contains(f.AccountId))
    .ToList();

// Group by account
var contactsByAccount = contacts.GroupBy(c => c.AccountId).ToList();

foreach (var group in contactsByAccount)
{
    SystemInfo.Debug($"Account {group.Key}: {group.Count()} contacts");
}

Creating Records with Relationships

Create Child Record with Parent Reference:

// Create contact linked to account
var contact = new Contact
{
    FirstName = "John",
    LastName = "Smith",
    Email = "john.smith@example.com",
    AccountId = accountId  // Link to parent account
};

Database.Create(contact);

Create Parent and Children Together:

// Create account first
var account = new Account
{
    Name = "Partner Company",
    Type = "Partner",
    Industry = "Technology"
};

Database.Create(account);

// Create contacts linked to new account
var contacts = new List<Contact>
{
    new Contact 
    { 
        FirstName = "John", 
        LastName = "Smith",
        Email = "john@example.com",
        AccountId = accountResult.Id 
    },
    new Contact 
    { 
        FirstName = "Jane", 
        LastName = "Doe",
        Email = "jane@example.com",
        AccountId = accountResult.Id 
    }
};
    
var contactResults = Database.Create(contacts);

Create Multiple Levels of Related Records:

public void CreateAccountHierarchy()
{
    // Create parent account
    var parentAccount = new Account
    {
        Name = "Parent Corporation",
        Type = "Partner"
    };
    
    var parentResult = Database.Create(parentAccount, new DatabaseOptions { IsApiMode = true });
    
    if (parentResult.HasError)
    {
        SystemInfo.Debug("Failed to create parent account");
        return;
    }
    
    // Create child accounts
    var childAccounts = new List<Account>
    {
        new Account 
        { 
            Name = "Subsidiary 1", 
            Type = "Partner",
            ParentId = parentResult.Id 
        },
        new Account 
        { 
            Name = "Subsidiary 2", 
            Type = "Partner",
            ParentId = parentResult.Id 
        }
    };
    
    var childResults = Database.Create(childAccounts, new DatabaseOptions { IsApiMode = true });
    
    // Create contacts for each subsidiary
    foreach (var childResult in childResults)
    {
        if (!childResult.HasError)
        {
            var contact = new Contact
            {
                FirstName = "Contact",
                LastName = "Person",
                Email = $"contact@{childResult.Id}.com",
                AccountId = childResult.Id
            };
            
            Database.Create(contact, new DatabaseOptions { IsApiMode = true });
        }
    }
    
    SystemInfo.Debug("Created account hierarchy with contacts");
}

Updating Relationships

Change Parent Reference:

// Move contact to different account
var contact = Database.Retrieve<Contact>(contactId);
contact.AccountId = newAccountId;

var result = Database.Edit(contact, new DatabaseOptions { IsApiMode = true });

if (!result.HasError)
{
    SystemInfo.Debug("Contact moved to new account");
}

Remove Parent Reference:

// Remove account from contact
var contact = Database.Retrieve<Contact>(contactId);
contact.AccountId = null;

var result = Database.Edit(contact, new DatabaseOptions { IsApiMode = true });

Bulk Update Relationships:

// Move all contacts from one account to another
var contacts = Database.Query<Contact>()
    .Where(f => f.AccountId == oldAccountId)
    .ToList();

foreach (var contact in contacts)
{
    contact.AccountId = newAccountId;
}

var results = Database.Edit(contacts, new DatabaseOptions { IsApiMode = true });

int updated = results.Count(r => !r.HasError);
SystemInfo.Debug($"Moved {updated} contacts to new account");

Update Parent and Children:

// Update account and all related contacts
var account = Database.Retrieve<Account>(accountId);
account.Industry = "Technology";

var accountResult = Database.Edit(account, new DatabaseOptions { IsApiMode = true });

if (!accountResult.HasError)
{
    // Update all contacts with new industry-specific field
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == accountId)
        .ToList();
    
    foreach (var contact in contacts)
    {
        contact.Industry__c = "Technology";
    }
    
    var contactResults = Database.Edit(contacts, new DatabaseOptions { IsApiMode = true });
    
    SystemInfo.Debug($"Updated account and {contactResults.Count(r => !r.HasError)} contacts");
}

Deleting Related Records

Delete Child Records with Parent:

// Delete all contacts before deleting account
var contacts = Database.Query<Contact>()
    .Where(f => f.AccountId == accountId)
    .ToList();

if (contacts.Count > 0)
{
    var contactResults = Database.Delete(contacts, new DatabaseOptions { IsApiMode = true });
    SystemInfo.Debug($"Deleted {contactResults.Count(r => !r.HasError)} contacts");
}

// Delete account
var accountResult = Database.Delete(accountId, new DatabaseOptions { IsApiMode = true });

if (!accountResult.HasError)
{
    SystemInfo.Debug("Account deleted");
}

Cascade Delete Pattern:

public void DeleteAccountWithRelatedRecords(string accountId)
{
    // Delete in order: children first, then parent
    
    // 1. Delete tasks
    var tasks = Database.Query<Task>()
        .Where(f => f.WhatId == accountId)
        .ToList();
    
    if (tasks.Count > 0)
    {
        Database.Delete(tasks, new DatabaseOptions { IsApiMode = true });
        SystemInfo.Debug($"Deleted {tasks.Count} tasks");
    }
    
    // 2. Delete opportunities
    var opportunities = Database.Query<Opportunity>()
        .Where(f => f.AccountId == accountId)
        .ToList();
    
    if (opportunities.Count > 0)
    {
        Database.Delete(opportunities, new DatabaseOptions { IsApiMode = true });
        SystemInfo.Debug($"Deleted {opportunities.Count} opportunities");
    }
    
    // 3. Delete contacts
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == accountId)
        .ToList();
    
    if (contacts.Count > 0)
    {
        Database.Delete(contacts, new DatabaseOptions { IsApiMode = true });
        SystemInfo.Debug($"Deleted {contacts.Count} contacts");
    }
    
    // 4. Finally delete account
    var result = Database.Delete(accountId, new DatabaseOptions { IsApiMode = true });
    
    if (!result.HasError)
    {
        SystemInfo.Debug("Account and all related records deleted");
    }
}

Check Dependencies Before Delete:

public bool CanDeleteAccount(string accountId)
{
    // Check for related contacts
    long contactCount = Database.Count<Contact>(f => f.AccountId == accountId);
    if (contactCount > 0)
    {
        SystemInfo.Debug($"Cannot delete: {contactCount} contacts exist");
        return false;
    }
    
    // Check for open opportunities
    long openOppCount = Database.Count<Opportunity>(
        f => f.AccountId == accountId && f.IsClosed == false
    );
    
    if (openOppCount > 0)
    {
        SystemInfo.Debug($"Cannot delete: {openOppCount} open opportunities exist");
        return false;
    }
    
    // Check for child accounts
    long childCount = Database.Count<Account>(f => f.ParentId == accountId);
    if (childCount > 0)
    {
        SystemInfo.Debug($"Cannot delete: {childCount} child accounts exist");
        return false;
    }
    
    return true;
}

Working with Hierarchies

Query Entire Hierarchy:

// Get all accounts in hierarchy
public List<Account> GetAccountHierarchy(string rootAccountId)
{
    var allAccounts = new List<Account>();
    var accountsToProcess = new List<string> { rootAccountId };
    
    while (accountsToProcess.Count > 0)
    {
        // Get next level of children
        var children = Database.Query<Account>()
            .Where(f => accountsToProcess.Contains(f.ParentId))
            .ToList();
        
        allAccounts.AddRange(children);
        
        // Prepare next level
        accountsToProcess = children.Select(c => c.Id).ToList();
    }
    
    return allAccounts;
}

Build Hierarchy Tree:

public class AccountNode
{
    public Account Account { get; set; }
    public List<AccountNode> Children { get; set; }
    
    public AccountNode(Account account)
    {
        Account = account;
        Children = new List<AccountNode>();
    }
}

public AccountNode BuildAccountTree(string rootAccountId)
{
    var root = Database.Retrieve<Account>(rootAccountId);
    var rootNode = new AccountNode(root);
    
    // Get all children recursively
    LoadChildren(rootNode);
    
    return rootNode;
}

private void LoadChildren(AccountNode parentNode)
{
    var children = Database.Query<Account>()
        .Where(f => f.ParentId == parentNode.Account.Id)
        .ToList();
    
    foreach (var child in children)
    {
        var childNode = new AccountNode(child);
        parentNode.Children.Add(childNode);
        
        // Recursively load children
        LoadChildren(childNode);
    }
}

Hierarchy Level Calculation:

public int GetAccountLevel(string accountId)
{
    int level = 0;
    string currentId = accountId;
    
    while (!string.IsNullOrEmpty(currentId))
    {
        var account = Database.Retrieve<Account>(currentId);
        
        if (account == null || string.IsNullOrEmpty(account.ParentId))
        {
            break;
        }
        
        level++;
        currentId = account.ParentId;
    }
    
    return level;
}

Avoiding N+1 Query Problem

The N+1 query problem occurs when you query a parent record, then execute additional queries for each related child record.

❌ BAD - N+1 Query Problem:

// Query accounts (1 query)
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Limit(10)
    .ToList();

// Query contacts for each account (N queries - 10 additional queries)
foreach (var account in accounts)
{
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == account.Id)
        .ToList();
    
    SystemInfo.Debug($"{account.Name}: {contacts.Count} contacts");
}
// Total: 11 queries (1 + 10)

✅ GOOD - Single Query with Aggregation:

// Query accounts (1 query)
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Limit(10)
    .ToList();

var accountIds = accounts.Select(a => a.Id).ToList();

// Query all contacts for all accounts at once (1 query)
var allContacts = Database.Query<Contact>()
    .Where(f => accountIds.Contains(f.AccountId))
    .ToList();

// Group contacts by account
var contactsByAccount = allContacts.GroupBy(c => c.AccountId)
    .ToDictionary(g => g.Key, g => g.ToList());

// Display results
foreach (var account in accounts)
{
    int contactCount = contactsByAccount.ContainsKey(account.Id) 
        ? contactsByAccount[account.Id].Count 
        : 0;
    
    SystemInfo.Debug($"{account.Name}: {contactCount} contacts");
}
// Total: 2 queries (1 + 1)

✅ GOOD - Eager Loading:

// Query contacts with account data loaded (1 query)
var options = new QueryOptions
{
    Includes = "Account",
    EagerLoadMode = EagerLoadMode.IdAndName
};

var contacts = Database.Query<Contact>(
    f => f.IsActive == true,
    options
).ToList();

// Access account without additional queries
foreach (var contact in contacts)
{
    SystemInfo.Debug($"{contact.Name} works at {contact.Account.Name}");
}
// Total: 1 query

Bulk Loading Pattern:

public class AccountWithContacts
{
    public Account Account { get; set; }
    public List<Contact> Contacts { get; set; }
}

public List<AccountWithContacts> GetAccountsWithContacts(List<string> accountIds)
{
    // Load all accounts (1 query)
    var accounts = Database.Query<Account>()
        .Where(f => accountIds.Contains(f.Id))
        .ToList();
    
    // Load all contacts for all accounts (1 query)
    var contacts = Database.Query<Contact>()
        .Where(f => accountIds.Contains(f.AccountId))
        .ToList();
    
    // Group contacts by account
    var contactsByAccount = contacts.GroupBy(c => c.AccountId)
        .ToDictionary(g => g.Key, g => g.ToList());
    
    // Combine results
    var results = new List<AccountWithContacts>();
    
    foreach (var account in accounts)
    {
        results.Add(new AccountWithContacts
        {
            Account = account,
            Contacts = contactsByAccount.ContainsKey(account.Id) 
                ? contactsByAccount[account.Id] 
                : new List<Contact>()
        });
    }
    
    return results;
    // Total: 2 queries regardless of account count
}

Related Record Examples

Account with All Related Records:

public class AccountDetails
{
    public Account Account { get; set; }
    public List<Contact> Contacts { get; set; }
    public List<Opportunity> Opportunities { get; set; }
    public List<Task> Tasks { get; set; }
}

public AccountDetails GetAccountDetails(string accountId)
{
    // Load account
    var account = Database.Retrieve<Account>(accountId);
    
    if (account == null)
    {
        return null;
    }
    
    // Load all related records
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == accountId)
        .ToList();
    
    var opportunities = Database.Query<Opportunity>()
        .Where(f => f.AccountId == accountId)
        .ToList();
    
    var tasks = Database.Query<Task>()
        .Where(f => f.WhatId == accountId)
        .ToList();
    
    return new AccountDetails
    {
        Account = account,
        Contacts = contacts,
        Opportunities = opportunities,
        Tasks = tasks
    };
}

Clone Account with Related Records:

public string CloneAccountWithContacts(string sourceAccountId)
{
    // Get source account
    var sourceAccount = Database.Retrieve<Account>(sourceAccountId);
    
    if (sourceAccount == null)
    {
        return null;
    }
    
    // Create new account (copy fields)
    var newAccount = new Account
    {
        Name = sourceAccount.Name + " (Copy)",
        Type = sourceAccount.Type,
        Industry = sourceAccount.Industry,
        Phone = sourceAccount.Phone,
        Website = sourceAccount.Website
    };
    
    var accountResult = Database.Create(newAccount, new DatabaseOptions { IsApiMode = true });
    
    if (accountResult.HasError)
    {
        return null;
    }
    
    // Get source contacts
    var sourceContacts = Database.Query<Contact>()
        .Where(f => f.AccountId == sourceAccountId)
        .ToList();
    
    // Clone contacts
    var newContacts = new List<Contact>();
    
    foreach (var sourceContact in sourceContacts)
    {
        newContacts.Add(new Contact
        {
            FirstName = sourceContact.FirstName,
            LastName = sourceContact.LastName,
            Email = sourceContact.Email,
            Phone = sourceContact.Phone,
            Title = sourceContact.Title,
            AccountId = accountResult.Id  // Link to new account
        });
    }
    
    if (newContacts.Count > 0)
    {
        Database.Create(newContacts, new DatabaseOptions { IsApiMode = true });
    }
    
    SystemInfo.Debug($"Cloned account with {newContacts.Count} contacts");
    
    return accountResult.Id;
}

Merge Accounts:

public void MergeAccounts(string masterAccountId, string duplicateAccountId)
{
    // Move all contacts to master account
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == duplicateAccountId)
        .ToList();
    
    foreach (var contact in contacts)
    {
        contact.AccountId = masterAccountId;
    }
    
    if (contacts.Count > 0)
    {
        Database.Edit(contacts, new DatabaseOptions { IsApiMode = true });
        SystemInfo.Debug($"Moved {contacts.Count} contacts");
    }
    
    // Move all opportunities to master account
    var opportunities = Database.Query<Opportunity>()
        .Where(f => f.AccountId == duplicateAccountId)
        .ToList();
    
    foreach (var opp in opportunities)
    {
        opp.AccountId = masterAccountId;
    }
    
    if (opportunities.Count > 0)
    {
        Database.Edit(opportunities, new DatabaseOptions { IsApiMode = true });
        SystemInfo.Debug($"Moved {opportunities.Count} opportunities");
    }
    
    // Delete duplicate account
    var result = Database.Delete(duplicateAccountId, new DatabaseOptions { IsApiMode = true });
    
    if (!result.HasError)
    {
        SystemInfo.Debug("Accounts merged successfully");
    }
}

Best Practices for Related Records

✅ Use eager loading to avoid N+1 queries

var options = new QueryOptions
{
    Includes = "Account",
    EagerLoadMode = EagerLoadMode.IdAndName
};

var contacts = Database.Query<Contact>(f => f.IsActive == true, options).ToList();

✅ Bulk load related records

// Load all related records in single query
var contacts = Database.Query<Contact>()
    .Where(f => accountIds.Contains(f.AccountId))
    .ToList();

✅ Check for null references

if (contact.Account != null)
{
    SystemInfo.Debug(contact.Account.Name);
}

✅ Validate relationships before delete

long childCount = Database.Count<Contact>(f => f.AccountId == accountId);

if (childCount > 0)
{
    SystemInfo.Debug("Cannot delete: related records exist");
    return;
}

✅ Use AllOrNone for related record creation

// Create parent and children together
var results = Database.Create(contacts, new DatabaseOptions 
{ 
    AllOrNone = true,
    IsApiMode = true 
});

❌ Never query in loops

// ❌ BAD
foreach (var account in accounts)
{
    var contacts = Database.Query<Contact>()
        .Where(f => f.AccountId == account.Id)
        .ToList();
}

// ✅ GOOD
var contacts = Database.Query<Contact>()
    .Where(f => accountIds.Contains(f.AccountId))
    .ToList();

❌ Don't forget to update relationships

// ❌ BAD - Creating orphaned record
var contact = new Contact
{
    FirstName = "John",
    LastName = "Smith"
    // Missing AccountId
};

// ✅ GOOD - Proper relationship
var contact = new Contact
{
    FirstName = "John",
    LastName = "Smith",
    AccountId = accountId
};

 

Last updated on 1/8/2026

Attachments


Privacy & Security StatementTerms & Conditions
Copyright © 2026 by Magentrix Corporation - All rights reserved.
Powered by Magentrix