I have 3 objects: Contacts, Accounts, Campaign, that are involved in the scenario. Contact is the parent object, Accounts and Campaigns are child objects to contact(Related Lists) There is a priority field on the account object. There are different campaigns namely, Red, Green, Blue which are mapped to the priority field of account. So Let's see priority 1 means blue bucket/campaign, priority 2 means orange campaign, priority 3 means red campaign and so on.
What I want to do:
I want my trigger/batch job to loop over all contacts in Salesforce, for each contact, it finds the accounts associated with the contact(related list), checks the priority field on those accounts. The code should find out the highest priority from all those accounts associated with the specific contact, then add the contact as a campaign member in the corresponding campaign. So if the highest priority from all those accounts come out to be 2, the contact should be added in orange campaign, if the highest priority comes out to be 3, the contact should be added as campaign member in red campaign.
I was thinking to start it by creating a bulk trigger on account object and maybe create a hidden 'Highest Priority' field on contact object. Every time account is added,updated or deleted, it should calculate the highest priority from all the accounts associated with the parent contact and save it in the hidden field on the contact object. Then write a second trigger that checks the value of 'highest priority' field on contact object and based on that add the contact in the corresponding campaign(red,orange,blue e.t.c). Thoughts on that? Is it possible to do? Any other way to achieve this whole functionality efficiently?