Apex Code Development You need to sign in to do that
Don't have an account?
cpo87 Proper syntax to do multiple where's in a SOQL Count query
I have a trigger that works when I limit the Count query to only 1 where statement. I am trying to figure out how to make it work with multiple criteria.
Code that works...
Integer accounts = [SELECT COUNT()FROM Account WHERE (Account.ownerId =: userInfo.getUserId())];
Code that doesn't work...
Integer accounts = [SELECT COUNT()FROM Account WHERE (Account.ownerId =: userInfo.getUserId()) AND (Account_Status__c NOT IN ('Current Customer', 'Seasonal - On', 'Seasonal - Off')) AND (Enb_Churn_Exclusion__c != null)];
Any ideas?
Thanks,
Christian
What's the error you are getting exactly?
i'm not actually getting an error, it is just that the trigger isn't firing when all conditions are met.
Have you tried just running that query in the system log or execute anonymous while hardcoding a userID to check that you are getting a good result from the query?
No I haven't, I guess I don't know how those work. I've tried the system log but I never figured out how to get anything usable out of it and I've never heard of executing anonymous. I'll try to figure those out.
Open up the System Log and try something like this
Integer accounts = [SELECT COUNT()FROM Account WHERE OwnerId ='PUT SOME USER ID HERE' AND Account_Status__c NOT IN ('Current Customer', 'Seasonal - On', 'Seasonal - Off') AND Enb_Churn_Exclusion__c != null];system.debug('*****account count***** '+accounts);
Then open up the log file it gives you and see what the count is. Then you can tweak the query parameters until you know you have it right.
I have a feeling one of your WHERE parameters is just making the query too restrictive.
Nice, I can now see that the code that isnt working is coming up with a count of 0 accounts were as the count with less conditions is showing 111 accounts which is the correct number I want to see. Because of that I was able to see what was wrong, It turned out that not all the conditions were being met, go figure. Thanks for your help David!
Any idea how if I had 75 accounts that met the count criteria and only wanted to allow a max of 100 but was trying to add 30 accounts into the system that fit this criteria I could have it allow the first 25 and error on the last 5?
Thanks,
Christian
Should be possible to do it, but it won't be "simple" I don't think.
Probably something along the lines of...
1. Loop through accounts being inserted/updated and create a Map with OwnerIds as the key and an integer as the value (initially set to null).
2. Get an AggregateResult of counts grouped by OwnerId using the keyset of your Map in the WHERE clause.
3. Loop through the Aggregate result and put the count for each owner into your map.
4. Loop through your accounts being inserted/updated again and increment the count in your map by one for each record for each owner, checking to make sure you're not passed your limit yet.
5. If you are at/over your limit, do something to the record (flag a custom field, add an error, etc.)
Not 100% positive. Just "thinking out loud".
Thanks David!
You're certainly welcome. Let me know how it goes and what you end up doing if you implement your "limit". I'm curious now.