You need to sign in to do that
Don't have an account?
DHedg
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).
*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).
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';
}
}
}
}
}