function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
bozotheclownbozotheclown 

Getting Errors w AggregateResult Code

Hello.  I am trying to update a custom object "Inventory_Name_c" to reflect the number of work orders associated to that inventory name that have a specific status.

 

I typically would do this via some sort of workflow field update.  Unfortunately, I have to make this update via Apex due to a follow-on action that requires Apex.

 

I modeled the below code after a solution mentioned on this discussion board...but I am still having issues.  Specifically, I am getting an error "Error: Compile Error: Invalid bind expression type of SOBJECT:WorkOrders_c for column of type String at line 10 column 34" when I do a quick save.

 

Any thoughts on how to resolve this would be greatly appreciated.  Below is the code.

 

*******

  

trigger CountWOStatus on WorkOrders_c (before insert) {

       
    Map <String, Integer> WOCount = new Map <String, Integer> ();
   
    String WOid = null;
    Integer nbr = null;
   
    for (AggregateResult agr: [SELECT  Inv_Name_ID_c, count(Id) numOfWorkOrd FROM WorkOrders_c
        WHERE Inv_Name_ID_c in :Trigger.new
         AND (WOStatus_c = 'Initial Stage')
           GROUP BY Inv_Name_ID_c]){
           
        WOid = string.valueOf(agr.get('Inv_Name_ID_c'));
        nbr = integer.valueOf(agr.get('numOfWorkOrd'));
        WOCount.put (WOid, nbr);   
        }  
   
    try {
       
        for (Inventory_Name_c IndivWOUpdate : Trigger.new){
           
            if (Integer.valueOf(WOCount.get(IndivWOUpdate.Id)) != null){
               
                IndivWOUpdate.Count_of_Work_Ord_c = IndivWOUpdate.Count_of_Work_Ord_c + Integer.valueOf(WOCount.get(IndivWOUpdate.Id));
            }
           
            else IndivWOUpdate.Count_of_Work_Ord_c = IndivWOUpdate.Count_of_Work_Ord_c;
           
        } // end for IndivWOUpdate
       
    }// end try
   
    catch (System.Queryexception e){
       
        System.debug('WO Item Count ERROR:' + e);
       
    } // end catch
   
}

kbromerkbromer

The collection :Trigger.new is a collection of objects, right now, you're asking the SOQL query to compare what I assume is a string (Inv_Name_ID__c) to an sobject (Trigger.new). You can do that in an after update if it's an SF ID, but I'm guessing you're Inv Name field is not a Salesforce ID.

 

You can create a list of strings from the Trigger.new collection to use in your comparison earlier:

 

list<string> WOList = new list<string>();

for (WorkOrder__c WO : Trigger.new)

WOList.add(trigger.new.<mystringfieldname>);

 

Replace <mystringfieldname> with the name of the string field in your object you want to use.  Be careful, if that field is null, you'll cause a null reference error to be thrown, so you may want to check for that beforehand. Then use the list of the strings WOList to bind in the query. 

bozotheclownbozotheclown

Thanks.  I will give that a try.  One comment though - "Inv_Name_ID__c" is actually a salesforce.com ID (via a formula field, I am updating each work order record with the corresponding ID for the Inventory Item name).  The reason for doing that was because I thought I had to compare IDs.

 

Thanks...as you can see, I am still learning about Apex.

kbromerkbromer

Off the top of my head, I'm pretty sure that formula field will be evaluated as a string, but I understand why that's confusing. 

bozotheclownbozotheclown

Again, thanks for the help.

 

That said, I am still struggling on what pieces of  my initial code should be removed and replaced by your recommendation.  I have made a couple of attempts at what I think should be done...but continue to get errors.

 

Thoughts?

 

Thanks again.

kbromerkbromer

THIS: 

 

    for (AggregateResult agr: [SELECT  Inv_Name_ID_c, count(Id) numOfWorkOrd FROM WorkOrders_c
        WHERE Inv_Name_ID_c in :Trigger.new
         AND (WOStatus_c = 'Initial Stage')
           GROUP BY Inv_Name_ID_c]){
           
        WOid = string.valueOf(agr.get('Inv_Name_ID_c'));
        nbr = integer.valueOf(agr.get('numOfWorkOrd'));
        WOCount.put (WOid, nbr);   
        }  

 

 

SHOULD BE:

 

list<string> WOList = new list<string>();
for (WorkOrder__c WO : Trigger.new){
if (wo.WOStatus__c != null)
WOList.add(wo.WOStatus__c);
}

    for (AggregateResult agr: [SELECT  Inv_Name_ID_c, count(Id) numOfWorkOrd FROM WorkOrders_c
        WHERE Inv_Name_ID_c in :WOList
         AND (WOStatus_c = 'Initial Stage')
           GROUP BY Inv_Name_ID_c]){
           
        WOid = string.valueOf(agr.get('Inv_Name_ID_c'));
        nbr = integer.valueOf(agr.get('numOfWorkOrd'));
        WOCount.put (WOid, nbr);   
        }  

 

A list or set will work for the collection of strings.

 

 

 

 

 

bozotheclownbozotheclown

Thanks.  That makes sense.

 

However, I just encountered a separate issue.  The line starting with the code "for (Inventory_Name_c IndivWOUpdate : Trigger.new)" is forcing the error "Loop variable must be of type SOBJECT:WorkOrders_c".

 

I assume that this is being caused because the trigger is being done on the "WorkOrders_c" object.  So my question is...is there any way I can do a FOR loop on the "Inventory_Name_c" object.  The end goal of this whole trigger is to populate the "Count_of_Work_Ord_c" field in each respective "Inventory_Name_c" object with the current number of work orders (and this must be done as the work orders are added).

 

I appreciate any further thoughts.

kbromerkbromer

Your assumption is close, it's that the FOR loop is being run on the WorkOrders__c object.   You can do a for loop on any type of object you'd like, including the string value from that field. You wouldn't want to do it directly on the trigger value though without checking to make sure the value exists, as if you hit a null 'Inventory Name' field, you'll cause an error.  Your best bet is to create a list of valid strings and loop through that if you wanted to change the operation of the loop.