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
Michael MMichael M 

Query to obtain all files related to Leads where the created date was last 7 days

Hi, my task is to schedule apex to delete all files related to Lead records after 7 days from the file's createddate. 

How would I write that query/ DML? 
Best Answer chosen by Michael M
SarvaniSarvani
Hi Michael,

ContentDocument.CreatedDate > Last_N_Days:7, I guess this will not return any records. Indirectly its seacrhing for future created date records. 
Assume this is executing today which 2/18/20 the LAST_N_Days:7.
Lets say we have record whose createddate is 2/14/20 which is within last 7 days. Its checks if the date is greater than last 7 days (But the date is in range of last 7 days). Answer is no therefore this record is not fetched.
Consider other record which has closeddate 2/20/2020(future date) this is fetched because the day is greater than last 7 days. (In real time there is no record with future createddate).
Consider 2/1/20 which is far less than past 7 days so this is not returned.

However, if your requirement is to delete files which are older than 7 days (which is delete after 7 days of creation date).

Try using below query: 
Select Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  FROMcontentDocumentLink where Linkedentityid IN (SELECT Id FROM lead)  and ContentDocument.CreatedDate < Last_N_Days:7
Thanks

All Answers

SarvaniSarvani
Hi Michael,

Please try with below query. The query will give you all the fiels related to leads which are created with in last 7 days. 
Select Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  FROM contentDocumentLink where Linkedentityid IN (SELECT Id FROM lead)  and ContentDocument.CreatedDate = Last_N_Days:7
Or to query all the files which are created one week ago you can use below: (which is 7 days from the file's createddate).
  
Select Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  FROM contentDocumentLink where Linkedentityid IN (SELECT Id FROM lead)  and ContentDocument.CreatedDate < Last_N_Days:7

Hope this helps! Please mark as best if it does.

Thanks​​​​​​
Michael MMichael M
Thank you, Sarvani. Quick question: on the second query, shouldn't it be: ContentDocument.CreatedDate > Last_N_Days:7? Or no?
SarvaniSarvani
Hi Michael,

ContentDocument.CreatedDate > Last_N_Days:7, I guess this will not return any records. Indirectly its seacrhing for future created date records. 
Assume this is executing today which 2/18/20 the LAST_N_Days:7.
Lets say we have record whose createddate is 2/14/20 which is within last 7 days. Its checks if the date is greater than last 7 days (But the date is in range of last 7 days). Answer is no therefore this record is not fetched.
Consider other record which has closeddate 2/20/2020(future date) this is fetched because the day is greater than last 7 days. (In real time there is no record with future createddate).
Consider 2/1/20 which is far less than past 7 days so this is not returned.

However, if your requirement is to delete files which are older than 7 days (which is delete after 7 days of creation date).

Try using below query: 
Select Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  FROMcontentDocumentLink where Linkedentityid IN (SELECT Id FROM lead)  and ContentDocument.CreatedDate < Last_N_Days:7
Thanks
This was selected as the best answer
Michael MMichael M
That makes sense, thank you! This seems to query the files I'm looking for. 
Michael MMichael M
Hi Sarvani, now that I look back, this doesn't seem to work. I uploaded a file this morning, and then I ran this query in Query Editor, and it returned the files I uploaded today....

SELECT Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  
                FROM contentDocumentLink 
                WHERE Linkedentityid 
                IN (SELECT Id FROM lead)  
                AND ContentDocument.CreatedDate < Last_N_Days:7

Why isn't it working?
SarvaniSarvani
Hi Michael,

No the query will not fetch the records which is not older than 7 days. I have tested what you said uploading a file and queried with same but did not get any results and worked as expected. I am not sure what's going on in your case (Might be other files attached to the record)

Please make sure you are looking for the correct file. Also cross check the file's ContentDocument.CreatedDate field in the result record which should not be today's. 

Thanks
Michael MMichael M
I think that's it-- the document itself was created more than 7 days ago. Is there a way to write the query so that it fetches documents that were UPLOADED (not created) more than 7 days ago?