Most Recent Notes & Attachments in Formula

Question

I am currently using this formula in a checkbox field:

(TODAY() - LastActivityDate) <= 30

Is it possible to do this with the Notes & Attachments related list?

Would it be:

(TODAY() - NotesAndAttachments) <= 30

Answer 1

If you are open to learning dlrs (free managed package written by @AndrewFawcett), you can get the data you need without writing a single trigger or batch. An example of how to do so is included below:

• Parent Object - e.g. Account
• Relationship Name - ParentId
• Child Object - Attachment (and a separate rollup for Note)
• Field To Aggregate - CreatedDate
• Field To Order By - CreatedDate (won't work without this part)
• Aggregate Operation - Last (most recent, whereas First would be least recent)
• Aggregate Result Field - e.g. Most_Recent_Attachment__c

Not sure if you can roll them both up to the same field, or if you will have to roll them up separately and then use a third formula for the checkbox. Your formula will then look like:

OR(
    NOW() - Most_Recent_Attachment__c <= 30,
    NOW() - Most_Recent_Note__c <= 30
)

You can calculate the most recent value for all records in your org by hitting the Calculate button on the Lookup Rollup Summary detail page.

Answer 2

Standard Salesforce functionality does not allow any modification to Note and Attachment object.

Best Approach to create Attachment trigger can be: Create trigger from Developer console.

You need to click New -> Apex Trigger -> Then you can select SObject as "Attachment" from dropdown.

A trigger can be written on Attachment using Force.com IDE.

A sample code would be:

trigger mostRecent on Attachment (after insert) {

List<Account> parentList = new List<Account>();
for(Attachment att: Trigger.new){
   //check for object for which you want this condtion
   if(String.valueOf(att.parentId).substring(0,3) == '001')
   {
     Account acc = new Account(id=att.parentId);
     parentId.fieldname__c = system.today();
     parentList.add(acc);
   }

}

update parentList;

}

Once you have that value filled you can create your formula to calculate most recent logic.