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
Kenneth KimbrellKenneth Kimbrell 

How to show only one record that contains the same field values in an APEX SOQL Query

Not sure the best way to ask this. But essentially I have a survey for instructors that a student submits during the mid-term. Each survey submission is of course a different record but the instructor and class would be the same. When querying results I only want to show one record for that teacher and class not multiple records from the same teacher and class. Here is an example:
  
    //Instrustor        //Registered Course
    Becky Gosky         English Composition                                            
    Bobbie Strother     Developmental Psychology                                            
    Bobbie Strother     Anatomy & Physiology                                            
    Bruce Priddy        Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    NCLEX Preparation



As you can see Bobbie teaches a class in Developmental Psychology and also in Anatomy and Physiology -- he has also received a survey review from a student in each.

Bruce Priddy teaches Developmental Psychology as well. 

Christy teaches Developmental Psychology too and has received 3 survey reviews and all 3 records are visible. I only want to output one record not multiple records that have the same instructor name and registered course. The reason why is for report user ability. I want to then add into a wrapper class the total response count for a specific instructor and and specific course.

I tried using a set, map, wrapper, etc... But the issue is instructor's names will be used more than once and so will the courses. So a set would not help. Unless I create a list, add the set to the new list, then make conditional statements and add to the list only when conditions are met. That way seems promising but I have not been able to completely make it work. Mostly because there is a time when both the instuctor and the course are in the set so I can no longer make a condition. I am convinced there has to be an easy way to just only output a record once if there are specified field names the same. I just can't seem to find a way. So I am reaching out to my community. Here is the method I am using:
  
public list<wrapper> getlistWrap(){
        wrap = new list<wrapper>();
        listSurveyData = new list<End_Of_Class_Survey__c>([SELECT        Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c,Question_1A_MidSurvey__c,Instructor__c,Registered_Course__c from End_Of_Class_Survey__c]);
        listInstructors = new list<Instructor__c>([SELECT Id, Name FROM Instructor__c]);
        getClassName  = new set<string>();
        getInstructorName = new set<string>();
        count = 0;
        for(integer i = 0; listSurveyData.size() > i; i++){
            if(listSurveyData[i].Instructor__c != '' && listSurveyData[i].Instructor__c != null){
                 getInstructorName.add(listSurveyData[i].Instructor__c);
            }
            if(listSurveyData[i].Registered_Course__c != '' && listSurveyData[i].Registered_Course__c != null){
                getClassName.add(listSurveyData[i].Registered_Course__c);
            }
        }
        listClassName = new list<string>(getClassName);
        instructorName = new list<string>(getInstructorName);   
        for(count = 0; listClassName.size() > count; count++ ){
            wrap.add(new wrapper(instructorName[count], listClassName[count]));  
        }
        return wrap;
}

 
Best Answer chosen by Kenneth Kimbrell
Dushyant SonwarDushyant Sonwar
Hi Kenneth,

If you want output like below , then wrapper class is the best option in my opinion.

//Instrustor             //Registered                               //Responses
Course Becky        Gosky English Composition               1
Bobbie Strother      Developmental Psychology               1
Bobbie Strother      Anatomy & Physiology                       1
Bruce Priddy           Developmental Psychology               1
Christy Saladino     Developmental Psychology                3
Christy Saladino     NCLEX Preparation                            1

1) Create a Wrapper Class 
Public class SurveyWrapper{
		public End_Of_Class_Survey__c surveyObj{get;set;}
		public Integer responseCount{get;set;}
		public wrapper(){
			surveyObj = new End_Of_Class_Survey__c();
			responseCount = 0;
		}
	}


2) Fill the wrapper with survey data 
public list<SurveyWrapper> getlistOfInstructorAndCourses(){
        list<SurveyWrapper> listOfInstructorAndCourses = new list<wrapper>();
        		    
        
        Map<String ,  list<End_Of_Class_Survey__c>> mapOfSurveyData =new Map<String ,  list<End_Of_Class_Survey__c>>();
		
		for(End_Of_Class_Survey__c surverObj : [SELECT Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c
												,Question_1A_MidSurvey__c,Instructor__c
												,Registered_Course__c from End_Of_Class_Survey__c 
												where Registered_Course__c!= null and Instructor__c != null]){
			list<End_Of_Class_Survey__c> lstSurveyData = new list<End_Of_Class_Survey__c>();
			String key = surverObj.Instructor__c + '-' + surverObj.Registered_Course__c;
			if(mapOfSurveyData.containsKey(key)){
				lstSurveyData = mapOfSurveyData.get(key);				
			}			
			lstSurveyData.add(surverObj);
			mapOfSurveyData.put(key, lstSurveyData);
		}
		for(string key : mapOfSurveyData.keyset()){
			SurveyWrapper wrapperObj = new SurveyWrapper();
			wrapperObj.surverObj = mapOfSurveyData.values()[0];
			wrapperObj.responseCount = mapOfSurveyData.get(key).size();
			listOfInstructorAndCourses.add(wrapperObj);
		}
        return listOfInstructorAndCourses;
	}
Below is the code 
public list<SurveyWrapper> getlistOfInstructorAndCourses(){
        list<SurveyWrapper> listOfInstructorAndCourses = new list<wrapper>();
        		    
        
        Map<String ,  list<End_Of_Class_Survey__c>> mapOfSurveyData =new Map<String ,  list<End_Of_Class_Survey__c>>();
		
		for(End_Of_Class_Survey__c surverObj : [SELECT Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c
												,Question_1A_MidSurvey__c,Instructor__c
												,Registered_Course__c from End_Of_Class_Survey__c 
												where Registered_Course__c!= null and Instructor__c != null]){
			list<End_Of_Class_Survey__c> lstSurveyData = new list<End_Of_Class_Survey__c>();
			String key = surverObj.Instructor__c + '-' + surverObj.Registered_Course__c;
			if(mapOfSurveyData.containsKey(key)){
				lstSurveyData = mapOfSurveyData.get(key);				
			}			
			lstSurveyData.add(surverObj);
			mapOfSurveyData.put(key, lstSurveyData);
		}
		for(string key : mapOfSurveyData.keyset()){
			SurveyWrapper wrapperObj = new SurveyWrapper();
			wrapperObj.surverObj = mapOfSurveyData.values()[0];
			wrapperObj.responseCount = mapOfSurveyData.get(key).size();
			listOfInstructorAndCourses.add(wrapperObj);
		}
        return listOfInstructorAndCourses;
	}
	
	Public class SurveyWrapper{
		public End_Of_Class_Survey__c surveyObj{get;set;}
		public Integer responseCount{get;set;}
		public wrapper(){
			surveyObj = new End_Of_Class_Survey__c();
			responseCount = 0;
		}
	}


on vf page :
  
<apex:pageBlockTable value="{!listOfInstructorAndCourses}" var="surveyObj">
		<apex:column headerValue ="Instructor" value="{!surveyObj.Instructor__c}" />
		<apex:column headerValue ="Registered Courses" value="{!surveyObj.Registered_Course__c}" />
		<apex:column headerValue ="Response Count" value="{!surveyObj.Responses}" />
	</apex:pageBlockTable>


 

All Answers

Dushyant SonwarDushyant Sonwar
Hi Kenneth ,

The description you have given above , from what i understood.
Currently your output is this 
//Instrustor        //Registered Course
    Becky Gosky         English Composition                                            
    Bobbie Strother     Developmental Psychology                                            
    Bobbie Strother     Anatomy & Physiology                                            
    Bruce Priddy        Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                            
    Christy Saladino    NCLEX Preparation
and you want is this
  
//Instrustor        //Registered Course
    Becky Gosky         English Composition                                            
    Bobbie Strother     Developmental Psychology                                            
    Bobbie Strother     Anatomy & Physiology                                            
    Bruce Priddy        Developmental Psychology                                            
    Christy Saladino    Developmental Psychology                                                                                    
    Christy Saladino    NCLEX Preparation

Then you can use  set/map to create a unique key to filter something like below: 
public list<End_Of_Class_Survey__c> getlistOfInstructorAndCourses(){
        list<End_Of_Class_Survey__c> listOfInstructorAndCourses = new list<End_Of_Class_Survey__c>();
        listSurveyData = new list<End_Of_Class_Survey__c>([SELECT        Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c,Question_1A_MidSurvey__c,Instructor__c,Registered_Course__c from End_Of_Class_Survey__c 
		where Registered_Course__c!= null and Instructor__c != null]);       
        
        Set<String> setOfInstructorAndCourses = new Set<string>();
		for(integer i = 0; i < listSurveyData.size(); i++){
            if(!setOfInstructorAndCourses.contains(listSurveyData[i].Instructor__c +'-'+listSurveyData[i].Registered_Course__c)){
				listOfInstructorAndCourses.add(listSurveyData[i]);
				setOfInstructorAndCourses.add(listSurveyData[i].Instructor__c +'-'+listSurveyData[i].Registered_Course__c);
			}
        }
        return listOfInstructorAndCourses;
	}

Hope this helps :) 

 
Kenneth KimbrellKenneth Kimbrell

This helped, exactly what I was looking for. What do you think would be the best way now to get the number of responses that each teacher recieved from the course? For example Christy received 3 survey responses from Christy Saladino Developmental Psychology. Now in that same row I want to be able to show an output for the response count per each teacher in the course. In this case the output on that row would be:

//Instrustor             //Registered                               //Responses
Course Becky        Gosky English Composition               1
Bobbie Strother      Developmental Psychology               1
Bobbie Strother      Anatomy & Physiology                       1
Bruce Priddy           Developmental Psychology               1
Christy Saladino     Developmental Psychology                3
Christy Saladino     NCLEX Preparation                            1
Dushyant SonwarDushyant Sonwar
Hi Kenneth,

If you want output like below , then wrapper class is the best option in my opinion.

//Instrustor             //Registered                               //Responses
Course Becky        Gosky English Composition               1
Bobbie Strother      Developmental Psychology               1
Bobbie Strother      Anatomy & Physiology                       1
Bruce Priddy           Developmental Psychology               1
Christy Saladino     Developmental Psychology                3
Christy Saladino     NCLEX Preparation                            1

1) Create a Wrapper Class 
Public class SurveyWrapper{
		public End_Of_Class_Survey__c surveyObj{get;set;}
		public Integer responseCount{get;set;}
		public wrapper(){
			surveyObj = new End_Of_Class_Survey__c();
			responseCount = 0;
		}
	}


2) Fill the wrapper with survey data 
public list<SurveyWrapper> getlistOfInstructorAndCourses(){
        list<SurveyWrapper> listOfInstructorAndCourses = new list<wrapper>();
        		    
        
        Map<String ,  list<End_Of_Class_Survey__c>> mapOfSurveyData =new Map<String ,  list<End_Of_Class_Survey__c>>();
		
		for(End_Of_Class_Survey__c surverObj : [SELECT Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c
												,Question_1A_MidSurvey__c,Instructor__c
												,Registered_Course__c from End_Of_Class_Survey__c 
												where Registered_Course__c!= null and Instructor__c != null]){
			list<End_Of_Class_Survey__c> lstSurveyData = new list<End_Of_Class_Survey__c>();
			String key = surverObj.Instructor__c + '-' + surverObj.Registered_Course__c;
			if(mapOfSurveyData.containsKey(key)){
				lstSurveyData = mapOfSurveyData.get(key);				
			}			
			lstSurveyData.add(surverObj);
			mapOfSurveyData.put(key, lstSurveyData);
		}
		for(string key : mapOfSurveyData.keyset()){
			SurveyWrapper wrapperObj = new SurveyWrapper();
			wrapperObj.surverObj = mapOfSurveyData.values()[0];
			wrapperObj.responseCount = mapOfSurveyData.get(key).size();
			listOfInstructorAndCourses.add(wrapperObj);
		}
        return listOfInstructorAndCourses;
	}
Below is the code 
public list<SurveyWrapper> getlistOfInstructorAndCourses(){
        list<SurveyWrapper> listOfInstructorAndCourses = new list<wrapper>();
        		    
        
        Map<String ,  list<End_Of_Class_Survey__c>> mapOfSurveyData =new Map<String ,  list<End_Of_Class_Survey__c>>();
		
		for(End_Of_Class_Survey__c surverObj : [SELECT Id,Question_1_Mid_Survey__c,Question_1B_Mid_Survey__c
												,Question_1A_MidSurvey__c,Instructor__c
												,Registered_Course__c from End_Of_Class_Survey__c 
												where Registered_Course__c!= null and Instructor__c != null]){
			list<End_Of_Class_Survey__c> lstSurveyData = new list<End_Of_Class_Survey__c>();
			String key = surverObj.Instructor__c + '-' + surverObj.Registered_Course__c;
			if(mapOfSurveyData.containsKey(key)){
				lstSurveyData = mapOfSurveyData.get(key);				
			}			
			lstSurveyData.add(surverObj);
			mapOfSurveyData.put(key, lstSurveyData);
		}
		for(string key : mapOfSurveyData.keyset()){
			SurveyWrapper wrapperObj = new SurveyWrapper();
			wrapperObj.surverObj = mapOfSurveyData.values()[0];
			wrapperObj.responseCount = mapOfSurveyData.get(key).size();
			listOfInstructorAndCourses.add(wrapperObj);
		}
        return listOfInstructorAndCourses;
	}
	
	Public class SurveyWrapper{
		public End_Of_Class_Survey__c surveyObj{get;set;}
		public Integer responseCount{get;set;}
		public wrapper(){
			surveyObj = new End_Of_Class_Survey__c();
			responseCount = 0;
		}
	}


on vf page :
  
<apex:pageBlockTable value="{!listOfInstructorAndCourses}" var="surveyObj">
		<apex:column headerValue ="Instructor" value="{!surveyObj.Instructor__c}" />
		<apex:column headerValue ="Registered Courses" value="{!surveyObj.Registered_Course__c}" />
		<apex:column headerValue ="Response Count" value="{!surveyObj.Responses}" />
	</apex:pageBlockTable>


 
This was selected as the best answer
Kenneth KimbrellKenneth Kimbrell
public list<End_Of_Class_Survey__c> listOfSurveyData {get;set;}
    
    //Start a wrapper class
    public class SurveyWrapper{
        
        public list<End_Of_Class_Survey__c> surveyObj {get;set;}
        public integer responseCount {get;set;}
        public SurveyWrapper(){
            surveyObj = new list<End_Of_Class_Survey__c>();
            responseCount = 0;
        }
    }
    
    public list<SurveyWrapper> getListOfInstructorAndCourses(){
        listOfSurveyData = new list<End_Of_Class_Survey__c>([SELECT Id, Question_1_Mid_Survey__c, Question_1B_Mid_Survey__c, Question_1A_MidSurvey__c, Instructor__c, Registered_Course__c FROM End_Of_Class_Survey__c WHERE Registered_Course__c != null AND Instructor__c != null]);
        List<SurveyWrapper> listOfInstructorAndCourses = new list<SurveyWrapper>();
        map<string, list<End_Of_Class_Survey__c>> mapOfSurveyData = new map<string, list<End_Of_Class_Survey__c>>();
        
        /*loop through list and find key values that contain both the instructor and the registered course.
         If map does contains(key) then get the key and add it to the lstSurveyData.
         Add the entire listOfSurveyData to the lstSurveyData
         Finally make the string a unique key in the map and add the lstSurveyData as its value
         This will allow for a unique list and no repeat of records pertaining to the same course and instructor*/
        for(End_Of_Class_Survey__c e: listOfSurveyData){
            
            list<End_Of_Class_Survey__c> lstSurveyData = new list<End_Of_Class_Survey__c>();
            string key = e.Instructor__c + '-' + e.Registered_Course__c; 
            if(mapOfSurveyData.containsKey(key)){
                lstSurveyData = mapOfSurveyData.get(key);
            }
            lstSurveyData.add(e);
            mapOfSurveyData.put(key,lstSurveyData);
        }
        
        //loop through the map's keyset
        for(string key: mapOfSurveyData.keySet()){
            //create a new wrapper object and add the values to the wrapper class
            SurveyWrapper wrapperObj = new SurveyWrapper();
            wrapperObj.surveyObj = mapOfSurveyData.values()[0];
            wrapperObj.responseCount = mapOfSurveyData.get(Key).size();
            listOfInstructorAndCourses.add(wrapperObj);
            
        }
        return listOfinstructorAndCourses;
    }

So this is what I did, (I had to make modifications becuase it threw errors from what you had) but I can not seem to gain access to the Instructor__c and  Registered_Course__c fields inside of the wrapper class. I get an error:
 Unknown property 'CLS_InstructorMidSurvey.SurveyWrapper.instructor__c
Dushyant SonwarDushyant Sonwar
Hi Kenneth,

Please do the following changes:

Change line no 39 statement  
mapOfSurveyData.values()[0];

to
  
mapOfSurveyData.get(key)[0];

and
line no 6 
public list<End_Of_Class_Survey__c> surveyObj {get;set;}

to
​ 
public End_Of_Class_Survey__c surveyObj {get;set;}

use below code to display data on your vf page. 
<apex:pageBlockTable value="{!listOfInstructorAndCourses}" var="surveyObj">
		<apex:column headerValue ="Instructor" value="{!surveyObj.Instructor__c}" />
		<apex:column headerValue ="Registered Courses" value="{!surveyObj.Registered_Course__c}" />
		<apex:column headerValue ="Response Count" value="{!surveyObj.Responses}" />
	</apex:pageBlockTable>

This should work fine.

If still issue continues . Could you post your vf page code , how you are using the variables to displaying the data on vf page?

​
 
Kenneth KimbrellKenneth Kimbrell
Hello Dushyant-

That was it man! I knew something was up. Because I originally had to change the wrapper surveyObj variable dataType into a list. Because the other code seemed to be getting the values of the list as a string and not the actual survey object itself. Thanks man! A lot of people can learn from this thread. A very clever way of accomplishing what I was after.