+ Start a Discussion
Tate PriceTate Price 

Getting Child Records from a SOQL Query

I have a custom VF page with a custom controller in which a User can see reservations that other User have made for specific offices. 
I cannot, however, get the Reservation.createdby.Name to populate in my VF page as I do not know how to create the proper SOQL query. 

Here is my controller code:
 
wrapperDesk objwrapperDesk;
        List<Desk__c> lstDesk = [select id, Name, Desk_Number__c, createdby.Name, (select id, createdby.Name, Start_Date__c, End_Date__c from Reservations__r) from Desk__c where Office__c =: objDesk.Office__c order by Desk_Number__c asc];
        for(Desk__c dsk: lstDesk){
            objwrapperDesk = new wrapperDesk();
            objwrapperDesk.strDeskNumber = dsk.Desk_Number__c;
            objwrapperDesk.objDesk = dsk;
            map<String, Boolean> mapAvailableTemp = new map<String, Boolean>();
            for(Reservation__c rv: dsk.Reservations__r){
                
                for(integer i = 0; i <= rv.Start_Date__c.daysBetween(rv.End_Date__c);i++){               
                    mapAvailableTemp.Put(String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, true);
                }
            }
            system.debug('#################'+mapAvailableTemp);
            objwrapperDesk.mapAvailable = mapAvailableTemp;
            objwrapperDesk.intMapSize = mapAvailableTemp.size();
            lstwrapperDesk.add(objwrapperDesk);

Here is the place in the VF page:
<apex:repeat value="{!lstwrapperDateRange}" var="dr">
                              
                                <apex:variable value="{!dr.objDate}{!dsk.objDesk.id}" var="mapKey"/>                                        
                                    
                                    <apex:variable value="{!1}" var="rowNum"/>  
                                    <apex:repeat value="{!dsk.mapAvailable}" var="mapAvail">                                        
                                        <apex:outputPanel rendered="{!mapAvail == mapKey}">
                                            <apex:variable var="rowNum" value="{!rowNum + 1}"/>                                                                                                                             
                                            <td class="myTabletd" bgcolor="{!If(mapAvail == mapKey,'#47BBFF', '')}">
                                                <apex:outputLabel value="{!dsk.objDesk.createdBy.Name}"></apex:outputLabel>
                                            </td>       
                                        </apex:outputPanel>                                                                       
                                    </apex:repeat>

This is the line I need changed but I cannot figure out how:
<apex:outputLabel value="{!dsk.objDesk.createdBy.Name}"></apex:outputLabel>
This needs to be the RESERVATION creator not the DESK creator. 

Please help!
 
Robert_StrunkRobert_Strunk
Hi Tate, 
    One problem here is that dsk.objDesk is is not the reservation__c object as you pointed out.  Instead, you would need to iterate over your list of Reservations__r records pulled from the sub query for the dsk.objDesk.  So basically, you would need to change it to somthing more like {!dsk.objDesk.Reservations__r[0].createdBy.Name} but obvously you do not want to hard code the index like I just did.  Looks like you have a lot to refactor here. 
 
Tate PriceTate Price
Robert,
Thanks for the prompt response. 

Without hard coding the individual attributes(which I agree is not good), how would I create a SOQL query to extract the Reservations per Desk based on the dates?

Is that an entirely different list of records? This should not be this complicated, right? 

 
Robert_StrunkRobert_Strunk
There are a lot of ways to do it I am sure but I think if you just added a new class member to your WrapperDesk class and set it the same way you are setting the mapAvailable class member you would be okay.  So your inner most for loop would look somethng like this:
for(integer i = 0; i <= rv.Start_Date__c.daysBetween(rv.End_Date__c);i++){               
	mapAvailableTemp.Put(
		String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
			rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, 
		true
	);
	NEW_TEMP_MAP.Put(
		String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
			rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, 
		rv.createdBy.name
	);	
}

Then when you are building your objWrapperDesk object you just add a new line to populate the new class member like so:
system.debug('#################'+mapAvailableTemp);
objwrapperDesk.mapAvailable = mapAvailableTemp;
objwrapperDesk.NEW_MEMBER = NEW_TEMP_MAP;
objwrapperDesk.intMapSize = mapAvailableTemp.size();
lstwrapperDesk.add(objwrapperDesk);


 
Tate PriceTate Price
Robert,

I think I follow your reasoning. Where do I define the variable for NEW_TEMP_MAP? And do I just need to nest the loop below the original loop I have created for finding the Start and End date? 

I.e. Like so:
wrapperDesk objwrapperDesk;
        List<Desk__c> lstDesk = [select id, Name, Desk_Number__c, createdby.Name, (select id, createdby.Name, Start_Date__c, End_Date__c from Reservations__r) from Desk__c where Office__c =: objDesk.Office__c order by Desk_Number__c asc];
        for(Desk__c dsk: lstDesk){
            objwrapperDesk = new wrapperDesk();
            objwrapperDesk.strDeskNumber = dsk.Desk_Number__c;
            objwrapperDesk.objDesk = dsk;
            map<String, Boolean> mapAvailableTemp = new map<String, Boolean>();
            for(Reservation__c rv: dsk.Reservations__r){
                
                for(integer i = 0; i <= rv.Start_Date__c.daysBetween(rv.End_Date__c);i++){               
                    mapAvailableTemp.Put(String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, true);
                }
                for(integer i = 0; i <= rv.Start_Date__c.daysBetween(rv.End_Date__c);i++){               
                    mapAvailableTemp.Put(
                        String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
                            rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, 
                        true
                    );
                    NEW_TEMP_MAP.Put(
                        String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
                            rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, 
                        rv.createdBy.name
                    );	
                }
            }


Thanks for your guidance. SOQL is not easy to pick up on the fly. 

 
Tate PriceTate Price
And finally, how do I call the attribute in the VisualForce Page? How do I reference the object and the createdby?
Robert_StrunkRobert_Strunk
First, add the line below to your wrapper class named wrapperDesk
public map<String, String> NEW_MEMBER {get;set;}

Next, modify your apex to look like the following:
wrapperDesk objwrapperDesk;

List<Desk__c> lstDesk = [
	SELECT 	id, Name, Desk_Number__c, createdby.Name, 
				(select id, createdby.Name, Start_Date__c, End_Date__c from Reservations__r) 
	FROM 	Desk__c 
	WHERE 	Office__c =: objDesk.Office__c order by Desk_Number__c asc
];

for(Desk__c dsk: lstDesk){

	objwrapperDesk = new wrapperDesk();
	objwrapperDesk.strDeskNumber = dsk.Desk_Number__c;
	objwrapperDesk.objDesk = dsk;
	map<String, Boolean> mapAvailableTemp = new map<String, Boolean>();
	map<String, String> NEW_TEMP_MAP = new map<String, String>();
	
	for(Reservation__c rv: dsk.Reservations__r){
		
		Integer counter = 0;
		
		for(integer i = 0; i <= rv.Start_Date__c.daysBetween(rv.End_Date__c);i++){               
			mapAvailableTemp.Put(
				String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
					rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id, 
				true
			);
			NEW_TEMP_MAP.Put(
				String.valueOf(rv.Start_Date__c.adddays(i).month() + '/' + 
					rv.Start_Date__c.adddays(i).day() + '/' + rv.Start_Date__c.adddays(i).year()) + dsk.id + String.valueOf(counter), 
				rv.createdBy.name
			);
			counter++;
		}
	}
	
	system.debug('#################'+mapAvailableTemp);
	objwrapperDesk.mapAvailable = mapAvailableTemp;
	objwrapperDesk.NEW_MEMBER = NEW_TEMP_MAP;
	objwrapperDesk.intMapSize = mapAvailableTemp.size();
	lstwrapperDesk.add(objwrapperDesk);

Lastly update your VF page to look like below:
<apex:repeat value="{!lstwrapperDateRange}" var="dr">

	<apex:variable value="{!dr.objDate}{!dsk.objDesk.id}" var="mapKey"/>                                        

	<apex:variable value="{!1}" var="rowNum"/> 
	<apex:variable value="{!0}" var="counter"/>
	
	<apex:repeat value="{!dsk.mapAvailable}" var="mapAvail">                                        
		<apex:outputPanel rendered="{!mapAvail == mapKey}">
		
			<apex:variable var="rowNum" value="{!rowNum + 1}"/>      
			
			<td class="myTabletd" bgcolor="{!If(mapAvail == mapKey,'#47BBFF', '')}">
				<apex:outputLabel value="{!dsk.objDesk.NEW_MEMBER[mapKey+counter].createdBy.Name}"></apex:outputLabel>
			</td>    
		
		<apex:variable var="counter" value="{!counter+1}"/>
		
		</apex:outputPanel>   
	</apex:repeat>

Line 14 in the above VF markup may give you an issue.  I am freehanding all of this in notepad++ so do not have access to see if it is the correct sytax but you will find out at runtime.  If it does give you an error all you need to do is google the correct syntax to pull from a map in VF and all should be well since the logic should be sound.  
Tate PriceTate Price
You were right. Line 14 is populating incorrectly. I am getting this error ​Invalid field NEW_MEMBER for SObject Desk__c

which means there is no NEW_MEMBER field on the Desk object. I get that. 

I searched around and I think I found the right way to call a map. I then changed the apex label to
 <apex:outputLabel value="{!dsk.NEW_MEMBER[mapKey+counter].createdBy.Name}"></apex:outputLabel>

and now I am seeing this error:

Incorrect parameter type for operator '+'. Expected Text, received Number

I am very lost in the weeds here. What am I doing wrong?
Robert_StrunkRobert_Strunk
Try changing that line to:
<apex:outputLabel value="{!dsk.NEW_MEMBER[mapKey+text(counter)].createdBy.Name}"></apex:outputLabel>

 
Tate PriceTate Price
That solved the problem on the VF page but inside the actual APP, I have a new error:

Unknown property 'String.createdBy'
Error is in expression '{!dsk.NEW_MEMBER[mapKey+text(counter)].createdBy.Name}' in component <apex:outputLabel> in page newreservation