You need to sign in to do that
Don't have an account?
Zaheer Khan
How to join multiple objects using SOQL, Apex, or ....?
Hi,
I need to join four objects for my Visualforce page.
I am trying to get customer info, ordered products, and invoiced amount. For example Customer “John” ordered product “ABC” and invoiced amount is “$1200”. In order to get this done, I need to join four objects. Two objects are custom objects and two standard objects.
Objects are: Account, Invoice__c, Invoice_Line__c, Product2
Account à Invoice__c à Invoice_Line__c
Product2 à Invoice_Line__c
Invoice__c object has Account__c lookup column to Account.Id column for relationship
Invoice_Line__c object has Invoice__c lookup column to Invoice__c.Id column for relationship
Invoice_Line__c object has Product__c lookup column to Product2.Id column for relationship
I need to merge these four objects to get the following information:
Account.Customer_Id__c, Account.Name from Account object
Invoice__c.Invoice_Id__c, Invoice__c.Invoice_Date__c from Invoice__c object
Invoice_Line__c.Brand__c, Invoice_Line__c.Name, Invoice_Line__c.Net_Amount_US__c from Invoic_Line__c object
Product2.ID__c, Product2.Brand__c, Product2.Description__c, Product2.Family, Product2.Product_Line__c, Product2.Product_Series__c from Product2 object
I am new to SOQL and Visualforce. What is the best way to get it done? Your help will be appreciated.
I need to join four objects for my Visualforce page.
I am trying to get customer info, ordered products, and invoiced amount. For example Customer “John” ordered product “ABC” and invoiced amount is “$1200”. In order to get this done, I need to join four objects. Two objects are custom objects and two standard objects.
Objects are: Account, Invoice__c, Invoice_Line__c, Product2
Account à Invoice__c à Invoice_Line__c
Product2 à Invoice_Line__c
Invoice__c object has Account__c lookup column to Account.Id column for relationship
Invoice_Line__c object has Invoice__c lookup column to Invoice__c.Id column for relationship
Invoice_Line__c object has Product__c lookup column to Product2.Id column for relationship
I need to merge these four objects to get the following information:
Account.Customer_Id__c, Account.Name from Account object
Invoice__c.Invoice_Id__c, Invoice__c.Invoice_Date__c from Invoice__c object
Invoice_Line__c.Brand__c, Invoice_Line__c.Name, Invoice_Line__c.Net_Amount_US__c from Invoic_Line__c object
Product2.ID__c, Product2.Brand__c, Product2.Description__c, Product2.Family, Product2.Product_Line__c, Product2.Product_Series__c from Product2 object
I am new to SOQL and Visualforce. What is the best way to get it done? Your help will be appreciated.
You have created a lookup field in Invoice Line for Product2. You have to use that api name. for example
if the lookup field name is product__c then the query will be like (Kindly check the lookup field api name)
Product__r.id,Product__r.Brand__c
If this solves your query. Mark it as answer.
Thanks
Manoj S
All Answers
This is my understanding about your hierarchy
1. Account
2. Invoice
3. Invoice Line Item
Product is not realted to account and invoice. we always have to start the query from the lowest child object. In our case its Invoice Line.
This is the sample query
Select id,Brand__c ---------------- (Invoice Line Related Fields)
Product2.id,Product2.Brand__c, ------------ (Products related to that invoice Line)
Invoice__r.id,Invoice__r.Invoice_Date__c, ----- (Invoice related to Invoice Line)
Invoice__r.Account__r.id, Invoice__r.Account__r.Name ---- (Acconut related to Invoice Line's Invoice)
u can add more fields as you wish.
Please mark this as answer if it helps.
Thanks
Manoj S
Thanks for your reply.
Query is working fine with linked objects but generates error when I include Product2 object. For example, as you said start from child object, so Invoice Line >> Invoice >> Account join works but when I include Product2 object, Invoice Line >> Product2 >> Invoice >> Account, it generates an error.
Please see code below (it works fine without Product2 object):
Select id, Brand__c, Invoice__r.id,Invoice__r.Invoice_Date__c, Invoice__r.Account__r.id, Invoice__r.Account__r.Name
From Invoice_Line__c
But the following code genrates error (with Product2 object)
Select id, Brand__c, Product2.Id, Product2.ID__c, Invoice__r.id,Invoice__r.Invoice_Date__c, Invoice__r.Account__r.id, Invoice__r.Account__r.Name
From Invoice_Line__c
Error messege says "INVALID FIELDS: Select id, Brand__c, Product2.Id, ..... Did not understand relationship 'Product2' in field path..................."
Look forward to hear from you soon.
Zaheer Khan
You have created a lookup field in Invoice Line for Product2. You have to use that api name. for example
if the lookup field name is product__c then the query will be like (Kindly check the lookup field api name)
Product__r.id,Product__r.Brand__c
If this solves your query. Mark it as answer.
Thanks
Manoj S
Thanks for your help. SOQL query is working fine now. As you suggested, api name was not right.
Thank you so much
Zaheer