Using forms to update a sheet

MLKG

Using a form to populate a sheet that is keeping track of new orders, step 1, as well as in process orders, steps 2,3 and 4. for previous orders that are not finished. If last minute orders for the day comes in, is there a way to update the sheet using the form or does this have to be manually or can the form be entered without creating a new row of information that does contain the new orders but also all the same older information. Thank you for any help in explaining best method to populate this sheet with daily updates.

Kerry St. Thomas

A form submission creates a new row, so you'll need a different process to update an existing row. Depending on your data/permissions needs and your plan, you may be able to send an update request to those who are submitting additional information, use premium app Dynamic View to allow people to have the ability to update only specific fields, share the sheet, or create a report for updating (including some locked columns that don't require updating if that's the case) and embed in a WorkApp for people to use for updating (without sharing the underlying sheet). There are other possibilities I'm sure, but these are the ones I can think of.

Good luck!

Kerry St. Thomas

A form submission creates a new row, so you'll need a different process to update an existing row. Depending on your data/permissions needs and your plan, you may be able to send an update request to those who are submitting additional information, use premium app Dynamic View to allow people to have the ability to update only specific fields, share the sheet, or create a report for updating (including some locked columns that don't require updating if that's the case) and embed in a WorkApp for people to use for updating (without sharing the underlying sheet). There are other possibilities I'm sure, but these are the ones I can think of.

Good luck!

MLKG

Thank you so much for your response. You have saved me a lot of time and also pointed me in different direction than I would have known about. Really appreciate this, this community makes the functionalities of Smartsheets more approachable.

Isis Taylor

@Kerry St. Thomas

Maybe you can help me with my issue. I have a source sheet, from there I need to have the activity owner answer a series of questions with some logic included. I was going to use an update request automation, but I don't think I can build the response logic there. I want to use a form to collect the additional information needed.

Is it possible to trigger a form to go out from another sheet, based on some trigger or value from the first sheet?

Kerry St. Thomas

As I mentioned earlier, a FORM creates a new row. If you have some way of identifying those people who would need that supplementary form in place (for example, only people who say they are in Japan or whatever), you could conceivably trigger an automation such as a Notification - you'd of course need to collect their email address, but then you could send a message that essentially says "Your responses require additional information - please click this link to complete the supplementary request" and include the link to your second form.

BUT… this will sever the link between "Part 1" and "Part 2" data. You could conceivably collect the email address twice, but that's subject to inconsistent data entry to cross-reference. If you do use an update request automation, you can keep everything on the same row - so the same trigger could just fire off that automation instead. Whatever works for your business case of course!

Isis Taylor

Thanks for the feedback.

I like the idea of a notification that goes out with the form link, but I also see what you mean about severing the link between the data on the two sheets.

mjones56181

This solution only works if your Transaction flow has incremental steps.

If you have some way to trigger an update request using a workflow, like a stage in the transaction, or when the previous columns are populated, then you create a workflow that can send a request tailored to only allow specific fields, to be updated/populated.

Per some of the previous comments, the ideal way would be Dynamic view

Toufong Vang

Is it possible to trigger a form to go out from another sheet, based on some trigger or value from the first sheet?

Hi, @Isis Taylor , yes, you can send the form from the other sheet to the activity owner and have the updates saved as static values in the source sheet. Here's how to do it:

Build the logic you need within your source sheet. From that logic, construct a form URL query string for the sheet into which the activity owner will submit supplemental data. Configure an automation in your source sheet to send the URL to the activity owner.

When the supplemental information is received, an automation moves the row from the source sheet into a 3rd sheet where the row is updated with details from the supplemental sheet, and then it is immediately moved back. The row in the source sheet now contains the updates from the supplemental sheet.

You'll need a helper column in the source sheet to determine whether or not supplemental data has been submitted, and another to record that the row has been updated (so as to avoid an infinite loop between Source sheet and Update sheet).

I deploy this setup where QA/QC needs to be performed on updates.

Hope this helped!

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

Isis Taylor

@Toufong Vang Thanks for that suggested solution. I may try that moving forward if I receive a similar request.

What I ended up doing in this case was send out a notification message including a link to a form on another sheet as suggested above. Then, using a unique identifier from the form collection sheet, I set up a Data Mesh configuration to immediately copy the responses collected on that sheet over to the corresponding item on the original sheet. In this case, the unique identifier was the contract number.