function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Steven KizySteven Kizy 

Outer Join is not working (using relationship)

I am using two tables. Invoice_Header(Parent) and Invoice_Line(Child) tables. Relationship is Invoice_Line__r(Being seen in Invoice_Line table in Schema Builder).

Fields we are going to Use: 

Invoice_Header:Invoice_no, Amount_minus_tax(we want sum of it), Salesrep__c

Invoice_Line: Invoice_no, prduct_group_id

Query Question: I want sum of all invoices (Amount_minus_tax) where salesrep is equal to Steven, product_group_Id = 'MERCH' 

 

I tried this Query, but it is not working:

select sum(amount_minus_tax__c) from CommercientSF10__EPICOREP21_InvoiceHeader__c where CommercientSF10__Salesrep__c = 'a0d61000004Jcvj' and CommercientSF10__EPICOREP21_Invoice_Lines__r.CommercientSF10__product_group_id__c = 'MERCH'

I can excecute query with left join but this will be having more rows than we need so I want to use Right join as shown in above Query.

Working Query with Left Join:

select CommercientSF10__invoice_no__c, (select CommercientSF10__invoice_no__c from CommercientSF10__EPICOREP21_Invoice_Lines__r where CommercientSF10__product_group_id__c = 'MERCH' ) from CommercientSF10__EPICOREP21_InvoiceHeader__c where CommercientSF10__Salesrep__c = 'a0d61000004Jcvj'

 

 

LBKLBK
IF you have 1:N relationship between Invoice_Header and Invoice_Line, AGGREGATION query on Invoice_Header with GROUP BY field from Invoice_Line will  not yield the correct value.

If you just restrict the GROUP BY to Invoice_Header, you can use the below query. 
SELECT sum(amount_minus_tax__c) FROM CommercientSF10__EPICOREP21_InvoiceHeader__c GROUP BY  CommercientSF10__Salesrep__c HAVING CommercientSF10__Salesrep__c = 'a0d61000004Jcvj'
If you bring in a Child table, there will be duplicate records will be summed up and you will end up with an inflated figure.

For example, If the Invoice_Header has one entry with $500 and Invoice_Line has two related entries that have the criteria matching your expectation, the SUM would show $1000.

My Suggestion would be to Use a Currency figure in Invoice_Line (if you have one) for the AGGREGATION.

For example, if you have a Price column in Invoice_Line (which adds up to your amount_minus_tax__c), you can use the query below.
  
SELECT SUM(price__c), CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c FROM CommercientSF10__EPICOREP21_InvoiceLine__c GROUP BY CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c,   CommercientSF10__product_group_id__c CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c = 'a0d61000004Jcvj' AND CommercientSF10__product_group_id__c = 'MERCH'
Hope this helps.
 
Steven KizySteven Kizy

Thank you so much for your reply, sir. I really appreciate it.

As you showed, I tried trhis Query,

 

SELECT SUM(CommercientSF10__unit_price__c), CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c FROM CommercientSF10__EPICOREP21_InvoiceLine__c GROUP BY CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c, CommercientSF10__product_group_id__c HAVING CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c = 'a0d61000004Jcvj' AND CommercientSF10__product_group_id__c = 'MERCH'
 

 

Showing error:

SUM(CommercientSF10__unit_price__c), CommercientSF10__EPICOREP21_Invoice_Header__r.CommercientSF10__Salesrep__c
                                     ^
ERROR at Row:1:Column:45
Didn't understand relationship 'CommercientSF10__EPICOREP21_Invoice_Header__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Steven KizySteven Kizy
I have good background in using SQL, but this relationships things in SOQL is driving me crazy. here I am attaching photos of relationshipTwo TablesRelationship Name
LBKLBK
Can you try to replace CommercientSF10__EPICOREP21_Invoice_Header__r with CommercientSF10__EPICOREP21_Invoice_Lines__r?
 
LBKLBK
Your query would be something like this...
  
SELECT SUM(CommercientSF10__unit_price__c), CommercientSF10__EPICOREP21_Invoice_Lines__r.CommercientSF10__Salesrep__c FROM CommercientSF10__EPICOREP21_InvoiceLine__c GROUP BY CommercientSF10__EPICOREP21_Invoice_Lines__r.CommercientSF10__Salesrep__c, CommercientSF10__product_group_id__c HAVING CommercientSF10__EPICOREP21_Invoice_Lines__r.CommercientSF10__Salesrep__c = 'a0d61000004Jcvj' AND CommercientSF10__product_group_id__c = 'MERCH'

 
Steven KizySteven Kizy

Sir, Thank you for your valuable time and response. I really appreciate it.

I have tried many different things to form working query and it is showing the same error at CommercientSF10__EPICOREP21_Invoice_Lines__r. And this is giving me really hard times.

 

Error:

SUM(CommercientSF10__unit_price__c), CommercientSF10__EPICOREP21_Invoice_Lines__r.CommercientSF10__Salesrep__c
                                     ^
ERROR at Row:1:Column:45
Didn't understand relationship 'CommercientSF10__EPICOREP21_Invoice_Lines__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.