Formula help - set values for current user and date when check box checked

I have a checklist I am building for a department where they will be primarily mobile users so I would like to reduce the thumb typing as much as possible.

I see where I can use test the value of a checkbox and make it do things.. but what I want to do is get the current date and current user. So that when someone clicks the "done" checkbox, it populates the "completed on" and "completed by" columns. I dont see how to reference those values.

Help?

Find more posts tagged with

Formulas

Real Estate

Comments

Brett Evans

In the column properties set a column to modified by andon e column to modified on.

These will reflect the last person to modify a row, not necessarily who was the one who checked a box.

Thanks Brett.. but I dont want the last update of the row (can be a mess if you are using webforms) .. but rather I want the check box to set the value of the column of the current user and date.. and ONLY the check box.

Bonus points would be to clear the values if unchecked (but I think I know how to do that based on some of the formula examples)

Question is still out there, how do I use a formula to set a column to the current user and another to the current date.

Brett Hill

i required a similar function, but i only wanted to view this information when required, and not report on it. If you only want to view it, in my case to stop the "i didnt tick that box" comment, you can right click on the cell and choose 'view histroy'. This does not seem to work through a web form as it doesnt need a user login to enter data.

Thanks Brett. And good point on the web form being more "anonymous".

I will post if I find an answer on this. Hopefully one of the smartsheet formula gurus will come to the rescue here.

J. Craig Williams

Currently, the System columns for Modified (Date) and Modified By are for changes to the row.

The only way I have found to "lock in" a name or date is if that is the last change expected/allowed to a row.

This works best if the users are updating data from a Report - then once the check box is checked, it doesn't show up in the Report anymore. That does not prevent them from opening the sheet directly, but people are remarkably lazy sometimes and when designing a system I try to minimize the reasons they need to look at the sheet.

But if done means done, easy to do.

For clearing, if there is a differnce between "it was never checked" and "it was checked, but now it isn't anymore" it gets more complicated.

If you are doing things through mobile devices, then something like AppSheet works better (in my opinion) than the Smartsheet App - I use Android and that is behind the functionality of the iOS and way behind the desktop version. But AppSheet is pretty cool, but has a steep learning curve. Minutes to make your first App, longer to get all the tweaks done. But still pretty slick and improving weekly.

If you are doing things via a WebForm, I'm migrating away from Smartsheet's WebForm except for pure new data entry and going to embedded forms in WordPress using various plug-ins. That also gives me the luxury of a few lines of code to access the API.

Craig