Resubmitting or editing a form?
Hi,
We have a Out of Office smartsheet that allows users to submit a form which creates a new row, and from there, additional users are pinged to approve or decline submissions. These results are shown in calendar view and we'd now like to be able to offer revisions of past form submissions. What would be the best way for users to either view just their submissions, or to re-submit a new form that would only affected existing row data (aka a previous out of office request)?
-Vlad Weinstein
Answers
-
The following is a solution using a report's current user filter and a form's Use of a URL query string to prefill default values on a form technique.
If you can access the Dynamic View App, the app will provide a more straightforward solution, though.
https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
Please check the demo dashboard in the following Link to examine each solution component.
Original Request Form
Considering the premise that 'Out of Office' requests can be viewed mutually among colleagues in the same office:
In this case, you'll input via a form, and it's okay to grant view access to the sheet so the reports can be seen. Since it's view access, there's no risk of someone modifying another person's request.
Out of Office Sheet (View Only)
When input from the 'Original Request Form', the values are recorded in the 'Out of Office Sheet.'
This content is displayed on the dashboard as published in "View Only." (Out of Office Sheet (View Only))
It's displayed in a calendar format on the dashboard. You can see other people's leave statuses. Colors are applied with Conditional Formatting when "Approve" is checked.
My Out of Office Requests: Current User Filter
This report is set up to show only the data of the current user based on the "Out of Office Sheet."
There are form URLs to revise the Start and End later.
Sheet with a form to make original request
The Out of Office Sheet has the following columns:
Date inputs from the form go into Original Start and Original End.
The calendar displays the Start and End dates. These dates are determined by a formula that checks if Revised Start and Revised End exist, and if they do, those values are used. Otherwise, Original Start and Original End are used.
Sheet with a form to make original request (Revise Request Sheet)
To revise an existing request, use a different sheet (Revise Request Sheet) form with the 'Use a URL query string to prefill default values on a form' to prefill the values of the rows of existing requests from the Out of Office Sheet. This means that the form of the update sheet displays data from the original sheet and can be filled with the formURL column using a function.
Although not displayed in this form, it contains the original request row number and the auto-numbered Row ID. Using this, the Revised Start and Revised End values of the existing request in the 'Out of Office Sheet' will be updated by the following formulas:
=IFERROR(INDEX({Start}, MATCH([Row ID]@row, {Row ID}, 0)), "")
=IFERROR(INDEX({End}, MATCH([Row ID]@row, {Row ID}, 0)), "")
The above formula {Start},{End} are the range of columns Start and End from the Revise Request Sheet.
Summary of mechanism for users to view only their own requests and update existing row data in form format
- To view only your requests, use the Current User filter in the report.
- To update existing row data in a form, use the technique 'Use a URL query string to prefill default values on a form.' Display a form prefilled with existing row data on an existing sheet and retrieve the data entered in that form from another sheet using the Row ID as a key.
