Smartsheet Form - Latest entry is the only "actively" reported

Simon Bamford · 10/17/22

Hi,

I have a form for people to fill in that reports how ready they are for security. As this is evolving they can be expected to fill this in from time to time to measure how ready they are. So I only want the latest line from the form to be reported on.

My system in place for this at the moment is to have a "Constant" column that registers which row to report on.

As shown above the column with a C in it will be the one that gets reported on. The one's with an N will not. All my cross sheet formulas depend on the C being present to read the right row.

I think this is all the correct way to do this however the process to report the correct row is manual and is left up to others rather than me to do.

Can anyone think of a way to make this process automatic. So that when a new row is added from the form it gets the C and the old rows get an N.

Thanks for taking the time to read this. Any help or suggestions would be really appreciated.

Christian G. · 10/17/22

Will there be only one row per name with "C" and all the other with N ?

I'm thinking of using the RowId and RanqEQ on a Collect by Name.

Then a checkbox is checked only if rank is 1.

So, Row ID is a number from 0 to anything.

checkbox column with the formula:

=iferror(if(Rankeq(RowId@row,COLLECT(RowId:RowId,Name:Name,[Name]@row),0)=1,1,0),"")

This, will only have a checked box if it is the last entry for that name.

If you want to keep "N" and "C" here is the edited formula:

=iferror(if(Rankeq(RowId@row,COLLECT(RowId:RowId,Name:Name,[Name]@row),0)=1,"C","N"),"")

I hope it help

Paul H · 10/17/22

Add a system "created date" column and a checkbox column. Use a Max(Collect formula to only check the latest entry

Here is a similar example

Simon Bamford · 10/20/22

https://community.smartsheet.com/discussion/comment/347465#Comment_347465

Hi Christian,

This is great. i have been able to make it work. The name could change so I created a column that just displays a D so it can constantly read that as the names change then you can get multiple C's.

Thank you for the help. This automates the whole process now.

Simon Bamford · 10/20/22

https://community.smartsheet.com/discussion/comment/347474#Comment_347474

Hi,

Thank you for taking the time to do this for me. This looks like a good solution that I will try out and see if I can get it to work on mine.

Smartsheet Form - Latest entry is the only "actively" reported

Answers

Categories