Help with Max/Collect Function

Katy Brown

Hello!

For my project, I have a database of task cards. Information for these task cards (such as Date updated, status, labor hours per skill set, etc) is input into a separate sheet, which is collected and fed into my main database.

Currently, I have a helper column that says "Is this the most recent row updated for this specific task card?" based on the date entered.

The problem is, when the task card is completed it gets touched by two different skill sets: Skill 1 and Skill 2, lets say - so both are completed as of the same date, and both are picked as 'Most Recent'. This causes problems because when the information is exported to my database, the column shows the information twice in that cell - which gets messy!

Currently, to get around this problem, I'm asking our team to just... leave one of them blank, but this isn't a pretty solution and is just begging for a mistake to get made down the road.

I was hoping some of the clever minds on this forum might have a suggestion.

Below is the formula I'm using for the checkbox.

=IF(MAX(COLLECT(Date:Date, [Control #]:[Control #], [Control #]@row)) = Date@row, 1, 0)

(I did try using a "Auto-Numbers" Modified By column, but the dates ended up being exactly the same anyway, and it caused an even bigger mess.

Another attempt had me tasking our team with checking 'Most Recent' manually, but I wasn't sure how to tell any other instances of that Task Card to UNCHECK 'Most Recent' automatically.)

Paul Newcome

We may be able to make the Modified (date) type column work. What exactly do you mean by it created an even bigger mess?

Katy Brown

https://community.smartsheet.com/discussion/comment/295754#Comment_295754

Certain columns in my database auto-update because of an Index/Match and every time that auto-update occurs (when a user clicks into the smartsheet, and it loads), the Modified by column would update at the exact same time stamp in every single cell in that column. So unfortunately, this option won't work for this particular problem.

Paul Newcome

Will Skill 1 always get completed before Skill 2?

Will they always be in chronological order so that if Rows 1 and 2 have the same date then row 2 will always be the most recent?

Katy Brown

https://community.smartsheet.com/discussion/comment/295812#Comment_295812

Not always. In example, Skill 1 would be ... lets say Avionics, and Skill 2 would be Inspection. Maybe 90% of the time it would be 1 then 2, but sometimes Inspection might hit it at the beginning AND end of the task, or sometimes it doesn't need inspection at all.

So I can't say that Inspection is always present, or always the last skill used, so I can't set it to only import Inspection rows' information.

Paul Newcome

Ok. My suggestion would be 4 helper columns, a helper sheet, and a copy row automation.

Insert your Modified (date) column.

Insert a text/number column called "DateTimeStamp" with the following column formula:

=VALUE(YEAR(Modified@row) + "" + IF(MONTH(Modified@row) < 10, "0") + MONTH(Modified@row) + "" + IF(DAY(Modified@row) < 10, "0") + DAY(Modified@row) + "" + IF(FIND("P", Modified@row) = 0, IF(VALUE(MID(Modified@row, 10, FIND(":", Modified@row) - 10)) < 10, "0" + MID(Modified@row, 10, FIND(":", Modified@row) - 10), IF(MID(Modified@row, 10, FIND(":", Modified@row) - 10) = "12", "00")), IF(MID(Modified@row, 10, FIND(":", Modified@row) - 10) <> 12, VALUE(MID(Modified@row, 10, FIND(":", Modified@row) - 10)) + 12, 12)) + "" + MID(Modified@row, FIND(":", Modified@row) + 1, 2))

Now that we have our Modified date/time stamp converted into a 12 digit number, we can set up a copy row automation to copy the row to the "Copy Sheet" when the Date gets filled in. When the Copy Row automation is triggered, it grabs static data, so the 12 digit number in the Copy Sheet will not change.

Next we insert an Auto-Number column with no special formatting that I will call "Auto" and a text/number column called "Row" with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Finally we use this in the checkbox column:

=IF(INDEX({Copy Sheet DateTimeStamp}, MATCH(Row@row, {Copy Sheet Row}, 0)) = MAX(COLLECT({Copy Sheet DateTimeStamp}, {Copy Sheet Control #}, @cell = [Control #]@row)), 1)

Katy Brown

https://community.smartsheet.com/discussion/comment/295841#Comment_295841

I tried this out, and i'm not sure if I missed a column but it resulted in "INVALID OPERATION"

Beyond that, I'm not sure if converting to a time stamp would work because while testing it the lines were often updated in the same minute, so even then they ended up with the same value and both checked anyway, even without being updated.

Also, it sort of caused a feedback loop - the Index/Match keeps calling back to the 2nd sheet, which keeps updating the modified date, which keeps updating the Date Time Stamp, which keeps adding new rows to the new test document, and so on.

I am going to close the question for now, as I'm going to test a few things but for now we are going to just make sure to only enter the date on one line, to avoid that issue. Thank you for all your input!