Large Sheet Notifications

ktcran

We have a handful of very intricate, detailed, and logic-filled intake forms that are tracked in 3 separate master sheets separated by the type of intake. Each sheet is over 150 columns wide.

We need to send a copy of the entire submission to the person who submitted it, along with several others selected in contact fields on the submission itself. It would be awesome if blank fields would not show (I know this has been a largely requested development)

Unfortunately, due to the unique nature of each request, narrowing down the fields is difficult and even if we could pair down a little, it seems Smartsheet cannot send more than I'd say 15 fields in a readable format unless we send the automated notification as an Update Request. See image below sent as an Alert Notification. But then we run into the problem of having a ton of pending update requests that don't actually require action 90% of the time.

I can't imagine what this must look like on mobile either, which is how our field team would be viewing the email notifications.

There has to be a better way!!!

James Keuning

I use a huge formula to do this. In fact, the formula is so huge I have to break the formula into three columns and then merge the three columns. And I only have less than 60 fields. I will try to describe this and I hope I get it all.

The formula looks like:

=IF(ISBLANK([Software Name]@row), "", CHAR(10) + CHAR(10) + "Software Name: " + [Software Name]@row) + IF(ISBLANK(Requestor@row), "", CHAR(10) + CHAR(10) + "Requestor: " + Requestor@row) + IF(ISBLANK([Executive Sponsor]@row), "", CHAR(10) + CHAR(10) + "Executive Sponsor: " + [Executive Sponsor]@row) + IF(ISBLANK([High Level Scope]@row), "", CHAR(10) + CHAR(10) + "High Level Scope: " + [High Level Scope]@row) + IF(ISBLANK([Describe Software or Solution]@row), "", CHAR(10) + CHAR(10) + "Describe Software or Solution: " + [Describe Software or Solution]@row) + IF(ISBLANK([What is the use case?]@row), "", CHAR(10) + CHAR(10) + "What is the use case?: "…

and it goes on like that.

Now, who wants to create that formula? So I created a "wizard" to create the formulas. You paste in your list of fields, and for each field, you can specify the "label" that you want to appear in the formula result. For the most part, your field names will be the label, but that's not always the case.

The wizard looks like this:

There are two formulas in Column5:

The first line in the "block" needs to open with an equals sign, so it is:

=IF(ISBLANK([Actual Field Name]@row), "", ("=IF(ISBLANK([" + [Actual Field Name]@row + "]@row)" + "," + """ + """ + "," + "CHAR(10)" + "+CHAR(10)+" + """ + [Label that you want to appear in the text]@row + ": " + """ + " + [" + [Actual Field Name]@row + "]@row" + ")"))

The other fields in the block do not need the equals sign so they get this formula:

=IF(ISBLANK([Actual Field Name]@row), "", ("+IF(ISBLANK([" + [Actual Field Name]@row + "]@row)" + "," + """ + """ + "," + "CHAR(10)" + "+CHAR(10)+" + """ + [Label that you want to appear in the text]@row + ": " + """ + " + [" + [Actual Field Name]@row + "]@row" + ")"))

Because this might not be clear, let me zoom way out. The way this works is you copy the whole block (Field1 block, Field2 block, etc) and paste it into the field. It opens and closes with parentheses, so it will parse as expected. (Also, the way /I get my list of fields in a huge sheet is I export to Excel and then copy paste the first row in Excel to switch rows to columns, then paste the list of fields into Smartsheet)

So my sheet has these three fields (Field1, Field2, and Field3), and then the Email Body field is just:

=[Email1]@row + [Email2]@row + [Email3]@row

And I use the Email Body field as the body in the notification.

I have only test this on less than 60 fields, so I do not know what will happen when you use it for hundreds of fields. You might need to create multiple Email fields, but in that case you can put each of them in the body.

____________________________________________________________________________________________________________________

Here is an example on a smaller scale:

My sheet:

My wizard (keep in mind that you are seeing the formula RESULTs here:

Those fields have a formula in them. eg:

When I copy the block, it creates this formula for me:

=IF(ISBLANK([This]@row),"",CHAR(10)+CHAR(10)+"This: " + [This]@row)

+IF(ISBLANK([That]@row),"",CHAR(10)+CHAR(10)+"That: " + [That]@row)

+IF(ISBLANK([Another thing]@row),"",CHAR(10)+CHAR(10)+"Another thing: " + [Another thing]@row)

And I paste that into my Combo field and then my sheet becomes: (Notice how it skips the blank "That" column in line 2, which is the goal of OP's question)

So the body of the email for line 1 is:

This: 1

That: 2

Another thing: 3

and line 2:

This: Hello

Another thing: Bye