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
R TyeR Tye 

Bulk Trigger on a SOQL Count() statement

Hello!  I'm trying to bulk trigger the following query:

for(Case TLnew : Trigger.new) {

High = TLnew.Value__c + 10;
Low = TLnew.Value__c -10 ;

Integer i = [SELECT count() FROM Case x WHERE x.Party1__c = :TLnew.Party1__c  AND x.Party2__c = :TLnew.Party2__c AND x.Value__c <= :High AND x.Value__c >= :Low AND ID != :TLnew.ID ];

TLnew.Similar__c = i;

}

Basically I want to return the number of cases that are similar to the triggering case and update that trigger's Similar__c with that number.  This includes cases with the same party values and within a +/- 10 range of a number.

The following works fine above, but obviously the SOQL is written into the loop.  I can't figure out how to bulk the trigger; I tried storing all the trigger objects in a map, but I can't determine how to iterate over it with the above query conditions.  I could store all the trigger variables in a set, but then I lose the 'AND' conditional link between objects.

Is there a solution to the above?
David VPDavid VP

Looks like a real catch-22.

I don't know if this will fit your requirements but if you just need to display this, you could perhaps inline a small VF page in your Cases detail page that will calculate and display this information ?


David
arnt72arnt72
I ran into similar problems. Here is a possible approach which is basically "the other way round". You start with a bulk query on the existing cases and then iterate through the Trigger data. Haven't tested it and the code is qad, please let me know if you could make it work:
  • do a bulk query for all cases
  • within that bulk query iterate through the Trigger cases and compare using an if statement. Statements are also limited per trigger but the number increases with bulk queries - see http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm note 4
it would look similar to this:

Code:
for (Case c: [select ID, Party1__c, Party2__c, Value__c from Case]){

   for (Case TLNew: Trigger.New){
     High = TLNew.Value__c + 10;
     Low = TLNew.Value__c -10;
     if( TLNew.Party1__c = c.Party1__c && TLNew.Party2__c = c.Party2__c && further criteria){
         TLNew.Similar__c ++ 1;
     }
   }
}

 








Message Edited by arnt72 on 10-13-2008 04:40 PM
R TyeR Tye
Thanks for the responses.  Arnt72, I was thinking the same thing.  I'm going to try a bulk query on the cases (sub-set of them) and return the count of rows that match the criteria for each trigger and update.  I'll post my results if I get it working. 

The other half of the problem is that the results I return that match, I would like to place in a map and update them with the same "similar" number as the other cases. 
arnt72arnt72
have you considered putting all of them in the same Map in an after insert trigger, loop through the map and then do an update?