Update Lookup field based on a matching value in another field

Question

Very new to apex and hoping someone can guide me on the best way to approach this problem

Object: ObjA__c
 - Field: LookupToObjB__c (lookup) (field to update)
 - Field: JobNumber__c (string)


Object: ObjB__c
 - Field: JobNumber__c (string)

The goal is to update the ObjA__c.LookupToObjB__c field if there is a matching JobNumber__c between the two objects.

The process is going to be scheduled (weekly) and as there will be a high number of objects to update it will need to be batchable as well.

I should be OK with the schedulable/batchable part, its just the logic regarding finding matching records/updating the lookup i'm not sure about.

Any pointers would be greatly appreciated.

Answer 1

It sounds like you're looking for the Aggregate-Query-Update pattern, which is something I find myself using almost constantly.

At a high level, what you're looking to do here is:

• Figure out which Job Numbers you need to search for (based on the records in the current scope of your batch job)
• Use those to query for records of the other object, and store those in a Map (where they key is the job number, and the value is the record Id)
• Iterate over your batch job's scope, and see if there is a match in your map (then add the record to a separate list of items to be updated)

If you aren't already aware, we're highly encouraged to work on collections of records instead of working (queries, dml) on one record at a time. The key here is that we have the Map class which acts like a Dictionary/Associative Array/Key-Value store.

A quick example in pseudocode

Map<String, Id> jobNumToId = new Map<String, Id>();
List<MyObject__c> recordsToUpdate = new List<MyObject__c>();

// This set isn't strictly required (we could use the map we previously defined)
//   but keeping things separate like this probably makes things easier to read
Set<String> jobNumSet = new Set<String>();

for(MyObject__c obj : <batch scope list here>){
    add obj job number to jobNumSet (for later use)
}

// Queries inside of loops are bad, but using a query like this to feed a loop
//   is fine, and actually generally encouraged
// This is called a "SOQL for loop", and it allows Salesforce to efficiently
//   process the query results (in chunks, if there are enough records returned)
// The "IN :jobNumSet" part of the query, specifically the ":jobNumSet", is called
//   a "variable bind", and allows us to inject variables into the query
for(OtherObject__c obj :[SELECT Id, JobNum FROM OtherObject__c WHERE JobNum IN :jobNumSet]){
    jobNumToId.put(obj job num, obj Id);
}

// Now that we have our map populated, time to see if there's anything to update
for(MyObject__c obj : <batch scope list here>){
    if our jobNumToId map contains the jobNum for this MyObject__c record{
        obj.lookup_field__c = jobNumToId.get(jobNum);
        recordsToUpdate.add(obj);
    }
}

// Salesforce is kind enough to automatically detect if we pass an empty list
//   for insert/update/delete, and not attempt to perform the DML operation if
//   it is empty.
// No need to check that yourself (as long as you make sure that the list is not
//   null (initializing the list makes it not null)
update recordsToUpdate;