+ Start a Discussion
NjangaNjanga 

SOQL

Hi Guys,

Can someone help me out here!


This is my situation:

I have two custom objects Job_ATS__c and Shortlists__c.

Job_ATS__c is the Parent and Shortlists__c is the Child. The relationship is one to many.


That means one job can have many shortlists and each shortlist has a Status__c. Status are 'Interested' or 'Not Interested'

 

So what I want is a query that will return Job Name and count for Interested and Not Interested shortlists.

 

E.g...

 

Job_ATS__c                      Shortlists__c                                                                           Desired Result

______________         __________________________________                       ______________________________
Job Name       id             Shortlist Name       id     job_id   Status                                   Job Name   Interested     Not Interested
______________         __________________________________                       _______________________________
Job1                 1               Shortlist1                1        1          Interested                             Job1                1                        1  
Job2                 2               Shortlist2                2        1          Not Interested                      Job2                1                        0

                                           Shortlist3                3         2          interested

 

I need to Display the desired result on Vf page.

 

Best Answer chosen by Admin (Salesforce Developers) 
vishal@forcevishal@force

That's because you have taken only one map. I think you should be querying the Job records such as:

 

List<Job__c> lstJobs = [Select Name, Id From Job__c Where Id IN : interestedRecords.keySet() AND Id IN : notInterestedRecords.keySet()];

 

Now you have a collection of those Job records.

 

Now on your page, inside your pageblocktable, iterate through this list instead of the interested map.

 

<apex:pageBlockTable value="{!lstJobs}" var="j">  
                                        <apex:column headerValue="Job Name">


                                         {!j.Name}


                                        </apex:column>  


                                        <apex:column headerValue="Interested">  


                                          {!interestedRecords[j.Id]}  


                                        </apex:column>  

                                      

                                        <apex:column headerValue="Not Interested">  


                                          {!notInterestedRecords[j.Id]}  


                                        </apex:column> 

 


                  </apex:pageBlockTable>

All Answers

vishal@forcevishal@force

You can easily do it with apex. But it would be even more simpler if you create two Roll up fields on Job_ATS__c, one with the filter for "Interested" status and other for "Not Interested" status. Then you can directly display those two fields on your visualforce.

 

If that doesn't work for you for some reasons, then this code should help you:

 

// take two maps, one will store the count for Interested records and other for Not Interested records

map<Id, Integer> interestedRecords = new map<Id, Integer>();

map<Id, Integer> notInterestedRecords = new map<Id, Integer>();

 

/// query shortlists record, check with the field names in the query, and add any filters you need

for(Shortlists__c s : [Select Job__c , Status__c From Shortlists__c Where "in case you have any filters"])

{

     // for interested records

     if(s.Status__c == 'Interested')

     {

          if(!interestedRecords.containsKey(s.Job__c))

          {

               interestedRecords.put(s.Job__c, 1);

          }

          else

          {

               interestedRecords.put(s.Job__c, interestedRecords.get(s.Job__c) + 1);

          }

     }

 

     // for not interested records

     else if(s.Status__c == 'Not Interested')

     {

           if(!notInterestedRecords.containsKey(s.Job__c))

          {

               notInterestedRecords.put(s.Job__c, 1);

          }

          else

          {

               notInterestedRecords.put(s.Job__c, notInterestedRecords.get(s.Job__c) + 1);

          }

     }

}

 

You can use this map to get "Interested" and "Not interested" records on your visualforce.

NjangaNjanga

Thank you For quick reply.

I have tried it and its working. the big challege is to display both maps.

I have tried with Interested and i can display Job Name and Interested Shortlists but i cant display corresponding Not Interested Shortlists.

This is my Vf Code:

 

 <apex:pageBlockTable value="{!interestedRecords}" var="interested">  
                                        <apex:column headerValue="Job Name">


                                          {!interested}  


                                        </apex:column>  


                                        <apex:column headerValue="Interested">  


                                          {!interestedRecords[interested]}  


                                        </apex:column>  


                  </apex:pageBlockTable>
                                    
 

vishal@forcevishal@force

That's because you have taken only one map. I think you should be querying the Job records such as:

 

List<Job__c> lstJobs = [Select Name, Id From Job__c Where Id IN : interestedRecords.keySet() AND Id IN : notInterestedRecords.keySet()];

 

Now you have a collection of those Job records.

 

Now on your page, inside your pageblocktable, iterate through this list instead of the interested map.

 

<apex:pageBlockTable value="{!lstJobs}" var="j">  
                                        <apex:column headerValue="Job Name">


                                         {!j.Name}


                                        </apex:column>  


                                        <apex:column headerValue="Interested">  


                                          {!interestedRecords[j.Id]}  


                                        </apex:column>  

                                      

                                        <apex:column headerValue="Not Interested">  


                                          {!notInterestedRecords[j.Id]}  


                                        </apex:column> 

 


                  </apex:pageBlockTable>

This was selected as the best answer
NjangaNjanga

Thank you so much for your time and effort. it has finally worked.