function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jeff SusichJeff Susich 

Need help with a SOQL on custom objects

I have an object called “Invoice Tracking”. It is the parent to “Invoice and Quote Line Items”. So one “Invoice Tracking” record, and many “Invoice and Quote Line Items” records.
 
I want to display certain "Invoice and Quote Line Items" records in a related list of an App that uses SOQL queries to build a list. I want ONLY records in which the “Activity Date” date field on an "Invoice and Quote Line Item" record is before a “Last Date Invoiced” date field on the Invoice Tracking object. I am using the following SOQL query:

Select Name FROM Invoice_Tracking__c WHERE Invoice_Sent__c < Invoice_Quote_Line_Items__c.r.Activity_Date__c 

It's not working. Any help appreciated.


 
Abdul KhatriAbdul Khatri
SOQL doesn't support that. Can you check anyway

User-added image

One possible solution could be below.
Map<Id, List<Invoice_Quote_Line_Items__c>> returnMap = new Map<Id, List<Invoice_Quote_Line_Items__c>>();
for(Invoice_Tracking__c it : [SELECT Name, Invoice_Sent__c, (SELECT Activity_Date__c FROM Invoice_Quote_Line_Items__r) FROM Invoice_Tracking__c]) {
    
    if(it.Invoice_Quote_Line_Items__r == null) continue;
    
    for(Invoice_Quote_Line_Items__c iqli : it.Invoice_Quote_Line_Items__r) {
        
        if(it.Invoice_Sent__c > Invoice_Quote_Line_Items__r.Activity_Date__c) continue;
        
        if(returnMap.containsKey(it.Id)){
            List<Invoice_Quote_Line_Items__c> tempList = returnMap.get(it.Id);
            tempList.add(iqli);
            returnMap.put(it.Id, tempList);
        }else{
            returnMap.put (it.Id, new List<Invoice_Quote_Line_Items__c> {iqli});
        }
        
    }
    
}



 
Abdul KhatriAbdul Khatri
Is this helped?