1

I have a Opportunity object which has a few objects like Job Documents. For example: I have Opportnity A which has a 3 JOB DOCUMENTS.

I need to build a report based on Opportunity with JOB DOCUMENTS but in report I would like to have only the newest JOB DOCUMENT. I do not want to all JOB DOCUMENTS but only one - newest.

I think that I can create a checkbox and write a trigger (after update) which will mark this checkbox on true when one of those 3 records have been modified. Of course if I have 3 JD (A,B,C) when JD A has been modified checkbox on JD A will be true but on the rest B and C will be false.

This is a good idea or maybe I can achieve this in other way ? Formula or flow ?

2
  • Define the newest JOB DOCUMENT. Does this mean the latest child record to be created of Opportunity i.e CreatedDate?
    – TSmith
    Jun 18, 2020 at 13:11
  • It seems that a filter most recent record for type is still not available - or am i mistaken? For Data model staging i need an option to show only the most recent records of type. For example another sytems creates records of type "product", "model", "color" and so on. I want to find for each type when was the latest (the most recent) data delivery for this record type.
    – surfmuggle
    Apr 26 at 7:15

3 Answers 3

1

Here is another approach which is similar to @Mahmood's answer, but this requires a master-detail relationship between Opportunity and Job Document object.

  1. Creating a Roll-Up Summary field Latest Job Document on Opportunity, choose Job Document as the Summary object, and use MAX formula to aggregate the date field.

  2. Create a formula field of type Checkbox on Job Document object, and use formula: <your date field> = Opportunity.'Latest Job Document'

  3. And you can filter your report by that checkbox to show only the latest job document.

2
  • 1. I've created a a field on Opportunity called 'Latest Job DC'. 2. Second I've created an PB which will be update automatically this field 'Latest Job DC' = LastModifiedDate from Job_Document__c when is it edited or created. 3. On Job_Document__c object I've created a formula (checkbox) which compare those dates and mark true when are equal. Do I need this roll-up summary ? What about when I delete some record which is actually latest ? I that situation when I delete actually newest record system should mark checbox as true on the next one which is newest. Jun 19, 2020 at 9:55
  • With roll-up summary, you don't need any process builder, because Latest Job DC would always have the MAX(date). If you use process builder, that means you got to handle the deletion scenario as well, which probably needs a trigger (@cropredy's answer). Jun 19, 2020 at 11:38
0

Your idea is fine, but how about

  1. Creating a datetime field on Opportunity, call it Latest Job Document.
  2. Fill it with CreatedDate when a Job Document record is created, using a Workflow or Process builder.
  3. Now that Opportunity (Parent) has the datetime reference, you can leverage it using a field in Job Document. Create a cross-object formula field on Job Document of type Checkbox and check the createdate of Job Document record with the Opportunity field we created in step-1.
  4. Now, in the report you can easily apply the filter to only show latest job document.
2
  • What if the most recent Job Document record is deleted? Opportunity's Latest Job Document would not have a match of Job Document record. Jun 18, 2020 at 13:57
  • Yeah, delete wont be handled. For that you could dlrs (as mentioned in other asnwer). or create your own trigger.
    – Mahmood
    Jun 18, 2020 at 13:59
0

One approach

  • Add to Opportunity a field called Most_Recent_Job_Doc_Datetime__c
  • Set by a trigger on Job Document (handles insert, update, delete) , or, more easily, use DLRS to do this without code
  • Add a formula checkbox field on Job_Document__c called Is_Most_Recent__c that is compares the Job Document's datetime to the Opportunity.Most_Recent_Job_Doc_Datetime__c

Then you can use reports to filter on the Job_Document__c.Is_Most_Recent__c

Lightning Flow could also do this ...except it can't manage deletes - hence the trigger approach (which is how DLRS works under the hood)

2
  • I've created a DATETIME field on Opportunity a now I should create a formula (checkbox) on JOB DOCUMENT object with formula IF(Opportunity.Most_Recent_Job_Doc_Datetime__c = Job_Document__c.CreatedDate, true, false ) ? Jun 19, 2020 at 8:49
  • YES, but you have to populate the field on the Opportunity with the correct value - I suggest using DLRS
    – cropredy
    Jun 19, 2020 at 14:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .