function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
PaulMacPaulMac 

Help with Insert Trigger that does lookup in another table (object)

Just hoping someone could get me started with the proper syntax or psuedo code.

 

The trigger should fire on Account "Before Insert"

 

On the incoming record, first I want to check if there's a value present for a particular field...say Account.MyIndustry__c

 

(If the length of MyIndustry__c is 0, I can exit the trigger)

 

If so, then do a lookup for this value on another table (object)

select NewIndustry__c
        from IndustryLookupTable__c
        where IndustryLookupTable__c.Name = (this incoming MyIndustry__c value)

 

Then append this found value in Account.Industry

 

In English, trying to insert a record where MyIndustry__c = "Auto Parts Manufacturing". The lookup for this value in IndustryLookupTable__c is = "Manufacturing". So I want "Manufacturing" inserted to Account.Industry

 

Thanks for any help you can offer!

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

This is the standard "aggregate-query-update" syntax that I'll be posting on my blog shortly, since it's such a common question. Namely, it looks like this (using your fields and object names as the example):

 

 

trigger trigger_copyIndustry on Account (before insert) {
  Map<String,String> industryLookup = new Map<String,String>();
  Set<String> industryNames = new Set<String>();

  // First, we "aggregate".
  // Here, we find all industries we need to translate.
  for(Account a:Trigger.new) {
    industryNames.add(a.MyIndustry__c);
  }

  // Then, we "query".
  // We map MyIndustry__c values to NewIndustry__c values.
  for(IndustryLookupTable__c ilt:
    [SELECT      Id,Name,NewIndustry__c
     FROM        IndustryLookupTable__c
     WHERE       Name IN :industryNames]) {
      industryLookup.put(ilt.Name,ilt.NewIndustry__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(Account a:Trigger.new) {
    if(industryLookup.containsKey(a.MyIndustry__c)) {
      a.Industry = industryLookup.get(a.MyIndustry__c);
    }
  }
}

Let me know if this is what you're looking for.

 

All Answers

Ritesh AswaneyRitesh Aswaney

trigger AccountBefore on Account (before insert)

{

 

Map<String, IndustryLookupTable__c> industriesMap = new Map<String, IndustryLookupTable__c>{};

 

for (Account acc : trigger.new)

if (acc.MyIndustry__c != null)

industriesMap.put(acc.MyIndustry__c, null); //lets aggregate all the industries to match on

 

 

if(industries != null && !industries.isEmpty())

{ //soql query to select all lookup values, which matched on name

for (IndustryLookupTable__c  lookupVal : [Select Id, Name, NewIndustry__c from IndustryLookupTable__c where Name in :industriesMap.keySey()])

industriesMap.put(lookupVal.Name, lookupVal);

 

for (Account acc : trigger.new)

acc.Industry = industriesMap.get(acc.MyIndustry__c).NewIndustry__c;

 

}

 

}
sfdcfoxsfdcfox

This is the standard "aggregate-query-update" syntax that I'll be posting on my blog shortly, since it's such a common question. Namely, it looks like this (using your fields and object names as the example):

 

 

trigger trigger_copyIndustry on Account (before insert) {
  Map<String,String> industryLookup = new Map<String,String>();
  Set<String> industryNames = new Set<String>();

  // First, we "aggregate".
  // Here, we find all industries we need to translate.
  for(Account a:Trigger.new) {
    industryNames.add(a.MyIndustry__c);
  }

  // Then, we "query".
  // We map MyIndustry__c values to NewIndustry__c values.
  for(IndustryLookupTable__c ilt:
    [SELECT      Id,Name,NewIndustry__c
     FROM        IndustryLookupTable__c
     WHERE       Name IN :industryNames]) {
      industryLookup.put(ilt.Name,ilt.NewIndustry__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(Account a:Trigger.new) {
    if(industryLookup.containsKey(a.MyIndustry__c)) {
      a.Industry = industryLookup.get(a.MyIndustry__c);
    }
  }
}

Let me know if this is what you're looking for.

 

This was selected as the best answer
PaulMacPaulMac

sfdcfox,

 

Thank you very much that worked perfectly. I'll have to check out your blog.

 

Can I ask you a follow up question. Before this trigger, Account.Industry was a required field (without a default picklist value being set). To get the trigger to work, I had to set the default value for Account.Industry as 'Other'. The trigger works fine, and changes the 'Other' to the desired lookup value.

 

Is it possible to get this trigger to work w/o setting a default value? The reason is that most Account inserts will not be affected by the trigger and I would still like to force the user to select the Industry from the picklist.

 

Thanks again! Now I'll go try to create a test case.
PaulMacPaulMac

Ritesh,

 

I ended up using the the response from sfdcfox, if not only because I tried that one first. But I thank you very much for your reply.

 

thanks!
sfdcfoxsfdcfox

You have to pass standard validation rules before you get to custom apex triggers. This means that you're limited to two easy alternatives: 1) use a validation rule for all times when it's required, and make the page layout not required, or 2) use record types (Enterprise Edition) with multiple page layouts; you can make the field required on one page layout but not another.