Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Daniel ProbertDaniel Probert 

Update 2 x Lookup field Custom Object based on matching fields

Hi,

 

I have a challenge that I can't for the life of me work out how to approach, I have 3 objects that need to be automatically linked, to help explain the Org setup here is a brief.

 

Object 1 = Entitlement__c

 

This has 5 fields in there:

 

Term__c - this is a picklist that has the option Term 1, 2 & 3

Year__c - this is a picklist that has the year 2012-2020

Student__c - this is a lookup to the Contact object

Entitlement_Sheet__c - this is a lookup to a custom object Entitlement_Sheet__c

District_Entitlement_Sheet__c - this is a lookup to a custom object District_Entitlement_Sheet__c

 

Object 2 = District_Entitlement_Sheet__c

 

This has 3 fields in there

 

Term__c - this is a picklist that has the option Term 1, 2 & 3

Year__c - this is a picklist that has the year 2012-2020

District__c - this is a lookup to a custom object District__c

 

Object 3 = Entitlement_Sheet__c

 

This has 3 fields in there

Term__c - this is a picklist that has the option Term 1, 2 & 3

Year__c - this is a picklist that has the year 2012-2020

 School__c - this is a lookup to a custom object School__c

 

My problem is this.

 

I need to create a trigger that when a new Entitlement__c is created it will automatically update the lookup fields Entitlement_Sheet__c & District_Entitlement_Sheet__c.

 

The logic on the connection for mapping Object 1 to 2 is:

 

If the following statement = true then update the Entitlement__c.District_Entitlement_Sheet__c with the ID of the District_Entitlement_Sheet__c.id

 

Entitlement__c.Term__c = District_Entitlement_Sheet__c.Term__c

Entitlement__c.Year__c = District_Entitlement_Sheet__c.Year__c

Entitlement__c.Student__c.District__c.ID = Districit_Entitlement_Sheet__c.District__c.id

 

The logic on the connection for mapping Object 1 to 3 is:

 

Entitlement__c.Term__c = Entitlement_Sheet__c.Term__c

Entitlement__c.Year__c = Entitlement_Sheet__c.Year__c

Entitlement__c.Student__c.School__c.ID = Entitlement_Sheet__c.School__c.id

 

I can't even begin to work out how I would approach this I'm pretty sure it can be done within a single trigger that occurs when a record is inserted but want to be sure.

 

As an FYI the Object 2 & 3 are pre loaded into the system and do contain much more information within them that feeds into formulas within Object 1.

 

Any guidance would be appreciated as I'm clueless on this one :)

 

 

 

 

 

 

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Daniel ProbertDaniel Probert

ok just worked out what my issue was sorted now has to make sure my districtID was visible so created a formula field to solve it and now I'm up and running - awesome and cheers for your support.

 

this is the final code:

 

Trigger entitlementTrigger on Entitlement__c (before insert, before update) {
Map<String, String> YearsMap = new Map<String, String>();
Map<String, String> TermsMap = new Map<String, String>();
Map<String, String> DistrictsMap = new Map<String, String>();
Map<String, String> SchoolsMap = new Map<String, String>();
for(Entitlement__c e: trigger.new) {
     YearsMap.put(e.Year__c, null);
     TermsMap.put(e.Term__c, null);
     DistrictsMap.put(e.DistrictID__c, null);
     SchoolsMap.put(e.School__c, null);
}
List<District_Entitlement_Sheet__c> DESList = [SELECT Id, Term__c, Year__c, District__c FROM District_Entitlement_Sheet__c WHERE Term__c in: TermsMap.KeySet() OR Year__c in: YearsMap.keySet() OR District__c in: DistrictsMap.KeySet()];
List<Entitlement_Sheet__c> ESList = [SELECT  Id, Term__c, Year__c, School__c FROM Entitlement_Sheet__c WHERE Term__c in: TermsMap.keySet() OR Year__c in: YearsMap.KeySet() OR School__c in: SchoolsMap.KeySet()];
//Now that we have the lists populated of any potential matches iterate through trigger list and match
for(Entitlement__c e: trigger.new) {
      for(District_Entitlement_Sheet__c des: DESList){
           if(e.Term__c == des.term__c && e.Year__c == des.Year__c && e.DistrictID__c == des.District__c) {
                 e.District_Entitlement_Sheet__c = des.Id;
           }
      }
      for(Entitlement_Sheet__c es: ESList){
           if(e.Term__c == es.term__c && e.Year__c == es.Year__c && e.School__c == es.School__c) {
                 e.Entitlement_Sheet__c = es.Id;
           }
      }
}
}

 

All Answers

Tim BarsottiTim Barsotti

Need to run 2 queries to get these IDs. One on the District_Entitlement_Sheet__c object and one on the Entitlement_Sheet__c object.

 

Since we are looking at the possibility of editing up to 200 records per trigger fire, we need to ensure the trigger will handle a bulk operation. 

 

1) Create a map for all applicable terms.

2) Create a map for all applicable years.

3) Create a map for all applicable districts.

4) Create a map for all applicable schools.

5) Populate maps

6) Query District_Entitlement_Sheet__c

7) Query Entitlement_Sheet__c

8) Match on criteria

 

See example code below

 

Trigger entitlementTrigger on Entitlement__c (before insert, before update) {

Map<String, String> YearsMap = new Map<String, String>();

Map<String, String> TermsMap = new Map<String, String>();

Map<String, String> DistrictsMap = new Map<String, String>();

Map<String, String> SchoolsMap = new Map<String, String>();

for(Entitlement__c e: trigger.new) {

     YearsMap.put(e.Year__c, null);

     TermsMap.put(e.Term__c, null);

     DistrictsMap.put(e.District__c, null);

     SchoolsMap.put(e.School__c, null);

}

List<District_Entitlement_Sheet__c> DESList = [SELECT Id, Term__c, Year__c, District__c FROM District_Entitlement_Sheet__c WHERE Term__c in: TermsMap.KeySet() OR Year__c in: YearsMap.keySet() OR District__c in: DistrictMap.KeySet()];

List<Entitlement_Sheet__c> ESList = [SELECT  Id, Term__c, Year__c, District__c FROM Entitlement_Sheet__c WHERE Term__c in TermsMap.keySet() OR Year__c in: YearMap.KeySet() OR School__c in: SchoolMap.KeySet()];

//Now that we have the lists populated of any potential matches iterate through trigger list and match

for(Entitlement__c e: trigger.new) {

      for(District_Entitlement_Sheet__c des: DESList){

           if(e.Term__c == des.term__c && e.Year__c == des.Year__c && e.District__c == des.District__c) {

                 e.District_Entitlement_Sheet__c = des.Id;

           }

      }

      for(Entitlement_Sheet__c es: ESList){

           if(e.Term__c == es.term__c && e.Year__c == es.Year__c && e.School__c == es.School__c) {

                 e.District_Entitlement_Sheet__c = es.Id;

           }

      }

}

}
Daniel ProbertDaniel Probert

Hi Tim,

 

thanks for pointing me in the right direction a few minor tweaks and I thinks it right only issue now is that I get an error when  the trigger runs:

 

Apex trigger entitlementTrigger caused an unexpected exception, contact your administrator: entitlementTrigger: data changed by trigger for field District Entitlement Sheet: id value of incorrect type: a10M0000000ILi2IAG

 

This is the code that seems to be working until the save point only minor changes:

 

Trigger entitlementTrigger on Entitlement__c (before insert, before update) {
Map<String, String> YearsMap = new Map<String, String>();
Map<String, String> TermsMap = new Map<String, String>();
Map<String, String> DistrictsMap = new Map<String, String>();
Map<String, String> SchoolsMap = new Map<String, String>();
for(Entitlement__c e: trigger.new) {
     YearsMap.put(e.Year__c, null);
     TermsMap.put(e.Term__c, null);
     DistrictsMap.put(e.Student__r.District__c, null);
     SchoolsMap.put(e.School__c, null);
}
List<District_Entitlement_Sheet__c> DESList = [SELECT Id, Term__c, Year__c, District__c FROM District_Entitlement_Sheet__c WHERE Term__c in: TermsMap.KeySet() OR Year__c in: YearsMap.keySet() OR District__c in: DistrictsMap.KeySet()];
List<Entitlement_Sheet__c> ESList = [SELECT  Id, Term__c, Year__c, School__c FROM Entitlement_Sheet__c WHERE Term__c in: TermsMap.keySet() OR Year__c in: YearsMap.KeySet() OR School__c in: SchoolsMap.KeySet()];
//Now that we have the lists populated of any potential matches iterate through trigger list and match
for(Entitlement__c e: trigger.new) {
      for(District_Entitlement_Sheet__c des: DESList){
           if(e.Term__c == des.term__c && e.Year__c == des.Year__c && e.Student__r.District__c == des.District__c) {
                 e.District_Entitlement_Sheet__c = des.Id;
           }
      }
      for(Entitlement_Sheet__c es: ESList){
           if(e.Term__c == es.term__c && e.Year__c == es.Year__c && e.School__c == es.School__c) {
                 e.District_Entitlement_Sheet__c = es.Id;
           }
      }
}
}

 

From what I can tell if all matches which it does in my sandbox it's found the correct id it just isn't inserting it if that makes sense.

 

cheers for you help on this the code concept makes it much easier for me to get just don't get why it won't update.

 

cheers

dan

 
Daniel ProbertDaniel Probert

ok just worked out what my issue was sorted now has to make sure my districtID was visible so created a formula field to solve it and now I'm up and running - awesome and cheers for your support.

 

this is the final code:

 

Trigger entitlementTrigger on Entitlement__c (before insert, before update) {
Map<String, String> YearsMap = new Map<String, String>();
Map<String, String> TermsMap = new Map<String, String>();
Map<String, String> DistrictsMap = new Map<String, String>();
Map<String, String> SchoolsMap = new Map<String, String>();
for(Entitlement__c e: trigger.new) {
     YearsMap.put(e.Year__c, null);
     TermsMap.put(e.Term__c, null);
     DistrictsMap.put(e.DistrictID__c, null);
     SchoolsMap.put(e.School__c, null);
}
List<District_Entitlement_Sheet__c> DESList = [SELECT Id, Term__c, Year__c, District__c FROM District_Entitlement_Sheet__c WHERE Term__c in: TermsMap.KeySet() OR Year__c in: YearsMap.keySet() OR District__c in: DistrictsMap.KeySet()];
List<Entitlement_Sheet__c> ESList = [SELECT  Id, Term__c, Year__c, School__c FROM Entitlement_Sheet__c WHERE Term__c in: TermsMap.keySet() OR Year__c in: YearsMap.KeySet() OR School__c in: SchoolsMap.KeySet()];
//Now that we have the lists populated of any potential matches iterate through trigger list and match
for(Entitlement__c e: trigger.new) {
      for(District_Entitlement_Sheet__c des: DESList){
           if(e.Term__c == des.term__c && e.Year__c == des.Year__c && e.DistrictID__c == des.District__c) {
                 e.District_Entitlement_Sheet__c = des.Id;
           }
      }
      for(Entitlement_Sheet__c es: ESList){
           if(e.Term__c == es.term__c && e.Year__c == es.Year__c && e.School__c == es.School__c) {
                 e.Entitlement_Sheet__c = es.Id;
           }
      }
}
}

 

This was selected as the best answer