How to get the latest update of primary column using Modified Date/time

Avinash DN

I have a form which will be filled by store managers, The unique (primary key) is the Store No and few columns after that. 1 store will submit multiple forms in a week. I need only the latest entry of each store (Distinct Store No) of all the stores.

In my example below, I need only rows 2 and 3 to be shown. row 1 is duplicate of row 3 with old / earlier modified date.

Appreciate your help in advance

Paul Newcome

@Avinash DN I wanted to confirm because (depending on the workflow) there could have been different outcomes based on which one you used. Using the Created Date column would look something like this:

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Store No]:[Store No], @cell = [Store No]@row)), 1)

Itai

Hey @Avinash DN ,

What would you like to do with the rows that you dont want to show? move them to an archive, Delete them?

Is the only difference in row 3 that the Status is checked?

Avinash DN

Hey @Itai

Thanks for the quick reply.

I can delete the rows or move the latest update into a new sheet as summary (But this needs to happen as and when a new update on that particular store number is entered).

Yes in the example the only difference in row 3 is the status checked (Do note that this is an example I made up, as I cant share the actual data) But you get the point. There may be date change or Tech name change as well.

Avinash DN

Paul Newcome

If they are submitting a form which creates a new row for each update, wouldn't you want to pull based on the Created Date instead of Modified? Or is it possible that Row 3 could later be modified and then you would want to pull that instead of row 1?

Avinash DN

Hey @Paul Newcome

You are right, I can pull data for created as well. As this sheet will not be accessed by the form users, both created and modified dates are same.

The problem statement is still the same. Need to consolidate the latest date and time (Created/Modified as both will be the same) for all unique store numbers.

Appreciate you looking into this

Avinash DN

Paul Newcome

@Avinash DN I wanted to confirm because (depending on the workflow) there could have been different outcomes based on which one you used. Using the Created Date column would look something like this:

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Store No]:[Store No], @cell = [Store No]@row)), 1)

Avinash DN

@Paul Newcome, Thanks for solution. Now im able to flag the latest for each store.

Avinash DN

Paul Newcome

Happy to help. 👍️

Kam114

I'm trying to set up something similar for latest updates for us as the manually entered dates we are using are confusing the sheet and only pulling the earliest status update when we have multiplate updates in the same day.

We did not have a 'created date' column (the autopopulated one smartsheet already has), so when i added that column in so that we could modify our formula, the created date that showed up for every single row was the date the SHEET was created. Any way to get the created date to show when each ROW was created?

for reference, we are trying to using this formula =IF(Summary@row, 0, IF([Created]@row = MAX([Created]:[Created]), 1))

however, this gives us a check in each row that was created before this moment in our 'latest update' column.

Paul Newcome

@Kam114 Exactly how was the sheet created? If the sheet was created and then all of the rows entered at the same time (no saves in between any rows), they will all have the same date/time stamp. Additionally, if the sheet was created as a "save as new" and the rows were already in the original sheet, all of the rows will have been created at the same date/time as the sheet.

Kam114

@Paul Newcome, the sheet is provisioned through a blueprint automation. this specific sheet was provisioned through our blueprint on 3/28/24 at 4:58pm with several templated rows from the blueprint. However, there have been several manual rows created since 3/28/24 until now, and those rows also show a 'created' date of 3/28/24. For example, the last two rows shown here had data entered into them for the first time yesterday (what i would consider, 'created',), yet their 'created' date here still shows the date the sheet was created. Any help is greatly appreciated, as our reports are showing incorrect data for 'latest updates' when we have multiple updates in the same day when we use a manual status update date column for our formula

which is currently =IF(Summary@row, 0, IF([Status Date]@row = MAX([Status Date]:[Status Date]), 1))

Paul Newcome

@Kam114 What you considered "created" vs what the system considers "created" could potentially be two different things.

If the rows in the template previously had data in them and the data was deleted from the cells, those rows still exist. They just don't have any data present, but you can still check cell history and whatnot. The only way to truly get rid of rows so that they have a new created date/time stamp is to delete the actual row itself.

Kam114

@Paul Newcome , understood. It does however appear that the 'created date' doesn't track newly 'created rows' until the 'created date' column is inserted in the first place. After having enabled the 'created' column (seen on the most left). As you can see the new row that we created on 5/23 (per the modified date), with status date 5/24, still shows a created date of 3/28/24, which is when our workspace was provisioned through the blueprint. I am wondering if the fact that the column formulas (which are part of the blueprint) are confusing the system's created date for those rows we had newly entered.

Paul Newcome

@Kam114 I use this all the time with no issue to include inserting it after the fact and getting the correct created date. Modified is completely separate from Created and can't be used as an indicator. It looks like those rows were already used in the sheet at the time of provisioning (in the template) which is why they are all showing as the same created date/time stamp. That final row being created today is in fact a new row that was not already in the template.

If you apply the Created date column to the template, is there a date/time stamp in some of those rows you are questioning?

Kam114

@Paul Newcome , thanks for all your help, Paul. I noticed a rogue entry with an existant modified date in this specific's sheet's template down about 130 rows that may be the issue. i will provision a few new sheets with the corrected template and see if that fixes the issue.