Discussions

Expand all | Collapse all

highlight most recent entry in group on report

  • 1.  highlight most recent entry in group on report

    Posted 07-16-2019 18:09
    I am trying to see if it is possible to highlight or conditionally format the most recent entry on a report _in each group_ - so if the report is grouped by Manager ID, and shows several updates per manager (see below), I want the latest entry to be a different color or highlighted


    ID       Date      Update                       notes
    1234
    _1234  7/10/19  update here                notes and such
    1234  7/1/19    another update here   more notes
    1234  6/15/19  another one                blah blah
    _5678_
    5678  7/9/19    update and what not   some notes
    5678  7/5/19    here is an update        note note note
    5678  5/15/19  again with an update   notes and notes and notes


    So the first lines for each ID would be highlighted (the one on 7/10/19 for 1234 and the one on 7/9/19 for 5678)

    Please help!!  And thanks in advance :)


  • 2.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 18:14
    This answer assumes:

    1.  Your ID (group value) is a related-reference field table parent record value
    2.  That your report will always include the latest note/entry

    In your Parent < Child relationship, build a summary-field to bring back the Maximum [Record ID#] from the child table.  Call this field [Max Notes Rec ID#]

    In your Parent < Child relationship add the new [Max Notes Rec ID#] as a Lookup field back into the child-table

    In your child-table, build a formula-checkbox field [Latest Note].  Formula:

    [Record ID#] = [Max Notes Rec ID#]

    In your report, you can then use Row colorization on the rows where the [Latest Note] checkbox is TRUE.


  • 3.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 18:24
    To piggy back on QBPros_IDS response, this was exactly what I was thinking, but they beat me to it.


  • 4.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:01
    My key field is not the Record ID - it is the Manager ID....


  • 5.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:06
    The principle is the same.  You still want the maximum Record ID# for the child record; and to look that up into the child table so you can evaluate if the [Record ID#] = [Max Record ID#] to determine it is still the latest-child record for the parent record.


  • 6.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:10
    Well I tried that, and it was checking the box on dates that are earlier than the latest update... 


  • 7.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:13
    It really needs to be based on that date field.....


  • 8.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:13
    Can you call me please at 626 771 0454


  • 9.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:19
    Thank you so much, IDS (I didn't catch your name) - You are awesome!


  • 10.  RE: highlight most recent entry in group on report

    Posted 07-16-2019 20:39
    You're welcome.  
    Laura Thacker