Apex Code Development You need to sign in to do that
Don't have an account?
TROG Multi-field select statement/join for trigger
I have a custom object called AU_c with a decimal field called detail__c, that I'd like to roll-up. There are three fields/attributes on this object that will be considered for helping with categorization and roll-ups. Call them X, Y, Z for simplicity. I've added a new field to AU__c called Total_Y_Z__c, and I want to develop a trigger that after insert/update/delete sums up all AU__c records with the same Y and Z attributes and updates all AU__c objects with the same Y and Z attributes with the new total - not just the one(s) that were in the trigger.
Is there a SOQL statement, or "work-around", that allows me to select records according to a specific combination of fields? In this case I want to select all records from AU__c where Y and Z = trigger Y and Z. I could loop through each trigger.new/old and perform the standard select ...where Y=triggerY and Z=triggerZ, but I understand best practice states NOT to put SOQL within a loop. Is there a way to write a SOQL statement to handle multiple unique combinations of fields? If not, is there a recommended way of accomplishing my goal?
Thanks!
Is there a SOQL statement, or "work-around", that allows me to select records according to a specific combination of fields? In this case I want to select all records from AU__c where Y and Z = trigger Y and Z. I could loop through each trigger.new/old and perform the standard select ...where Y=triggerY and Z=triggerZ, but I understand best practice states NOT to put SOQL within a loop. Is there a way to write a SOQL statement to handle multiple unique combinations of fields? If not, is there a recommended way of accomplishing my goal?
Thanks!
I think you can try creating Formula fields:
Create a formula field of text type which holds your desired string based on your conditions., for eg.,
assume the api name of formula field is formula__c
If(Y=='somevalue && x=='Somevalue', 'Condition 1',If(z=='Somevalue'&&y=='Somevalue','Condition2','No Condition Met'))
By this formula you can have different values in the formula field based on your condition. Now you can query the records based on this fields excluding the ids on which the trigger is fired currently.
So you can have counts in one query like this:
[select count(id)recordsCount,formula__c from yourobject__c where id not in :trigger.newMap.keyset() group by formula__c];
now you can parse through the above aggregrate result and use them accordingly.
For more info on using aggregrate result:
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
Hope it helps;
Thanks,
balaji
All Answers
I think you can try creating Formula fields:
Create a formula field of text type which holds your desired string based on your conditions., for eg.,
assume the api name of formula field is formula__c
If(Y=='somevalue && x=='Somevalue', 'Condition 1',If(z=='Somevalue'&&y=='Somevalue','Condition2','No Condition Met'))
By this formula you can have different values in the formula field based on your condition. Now you can query the records based on this fields excluding the ids on which the trigger is fired currently.
So you can have counts in one query like this:
[select count(id)recordsCount,formula__c from yourobject__c where id not in :trigger.newMap.keyset() group by formula__c];
now you can parse through the above aggregrate result and use them accordingly.
For more info on using aggregrate result:
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
Hope it helps;
Thanks,
balaji
That being said, there is also an excellent tool on the App Exchange called "Rollup Helper", if you haven't already check it out here: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI
If you still want to roll this on your own, there are several examples of rolling up children to parents if you search around on google. The gist is, every time you update, delete, insert a child record, you'd perform a SOQL on the parent object (and bring in the children that you're rolling up via a subquery), then perform some Apex around the SOQL to calculate the value you'd want.
This example uses an aggregate query (e.g. Group By, Max, Min, etc), but you'd likely want to use a child subquery instead, and then calculate the rollup value based on "X, Y, and Z". http://www.anthonyvictorio.com/salesforce/roll-up-summary-trigger/
Note: it's not ideal though, b/c I'd hate to continue adding temp-working fields to my objects when/if I could handle the problem programatically. I assume there is a way I can perhaps create this string concatenation of Y and Z "on the fly" in the program, and perhaps add it to a map of (YZ, AU__c)....but I haven't quite figured out how to use maps. Perhaps an area I should spend time studing to figure it out.
James - I've worked alot w/ master-detail roll-ups and also currently use Rollup Helper. I likely confused you by using the word rollup in my question/request, where my question is more of an aggregation within the same object (not across different objects via any form of relationship). Detail__c (what I'm aggregating) and Total_Y_Z__c (where I'm putting the aggregation of all AU__c objects with the same Y and Z attributes) are both on the same object AU__c.
As per the question i assumed that you need to query all the records every time you need to update the counts on parent record based on the conditions. But later realized that, first you can update the records with appropriate counts and then write a trigger just to maintian them, in that case you doesnt need to query for exisitng records. All you need to do is: based on the trigger event, old values and new values in trigger context you need to update the parent records with appropriate counts for eg.,
In case of insert you need to update the count of parent just by adding to the current value of the parent.
In case of update, you need to recalculate the change in condition and decide whether to increment or decrement from the parent,
In case of reparenting(changing the parent), then you need to decrement the count in old parent and increment the count of the new parent.
In case of delete, you need to decrement the count of the parent
In case of undelete, you need to add the count back to the parent
To bulkify you can defintely use maps. In trigger first you need to collect the parent ids from both old and new tigger maps, add them to a set. you can fire one query to get the counts and other details from the parent record. Then create a map with id of record and the count you need to update with. Loop through the old and new child records on which the trigger got fired, maintain the values in the map based on your condition logic. after the loop, update the parent records with their updated counts.
Using the concepts of maps is quite easy, all it has is two properties, first one is key(unique) and the other is value. Key can be of any primitive type and value can be of any type(including object). Two methods put and get are used to insert a value to map or fetch the value from map. you can find an example in detail in the below link:
https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_collections_maps.htm
Different methods of map can be found here:
http://www.salesforce.com/us/developer/docs/dbcom_apex250/Content/apex_methods_system_map.htm
Coming to your problem with formula fields, formula fields will have null or previous value in case of before triggers and will have the calculated value ready in case of after event triggers.
Hope it helps:
Thanks,
balaji