Showing individual form submissions "visually"

James Fusco · 03/19/21

Hi everyone! I created a contract intake form in SmartSheet that allows for people to request contracts with my department. When the form is submitted, the data from the form goes into the Sheet as a new row.

However, for the Contract Specialists, seeing an entry of a row with 30 columns in a sea of other rows isn't a great way to see the details of the request.

So, basically I want to recreate the look of the original form, except it shows the questions and answers that were submitted for one request. It's a visual way of seeing the request. It would kind of mirror the email notification that's sent out. I'm just wondering how that's accomplished and if I'd have to create it myself or if that functionality is built-in. Any direction would be appreciated!

~Jim

Paul Newcome · 03/22/21

Ah. Ok. Here is my suggestion then...

Set up a second sheet ("Info Request Sheet). On this sheet we need an email address column (contact type), a ticket number column (text number), and a flag column (flag symbol).

Create a form that captures the requestor's email address and the ticket number they are requesting info on. The form should populate on the TOP ROW (important for simplicity later on).

In the flag column:

=IF(ISERROR(MATCH([Ticket Number]@row, {Main Sheet Ticket Number Column}, 0)), 1)

This will flag the row if the ticket number requested is not in the Main Sheet. Set up an alert to automatically go to the requestor when this column is flagged so that they know they entered an invalid ticket number. I suggest having them fill out another entry as opposed to an update request.

Now we go back to the main sheet. Insert a counter column (text/number) and an email address column (contact type).

In the counter column:

=COUNTIFS({Info Request Sheet Ticket Number Column}, [Ticket Number]@row)

In the email address column:

=INDEX({Info Request Sheet Email Address Column}, MATCH([Ticket Number]@row, {Info Request Sheet Ticket Number Column}, 0))

Now we set up an automation that will send the row to the email address listed whenever the number in the counter column changes to any value.

.

Here's the flow of it all...

I submit a form to request info for ticket number 12345. The form populates a new row at the top of the Request Sheet. The number in the Counter Column on the main sheet increases which triggers the automation to send to the email address that was pulled via the INDEX/MATCH (which would be mine).

You can select the columns that get sent to exclude the helper columns and it should replicate your screenshot above as an email, or you could go with the placeholders or even a combination of both such as a placeholder in the Subject line to say something along the lines of

"Requested information for Ticket Number {{Ticket Number Column}}"

and then leave the body alone so that it sends the actual columns as in your screenshot.

James Fusco · 03/19/21

I guess it would ideally look kind of like this. You could see each request visually like this instead of as a row on a spreadsheet.

Paul Newcome · 03/22/21

Have you looked into using placeholders in the message body of the notification? It would look something like this...

Manufacturer: {{Manufacturer}}

Model Number: {{Model Number}}

Quantity To Sell: {{Quantity To Sell}}

Basically you use a double set of curly brackets around a column name, and it will automatically pull the data from that column from whichever row triggered the automation.

James Fusco · 03/22/21

Hi Paul- thank you for the reply. What I'm trying to do is recreate that notification, but in SmarSheet, not over email.

It's so hard to describe. But, what I'm looking for is basically a blank report with a search box or dropdown menu. From there, you would choose the Ticket number (the auto-generated number from the Sheet) and then, upon choosing it, it would display all the information about that "row" in the sheet, but in a visual format that mirrored the style of the screenshot I sent above.

This would allow a person who received a ticket to see just that one individual submission in a nice one-pager type of format rather than as a line in a spreadsheet.

Does that describe what I'm looking for a bit better?