Using forms to update a record

Greg Fair

I've perused the Smartsheet community posts for the answer to this question and get varying results.

Objective: Use a Smartsheet Form to update a row (a record) of data.

Background: We are currently using an Excel file with a VBScript form to update records of data. The VBScript form allows us to select the record to update, reads the row, presents selected data to the user and allows for data manipulation by the user. Each record has a unique identifier (number). The process works just fine - however, looking to elevate the workflow to a centralized platform versus managing at the end-user machine.

There have been all sorts of workflows presented to the Smartsheet community - however, I couldn't find this most basic request.

Note: The original record of data would be imported and the form would allow for data manipulation, i.e., drop-down selection boxes.

Thanks in advance!

Greg

Heather Duff

@Greg Fair

I thought the answer was going to be no, but EUREKA! I figured it out.

If you're setting an automation for an update request, you can edit the email that will be sent out. On that edit screen, you can include {{curly bracketed column names}} to insert fields for that row. For example, I can put "Please update enrollment numbers for {{course code}}" or "{{Course code}} enrollments update" in the subject or body of my email, and it will insert the course code. Then I will make sure NOT to include the Course Code column in the fields that are sent for update.

It looks like this in the email:

And the form:

I tried sending it manually, but using the {{column name}} didn't seem to work in the update request that way. However, if Case # is your primary column, then it will automatically show up in the header of the email and the header of the form, as my Course Code (primary field) showed up in my first set of screenshots. Then you'd just want to be sure, when selecting which fields are sent for update, that you uncheck the Case # column so that it is not sent for update.

Hope it works for you!

Best,

Heather

Heather Duff

Hi @Greg Fair ,

I haven't found a way to do this via a form. However, if it's something that happens either on a date-based timeframe or on an as-needed basis, here are the 2 techniques I use:

1 - Manually send an update request by right clicking on the row and sending an update request to that person.

2 - For date-based updates (let's say, it needs to be updated annually, or 6 months after it was last edited, etc.) - I have set up workflows to email the assigned contact an update request 6 months after the most recent update.

In both of these options, you can select which cells request an update, so it can be as short or as long of an update as needed.

Hope this helps!

Best,

Heather

Greg Fair

Hello Heather,

Thanks for your swift response and feedback.

Seem silly that this most basic of workflows isn't available with ease. Modification of a record seems to be a fairly common practice for many workstreams. Being able to provide a front-end GUI to view the records for modifications seems to be fundamentally a must.

We do assign records of data to be updated by an engineer. Historically they have just navigated the rows selecting the ones assigned to them making the modifications and saving the records as they go. I can look into the manual update request - however, would that require the end-user to modify the record directly instead of using a form? Sorry for my lack of knowledge here, we have been investigating solutions and Smartsheets came up as a potential tool/workflow.

Regards,

Greg

Heather Duff

No worries, @Greg Fair ! Here's what an update request process I use regularly looks like:

When I send the update request (by right clicking on the row, then clicking Send Update Request), I select only the fields I want to be sent to the person. It's not necessary to select the fields - it defaults to include all columns for that row.

An email comes to the person that looks like this:

When they click Open Update Form, it opens in a browser and looks like this:

So it essentially turns it into a form. They can then edit any of the fields included in the update. (In this instance, I update the enrollment numbers for upcoming courses.

I currently have an automation set to send me an update request every Monday/Wednesday/Friday morning to ensure the dashboards we use have up-to-date enrollment numbers on them. It will also send update requests for more than one row in one request, so it will ask them to update one record, then click the "next" button to move to the next record, and so on and so forth until they have updated all necessary records.

If you automate it, you can select the specified fields you want to be in the form, and only those fields will be available for updating. I haven't found a way to make any of the fields read-only, however.

You could set the automated update request to send the update to someone in the contact column, rather than one specific person. That way only the rows assigned to them are sent to them. Filters would allow you to filter to specific date ranges, statuses, etc.

Hope this helps. Let me know if you have any other questions!

Best,

Heather

Greg Fair

Thanks Heather! That is definitely a workflow possibility. Appreciate you sharing your insights/experience :)

Heather Duff

Happy to help! If you hit any snags if/when you decide to set something up, feel free to reach out!

Greg Fair

Hi again Heather,

I tested the functionality, pretty slick. That said, would like to have some reference data included (case #) but don't want it to be modified, (just used as a reference). I noticed that you have included "course code" in your example above; I suspect this is static and you don't want this modified either? Familiar with how to accomplish this?

Thanks again,

Greg

Heather Duff

@Greg Fair

I thought the answer was going to be no, but EUREKA! I figured it out.

If you're setting an automation for an update request, you can edit the email that will be sent out. On that edit screen, you can include {{curly bracketed column names}} to insert fields for that row. For example, I can put "Please update enrollment numbers for {{course code}}" or "{{Course code}} enrollments update" in the subject or body of my email, and it will insert the course code. Then I will make sure NOT to include the Course Code column in the fields that are sent for update.

It looks like this in the email:

And the form:

I tried sending it manually, but using the {{column name}} didn't seem to work in the update request that way. However, if Case # is your primary column, then it will automatically show up in the header of the email and the header of the form, as my Course Code (primary field) showed up in my first set of screenshots. Then you'd just want to be sure, when selecting which fields are sent for update, that you uncheck the Case # column so that it is not sent for update.

Hope it works for you!

Best,

Heather