Formula to Exclude Blank Fields from a Workflow Alert Message?

Options

I have an automated workflow setup to alert someone when a new row is added and specific criteria is met, however, I want to customize the alert message to only include specific fields if they aren't blank. Is there a way I can setup the workflow criteria with a formula so that that the message being sent will exclude blank fields and only the fields in the row that's aren't blank will be listed in the message?

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You would need to piece together the message in a separate cell and then use a {{Placeholder}} in the automation to pull in this message.

    ="Hello " + [ASsigned To]@row + "," + CHAR(10) + CHAR(10) + "You have been assigned the following:" + CHAR(10) + IF([Column A]@row <> "", [Column A]@row + CHAR(10), "") + IF([Column B]@row <> "", [Column B]@row + CHAR(10), "") + IF([Column C]@row <> "", [Column C]@row + CHAR(10), "") + CHAR(10) + CHAR(10) + "Thank you," + CHAR(10) + "Bridget"


    The above would output something along the lines of (column data would only be present if not blank)


    "Hello Paul,


    You have been assigned the following:

    Column A Data

    Column B Data

    Column C Data


    Thank you,

    Bridget"


    So in the above, if Column B was blank, it would read more like:


    "Hello Paul,


    You have been assigned the following:

    Column A Data

    Column C Data


    Thank you,

    Bridget"

  • Bridget Dempsey
    Answer ✓
    Options

    This will work and is such a creative idea! Thanks for your help, Paul!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You would need to piece together the message in a separate cell and then use a {{Placeholder}} in the automation to pull in this message.

    ="Hello " + [ASsigned To]@row + "," + CHAR(10) + CHAR(10) + "You have been assigned the following:" + CHAR(10) + IF([Column A]@row <> "", [Column A]@row + CHAR(10), "") + IF([Column B]@row <> "", [Column B]@row + CHAR(10), "") + IF([Column C]@row <> "", [Column C]@row + CHAR(10), "") + CHAR(10) + CHAR(10) + "Thank you," + CHAR(10) + "Bridget"


    The above would output something along the lines of (column data would only be present if not blank)


    "Hello Paul,


    You have been assigned the following:

    Column A Data

    Column B Data

    Column C Data


    Thank you,

    Bridget"


    So in the above, if Column B was blank, it would read more like:


    "Hello Paul,


    You have been assigned the following:

    Column A Data

    Column C Data


    Thank you,

    Bridget"

  • Bridget Dempsey
    Answer ✓
    Options

    This will work and is such a creative idea! Thanks for your help, Paul!

  • Naina Dave
    Naina Dave ✭✭✭✭
    Options

    I would like to do the same - have the automated email sent after excluding blank fields. However, the Form that I would like to be sent automatically has about 25 default fields, but when using logic based responses on some of those fields, the Form expands to more than 100+ fields. Although this solution is excellent, it's lot of work to add all those 100+ fields with correct formula. Is Smartsheet planning to add the option to the Automated Workflow itself, to make it easier? Add one more radio button at the bottom under Message Includes: section?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Naina Dave It would be nice if something were built in to give us the option to "Exclude Blank Fields" in automations. Feel free to browse the Product Ideas topic (link a the top of the page). If someone else has already submitted it then you can vote on it. If no one has submitted it yet then you can submit it yourself to let Smartsheet know that you are interested in the feature and to allow others the chance to vote on it as well.


    Regardless of whether you create it yourself or vote on someone else's feel free to post a link to the submissions here so that others looking for the same thing can easily add their vote.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!