I have the following code. The SAP Matrix__c Field is a lookup field. When it references another object. If it finds a match it will update to the proper Id but when it does not find a match, it does nothing. If it does not find a match I need it to update the SAP_Matrix__c field on the quote to blank.

trigger SAPID on FX5__Quote__c (before insert, before update,after insert) {

   for(FX5__Quote__c cm: Trigger.new)

       List<SAP_Matrix__c> tr = [
           SELECT Id, 
           FROM SAP_Matrix__c
           WHERE Bill_To_Address__c = :cm.Bill_To_Address__c 
           AND Legal_Entity__c = :cm.Legal_Entity__c 

       for(SAP_Matrix__c t : tr){
           if(tr.isEmpty() != null && tr.size()> 0){
               cm.SAP_Matrix__c = t.ID ;
           else {
               cm.SAP_Matrix__c = '';
  • I have also tried Null instead of '' – Anas Adam Sorathia Dec 19 '19 at 23:22
  • Welcome to SFSE. Please take a moment to edit this question to provide a detailed description of the problem with this code. Please do not post comments to add information. – David Reed Dec 19 '19 at 23:27
  • Is the field required? What is the current behavior? Are you getting any sort of errors? – Brand0R Dec 19 '19 at 23:27
  • 4
    first issue here is you are doing SOQL in a for loop - this will blow up limits - consult Apex doc on bulkification – cropredy Dec 19 '19 at 23:30
  • 3
    You've got a lot of unnecessary logic in your if statements, you don't need to check if !tr.isEmpty() AND if tr.size() is greater than 0. Those are effectively the same. If you only want to run that logic block when tr isn't empty then you can just do if(!tr.isEmpty()). Also you can't set a lookup field to a string value of ''. That will result in an "Invalid Id:" error. You need to use cm.SAP_Matrix__C = null; – Morgan Marchese Dec 20 '19 at 0:54

SOQL within for loops is an easy way to hit your governor limits and doesn't follow best practice. This link on Apex Best Practices is a good place to start :)

To avoid using SOQL inside a for loop I find maps the easiest to work with. The solution would look something like this.

// by using just the before triggers we can update the value of SAP_Matrix__c without a need for a DML statement (database commit)
trigger SAPID on FX5__Quote__c (before insert, before update) {
    // getting the trigger values here so we can iterate through them twice
    List<FX5__Quote__c> quotes = Trigger.new;
    // by using a set we here we can make sure there are no duplicates in the array
    Set<String> billToAddresses = new Set<String>();
    Set<String> legalEntities = new Set<String>();
    List<SAP_Matrix__c> matrixItems = new List<SAP_Matrix__c>();
    Map<String, Id> identifierToId = new Map<String, Id>();

    // first we iterate throuth all the quotes to find potential values
    for(FX5__Quote__c cm : quotes) {

    // then we query SAP_Matrix__c using all of the values found above
    List<SAP_Matrix__c> matrixItems = [
        SELECT Id,
        FROM SAP_Matrix__c
        WHERE Bill_To_Address__c IN :billToAddresses AND
            Legal_Entity__c IN :legalEntities 

    // then we iterate through the matrix items and organise them into a map
    for(SAP_Matrix__c m : matrixItems) {
        // we create an string identifier that combines the values of Bill_To_Address__c and Legal_Entity__c
        String identifier = m.Bill_To_Address__c + m.Legal_Entity__c;
        identifierToId.put(identifier, m.Id);

    for(FX5__Quote__c cm : quotes) {
        // we then rebuild the identifier to retrieve the results
        String identifier = cm.Bill_To_Address__c + cm.Legal_Entity__c;
        // if there is a matching value the Id will be populated
        // if no matching value is found then the field will be empty
        cm.SAP_Matrix__c = identifierToId.get(identifier);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.