How to use value from a SOQL Query within a For Loop?

Question

I have an after trigger that runs when a new opportunity is created. My end goal is to select the Salesforce Record ID value from another type of Opportunity and use that value to update a custom field on another opportunity record of a different record type.

I have multiple record types on the opportunity object.

The last loop in my code doesn't work obviously, but I am trying to use the Opportunity__c values returned in "custOpps" inside of that for loop at the bottom so I can copy the value into the "Link_To_Related_Opportunity__c" field

  static Id customerOpportunityRecordTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('Customer').getRecordTypeId();

  Set<Id> custOppIds = new Set<Id>();
  Set<String> custInvoiceIds = new Set<String>();
  List<Opportunity> custOpps = new List<Opportunity>();
 List<Opportunity> oppRecordsToUpdate= new List<Opportunity>();

public void OnAfterInsert(List<Opportunity> newOpportunityList) {

  for(Opportunity opp : newOpportunityList) {
            if( opp.RecordTypeId == customerOpportunityRecordTypeId && opp.Invoice_ID__c != '') {
                custInvoiceIds.add( opp.Invoice_ID__c );
                custOppIds.add( opp.Id );
            }
        }

// other type of opportunity
custOpps = [SELECT Id, name, Opportunity__c, Invoice_ID__c FROM Invoices__c WHERE Invoice_ID__c = : custInvoiceIds ];

// loop over new opportunity records again
for(Opportunity opp : newOpportunityList) {
  Opportunity oppToUpdate = new Opportunity();
  oppToUpdate.Id = opp.Id;
  oppToUpdate.Link_To_Related_Opportunity__c = custOpps.Opportunity__c // this is the part where I need assistance
  oppRecordsToUpdate.add(oppToUpdate);
}

update oppRecordsToUpdate;


} // end method

David Reed · Accepted Answer · 2020-02-17 19:49:44Z

        if( opp.RecordTypeId == customerOpportunityRecordTypeId && opp.Invoice_ID__c != '') {

You should compare to null or use String.isBlank(). Empty strings are not stored in the database.

custOpps = [SELECT Id, name, Opportunity__c, Invoice_ID__c FROM Invoices__c WHERE Invoice_ID__c = : custInvoiceIds ];

Here, you need to create a Map<String, Invoice__c>. (I'm assuming Invoice_Id__c is a String value). Iterate over the result of this query and place each record into the Map, keyed on its Invoice Id.

Then, here,

  oppToUpdate.Link_To_Related_Opportunity__c = invoiceMap.get(opp.Invoice_Id__c);

you use the Map to get your related record Id without iterating over the list.

As Adrian rightly points out in the comments, if Invoice_Id__c is not an External Id field with a uniqueness constraint applied, you should be prepared to handle the situation where multiple Invoices__c records are returned for a single Id.

Worth noting potential collisions.

Adrian Larson
– Adrian Larson ♦

2020-02-17 19:42:20 +00:00
Commented Feb 17, 2020 at 19:42 — Adrian Larson
– Adrian Larson ♦, Commented Feb 17, 2020 at 19:42

Stack Exchange Network

How to use value from a SOQL Query within a For Loop?

1 Answer 1

You must log in to answer this question.

Hot Network Questions