function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
DHedgDHedg 

formula to another object

I have a custom field on my Contact record (calculated_region__c).  I need to populate calculated_region__c based on the following criteria:

*I have already set up two custom objects to house:
1. All zips in my state with their city name, county name, and its associated region
2. All Campaigns_c (custom picklist field on contact) names with their associated region.


If Campaign is not empty then match campaign to Campaign Region associated with contact Campaign
Else
 If contact zip code is not empty then match to region associated with contact zip
Else
If contact city name is not empty then match to region associated with contact city name
Else
If account zip code is not empty then match to region associated with account zip
Else
If account city name is not empty then match to region associated with account city name
Else
If account county name is not empty then match to region associated with account county name

I started a trigger for this but it quickly got very confusing (or at least I did).  
 
DHedgDHedg
Solved it with an if wrapped aroung individual maps that looked for the associated values. I'll post when done for advice on making the code more efficient. 
DHedgDHedg
Here is my code.  My results seem inconsistant.   Any help is much appreciated.

trigger CalculatedRegionTrigger on Contact (before insert, before update) {
  Map<String,String> camplookup = new Map<String,String>();
  Map<String,String> ziplookup = new Map<String,String>();

  Set<String> regionNames = new Set<String>();
   Set<String> regionbNames = new Set<String>();


for(Contact c:Trigger.new){

If(!String.isBlank(c.Campaign__c) ){



for(Contact b:Trigger.new) {
regionbNames.add(b.Campaign__c);
}

for(Region_B__c rgb:
    [SELECT      Id,Name, Calculated_Region__c
     FROM        Region_B__c
     WHERE       Name IN :regionbNames]) {
      campLookup.put(rgb.Name,rgb.Calculated_Region__c);
  }
  
   for(Contact b:Trigger.new) {
     if(camplookup.containsKey(b.Campaign__c)) {
      b.Calculated_Region__c = campLookup.get(b.Campaign__c);
      }else{
    b.Calculated_Region__c = 'Check Campaign';
   
  }
  }
 //**************************************************************
 // else for wrapper for if statment - ZIP
 }else if (!String.isBlank(c.Short_Zip__c)){

  // First, we "aggregate".
  // Here, we find all zips we need to translate.
  for(Contact a:Trigger.new) {
    regionNames.add(a.Short_Zip__c);
  }

  // Then, we "query".
  // We map zip values to region values.
  for(Region__c rg:
    [SELECT      Id,Name,Region__c
     FROM        Region__c
     WHERE       Name IN :regionNames]) {
      zipLookup.put(rg.Name,rg.Region__c);
  }

  // Finally, we "update".
  // Just loop through again; if there is a valid translation,
  // put the translation into the field. Otherwise, it will be
  // ignored.
  for(Contact a:Trigger.new) {
     /// may not need this if statement once all criteria are in
    if(ziplookup.containsKey(a.Short_Zip__c)) {
      a.Calculated_Region__c = zipLookup.get(a.Short_Zip__c);
      }
      else {
      a.Calculated_Region__c = 'Check Zip';
    }
    
  }
  //**************************************************************
  // else for wrapper for if statment - Contact Mailing City
   }else if (!String.isBlank(c.MailingCity)){
      Map<String,String> citylookup = new Map<String,String>();
      for(Contact a:Trigger.new) {
    regionNames.add(a.MailingCity);
  }

  // Then, we "query".
  // We map zip values to region values.
  for(Region__c rg:
    [SELECT      Id,Name,City__c, Region__c
     FROM        Region__c
     WHERE       City__c IN :regionNames]) {
      cityLookup.put(rg.City__c,rg.Region__c);
  }

  // Finally, we "update".
  // Just loop through again; if there is a valid translation,
  // put the translation into the field. Otherwise, it will be
  // ignored.
  for(Contact a:Trigger.new) {
     /// may not need this if statement once all criteria are in
    if(citylookup.containsKey(a.MailingCity)) {
      a.Calculated_Region__c = cityLookup.get(a.MailingCity);
      }
      else {
      a.Calculated_Region__c = 'Check city';
    }
    
  }
  //**************************************************************
  // else for wrapper for if statment - Account Zip
  // only finds contacts with blank cities that belong to Organizations since the Household account/contacts are synced automatically 
   }else if (!String.isBlank(c.Account_Zip__c)){
      Map<String,String> accziplookup = new Map<String,String>();
      for(Contact a:Trigger.new) {
    regionNames.add(a.Account_Zip__c);
  }

  // Then, we "query".
  // We map zip values to region values.
  for(Region__c rg:
    [SELECT      Id,Name,City__c, Region__c
     FROM        Region__c
     WHERE       Name IN :regionNames]) {
      acczipLookup.put(rg.Name,rg.Region__c);
  }

  // Finally, we "update".
  // Just loop through again; if there is a valid translation,
  // put the translation into the field. Otherwise, it will be
  // ignored.
  for(Contact a:Trigger.new) {
     /// may not need this if statement once all criteria are in
    if(accziplookup.containsKey(a.Account_Zip__c)) {
      a.Calculated_Region__c = acczipLookup.get(a.Account_Zip__c);
      }
      else {
      a.Calculated_Region__c = 'Check Acct Zip';
    }
    
  }
  
  //**************************************************************
  // else for wrapper for if statment - Account City
  // only finds contacts with blank cities that belong to Organizations since the Household account/contacts are synced automatically 
   }else if (!String.isBlank(c.Account_City__c)){
      Map<String,String> acccitylookup = new Map<String,String>();
      for(Contact a:Trigger.new) {
    regionNames.add(a.Account_City__c);
  }

  // Then, we "query".
  // We map zip values to region values.
  for(Region__c rg:
    [SELECT      Id,Name,City__c, Region__c
     FROM        Region__c
     WHERE       City__c IN :regionNames]) {
      acccityLookup.put(rg.City__c,rg.Region__c);
  }

  // Finally, we "update".
  // Just loop through again; if there is a valid translation,
  // put the translation into the field. Otherwise, it will be
  // ignored.
  for(Contact a:Trigger.new) {
     /// may not need this if statement once all criteria are in
    if(acccitylookup.containsKey(a.Account_City__c)) {
      a.Calculated_Region__c = acccityLookup.get(a.Account_City__c);
      }
      else {
      a.Calculated_Region__c = 'Check Acct city';
    }
    
  }
  
  //**************************************************************
  // else for wrapper for if statment - Account County
  // only finds contacts with blank cities that belong to Organizations since the Household account/contacts are synced automatically 
   }else if (!String.isBlank(c.Account_County__c)){
      Map<String,String> acccountylookup = new Map<String,String>();
      for(Contact a:Trigger.new) {
    regionNames.add(a.Account_County__c);
  }

  // Then, we "query".
  // We map zip values to region values.
  for(Region__c rg:
    [SELECT      Id,Name,City__c,County__c, Region__c
     FROM        Region__c
     WHERE       County__c IN :regionNames]) {
      acccountyLookup.put(rg.County__c,rg.Region__c);
  }

  // Finally, we "update".
  // Just loop through again; if there is a valid translation,
  // put the translation into the field. Otherwise, it will be
  // ignored.
  for(Contact a:Trigger.new) {
     /// may not need this if statement once all criteria are in
    if(acccountylookup.containsKey(a.Account_County__c)) {
      a.Calculated_Region__c = acccountyLookup.get(a.Account_County__c);
      }
      else {
      a.Calculated_Region__c = 'Check Acct county';
    }
    
  }
  }
  }
}