Index and collect most recent text and where not blank based on Max Modified date?

I'm using the following formula to collect the most recent comment from children cells. We are having an issue when a user adds a comment and then deletes it and/or types a new comment within the same cell within the same minute. When this happens, the parent retains the blank cell "comment" or doesn't recognize the new comment. How can I solve this issue?

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(Modified@row)), CHILDREN(Modified@row), 0))

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You could use a JOIN/COLLECT instead.

=JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> "", CHILDREN(Modified@row), @cell = MAX(CHILDREN(Modified@row))), CHAR(10))

ErinTyler

Hi @Paul Newcome. I'm not sure that accomplishes the index ignoring the cells with no status update data (deleted) and choosing the next to most recent status update comment that has data... I think the main issue is that the Modified column does not timestamp milliseconds and when 2 or 3 updates are made within the same minute. Should I use a custom timestamp field formula to collect data/time with milliseconds? Does such a formula/functionality exist?

Thank you!!

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.