0

I’m trying to create a SOQL query that returns attachment IDs from a custom object (Invoice__c) related to Account.

The tricky part is to extract only the most recent attachment from each Invoice__c. Please note, an Account may have more than one invoice object, each with several attachments.

I was able to create the following query:

Select Id FROM Attachment WHERE ParentId IN (SELECT Id from Invoice__c WHERE Account_ID__c = 'xxx')

But the problem is I’m getting ALL attachments, whereas I need only the most recent Attachment from each invoice. The result should be multiple Id’s, but not all.

If I add "ORDER BY Createddate LIMIT 1", then, as a result, I'm getting only 1 Id, which is not what's required.

Any advice would be much appreciated!

3

You need a subquery for this:

select (select id from attachments order by createddate desc limit 1) from invoice__c

The result will be a list of Invoice__c records, with a child record for each attachment:

for(Invoice__c invoiceRecord: results) {
  for(Attachment attachmentRecord: invoiceRecord.Attachments) {
    // most recent attachment for that invoice here
  }
}
  • The nested statement does the trick! However, as an output, I get Id's with additional symbols: trueAttachment00PD00000xxxUo9MAE00PD00000xxxUo9MAE1,trueAttachment00PD00000yyy99KMAS00PD00000yyy99KMAS1. How do I get rid of excessive symbols? I only need the following Id's 00PD00000xxxUo9MAE and 00PD00000yyy99KMAS – Ed T. Nov 4 '18 at 18:00
  • @EdT. They shouldn't be there. I presume you're seeing this in a debug statement, so it's probably something you've included in the debug statement. – sfdcfox Nov 4 '18 at 19:55
  • You're right, I'm using the query in Conga Mail Composer. – Ed T. Nov 5 '18 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.