+ Start a Discussion
GuanDongGuanDong 

Include a field value in 'where' query clause

Hello!

Here's my code to update a lookup field value (Campagna_Account__c) on an Account object using a trigger that update it getting the value from a custom object (Sottocampagna__c):
trigger PopolaCampagna on Account (before insert, before update) {

    Sottocampagna__c var = [

        SELECT Campagna__r.Id
        FROM Sottocampagna__c
        WHERE Name='Segugio'
   
    ];

   for (Account a: Trigger.new) {
   a.Campagna_Account__c = var.Campagna__r.Id;

}

Now my question is: how can I substitute the value 'Segugio' with a variable that pick up the value of a particular field on Account object called Sottocampagna__r.Name?

I'm a newbie, and your answer let me learn tons of things...

Thank you so much
 
AMIT KAMBOJAMIT KAMBOJ
Hi ,

Please use below code and see if it works.
If this works please make sure to mark this answer as solution.
trigger PopolaCampagna on Account (before insert, before update) {

/*    Sottocampagna__c var = [

        SELECT Campagna__r.Id
        FROM Sottocampagna__c
        WHERE Name='Segugio'
   
    ];*/
Map<Id,Account> accmap = new map<id,account>([select Id, Name, Sottocampagna__r.Name, Sottocampagna__r.Campagna__r.Id from account Where id in :trigger.new.keyset()]);
    
   for (Account a: Trigger.new) {
   //a.Campagna_Account__c = var.Campagna__r.Id;
   a.Campagna_Account__c = accmap.get(a.Id).Sottocampagna__r.Campagna__r.Id;

}

 
GuanDongGuanDong
Hi there!

This error appears:

[Error] Error...: Method does not exist or incorrect signature: [List<Account>].keyset() row 10 column 145

Thanks a lot

 
Sameer PrasonnSameer Prasonn
Hi,

Since trigger.new return a list of sObject not a map. so first we collect all the Id and then proceed with the remaining code.
trigger PopolaCampagna on Account (before insert, before update) {

  Set<Id> accmap=new Set<Id>();
   for (Account a: Trigger.new) {
       accmap.add(a.Id);
   }

   Map<Id,Account> accmap2 = new map<id,account>([select Id, Name, Sottocampagna__r.Name, Sottocampagna__r.Campagna__r.Id from account Where id in :accmap]);

     

   for (Account a: Trigger.new) {
        a.Campagna_Account__c = accmap2.get(a.Id).Sottocampagna__r.Campagna__r.Id;
   }
}

If it resolve the query then please mask it as a solution. Please refer https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_context_variables.htm for more informattion about trigger context variables.
 
GuanDongGuanDong
Hi there, thanks a lot!

It seems to work, but with 1 exceptions:

2) Both on existing or new records it works, but I need to save 2 times the record. The first time it doesn't work because the field appears still empty, but then, when I press 'Edit' and then 'Save', trigger does updates the field.

What do you suggest?

Thanks a lot for your precious help.
 
Sameer PrasonnSameer Prasonn
Hi,
I believe we need to use the trigger once we have the account saved. in this case we need to use after insert trigger.  I hope this works fine and you need not to save a record twice. since you won't get records in 
Map<Id,Account> accmap2 = new map<id,account>([select Id, Name, Sottocampagna__r.Name, Sottocampagna__r.Campagna__r.Id from account Where id in :accmap]);

Since records are still not saved and we are firing SOQL on the same.
GuanDongGuanDong
Hi,

unluckly it doesn't work. Putting 'after insert' causes the following error message:

execution of AfterInsert caused by: System.FinalException: Record is read-only: Trigger.PopolaCampagna: line 13, column 1


Thank you so much

P.S.: I've received several mail from you with different solution, I've tried just the one shown here above.