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
Leah MorganLeah Morgan 

Send an email when cases hit certain criteria

Hello,

I am attempting to set up a workflow process that will send me and a few other admins an email when the number of cases for a certain location hits 7 within 7 days.  I was looking at this, and it seems to be somewhat close to what I am tring to do, but not quite.

If anyone has any suggestions I would appreciate it!

Thank you!
Best Answer chosen by Leah Morgan
RaidanRaidan
Ah, I thought you needed help tweaking the code from the other post. So, here are the complete steps you can do:
  1. In Case object, create a new checkbox field: "Send Alert" (Send_Alert__c)
  2. Create a new Case trigger and paste the code below
  3. Set up a new Workflow Rule for Case object to check if Send_Alert__c = true
    1. Send an email alert
    2. Set the Send_Alert__c back to false
And here is the code for the Case trigger:
trigger CaseTrigger on Case (before insert) {
	Map<String, Integer> caseCountByLocation = new Map<String, Integer>();
	for (AggregateResult ar : [
		SELECT Location__c, COUNT(Id) Total
		FROM Case
		WHERE CreatedDate = THIS_WEEK
		GROUP BY Location__c
	]) {
		caseCountByLocation.put((String) ar.get('Location__c'), (Integer) ar.get('Total'));
	}
	for (Case c : Trigger.new) {
		if (c.Location__c != null && caseCountByLocation.containsKey(c.Location__c)) {
			Integer totalCases = caseCountByLocation.get(c.Location__c);
			if (totalCases >= 7) {
				c.Send_Alert__c = true;
			}
		}
	}	
}


 

All Answers

RaidanRaidan
Hi Leah,

I made the tweak to the code from Daniel. Assuming you have a Location__c field in the Case object, the code looks like below. Put the code inside the Case before insert trigger.
Map<String, Integer> caseCountByLocation = new Map<String, Integer>();
for (AggregateResult ar : [
	SELECT Location__c, COUNT(Id) Total
	FROM Case
	WHERE CreatedDate = THIS_WEEK
	GROUP BY Location__c
]) {
	caseCountByLocation.put((String) ar.get('Location__c'), (Integer) ar.get('Total'));
}
for (Case c : Trigger.new) {
	if (c.Location__c != null && caseCountByLocation.containsKey(c.Location__c)) {
		Integer totalCases = caseCountByLocation.get(c.Location__c);
		if (totalCases >= 7) {
			c.Send_Alert__c = true;
		}
	}
}

 
Leah MorganLeah Morgan
Thank you Raidan, this is probably a dumb question...I'm still fairly new to SalesForce...but when you say 'put the codes inside the case' what exactly do you mean by that?  Thank you for your help again, it's much appreciated!
Leah MorganLeah Morgan
I attempted to make a new feild on the case and put the code in there but that didn't seem to work.  Sorry to be a pain, I appreciate your support!
RaidanRaidan
Ah, I thought you needed help tweaking the code from the other post. So, here are the complete steps you can do:
  1. In Case object, create a new checkbox field: "Send Alert" (Send_Alert__c)
  2. Create a new Case trigger and paste the code below
  3. Set up a new Workflow Rule for Case object to check if Send_Alert__c = true
    1. Send an email alert
    2. Set the Send_Alert__c back to false
And here is the code for the Case trigger:
trigger CaseTrigger on Case (before insert) {
	Map<String, Integer> caseCountByLocation = new Map<String, Integer>();
	for (AggregateResult ar : [
		SELECT Location__c, COUNT(Id) Total
		FROM Case
		WHERE CreatedDate = THIS_WEEK
		GROUP BY Location__c
	]) {
		caseCountByLocation.put((String) ar.get('Location__c'), (Integer) ar.get('Total'));
	}
	for (Case c : Trigger.new) {
		if (c.Location__c != null && caseCountByLocation.containsKey(c.Location__c)) {
			Integer totalCases = caseCountByLocation.get(c.Location__c);
			if (totalCases >= 7) {
				c.Send_Alert__c = true;
			}
		}
	}	
}


 
This was selected as the best answer
Leah MorganLeah Morgan
Thank you!  Sorry about the long response time.  I created the object, however when I attempt to create the apex trigger for the case it is telling me that I cannot create it cannot create an Apex trigger on an active organization.  I've tried looking into why I may be getting this issue and have not found any resolution, Is there anything that you can think of that I might be doing wrong?

Thank you!
RaidanRaidan
You won't be able to create a trigger in Production. You will have to spin off a developer sandbox and then create the trigger (and a test class) there. Once everything is ready, you can deploy the changes to production using the Change Set.
Deepak RathinaveluDeepak Rathinavelu
Hi Leah,

Need more info:
  • 7 days from what date?
  • From where is the location coming from? (which object: a field from same record or a parent object has the location)
  • How many locations are there?
Regards,
Deepak Rathinavelu
Leah MorganLeah Morgan
Hello Deepak,

I believe that I am on the right track with the info that Raidan provided, however when I went to upload the new apex trigger from my sandbox to the producation org, it is giving me the below error:
User-added image
so perhaps I am going to need to do it another way?  I was under the assumption that my org was able to do this, but it looks like I was mistaken.
Deepak RathinaveluDeepak Rathinavelu
Leah,
Its more off a permission then an error, hope you have the necessary access and permissions.

Check out this link, 
https://developer.salesforce.com/forums/?id=906F0000000902tIAA
Leah MorganLeah Morgan
I figured it out, I didn't have the correct setting checked in my production org.  I switched a few things and it is now uploading.  Thank you!
RaidanRaidan
Great to know that is working! Please mark the answer as Solved so others can also benefit from it. Thanks!
Leah MorganLeah Morgan
Ok, so I think that I am almost completely up and running with this.  I have the workflow in place, the email template created and the apex triger just finished validating.  I just have (hopefully) one more question:  When the deployed trigger finished validating I recieved this error message:
User-added image

I believe that it may have to do with the fact that I do not have a 'Location__c' feild on my cases so I am using the custom feild that we have which is basically the same thing.  THat's my only guess.

Thank you so much for all of your support while I figure this out, this community rocks! :)
RaidanRaidan
I think it has something to do with the test classes. The test classes are trying to create a new record, but the required fields are not populated. This could happen when the fields are made required after the test classes have been created. You will have to go to each test class and fix the lines (add the required fields to the object creation). Another trick is to disable the required fields during deployment.

You probably want to run all test in your sandbox first to make sure all is good.
 
Leah MorganLeah Morgan
Ok, I am setting it up in the sandbox to try and run it and then will see if I can import everything back over.  Fingers crossed!
Leah MorganLeah Morgan
Actually scratch that...I do have that field, for some reason the apex trigger has just been giving me an error that it doesn't find it: 
User-added image

 
RaidanRaidan
That is weird! That Location__c field is in the Case object, right? Another possibility is the field is hidden from the Admin profile.
Leah MorganLeah Morgan
You're right, the field is hidden, I just finished looking into that.

I believe it's because we are using a slightly different set up with our service, instead of having the location on the account we have a list of location (sites) associated with the account below the main info.  They have the field SVMXC__Site__c appended to them.  I tried to use this field to update the account, and it didn't work though.  I'm guessing this has something to do with the fact that we're using service max...

User-added image
RaidanRaidan
I am a bit confused when you are referring to the Account. Is the Location field in the Case populated? Once the trigger runs, it will mark the Case to send an alert. You will have to create a workflow to send an email based on the Send Alert field. I wished I could see your org to get the whole idea what is going on.
Leah MorganLeah Morgan
Yes, there is a location field in the case, but it links to SVMXC__Site__c, instead of location__c which I believe is different than default.

User-added image

other than this field I am not sure what I could use to make thetrigger
 
RaidanRaidan
If the field is correctly populated, you can change the trigger by replacing Location__c with SVMXC__Site__c.
Leah MorganLeah Morgan
That is what I did and the trigger seems to be 'working' (it's not giving me any errors) but I am not getting any emails.  So maybe my probelm is somewhere else in the chain.

could it be that the check box field should be in the location, not the case?  I tried switching it to a field in the location, but the apex trigger now has to be changed to reference it in the new location and I'm not sure how to do that.

 
Leah MorganLeah Morgan
I'm guessing that this is the line that has to be changed:
 
c.Send_Alert__c = true;

 
RaidanRaidan
That field is just a flag in the Case field. What you need to do next is to create a Workflow Rule for the Case object. Make sure it evaluates when "created, and any time it's edited to subsequently meet criteria". And the criteria should be Send_Alert__c = true. Lastly, for the action, you create an Email Alert. Let me know if you need help with this.
Leah MorganLeah Morgan
I believe that is what I have here, tell me if this looks correct.  And thank you so much for all of your help!

User-added image
RaidanRaidan
The picture is really small :-) But it looks correct. Give it a try and see if that works.
Leah MorganLeah Morgan
I just tried checking the check box on one of the cases myself to see if a could manually get an email sent to myself, and it did nothing.  I'm I way off course here?  Shouldn't checking the checkbox and then saving the account do the same thing as having the apex trigger check the box automatically?  Maybe I have an issue with my email alert?
Leah MorganLeah Morgan
I'm also getting this pop up on my location whenever I click pretty much anything.  I was just assuming it was because I was working in a sandbox, but is it possibly something more?

User-added image
RaidanRaidan
Yes, it should send an email if the Send_Alert__c changes from false to true.

Things to check:
- In the Workflow Rule criteria, make sure you select: "criteria are met". And choose the Send Alert from the Field dropdown, then Operator is Equals and value is TRUE.
- In the Email Alert action, make sure an email template is selected and you are the recipient.

You can also enable Debug Log to see what is going on behind the scene
Leah MorganLeah Morgan
Both of the above are true.  I enabled debug log, but am at a loss as to what any of it means!

I also temporarily removed the field update for the workflow rule, on the offchance that their was a problem with that.  With the field update disabled the checkbox stayed 'true' but I still didn't recieve an email, so there is definately a problem with that part of the proces...I double checked the email template and sent a test and it came straight to me, so that's not it.  I have no idea why it's giving me so much trouble!
RaidanRaidan
Just to test this, you can change the evaluation criteria to: created and every time it's edited. See if you get the email.
Leah MorganLeah Morgan
I did not.  HOwever in the meantime I also tried testing another email alert for a different aspect of the case that we already have in place.  THis didn't work either, which leads me to believe that the sandbox might be to blame.  As far as i can tell, this should be working.
RaidanRaidan
Oh this is your sandbox. The email must be disabled. You can go to Setup->Email Administration->Deliverability to enable it.
Leah MorganLeah Morgan
That was it!  It worked!  You have no idea how happy I am right now!!

So now I just need to figure out how to work around these test classes... ;)  You m entioned something about going into the test classes and fixing lines?  Any pointers on where to start with that?  I really appreciate all of the help, this project has definately been more of a headache than I first suspected it would be!
RaidanRaidan
In each test class, you have to find the lines where the object is created, then set the value of the required fields before the "insert" or "update" statements. After that, run the test class again to verify it is fixed.

If it is too much, you can take a shortcut by unchecking the Required box, deploying the changes, then checking it back. However, this has to be done when people are not using Salesforce (down-time) to make sure nobody is entering an invalid record.
Leah MorganLeah Morgan
this might be a dumb question, but where is the required box?
Leah MorganLeah Morgan
Actually I may be able to figure this out so that I don't have this problem in the future.  If this is my error:
 
System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Short_Name__c]: [Short_Name__c] 
Stack Trace: Class.ChatterAnswersRegistration.createAccount: line 8, column 1 Class.ChatterAnswersCreateAccountTest.validateAccountCreation: line 11, column 1

and this is my code it is referencing: 
 
@isTest
private class ChatterAnswersCreateAccountTest {
    static testMethod void validateAccountCreation() {
        Profile[] p = [SELECT Id FROM Profile WHERE UserType = 'Standard'];
        User[] user = [SELECT Id, Firstname, Lastname FROM User WHERE IsActive = true and ProfileId =: p[0].Id];
        // We cannot create account without a user.
        if (user.size() == 0) { return; }
        String firstName = user[0].FirstName;
        String lastName = user[0].LastName;
        String userId = user[0].Id;
        String accountId = new ChatterAnswersRegistration().createAccount(firstName, lastName, userId);
        Account account = [SELECT name, ownerId from Account where Id =: accountId];
        System.assertEquals(firstName + ' ' + lastName, account.name);
        System.assertEquals(userId, account.ownerId);
    }
}

Can you point me in the right direction of what I will have to fix?  I should be able to do the other ones by myself if I can just get the general idea of what I'm trying to do here.
RaidanRaidan
It seems that the problem comes from the Account creation, which is in the ChatterAnswersRegistration class. In that class, there is a createAccount method. Somehow this method fails because Short Name is now required. You probably will need to modify the method to populate the Short Name (say, by taking the first 20 characters of the Name field).

The Required checkbox is in the Short Name field (when you click Edit). I believe it is one of the Account custom fields.
Leah MorganLeah Morgan
Ok, I believe I got them all fixed, there is just one that is still giving me a problem, it looks like it is trying to update something in the location but I'm not sure how to trace it to it's source to fix it.  Once again...I really appreciate all your help.  I'm probably driving you crazy with all my questions!!
 
ServiceMaxTests	test_UpdateLocationTrigger	System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, A workflow or approval field update caused an error when saving this record. Contact your administrator to resolve it. Required fields are missing: [Name]: [] 

Stack Trace: Class.ServiceMaxTests.test_UpdateLocationTrigger: line 24, column 1
 
@isTest(SeeAllData=true)
private class ServiceMaxTests {
  public static Account a = new Account(); 
  public static Contact c = new Contact(); 
  public static SVMXC__Service_Order__c wo = new SVMXC__Service_Order__c();
  public static void createData1(){
    a = new Account(Name='Account1'); insert a;
    c = new Contact(FirstName='C', LastName='L', AccountId=a.id); insert c;
    wo = new SVMXC__Service_Order__c(); wo.SVMXC__Order_Status__c='Closed';
    insert wo;
  }
  
  testmethod static void test_SVMX_SFM_Extentions(){
    createData1();
    SVMX_SFM_Extentions extclass = new SVMX_SFM_Extentions();
    SVMX_SFM_Extentions.SVMX_GenSrvRpt(wo.id);
  }
  
  public static SVMXC__Installed_Product__c ip = new SVMXC__Installed_Product__c();
  public static SVMXC__Installed_Product__c ip2 = new SVMXC__Installed_Product__c();
  public static SVMXC__Site__c loc = new SVMXC__Site__c();
  testmethod static void test_UpdateLocationTrigger(){
    // Test IP without Site insert and location update
    ip.Name='IP1'; insert ip;
    loc.SVMXC__City__c='city'; insert loc;
    update ip; ip.SVMXC__site__c =loc.id; update ip;
    //Test IP with Site insert
    ip2.Name='IP2'; ip2.SVMXC__site__c =loc.id; insert ip2;
    //Test IP delete Site from previous having one
    ip.SVMXC__Site__c=null; update ip;
  }
    
    static testMethod void servicemaxtimesheetutil1(){
      ServiceMaxUtils.getCustomOBjects('BusinessHours', 'where isDefault=true','LastModifiedBy.Name', null);
    }
    
        static testMethod void SVMX_PS_ServiceReport_Test()
    {
        //Create Account
        Account ac = new Account(
                                        Name ='Account',
                                        ShippingStreet='ABC',
                                        ShippingCity='PQR ',
                                        ShippingState = 'XYZ',
                                        ShippingPostalCode='111',
                                        ShippingCountry='XYZ'
                                        ); 
        insert ac;    
        //Create Contact
        Contact  ct = new Contact(LastName='Last',AccountId =ac.Id); 
        insert ct;      
        //Create Case    
        Case cs = new Case(Status ='New', Priority = 'Medium', 
                                            Origin = 'Email', 
                                            //SVMX_Serial_Number__c ='123',
                                            //CurrencyIsoCode = 'USD',                                          
                                            ContactId =ct.Id,
                                            AccountId=ac.Id
                                            ); 
        insert cs;     
        
        //Create Work Order
        SVMXC__Service_Order__c svo = new SVMXC__Service_Order__c (
                                        SVMXC__Case__c = cs.Id ,
                                        SVMXC__Company__c = ac.Id , 
                                        SVMXC__Order_Status__c = 'Open',
                                        SVMXC__Priority__c ='Medium',
                                        SVMXC__Order_Type__c='Field Service'
                                        );
         insert svo;
         
        //Test coverage for the SVMX_VF_ServiceReport visualforce page
        PageReference pageRef = Page.SVMX_VF_EngineerReport;
        pageRef.getParameters().put('RecordId',svo.Id);
        Test.setCurrentPageReference(pageRef);
        
        // create an instance of the controller
        SVMX_PS_ServiceReport myPageCon = new SVMX_PS_ServiceReport();
        
        //try calling methods/properties of the controller in all possible scenarios
        // to get the best coverage.
        SVMXC__Service_Order__c pWO = myPageCon.getWorkOrder();
    
        List<SVMXC__Service_Order_Line__c> pWOLines =  myPageCon.getWorkOrderDetail();
     
        String strWOdt =  myPageCon.getWODate();
        String strCurrDt =  myPageCon.getCurrentDate();
        
        Date dat = System.today();
        String strDt =  myPageCon.getFormattedDate(dat);
        String str1 =  myPageCon.getcontype();
        String str2 =  myPageCon.getrender();  
        
        myPageCon.getCreateDate();  myPageCon.getCloseDate();  
    } 
    
}


 
RaidanRaidan
No worries, Leah! Fortunately I am pretty free today :-)
It seems that the problem comes from a Workflow Rule for the SVMXC Installed Product object. I can't see what it is doing, though, so it's hard to tell you what to do. Perhaps you can just deactivate the workflow during deployment and reactivate it back after. However, test it first to make sure that the workflow is the cause of the problem.
 
Leah MorganLeah Morgan
yes, that fixed it, I just disabled it temporarily and now it's back up and running fine.

The apex trigger is imported, and it's running just how we needed it too.  *sigh* that was quite the process.  Thank you so much for your help.  I'm almost afraid to ask this last question...but would you be willing to direct me to where I can figure out how to insert a hyper link into these auto generated emails that will take the reciever directly to the location referenced?  If not its no biggie.  You've literally saved me days of frustration.  Thank you so much for all of your help! ;)
RaidanRaidan
You're welcome!

Are you referring to the link of the Case or the Location object? If it is the Case, in you template merge field, select the Detail Link. It will give you the URL of the Case record. However, it will be tricky for the Location object. You will probably have to create your own link. Something like: https://<instance>.salesforce.com/{!Case.Location__c}. Replace the <instance> with your org na number (e.g.: na23). And if you are using a different field, replace the Location__c with the other field's name.
Leah MorganLeah Morgan
the location link.  this is what I did:  

User-added image

and this is what it is coming out as:
User-added image

The location is coming through fine, but the link is not populating correctly
RaidanRaidan
Perhaps you want to just add the Id and not the name to the link. Something like:

https://na15.salesforce.com/{!SVMXC__Site__c}
Leah MorganLeah Morgan
I figured it out!  I appended .Link after the custom field and it put the URL in there.  Thank you so much for your help with all of this!  It is very, very appreciated!!! :)
RaidanRaidan
Nice job! Glad to help!