Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
HL-DevHL-Dev 

Parent to Child relationship query question

I have a parent to child relationship query question. Here's a snippet of my code:
 
Code:
var queryString = "SELECT FirstName, LastName, " +
          "(SELECT Subject, Id FROM Events WHERE Subject = 'x') " +   
          "FROM Contact " +
          "WHERE Organization__c = '{!Contact.Organization__c}' " +
          "AND ZoneR__c = 'x' " + 
          "AND Sub_ZoneR__c = 'x' " +
          "AND Primary_Cost_Center__c = 'xxxx' " +
          "AND ((Production__c = 'A') OR (Id = '{!Contact.Id}') OR " +
          "(Production__c = 'B') OR (Production__c = 'C') OR " +
          "(Production__c = 'C')) " +
          "LIMIT 100";
var queryResult = sforce.connection.query(queryString);
results = queryResult.getArray ("records");
 
I'm trying to gather events information from a list of contacts. In my query, I'm trying to get a list of contacts that match a certain criteria and for each contact, I get the events' id and subject if they have any. Question is: how can I store the results that I get back from the parent-child relationship into a variable? In my example, if I wanted to get the contact's first name from the query result, I could assign a variable to results.FirstName. But I'm not sure how to accomplish the same for the results I get from the Parent to Child relationship (the events' info that I get back).
 
Thanks in advance.
 


Message Edited by HL-Dev on 06-13-2008 01:27 PM
Greg HGreg H
Your relationship query may result in many Event records being returned for any contact.  So you will need to loop through those records as you are looping through the records in your "results" array variable.  I wrote the code below to loop through the Events associated to your first results record (results[0]) but if there are no Events associated to this contact then your resulting array (eventArray) will be empty.
Code:
var eventArray = new Array(); //will hold events associated to contact in results[0]
var tempEventsArray = results[0].Events; //holds Event records returned from relationship query
if (tempEventsArray != null && tempEventsArray.size > 0) { //if the relationship query has results
 var eventRecords = tempEventsArray.getArray("records"); //get an array of those Events
 for (var a=0; a<eventRecords.length; a++) { //for all records from Event query
   eventArray.push(eventRecords[a].Subject,eventRecords[a].Id); //push the record Subject and Id to my eventArray variable
 }
}

Try incorporating something like this into your code and see if it helps.
-greg
HL-DevHL-Dev

I will give this a shot. Thanks!
mtbclimbermtbclimber
Have you heard of/tried visualforce?  Based on what I see you doing here the Visualforce solution might look like this:

Visualforce Page:
<apex:page controller="ContactsWithEventsCon" tabstyle="Contact">
    <apex:repeat value="{!contacts}" var="c">
        <apex:pageBlock title="Contact Detail: {!c.name}">
            <apex:pageBlockSection title="Contact Fields">
                <apex:outputField value="{!c.phone}"/>
                <apex:outputField value="{!c.email}"/>                
            </apex:pageBlockSection>
            <apex:pageBlockSection title="Events" columns="1">
                <apex:pageBlockTable value="{!c.events}" var="e">
                    <apex:column value="{!e.activitydatetime}"/>
                    <apex:column value="{!e.subject}"/>                    
                </apex:pageBlockTable>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:repeat>
</apex:page>

Apex Controller:

public class ContactsWithEventsCon {
    public List<Contact> contacts { 
        get {
            return [select name, email, phone,
                           (select subject, activitydatetime 
                            from events 
                            order by activitydatetime desc)
                    from contact
                    limit 10];
        }
    }
}

 If you are not familiar with Visualforce follow these steps in your Developer Edition or Sandbox account:
  1. Go to your user record under setup and click edit
  2. Check the "Development Mode" preference (if you don't see it then you are probably not an administrator)
  3. Save your user record
  4. In your address bar type /apex/contactsWithEvents after ...salesforce.com
  5. Hit enter
  6. Click the link that says "Create Page contactsWithEvents"
  7. Click the "Page Editor" tab in the footer
  8. Paste in the page markup above
  9. Click the save button in the toolbar
  10. When you are prompted that the controller ContactsWithEventsCon does not exist, click the link to create it
  11. You'll then be prompted to create a "getContacts" method, don't click on that. Instead click on the controller tab.
  12. Replace the default controller code with that from above
  13. Click the save button in the toolbar.
  14. Viola!
These and other getting started details can be found in the visualforce developer guide

[edit] thought you might be interested in the output of the above Visualforce page/controller - see screenshot below my signature

Regards,


Message Edited by mtbclimber on 06-21-2008 11:15 AM