Flag latest update based on date

Hello,

I'm trying to set a star flag for a row that meets a specific criteria. My table has a list of project codes and project updates containing a date. I'd like to be able to filter the list to a distinct list of projects showing only the updates with the latest date.

I tried using the following:

=MAX(COLLECT([Update Date]:[Update Date], ProjectCode:ProjectCode, ProjectCode@row ))

This returns a date, but I don't know how to compare it against [Update Date]@row to flag only the row with the latest date.

Thanks for any help!

Find more posts tagged with

Accepted answers

Paul Newcome

You would use an IF statement.

=IF(MAX(COLLECT(………..)) = [Update Date]@row, 1)

Adam Murphy

Hi @mela0645, it looks like your formula already compares within the Update Date column and returns the most recent date for the ProjectCode@row. It sounds like what you want it to do is to now detect if the current row has the Update date…in other words is the row the most recent update. I think you can accomplish this with an if statement around your current formula. Something like:

=IF(MAX(COLLECT([Update Date]:[Update Date], ProjectCode:ProjectCode, ProjectCode@row )) = [Update Date]@row, 1, 0)

Hope this helps!

AdamSYNH

@mela0645

Based on the information provided, add a symbol column with the formula below. If there is potential for there to be some overlap in separate rows with updates provided for the same project on the same date, this would highlight all of them for a manual date column. If you are using the modified date column type, the time of the update would mean that you only get the very latest change in the row, which would be the checkbox update in your calculated column.

=IF([Update Date]@row = MAX(COLLECT([Update Date]:[Update Date], ProjectCode:ProjectCode, ProjectCode@row )),1,0)

All comments

Paul Newcome

You would use an IF statement.

=IF(MAX(COLLECT(………..)) = [Update Date]@row, 1)

Adam Murphy

=IF(MAX(COLLECT([Update Date]:[Update Date], ProjectCode:ProjectCode, ProjectCode@row )) = [Update Date]@row, 1, 0)

Hope this helps!

AdamSYNH

@mela0645

=IF([Update Date]@row = MAX(COLLECT([Update Date]:[Update Date], ProjectCode:ProjectCode, ProjectCode@row )),1,0)

mela0645

Thanks for the quick response! I had been staring at this for too long. Your responses worked perfectly!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.