+ Start a Discussion
Lago S.p.a.Lago S.p.a. 

SObject row was retrieved via SOQL without querying the requested field :(

Hi everybody, can someone help me with this simple trigger?
I want to assign in a lookup field of a custom object, an account name.
This object has a lookup fielld Contract__c.
Here's the trigger:

trigger AssignAccountProjectTrigger on Progetti__c (after insert) {
    for (Integer i = 0; i < Trigger.new.size(); i++){
        List <Progetti__c> prg = [select p.Id
            from Progetti__c p where p.Id = :Trigger.new[i].Id];
        if (prg.size() == 0 || prg.size() >1)
           return;
        else{
            Progetti__c p=prg.get(0);
            List <Account> acc = [select a.Id,a.Name from Account a where a.Id=:p.contract__r.Account.Id];
            Account cliente=acc.get(0);
            p.account__c = cliente.Id;
            update p; 
        }   
    }
}

Thank you in advance.
 
Best Answer chosen by Lago S.p.a.
Amit Chaudhary 8Amit Chaudhary 8
Please try below code :-
 
trigger AssignAccountProjectTrigger on Progetti__c (after insert) 
{
    for (Integer i = 0; i < Trigger.new.size(); i++)
	{
        List <Progetti__c> prg = [select id, account__c,contract__r.AccountId from Progetti__c  where Id = :Trigger.new[i].Id];
        if (prg.size() == 0 || prg.size() >1)
		{
           return;
        }
		else
		{
            Progetti__c p=prg.get(0);
            List <Account> acc = [select a.Id,a.Name from Account a where a.Id=:p.contract__r.AccountId ];
            Account cliente=acc.get(0);
            p.account__c = cliente.Id;
            update p; 
        }   
    }
}

 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Please try below code :-
trigger AssignAccountProjectTrigger on Progetti__c (after insert) 
{
    for (Integer i = 0; i < Trigger.new.size(); i++)
	{
        List <Progetti__c> prg = [select id, account__c from Progetti__c  where Id = :Trigger.new[i].Id];
        if (prg.size() == 0 || prg.size() >1)
		{
           return;
        }
		else
		{
            Progetti__c p=prg.get(0);
            List <Account> acc = [select a.Id,a.Name from Account a where a.Id=:p.contract__r.Account.Id];
            Account cliente=acc.get(0);
            p.account__c = cliente.Id;
            update p; 
        }   
    }
}

Please mark this as solution if this will help you
 
Lago S.p.a.Lago S.p.a.
Hi Amit, unfortunatelly your solution doesn't work..here's the error:
AssignAccountProjectTrigger: execution of AfterInsert caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Progetti__c.contract__r: Trigger.AssignAccountProjectTrigger: line 13, column 1.
If you have any other tricks let me know!
Thanks
Amit Chaudhary 8Amit Chaudhary 8
Please try below code :-
 
trigger AssignAccountProjectTrigger on Progetti__c (after insert) 
{
    for (Integer i = 0; i < Trigger.new.size(); i++)
	{
        List <Progetti__c> prg = [select id, account__c,contract__r.AccountId from Progetti__c  where Id = :Trigger.new[i].Id];
        if (prg.size() == 0 || prg.size() >1)
		{
           return;
        }
		else
		{
            Progetti__c p=prg.get(0);
            List <Account> acc = [select a.Id,a.Name from Account a where a.Id=:p.contract__r.AccountId ];
            Account cliente=acc.get(0);
            p.account__c = cliente.Id;
            update p; 
        }   
    }
}

 
This was selected as the best answer
pigginsbpigginsb
There are a few things I notice in your trigger. First, you are trying to traverse the contract relationship, using Contract__r.AccountId in where clause without having queried for this field earlier in your trigger. I also see that you are including two queries and an update statement inside your for loop. Even if the assumption is that the trigger should only ever see one record at a time, it is a best practice to avoid doing this.

As far as what the trigger needs to do, it looks like you want to assign the Account Id from a Progetti's Contract to the Account__c field on the Progretti record itself, since you are updating the same Progretti record that caused the trigger to fire in the first place. This can typically be done with a before trigger, as the record is already on its way into the database, and by writing a value to the record at this time there is no need to perform any update dml in the trigger.

However, since this is a before insert trigger, you will not be able to query for the Progetti record, but you should be able to query for the Contract records whose Ids are in the Progretti's Contract__c field.

Please see the code below:

trigger AssignAccountProjectTrigger on Progetti__c (before insert) {
    
    // collect contract Ids from trigger.new into set in order to query for their AccountId values
    Set<Id> progettiContractIdSet = new Set<Id>();
    
    for (Progetti__c each : trigger.new)
        progettiContractIdSet.add(each.Contract__c);
    
    // put query for contracts directly in a map constructor, to have contract id as the key and the Contract__c record itself as the value
    // this query is outside of the for loop, to avoid potential for too many SOQL queries in a transaction
    Map<Id, Contract__c> progettiContractMap = new Map<Id, Contract__c>([select AccountId from Contract__c where Id in :progettiContractIdSet]);
    
    for (Progetti__c each : trigger.new) {
        // check that the map contains the Progretti's Contract__c value before attempting to assign Progetti's Account__c value, in case Progetti has no Contract__c
        if (progettiContractMap.containsKey(each.Contract__c)) {
            each.Account__c = progettiContractMap.get(each.Contract__c).AccountId;
        }
    }
    
    // no need to include an update statement in this before trigger, as the Progetti records in trigger.new continue into the database with their new Account__c values
}
Lago S.p.a.Lago S.p.a.
Hi Amit, thanks for your answer. Now my issue is solved in a quick way! Very good, thanks and have a nice day :)
@ bjpiggins : thank you . Next time I'll try to follow your instructions, I'm a new entry in the SF developers and I'm trying to do the best results with the simple way of coding ;)
Thanks.