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
Frank van Meegen 42Frank van Meegen 42 

populate lookup via map

I'm looking for a way to populate a lookup field based on a similar text value on the source and target object.

I have an object child__c with a lookup to parent__c and a field color__c. The field color__c is also available on the parent__c object with unique values.

How can I query the child__c records and populate the lookup with the parent__c record with the same color in color__c in APEX?
Best Answer chosen by Frank van Meegen 42
Alain CabonAlain Cabon
Ok,  your problem is more clear indeed.

The minimal code could be: 
List<MyColor__c> mc = [select id,color__c from MyColor__c];
Map<String,Id> mcolor = new Map<String,Id>();
for (MyColor__c m:mc) {
    mcolor.put(m.color__c,m.id);
}
List<MyDependantColor__c> md = [select id,color__c,ParentMyColor__c from MyDependantColor__c where color__c in :mcolor.keySet() and ParentMyColor__c = null];
for (MyDependantColor__c m: md) {
    m.ParentMyColor__c = mcolor.get(m.color__c);
}
update md;

but that works only if you update a list with less than 10,000 rows or you must work by range of colors and you relaunch the anonymous code several times.

List<MyColor__c> mc = [select id,color__c from MyColor__c where color__c in ('Red','Blue')];
...
List<MyColor__c> mc = [select id,color__c from MyColor__c where color__c in ('Yellow,'Green')];
...
 

All Answers

Alain CabonAlain Cabon
Hi,

Is it a one shot initialization for all the child__c? (+ a trigger for the new children)

If you have more than 10,000 records (governor limit), you need a batch probably otherwise, it is easy with a simple loop in apex (anonymous code: https://help.salesforce.com/articleView?id=code_dev_console_execute_anonymous.htm&type=5 )

For a one-shot initialization task with a lot of records, the easy way is to export all the children with the dataloader:
select id, parent__r.color__c from child__c   
and you update all the same children with the dataloader using the previous exported data.
 
RKSalesforceRKSalesforce
Hi Frank:

Anonymous code is below, You can try batch if there are more than 10k records with same logic: 
List<Child__c> childListToUpdate = new List<Child__c>();
for(Child_c chld:[Select Id, color__c, Parent__r.Color__c from Child__c]){
	chld.color__c = parentIdAndColorMap.get(Parent__c);
	childListToUpdate.add(chld);
}
update childListToUpdate;
for Trigger Please use below code : 
Trigger updateColorOnChild on Color_c(before Insert, before Update){
        
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsBefore){
        For(Child__c col: Trigger.New){
            if(col.Parent__r.Color__c  != null){
                col.Color_c = col.Parent__r.Color__c;
				
            }  
        }
    }
}

Please mark as best answer if helped.

Regards,
Ramakant​
 
Frank van Meegen 42Frank van Meegen 42
Hi guys,

Thank you for your replies. I realize I have to ask my question more clear, please excuse me for that.

I have a parent__c record with for example:
Id : X
Color__c : Green

And onother with:

Id : Y
Color__c : Red

Whenever a new child__c record is inserted I need to populate the lookup with the corresponding parent record based on color:

Id : Z
Color__c : Green
ParentId__c : 
 
ParentId__c should be populated via a trigger/Class with in this case 'X' because the color__c green matched with parent__c record X. How can I achieve this with a trigger and preventing soql queries in a for loop?
Dmitry OfitserovDmitry Ofitserov
Hi Frank,
Try using the following: 
Trigger ChildObjectTrigger on child__c (before Insert, before Update){
    Map<String,String> colorsMap = new Map<String,String>();
    
    for(child__c childRecord : Trigger.new){
        colorsMap.put(childRecord.Color__c, '');
    }
    
    for(parent__c parentObject : [SELECT Id,Color__c FROM parent__c WHERE Color__c IN: colorsMap.keySet()]){
        colorsMap.put(parentObject.Color__c, parentObject.Id);
    }
    
    for(child__c childRecord : Trigger.new){
        childRecord.ParentId__c  = colorsMap.get(childRecord.Color__c);
    }
}
Alain CabonAlain Cabon
Ok,  your problem is more clear indeed.

The minimal code could be: 
List<MyColor__c> mc = [select id,color__c from MyColor__c];
Map<String,Id> mcolor = new Map<String,Id>();
for (MyColor__c m:mc) {
    mcolor.put(m.color__c,m.id);
}
List<MyDependantColor__c> md = [select id,color__c,ParentMyColor__c from MyDependantColor__c where color__c in :mcolor.keySet() and ParentMyColor__c = null];
for (MyDependantColor__c m: md) {
    m.ParentMyColor__c = mcolor.get(m.color__c);
}
update md;

but that works only if you update a list with less than 10,000 rows or you must work by range of colors and you relaunch the anonymous code several times.

List<MyColor__c> mc = [select id,color__c from MyColor__c where color__c in ('Red','Blue')];
...
List<MyColor__c> mc = [select id,color__c from MyColor__c where color__c in ('Yellow,'Green')];
...
 
This was selected as the best answer
Alain CabonAlain Cabon
In fact, we don't see the reponses sent in parallel while we are writting a response that is why Dmitry Ofitserov has almost exactly the same code as me and this trigger is suffisant including for the initialization.

A simple way to activate the Dmitry Ofitserov''s trigger for the initialization is to make a "dummy" update of all the records with the dataloader updating only the exported IDs of all the children (just the Ids in the file). This is needed only if you have hundreds of thousands rows
Frank van Meegen 42Frank van Meegen 42
Hi Alain,

Thank you for the example. This was exactly what I needed to understand map functionality. I was able to succesfully implement the solution.
 
Regards,

Frank