0

I am working with 3 SalesForce objects: Enrollment_New__c, Application_Checklist__c, and Opportunity.

Application_Checklist__c and Opportunity are both in a "child" relationship to the parent Enrollment_New__c.

My ultimate development goal is to put a little snippet of VisualForce on the bottom of an Opportunity page layout that displays the "Requirement" & "Completed Date" fields of whatever Application_Checklist__c records have the same Enrollment_New__c parent as the Opportunity in question.

So I started reading http://developer.force.com/cookbook/recipe/selecting-records-with-a-visualforce-custom-list-controller, because it looked like a good tutorial to experiment with, and realized that perhaps the first thing I should do is write an SOQL query that returns the relevant data (Opportunity.Id, Application_Checklist__c.Requirement__c, & Application_Checklist__c.Completed_Date__c).

I wrote the following small test query (I know, it doesn't quite return what I'll eventually have it return - I'll want to filter on the value of Opportunity, not the value of Enrollment_New__c) :

SELECT (SELECT Id FROM Opportunities__r), (SELECT Requirement__c, Completed_Date__c FROM Application_Checklist__r) FROM Enrollment_New__c WHERE Name='E-0012345'

It returns 2 columns, Opportunities_r and Application_Checklist_r.

Opportunities_r says:

[{"Id":"008z000000ZzBlARG"},{"Id":"003F000000ZzBlAHH"}]

Application_Checklist_r column says:

[{"Requirement__c":"ABCD","Completed_Date__c":null},{"Requirement__c":"ZYXW","Completed_Date__c":"2015-03-11"}]

My dream would have been to be working with a return value more like this (mostly because I'm biased toward traditional relational databases):

OppId               Req     CmplDt
008z000000ZzBlARG   ABCD    null
008z000000ZzBlARG   ZYXW    2015-03-11
003F000000ZzBlAHH   ABCD    null
003F000000ZzBlAHH   ZYXW    2015-03-11

However, after reading about SOQL, I don't think that's in the cards.

I can't find a lot of VisualForce tutorials that involve queries returning cells with sets inside of the cells rather than plain old single-value cells.

Using the return value I've been given, could anyone help me figure out:

  1. what kind of data structure I need to transform these return values into in Apex so that I can move on with my goal of building a VisualForce list of Application_Checklist__c records embedded on an Opportunity page layout and
  2. if it's not obvious, what kinds of Apex data-access methods I might want to use to do that transformation?

Thank you so much!

==================================================

Updated question in response to Brian's guidance that processing this data is just a matter of pulling nested Lists apart (at which point I got stuck because I couldn't actually retrieve a list to try pulling apart, even though I expected one):

Does anyone have any idea why myQuery() below returns "0" instead of "1" when the Query Editor definitely returns 1 line for the following query?

SELECT (SELECT Id FROM Opportunities__r), (SELECT Requirement__c, Completed_Date__c FROM Application_Checklist__r) FROM Enrollment_New__c WHERE Id IN (SELECT Related_Admission_Record__c FROM Opportunity WHERE Id = '008z000000ZzBlARG') AND Id IN (SELECT Application__c FROM Application_Checklist__c)

My Apex code:

public class My20150327HelloWorld {
public static string myQuery() {
    string queryResult1;
    List<Enrollment_New__c> myQueryResult = [SELECT (SELECT Id FROM Opportunities__r), (SELECT Requirement__c, Completed_Date__c FROM Application_Checklist__r) FROM Enrollment_New__c WHERE Id IN (SELECT Related_Admission_Record__c FROM Opportunity WHERE Id = '008z000000ZzBlARG') AND Id IN (SELECT Application__c FROM Application_Checklist__c)];
    queryResult1 = string.valueof(myQueryResult.size());
    return(queryResult1);
    }
}

==================================================

Update: Brian helpfully pointed out that I can't get real data in a test-class environment. Thanks for everything, Brian! Question closed for now.

0

If I'm understanding correctly, you will be looking at a single Opportunity record detail page, and you want to display the Application_Checklist__c records that have the same parent Enrollment_New__c?

You can do this with a simple SOQL query in the controller for the VF page that you will be adding to the Opportunity page layout. You create a List<String> and a List<Date> as class variables and provide getter and setter methods for each, and simply use the VF markup to retrieve the values from the controller. As far as data structures go, your SOQL query above is already returning a List of the two pieces of information you want (Requirement__c and Completed_Date__c), so you shouldn't need to do any sort of transformation.

  • Hi Brian, thanks for the input. Yes, you understood me correctly - thanks for checking. I have a few questions. 1) You say "with a simple SOQL query" but then refer to the one I already wrote. That's left me a little confused - do you think I need a rewrite of the SOQL or is mine one that you think would work? 2) To access the List of Requrirement__c and Completed_Date__c, do I just refer to them with a "Application_Checklist_r." prefix or something? Sorry, I'm still VERY clumsy with syntax for manipulating data in various types of Apex collection - thanks! – k.. Mar 27 '15 at 16:05
  • For the query, you have the right idea, but since the Opportunity in question will change for each record, you will want to retrieve the ID of the single Opportunity that you're looking at - e.g. by using StandardController.getid() in your controller - and then do a query that retrieves the Application_Checklist__c records that have the same parent based on that id. Structure this query similar to the one you listed above, and parse the list that SOQL returns to assign the Requirement__c values to the List<String> and the Completed_Date__c to the List<Date>, then render them on the VF page. – Brian Mansfield Mar 27 '15 at 16:26
  • Or, if you wish, you can instead of the two Lists, use one List<Application_Checklist__c> in your controller, but it seems that you only require the two fields Requirement__c and Completed_Date__c, so the two separate Lists may be easier to understand and manage. – Brian Mansfield Mar 27 '15 at 16:27
  • Hi Brian, So, if I understand correctly, you're telling me that there's nothing weird about getting at this data - it's just patiently pulling lists of various data types apart, one nest at a time. I tried doing that (was working on something to return the first value for Requirement__c found for an Opportunity with Id 008z000000ZzBlARG), but I'm getting a weird issue where my query definitely returns 1 row when executed in Query Editor, yet returns a List<Enrollment_New__c> of size 0 when executed by an Apex class. Code in updated question above - does anyone have any insights? Thanks! – k.. Mar 27 '15 at 16:52
  • Are you running the Apex from a test context? By default, real org data is obscured from test classes. You can set seeAllDate=true, but it's not recommended. – Brian Mansfield Mar 27 '15 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.