[SalesForce] Junction Object to create many to many relationship between Tasks and Accounts

It's a few months later and I finally found the solution to this problem -- and it turns out it's already built into Salesforce (although I believe it's only available in the enterprise edition and above): Partner Portals. You don't actually have to use the core partner portal functionality, but by activating the Partner object, you automatically get a many-to-many relationship between accounts (which is exactly what I wanted). Here's what I did:

Turn on the Partner object: Setup > Customize > Partners > Settings; then check the box to enable partners and hit save.

Add the Partner Account field to the Account Layout: Setup > Customize > Accounts > Page Layouts; edit the default page layout and just drag the Partner Account field somewhere. It’s actually not editable, but I guess it’s nice to see…or maybe making it visible triggers its functionality (not sure).

To make an account a partner account, you must first create it and save it, and then you'll see a button that says “Manage External Account”; click on it an select “Enable As Partner” – now the Partner Account checkbox will be checked.

Then, the way you link an account with a partner account is you scroll down to the bottom of the account page to Partners related list. Click on “New” and select a partner account, give it a role, and save.

If you want to then run a report showing all contacts for a specific account's partners, you'll need to create a custom report with the primary object being accounts, the secondary object being partners, and the tertiary object being contacts. When you run the report, filter it by the original account, and you'll see all contacts for that account's partners!

One of the things I like to do is add formula fields to my Junction objects to simplify things. So the first step would be to make a field on Sponsorship__c; I'll call it: Sponsored_Child_Name__c.

Formula: Sponsored_Child__c.First_Name__c

Another very important thing to keep in mind is bulkification:

Pseudo Code

trigger triggerName on Sponsorship__c (events) {
if (event) { // trigger.isUpdate, trigger.isBefore, etc
Map<Id, Sponsorship__c> contactToSponsorship = new Map<Id, Sponsorship__c>();
Lisit<Contact> contactsToUpdate = new List<Contact>();

for (Sponsorship__c s : trigger.new) {
contactToSponsorship.put(s.contact__c, s);
}

for (Contact c : [SELECT Child_Names__c FROM Contact WHERE Id IN :contactToSponsorship.keySet()]) {
c.Child_Names__c += contactToSponsorship.get(c.Id).Sponsored_Child_Name__c;
contactsToUpdate.add(c);
}

update contactsToUpdate;
}

That should get you close.