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
Sylvie Serplet 1Sylvie Serplet 1 

SOQL for last Cases for Contact

Hi,

I am trying to write a query to get a list of the last 5 cases from the contact in the current case. A Visualforce page will display this list in the Case layout. That is what I have come with but I could not make the In clause to work. Any ideas? 
Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId In:.............. Order by CaseNumber DESC Limit 5

Thank you for your help.
Sylvie
James LoghryJames Loghry
If you're querying cases related to a single contact, the correct syntax is:
  
Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId = :contactId Order by CaseNumber DESC Limit 5

Otherwise, if you're querying for multiple contacts, the correct syntax is:
  
Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId in :contactIds Order by CaseNumber DESC Limit 5

In the second example, contactIds is a collection (either set or list) of Contact Ids.
Sylvie Serplet 1Sylvie Serplet 1
Thank you James for your answer I am looking for the 1st scenario.

Unfortunately I got the message " Error: Compile Error: Variable does not exist: contactId at line 8 column 107"

This is the complete Apex Class
  
public with sharing class CaseListcontroller {
    
List<Case> CA;

public List<Case> getCA() {

    if (CA == null) 
        CA = [Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId = :contactId Order by CaseNumber DESC Limit 5];
 return CA;
}
}

 
James LoghryJames Loghry
Rereading your message, you'll want something like below. Note, I updated your class to use an extension constructor/controller. 
  
public with sharing class CaseListcontroller {
    
    public List<Case> CA {get; set;}

    public CaseListController(ApexPages.StandardController sc){
        Case currentCase = (Case)sc.getRecord();
        CA = [Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId = :currentCase.ContactId Order by CaseNumber DESC Limit 5];
      }
}

In the constructor, it pulls in the case, including it's contactid field from the Case standard controller (passed in automatically via Visualforce).

Next, it queries for the list of cases associated with that same contact id.
  
Sylvie Serplet 1Sylvie Serplet 1
Sorry James but now I have a message "Error: Compile Error: The method List<Case> getCA() is referenced by Visualforce Page (CaseList) in salesforce.com. Remove the usage and try again. at line 5 column 19" on my Visualforce page which is: 
<apex:page controller="CaseListcontroller">

<apex:pageBlock >
<apex:pageBlockTable value="{!CA}" var="C" columnsWidth="10%,50%,20%,20%" >                                                     
                <apex:column headerValue="Case Number" value="{!C.CaseNumber}" />
                <apex:column headerValue="Subjet" value="{!C.Subject}" />
                <apex:column headerValue="Status" value="{!C.Status}" />
                <apex:column headerValue="Created Date" value="{!C.CreatedDate}" />       
</apex:pageBlockTable>               
</apex:pageBlock>  
</apex:page>

A huge THANK YOU for your help.
Sylvie
 
pigginsbpigginsb
Hi, Sylvie.

The example class that James provided is an Apex extension, so your page tag should include the standardController and extenions attribute. Without standardController="Case", you will not be able to include the Visualforce page in the Case layout. 
<apex:page standardController="Case" extensions="CaseListController">
Hope this helps!
 
Sylvie Serplet 1Sylvie Serplet 1
Hello,

My Visualforce page is now:
  
<apex:page standardController="Case" extensions="CaseListcontroller">

<apex:pageBlock >
<apex:pageBlockTable value="{!CA}" var="C" columnsWidth="10%,50%,20%,20%" >                                                     
                <apex:column headerValue="Case Number" value="{!C.CaseNumber}" />
                <apex:column headerValue="Subjet" value="{!C.Subject}" />
                <apex:column headerValue="Status" value="{!C.Status}" />
                <apex:column headerValue="Created Date" value="{!C.CreatedDate}" />       
</apex:pageBlockTable>               
</apex:pageBlock>  
</apex:page>

And my Apex class:
  
public with sharing class CaseListcontroller {
 public List<Case> CA {get;}

    public CaseListcontroller(ApexPages.StandardController controller) {

        Case currentCase = (Case)controller.getRecord();
        CA = [Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId = :currentCase.ContactId Order by CaseNumber DESC Limit 5];
      }
}

This is the error message I get:
Error: Compile Error: member variable not visible for assignment at line 7 column 14

Thank you for your help.
Sylvie
James LoghryJames Loghry
There is no setter for your CA variable therefore your VF page is complaining it can't assign / set / update your CA variable.  To fix this, add set; next to get; 
Sylvie Serplet 1Sylvie Serplet 1
When I add the set this is the error message I get:

[Error] Error: Compile Error: The method List<Case> getCA() is referenced by Visualforce Page (CaseList) in salesforce.com. Remove the usage and try again. at line 8 column 19
pigginsbpigginsb
It seems like you might have a different version of your page than what you last shared. When I put the following code into a dev org, the page loads without any errors. Admittedly, I did make a couple of updates to the controller.

The table being read-only, I went ahead and gave the CA property a private setter. I also had to use addFields() in order to include the ContactId field with the Case when getting out of the standard controller.

I also found that the Case for which the page is loaded also appeard in the Case List table. So I have updated the where clause to exclude this case from the results. 
<apex:page standardController="Case" extensions="CaseListController">
	<apex:pageBlock >
		<apex:pageBlockTable value="{!CA}" var="C" columnsWidth="10%,50%,20%,20%" >                                                     
			<apex:column headerValue="Case Number" value="{!C.CaseNumber}" />
			<apex:column headerValue="Subject" value="{!C.Subject}" />
			<apex:column headerValue="Status" value="{!C.Status}" />
			<apex:column headerValue="Created Date" value="{!C.CreatedDate}" />       
		</apex:pageBlockTable>               
	</apex:pageBlock>  
</apex:page>
public with sharing class CaseListController {

	public List<Case> CA { get; private set; }

    public CaseListController(ApexPages.StandardController controller) {
		controller.addFields(new List<String>{ 'ContactId' });
        Case currentCase = (Case)controller.getRecord();
        CA = [Select Id, CaseNumber, CreatedDate, Status, Subject, ContactId FROM Case Where ContactId = :currentCase.ContactId and Id != :currentCase.Id Order by CaseNumber DESC Limit 5];
      }
}
Does this get you what you need?

 
Sylvie SerpletSylvie Serplet
Thank you very much, it works perfectly.
I have also added a link on the case number that open the case in a new tab as below:
  
<apex:page standardController="Case" extensions="CaseListcontroller">
<apex:form > 
<apex:pageBlock >
<apex:pageBlockTable value="{!CA}" var="C" columnsWidth="10%,50%,20%,20%" >                                                     
                <apex:column headerValue="Case Number" >
                <apex:commandLink value="{!C.CaseNumber}" action="/{!C.Id}" target="_blank" />
                </apex:column>                
                <apex:column headerValue="Subjet" value="{!C.Subject}" />
                <apex:column headerValue="Status" value="{!C.Status}" />
                <apex:column headerValue="Created Date" value="{!C.CreatedDate}" />       
</apex:pageBlockTable>               
</apex:pageBlock>
</apex:form>
</apex:page>

 
Sylvie Serplet 1Sylvie Serplet 1
Hello,

I have also written the test but it does not pass. Could you please tell me what is wrong whit it? Thank you.
  
@isTest 
public  with sharing class CaseListcontrollerTest 
{
 static testMethod void TestCaseListcontroller () 
 {
Case CA = new Case ();
CA.Status ='New';
CA.Subject='Test';
 insert CA;

 Test.StartTest(); 

CaseListcontroller controller = new CaseListcontroller(new ApexPages.StandardController(CA));

  PageReference pageRef = Page.CaseList; 
  pageRef.getParameters().put('id', String.valueOf(CA.Id));
  Test.setCurrentPage(pageRef);

Test.StopTest();
 }
}


 
Sylvie Serplet 1Sylvie Serplet 1
Hi,
I have tried solutions 1 and 3 and the test still not passed.
For solutions No. 2 I am not sure where to insert the if statement. Do I need to delete some lines in my test above? 
Sorry I am a beginner! Thank you for your time.
pigginsbpigginsb
I apologize. I realize I must have changed the controller code when I tested the first solution in my previous answer. So, we really are left with 2 options only. I'll have to remove the previous answer in order to not confuse others later (also because it's plain embarrassing).

I believe you are encountering an error that says, "You cannot call addFields when the data is being passed into the controller by the caller."

1. You can add an if statement to the CaseListController constructor, checking to see whether a test is running. This way you are only calling the addFields() method when the unit test is not running (also meaning your unit test would never execute this line of actual code). 
// this IF statement goes before your call to addFields()
// so that the statement is only executed when NOT running a test
// I've added curly braces for clarity

if (Test.isRunningTest() == false) {
      controller.addFields(new List<String>{ 'ContactId' });
}
2. You can also REMOVE the addFields() call from your constructor and include a hidden reference to the field in your visualforce markup. Any field referenced in your page will automatically be included in the standard controller. 
<!-- remove the addFields() call from your constructor if using a VF reference to bring the necessary fields in, else your test will still fail -->
<apex:outputText style="display:none;" value="{!Case.ContactId}"/>
Either of these approaches should get your unit test to pass. You would probably also be creating some additional cases in your test in order to assert that the list includes the expected results.

Please share your updated classes and visualforce if you are still encountering the error.
Sylvie Serplet 1Sylvie Serplet 1
Thank you so much, the test passed. I have used solution 1.