0

I am trying to write a trigger, which counts the number of open jobs under one account. I am just a begginer and the code I made is based on a similar trigger for tasks.

I get the following Error message:

(Select AVTRRT__Stage__c From AVTRRT__Jobs__r Where AVTRRT__Stage__c ^ ERROR at Row:1:Column:60 Didn't understand relationship 'AVTRRT__Jobs__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names

and

Variable does not exist: AVTRRT__Jobs__r

The code is the following:

trigger CountOpenJobs on AVTRRT__Job__c (after insert, after undelete, after delete, after update) {    
    Set<Id> setOpenJobAccountIds = new Set<Id>();
    Set<Id> setClosedJobAccountIds = new Set<Id>();
    List<Account> listAccountUpdate = new List<Account>();
    List<Account> listOpenJobAccounts = new List<Account>();
    List<Account> listClosedJobAccounts = new List<Account>();
    if(Trigger.IsAfter){
        if(Trigger.IsInsert || Trigger.IsUndelete  || Trigger.isUpdate) {
            for(AVTRRT__Job__c t: Trigger.new) {
                if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                    if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                        setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        System.debug('@@@@Inside Completed@@@'+setClosedJobAccountIds);
                    }
                    else {
                        setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        System.debug('@@@@Inside Open@@@'+setOpenJobAccountIds);
                    }
                    if(Trigger.IsUpdate){
                        if(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c != t.AVTRRT__Account_Job__c){
                            if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                                setClosedJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                                System.debug('@@@Inside Update Closed@@@'+setClosedJobAccountIds);
                            }
                            else {
                                setOpenJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                                System.debug('@@@Inside Update Open@@@'+setOpenJobAccountIds);
                            }
                        }
                    }
                }
            }
        }        
        if(Trigger.IsDelete){
            for(AVTRRT__Job__c t : Trigger.Old){
                if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                    if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                        setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        System.debug('@@@@Inside Deleted Completed');
                    }
                    else {
                        setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        System.debug('@@@@Inside Deleted Open');
                    }
                }
            }
        }            
        listOpenJobAccounts =[SELECT Id, Name, Open_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Jobs__r Where AVTRRT__Stage__c != 'Open' ) FROM Account WHERE Id =:setOpenJobAccountIds];
        listClosedJobAccounts =[SELECT Id, Name, Closed_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Jobs__r Where AVTRRT__Stage__c = 'Open' )  FROM Account WHERE Id =:setClosedJobAccountIds];
        System.debug('@@@@ListOpenJobAccounts'+listOpenJobAccounts );
        System.debug('@@@@ListClosedJobAccounts'+listClosedJobAccounts );       
        for(Account acc:listOpenJobAccounts) {
            List<AVTRRT__Job__c> listJob = acc.AVTRRT__Jobs__r;
            acc.Open_Job__c = listJob.size();
            listAccountUpdate.add(acc);           
        }
        for(Account acc:listClosedJobAccounts) {
            List<AVTRRT__Job__c> listJob = acc.AVTRRT__Jobs__r;
            acc.Closed_Job__c = listJob.size();
            listAccountUpdate.add(acc);           
        }
        try{
            System.debug('@@@@listAccountUpdate'+listAccountUpdate);
            update listAccountUpdate;
        }catch(System.Exception e){
            System.debug('@@@error'+e.getMessage());
        }
    }
}
6
  • Make sure you are using the correct child relationship API name in your inner query. you can find the child relationship API name by going into fields of your AVTRRT Job object and then find the lookup field for an account and open it to see the child relationship API name. Feb 8, 2021 at 8:40
  • There are some other improvements you can do to your code. Before post-processing or doing soql queries always check if you have data that meets the criteria. In your case check if listOpenJobAccounts and setClosedJobAccountIds is not empty then post-process. Another thing you should only query records for an account that are in your set (listOpenJobAccounts, setClosedJobAccountIds) because these are the once which meet the criteria. Feb 8, 2021 at 8:51
  • Thanks a lot Bilal for the comment. I have changed it accordingly, the API name stated there is AVTRRT__Account_Job__c, but I am still receiving the same error code :( I will amend the improvements you have suggested, thanks Feb 8, 2021 at 11:47
  • so did you updated api name in your apex trigger in SOQLs. Your soql should be like this [SELECT Id, Name, Open_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Account_Job__r Where AVTRRT__Stage__c != 'Open' ) FROM Account WHERE Id =:setOpenJobAccountIds]; Feb 8, 2021 at 12:07
  • Instead of AVTRRT__Jobs__r change it to AVTRRT__Account_Job__r Feb 8, 2021 at 12:08

1 Answer 1

0

if your child relationship API name is AVTRRT__Account_Job__c then you need to modify the API name in your inner SOQL query and for loops where you are setting open and close job on account with AVTRRT__Account_Job__r

Your code should something like this.

trigger CountOpenJobs on AVTRRT__Job__c (after insert, after undelete, after delete, after update) {    
    Set<Id> setOpenJobAccountIds = new Set<Id>();
    Set<Id> setClosedJobAccountIds = new Set<Id>();
    List<Account> listAccountUpdate = new List<Account>();
    List<Account> listOpenJobAccounts = new List<Account>();
    List<Account> listClosedJobAccounts = new List<Account>();
    if(Trigger.IsAfter){
        if(Trigger.IsInsert || Trigger.IsUndelete  || Trigger.isUpdate) {
            for(AVTRRT__Job__c t: Trigger.new) {
                if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                    if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                        setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                    }
                    else {
                        setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                    }
                    if(Trigger.IsUpdate){
                        if(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c != t.AVTRRT__Account_Job__c){
                            if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                                setClosedJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                            }
                            else {
                                setOpenJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                            }
                        }
                    }
                }
            }
        }        
        if(Trigger.IsDelete){
            for(AVTRRT__Job__c t : Trigger.Old){
                if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                    if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                        setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                    }
                    else {
                        setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                    }
                }
            }
        }
        if(!listOpenJobAccounts.isEmpty())
            listOpenJobAccounts =[SELECT Id, Name, Open_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Account_Job__r Where AVTRRT__Stage__c != 'Open' ) FROM Account WHERE Id =:setOpenJobAccountIds];
        if(!listClosedJobAccounts.isEmpty())
            listClosedJobAccounts =[SELECT Id, Name, Closed_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Account_Job__r Where AVTRRT__Stage__c = 'Open' )  FROM Account WHERE Id =:setClosedJobAccountIds];
        
        for(Account acc:listOpenJobAccounts) {
            acc.Open_Job__c = acc.AVTRRT__Account_Job__r.isEmpty()? 0 : acc.AVTRRT__Account_Job__r.size();
            listAccountUpdate.add(acc);           
        }
        for(Account acc:listClosedJobAccounts) {
            acc.Closed_Job__c = acc.AVTRRT__Account_Job__r.isEmpty()? 0 : acc.AVTRRT__Account_Job__r.size();
            listAccountUpdate.add(acc);           
        }
        try{
            if(!listAccountUpdate.isEmpty()) update listAccountUpdate;
        }catch(System.Exception e){
            System.debug('@@@error'+e.getMessage());
        }
    }
}.

    trigger CountOpenJobs on AVTRRT__Job__c (after insert, after undelete, after delete, after update) {    
        Set<Id> setOpenJobAccountIds = new Set<Id>();
        Set<Id> setClosedJobAccountIds = new Set<Id>();
        List<Account> listAccountUpdate = new List<Account>();
        List<Account> listOpenJobAccounts = new List<Account>();
        List<Account> listClosedJobAccounts = new List<Account>();
        if(Trigger.IsAfter){
            if(Trigger.IsInsert || Trigger.IsUndelete  || Trigger.isUpdate) {
                for(AVTRRT__Job__c t: Trigger.new) {
                    if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                        if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                            setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        }
                        else {
                            setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        }
                        if(Trigger.IsUpdate){
                            if(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c != t.AVTRRT__Account_Job__c){
                                if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                                    setClosedJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                                }
                                else {
                                    setOpenJobAccountIds.add(Trigger.oldMap.get(t.Id).AVTRRT__Account_Job__c );
                                }
                            }
                        }
                    }
                }
            }        
            if(Trigger.IsDelete){
                for(AVTRRT__Job__c t : Trigger.Old){
                    if(String.valueOf(t.AVTRRT__Account_Job__c).startsWithIgnoreCase('001')) {
                        if(t.AVTRRT__Stage__c.equalsIgnoreCase('Open')){
                            setClosedJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        }
                        else {
                            setOpenJobAccountIds.add(t.AVTRRT__Account_Job__c);
                        }
                    }
                }
            }
            if(!listOpenJobAccounts.isEmpty())
                listOpenJobAccounts =[SELECT Id, Name, Open_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Account_Job__r Where AVTRRT__Stage__c != 'Open' ) FROM Account WHERE Id =:setOpenJobAccountIds];
            if(!listClosedJobAccounts.isEmpty())
                listClosedJobAccounts =[SELECT Id, Name, Closed_Job__c,(Select AVTRRT__Stage__c From AVTRRT__Account_Job__r Where AVTRRT__Stage__c = 'Open' )  FROM Account WHERE Id =:setClosedJobAccountIds];
            
            for(Account acc:listOpenJobAccounts) {
                acc.Open_Job__c = acc.AVTRRT__Account_Job__r.isEmpty()? 0 : acc.AVTRRT__Account_Job__r.size();
                listAccountUpdate.add(acc);           
            }
            for(Account acc:listClosedJobAccounts) {
                acc.Closed_Job__c = acc.AVTRRT__Account_Job__r.isEmpty()? 0 : acc.AVTRRT__Account_Job__r.size();
                listAccountUpdate.add(acc);           
            }
            try{
                if(!listAccountUpdate.isEmpty()) update listAccountUpdate;
            }catch(System.Exception e){
                System.debug('@@@error'+e.getMessage());
            }
        }
    }
2
  • Thanks a lot Bilal, I have pasted it, but the error is still the same :( I wrote a message to TargetRecruit, as the Object is made by them, to check if I should use different API Feb 8, 2021 at 13:58
  • Hi Bilal , it seems that the name of the child object was different. Thanks for the help. I still have an issue, if you have time, thanks for checking it: salesforce.stackexchange.com/questions/335094/… Feb 23, 2021 at 19:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .