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
AhinAhin 

Simple trigger to lookup a value from a non-related table

I'm very new to writing triggers, but I think it's time to learn.  Here's the scenario:

 

I'm building an Invoice__c record by adding multiple Invoice_Item__c as Master Detail related list

 

An Invoice_Item__c consists of an Equipment__c lookup, an Equipment__c.Hourly_Rate__c.  

Next, I need to calculate the tax added to this related record.  I keep the tax rates in a non-related table
Tax_Rates__c

 

How would I write a trigger on the Invoice_Item__c object to pull Invoice__c.State then match it to Tax_Rates__c.State and return Tax_Rates__c.Rate into a field on Invoice_Item__c?

Best Answer chosen by Admin (Salesforce Developers) 
timainmantimainman

Okay. This should be pretty close (It feels a little heavy...but dont have the objects in an org to really test).

 

Notes.

 

1) With formula fields this would be a lot simpler...but I understand what you are doing

2) I actually would write this on the invoice level and let the rate trickle down via aformula field

 

 

trigger taxRateTrigger on Invoice_Item__c (before insert) {

	/// Just did it on an insert but you could expand the logic.

	// #1 - I would actually write this code all in a class and 
	// reference it but I am just going to stick it in here for now
	
	
	// #2 - Create a set of Invoice__c ids from the Invoice items
	
	Set<Id> parentInvoiceIds = new Set<Id>();
	
	for(Invoice_Item__c thisInvoice:Trigger.new()) {
		
		/// You said master/detail so thisInvoice.Invoice__c should never be null
		parentInvoiceIds.add(thisInvoice.Invoice__c); 
	}

	// #3 Create a map of Invoice__c items with thier state
	
	Map<Id, Invoice__c> parentInvoices = new Map<Id,Invoice__c>([Select State from Invoice__c Where Id in :parentInvoiceIds]);
	
	// #4 Need to get a set of all the states- Might be an esier way to do this...
	
	List<Invoice__c> parentInvoiceList = parentInvoices.values(); ///Turn it into a list.
	
	Set<String> states = new Set<String>();
	
	// #4 Need to get all the states in a set 
	
	for(Invoice__c thisParentInvoice:parentInvoiceList) {
		
		if(thisParentInvoice.state != null) {

			states.add(thisInvoice.state); 
		}	
	}

	//#5 FInally get all the tax rates

	List<Tax_Rates__c> taxRates = [Select State,Rate from Tax_Rates__c Where State in :states];
	
	
	/// Now that I got all the data I can update the Invoice_Item__c
	
	for(Invoice_Item__c thisInvoiceItem:Trigger.new()) {
		
		for(Tax_Rates__c thisTaxRate:taxRates) {
			
			if(thisTaxRate.State == parentInvoices.get(thisInvoiceItem.Invoice__c).State) {
				
				thisInvoiceItem.Tax_Rate__c = thisInvoiceItem.Rate;
			}	
		}
	}
	
	// Update will happen automatically since we did it before update.
	
}

 

 

All Answers

timainmantimainman

Some clarification:

 

1) Do you simply want to pull the Tax Rate to the Invoice_Item__c table? No calculations?

2) Can I ssume that you are going to pull the Invoice__c.State down to all child Invoice_Items__c via  aformula field??
AhinAhin

Very good questions.  My plan is to learn how to do something like this without adding more fields  to learn how I would go about coding this (vs. configure).

 

1 - I have the calculation already taken care of - right now the tax rate is a number field that the user can type into (highly error prone).  I'd rather manage a taxation list and have this field auto-populate based on the "Invoice header"

 

2 - I would prefer not to pull the state down so I can learn something new (the hard way? :) 

 

I hope my configuration is not too irritating, but I've been exploring this idea for quite a few hours and can't seem to get it to work in this manner due to syntax issues.  I think I have the logic correct.
timainmantimainman

Okay. This should be pretty close (It feels a little heavy...but dont have the objects in an org to really test).

 

Notes.

 

1) With formula fields this would be a lot simpler...but I understand what you are doing

2) I actually would write this on the invoice level and let the rate trickle down via aformula field

 

 

trigger taxRateTrigger on Invoice_Item__c (before insert) {

	/// Just did it on an insert but you could expand the logic.

	// #1 - I would actually write this code all in a class and 
	// reference it but I am just going to stick it in here for now
	
	
	// #2 - Create a set of Invoice__c ids from the Invoice items
	
	Set<Id> parentInvoiceIds = new Set<Id>();
	
	for(Invoice_Item__c thisInvoice:Trigger.new()) {
		
		/// You said master/detail so thisInvoice.Invoice__c should never be null
		parentInvoiceIds.add(thisInvoice.Invoice__c); 
	}

	// #3 Create a map of Invoice__c items with thier state
	
	Map<Id, Invoice__c> parentInvoices = new Map<Id,Invoice__c>([Select State from Invoice__c Where Id in :parentInvoiceIds]);
	
	// #4 Need to get a set of all the states- Might be an esier way to do this...
	
	List<Invoice__c> parentInvoiceList = parentInvoices.values(); ///Turn it into a list.
	
	Set<String> states = new Set<String>();
	
	// #4 Need to get all the states in a set 
	
	for(Invoice__c thisParentInvoice:parentInvoiceList) {
		
		if(thisParentInvoice.state != null) {

			states.add(thisInvoice.state); 
		}	
	}

	//#5 FInally get all the tax rates

	List<Tax_Rates__c> taxRates = [Select State,Rate from Tax_Rates__c Where State in :states];
	
	
	/// Now that I got all the data I can update the Invoice_Item__c
	
	for(Invoice_Item__c thisInvoiceItem:Trigger.new()) {
		
		for(Tax_Rates__c thisTaxRate:taxRates) {
			
			if(thisTaxRate.State == parentInvoices.get(thisInvoiceItem.Invoice__c).State) {
				
				thisInvoiceItem.Tax_Rate__c = thisInvoiceItem.Rate;
			}	
		}
	}
	
	// Update will happen automatically since we did it before update.
	
}

 

 

This was selected as the best answer
AhinAhin

Awesome!  Thank you so much for the help (and the thought process behind each step!)  I will give it a whirl and let you know the outcome.

 

-Aaron
AhinAhin

Below is the code that works:

I had to change some of the parts of the final FOR loops, but it's tested and working.  I tried to comment my thoughts as I was building each part of the trigger.

 

Some questions:

1 - Is it rather common to pull IDs into a SET, then build a map of <ID, 'data element'>, then add this info to a List?

      Is the List the only way the info can be worked with?  I'm not certain I understand the principles.

 

2 - You mentioned in your help that this would be better written with Classes.  If you have some time, could you help me understand why Classes are better in this case and maybe show me the same trigger built with classes instead?

 

3 - Are my comments correct relating to each part of the trigger code is what is occuring?

 

Thank you very much for your help!

 

 

trigger SetProperStateTaxRate on Invoice_Item__c (before insert, before update) {
  
  //Grab all the invoices IDs related to this Invoice_Item (there will be only 1)
  Set<ID> parentInvoiceIDs = new Set<ID>();
    // variable definition complete
    //Fill the Set with the right data
    For(Invoice_Item__c thisInvoice : Trigger.new) { 
      // creates variable thisInvoice
      
      //If not M:D be sure to check for NULL or code will break
      parentInvoiceIDs.add(thisInvoice.Invoice__c);
    } //end FOR from line 7
    
  // Create Map Invoice IDs and their respective State
  Map<ID, Invoice__c> parentInvoiceInfo = new Map<ID, Invoice__c> ([
    SELECT Well_State_Location__c
    FROM Invoice__c
    WHERE ID in : parentInvoiceIDs]);
    
  //Make the Map info into a List so it's workable (???)
  List<Invoice__c> parentInvoiceList = parentInvoiceInfo.values();
  
  //Put States into a set
  Set<String> States = new Set<String>();
  // definition of the set - now fill it with the proper data
  
  for(Invoice__c thisParentInvoice : parentInvoiceList) {
    if(thisParentInvoice.Well_State_Location__c != NULL) {
      States.add(thisParentInvoice.Well_State_Location__c);
    } //end if from line 28
  } // end for from line 27
  
  //Get the Tax Rates from the lookup object
  List<Tax_Table__c> taxRates = [
    SELECT State_County_City__c, State_County_City_Tax_Rate__c
    FROM Tax_Table__c
    WHERE State_County_City__c in : States];
    
  // Data is prepared!
  // Now put it all together
  
  For(Invoice_Item__c thisInvoiceItem : Trigger.new) {
    for(Tax_Table__c thisTaxRate : taxRates) {
      if(thisTaxRate.State_County_City__c == parentInvoiceInfo.get(thisInvoiceItem.Invoice__c).Well_State_Location__c) {
          thisInvoiceItem.Trigger_State_Tax_Rate__c = thisTaxRate.State_County_City_Tax_Rate__c;
      } // end if from line 44
    } // end for from line 43
  } // end for from line 42
} // end trigger

 

 
timainmantimainman

Here are my thoughts

 

1. It is common to create a set to get unique set of ids and then use them in a query to create a map or a list. I created a map in this case because they are easy to reference and reduce loops. I converted it to a list simply based on that is how I like to loop things. I assume you can loop through a map...i just dont that often. Bottom line is you to all your data collection outside loops.

 

2. I dont put logic in triggers for two reasons (A) They get really sloppy once you start adding more and more logic (2) When you stick the logic in a class it is reusable other places. Below is some quick code with it in a class: 

trigger SetProperStateTaxRate on Invoice_Item__c (before insert, before update) {

setTaxRate newTaxRatesClass = new setTaxRate();
setTaxRate.newInvoiceItems = Trigger.new();

if(Trigger.isBefore) {

setTaxRate.processInvoiceItems()
}



}

/// CLASS

public without sharing class setTaxRate{

public List<Invoice_Item__c> newInvoiceItems = new List<Invoice_Item__c>();

public void processInvoiceItems() {

/*

Put all your trigger code here. The only difference is you 
reference the "newInvoiceItems" list instead of Trigger.new

*/


}

}

 

 

3. Not this line

//Grab all the invoices IDs related to this Invoice_Item (there will be only 1)

Triggers can happen in a bulk context so you could have up to 200.

 

Hope this helps!