"Edit" Row Via A Form, Functional Workaround

Paul H

I used a simple visitor sign in/out kiosk for the example, it details a way for view only users to pick the row they want to "Edit" and do it via a form. Looking for feedback or additional ides before I build this into a larger solution.

Submission sheet: Populated by a web form, Auto Numbered, Row ID =auto@row

Create an Automated work flow to copy all new the rows to the visitor log sheet.

Visitor Log Sheet: Hide the "Auto" Row its no longer needed, Row ID is now the Unique identifier.

Create a web form on this sheet and get its URL, Make the Row ID hidden on the form so it cant be changed. Next build a custom URL using string queries to autofill the form with existing info.

Clicking the Sign-out form hyperlink gives you this.

Submitting the Form creates a duplicate line with the same ID

Use two check boxes using max collect formulas to determine what is the newest entry and which one is old based on the created date

Finally using a Move row automation trigger by the "Old" Checkbox to move the row to a "Trash Can" Sheet

End result an "Edited" but secretly new row with the auto generated Row ID still unique

Paul Newcome

Did you have a question about something above, or did you mean for this to be a discussion instead?

Paul H

@Paul Newcome

No specific questions, just to share a workaround with the community and for feedback and to see if I am doing anything overly complex or unnecessary.

Paul Newcome

Ah. Ok. Is there a specific reason for not using Update Requests?

Paul H

@Paul Newcome Not practical to have visitors enter an email address and receive an update request on another device. Need to have them sign out at the terminal right as they leave the facility so we know whos on site in case of evacuation. Trying to do it with reports restricted to read only on a published dashboard

Paul Newcome

Understood. It seems to be pretty well thought out.

I personally would have gone a slightly different approach.

For sign-in I would have a form where they simply enter their name. I would use a created date system generated column to capture the date/time for that one.

For sign-out I would have them check a box (as opposed to clicking a link and filling out a second form). From there you can set up a copy row automation to lock in the static date/time stamp from the modified column (trigger when the box is checked), and use an INDEX/MATCH on the Auto column to pull that back into the main sheet as the sign-out date time.

Are you able to expand on the Row ID portion of your solution though? I may be missing something, but it seems to me that it is just replicating the Auto column. What is the reason behind that as opposed to just referencing the Auto column directly?

Paul H

@Paul Newcome Thanks for the input

I will change to just a name sign it, see if I can work you other ideas in.

The Auto column copied over as an system column using copy row, so as I add rows using the sign out form it goes out of sync.

Paul Newcome

Ah. Understood. So the Row ID basically gets captured as static data during the first Copy Row automation. Got it.

By the way... You may want to have a conversation with that Paul character about checking in more than once per visit... Hahaha

JoshBauer

I'm trying to find a way to have users select from a dropdown list to populate the form with previously entered data. The dropdown list should be populated with values from a specific column such as workorder numbers. All data can be contained in the same grid

JoshBauer

Paul, when I try to create the hyperlink following the format you provided I get a #UNPARSEABLE message in that cell. Any suggestions?

JoshBauer

Scratch that. I corrected some formatting but now when I click the hyperlink, it takes me to a page that reads:

JoshBauer

What does the actual URL look like in the browser?

Paul H

This is one of the links from the example I posted, bold is the standard link to the blank form, everything after that is what was added via the formula.

app.smartsheet.com/b/form/f0b29738d77a48d794ad56c5cfffd779?Row%20ID=V-00011&Name=Paul&Date=04/12/22&Time%20In=9:00%20AM&Time%20Out=3:00%20PM

Start simple add one element at a time and test it, I would have started with this.

app.smartsheet.com/b/form/f0b29738d77a48d794ad56c5cfffd779?Row%20ID=V-00011

JoshBauer

Figured it out.

(I'm impatient)

JoshBauer

Paul H.

Please post your formula for Max(Collect

I'm still suffering with this part

Paul H

Newest

=IF([Row Created]@row = MAX(COLLECT([Row Created]:[Row Created], [Row ID]:[Row ID], [Row ID]@row)), 1)

Old

=IFERROR(IF([Row Created]@row = MAX(COLLECT([Row Created]:[Row Created], [Row ID]:[Row ID], [Row ID]@row, Newest:Newest, 0)), 1), 0)