+ Start a Discussion
Wayne_ClarkWayne_Clark 

SOQL Help Needed

Hello,

 

I am trying to build an apex controller for a visualforce email template that will pull information from multiple related list on a custom object. Sort of like the Quote PDF's work with including the Quote Line Items.

 

Order__c is the master object, Order_Line_Items__c and Order_Stops__c are the detail objects. 

 

There is also another detail object on the Order__c called Order_PDFs__c where I will generate the email from.  I'm trying to get the Order_PDF__c email template to include information from the Order_Line_Item and the Order_Stops where the id's all match the master order ID.  Below is a drawing of the schema and what I have as far as code so far.  I'm having trouble pulling the order line items and order stops that only contain the same order ID.

 

I'm not sure how to match the ID of the order in the Order PDF with the order ID in the Order Line Item in the Where clause.  Can anyone point me in the right direction?  Thanks in advance!

 

Visualforce Email Component Code:

<apex:component controller="SalesOrderEmailExtension" access="global">
    <apex:dataTable value="{!CustomerOrderList}" var="cust">
        <apex:column>
            <apex:facet name="header">Order Line Item Name</apex:facet>
            {!cust.Name}
        </apex:column>
            <apex:column>
            <apex:facet name="header">Product</apex:facet>
            {!cust.Payor__r.Name}
        </apex:column>
    </apex:dataTable>
</apex:component>

 

 

 

Apex Controller:

public with sharing class SalesOrderEmailExtension {
    public SalesOrderEmailExtension() {
    }


    private final List<Customer_Order_Line_Item__c> CustomerOrderList;
    
public SalesOrderEmailExtension(ApexPages.StandardController controller) {
        CustomerOrderList = [select id, Name, order__c, Order__r.ID, Product__c,
        from Customer_Order_Line_Item__c  where Order__r.ID = Order_PDFs__r.order__r.id];
    }

public List<Customer_Order_Line_Item__c> getCustomerOrderList() {
            return CustomerOrderList;
      }

}

 

 

 

TinkuTinku

try

 

where Order__r.ID := Order_PDFs__r.order__r.id

 

Or else you can assign one of the Id's to a variable and then use that variable in the where clause. If you post the error, i think i can point u in the right direction.

One more option is to try out the query in Force.IDE. you will know the relationships better there.

Wayne_ClarkWayne_Clark

Tinku,

 

Here is the error message I received: Variable does not exist: Order_PDF__r.order__r.id at line 13 column 62

The correct object name is Order_PDF__c, not PDFs__c. 

 

I thinik I have to somehow query the Order_PDF_r.order__r.id before I put it in the where clause.  I'm just not sure how to query the order__r.id from two seperate objects and pull the records of the order__r.id's that match. 

 

I'm not sure how to query in Force IDE, I only use that to put the class and test methods into production.

 

Thanks


TinkuTinku

It says varialbe Order_PDF__r.order__r.id,

 

Check for the avilable variables.

 

Write the same SOQL query in your Force.IDE

 

On what object are you writing the SOQL query? Check  what is the field where ID of order_r is stored?

 


TinkuTinku

There is also a way to do this.

 

declare ID ID1;

              ID1= Order_PDF__r.order__r.id;

 

And then inside you SOQL query, say where order_r.id=:ID1;

 


Wayne_ClarkWayne_Clark

Tinku,

 

I think you're on the right track. 

I'm writing this class on the Order_PDF__c object and the Order ID field would be stored in Order__r.ID on both the Order_PDF__c object and the Customer_Order_Line_Item__c object.

 

I'm not sure which line to delcare ID1 in like you suggested, here is the code I have so far in my apex class. I think you're right though, once I declare it and am able to get it in the where clause, it will list the record I need.

 

I'm able to query both objects in the Apex class, but I'm still not sure how to only bring records from both objects where the Order__r.ID is equal.

 

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public with sharing class SalesOrderEmailExtension {

    public SalesOrderEmailExtension() {

    }


    private final List<Customer_Order_Line_Item__c> CustomerOrderList;
    private final List<Order_PDF__c> ID1;

    
public SalesOrderEmailExtension(ApexPages.StandardController controller) 
        {        
        CustomerOrderList = [select id, Name, order__c, Order__r.ID, Payor__r.Name, Customer_Charge__c, Product__c, Quantity__c, Total_Price__c
        from Customer_Order_Line_Item__c ];
        }
    
        {
        ID1=[select Order__r.Id from order_pdf__c];
        }

public List<Customer_Order_Line_Item__c> getCustomerOrderList() {
            return CustomerOrderList;
      }
      
public List<Order_PDF__c> getID1() {
            return ID1;
      }

}
TinkuTinku

Just declare ID1 as a global variable to this public class.

 

Declare it as ID ID1;

 

ID1 = Customer_Order_Line_Item__c.Order__r.ID

 

And you will be good to go

Wayne_ClarkWayne_Clark

Thanks Tinku,

 

I'm not able to insert that line anywhere in the class without getting error messages.

 

After doing some more research, I think I need more SOSL help since I need to bring data over from multiple objects. 

 

I'm writing the class on a child obejct, but need to match data from 2 other child objects, which are all related to the master object (Order__r.ID) 

 

I think I figured out how to write these in Force IDE, I'll let you know how I do.

 

 

Wayne_ClarkWayne_Clark

Still no luck with this, I'll have to rearrange our objects for now and keep trying to figure this out