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
degmodegmo 

SOQL find records on the detail side of a relationship

Hi,
I have master detail relationship between two custom objects.  The master object is called transaction has a date field called 'transaction_date__c' and detail object is called items has a field called 'item_added_date'.  What I am trying to do is, for a given transaction, get the line items whose item_added_date is < the transaction_date.  Could someone point me in the write direction to writing a SOQL for this?
ANUTEJANUTEJ (Salesforce Developers) 
Hi Degmo,

You can try something like the below snippet and modify it as per the names present in your org:
  
list<master__c> mlist=[select id,name,MDate__c,(select id,name,DDate__c from details__r) from master__c];
system.debug(mlist);
for(master__c m : mlist) 
{
    system.debug(m);
    for(Detail__c d: m.details__r)
    {
        if(d.DDate__c < m.MDate__c)
        {
            system.debug(d);
        }
    }
}

In the above snippet of code the values present are as per my org.

Objects: Master__c, Detail__c
Fields: 
MDate__c - date on master object
DDate__c  - date on Detail Object

I queried all the detail object records along with master object records and ran for loops to get only those records where the date on detail record is < than the date field on master record.

I would suggest creating a formula field on detail object records that have the date value of master record and in a single query you can have a filter with out sub queries to get the records which satisfy the date < date on master record.

>> Documentation regarding Formula Field: https://help.salesforce.com/articleView?id=pardot_sf_connector_copy_account_fields.htm&type=5

>> Using Formula fild in soql query: https://salesforce.stackexchange.com/questions/112148/formula-field-in-soql-query

>> Best practices when using soql along with formula field: https://developer.salesforce.com/blogs/engineering/2013/02/force-com-soql-best-practices-nulls-and-formula-fields.html

Let me know if it helps you and close your query by marking it as solved so that it can help others in the future.  

Thanks.