Automated workflow with subscribers

Netty99

I've got a form that is completed when a staff member has available time to help. I have a list of approximately 20 names that want to know when new entries are created. I created a workflow to trigger when a new line is created, but I can't link to that other sheet which has the "subscriber" list of email addresses. There's got to be a way to make this work without having to add the emails separately in the workflow. Help!? Thanks in advance.

brianschmidt

I'm not a Smartsheet staff member, but I can do my best to address this. A bit of a lengthy response below, but the bottom line is that it appears that there's no way yet to achieve what I believe you're striving to accomplish.

This is definitely a tricky one. There's a whole discussion (https://community.smartsheet.com/discussion/82311/open-discussion-formula-to-populate-multiple-contacts-in-a-single-cell) where a glitch was found to join multiple contacts in a single cell that you could pull from for your automated workflow. However, it's not really a reliable solution that I would trust to flow up into your notification workflow. The way I understand this, the best solution would be to have a helper column with a column formula that automatically generates all the desired contacts on each line as it's added (described further down in relation to using summary data fields). Then, you can set up your workflow to notify contacts in that cell on each row that's added. However, it all comes down to how the information can be mapped between contact columns (especially related to limitations with multi-contact columns). Here's a good breakdown of how contacts can be transferred between columns taken from that aforementioned discussion:

In addition to this, sometimes summary fields are used as a workaround. In that instance, you could have a hidden column in which one single-contact cell houses a contact. Then, in your summary data, you add a contact field which pulls that contact. Then, a separate helper column can be added with a column formula that equals your summary data (i.e. =Contact#). This ensures that contact appears in that helper column on every added row and can be used for notification workflows. However, summary fields only work for translating single contacts (multi-contact data will simply appear as a text string).

A lengthy and somewhat convoluted response, but I hope it helps clarify some for you. This is something that would make a great suggestion for Smartsheet's dev team. You can do so by upvoting comments like this and/or submitting a product improvement form: https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?Origin=help&_gl=1*11ejvl9*_ga*MTkzNTU1OTA3LjE2Nzc1OTU0MjQ.*_ga_ZYH7XNXMZK*MTcwNjIxODQ1My4yNDguMS4xNzA2MjIyMzU2LjU5LjAuMA..&_ga=2.6100659.1454101603.1706114943-193555907.1677595424

brianschmidt

I take it back...I think I found a way to accomplish this using an INDEX(MATCH)) formula. Here's the only catch: you would need all the contact in your subscriber list in a single cell. So, as you add subscribers to your contact list, even if you add them as separate lines, you'd also need to make sure you manually add them to that single cell. I still don't think there's a way to join separate contact lines without returning a text string.

In any case, in whatever sheet you have the single cell with all the contacts, you'll want an additional helper text column. In it, you'll put a number (I put "1" in my test). Then, in whatever sheet you are sending the notifications from as rows are added, you will want a multi-contact column as well as it's own separate number helper column. In the number helper column, I put in a column formula of "=1" (whatever your corresponding number or symbol was in the other number column. Then, in your multi-contact field, you will add a column index match formula:

If you subscriber contact cell is in the same sheet, your column formula will look something like this:

=INDEX([Contact List]:[Contact List], MATCH([Number Helper 2]@row, [Number Helper]:[Number Helper], 0))

For a cross-sheet reference (where your master subscriber contact cell is in another sheet), it will look something like this:

=INDEX({Contact List - Email/Contact}, MATCH([Number Helper 2]@row, {Contact List - Number Helper}, 0))

This should pull all the contacts from your master subscriber cell automatically into each row, allowing you to use that contact column for automated notices. Here's what it looks like on the test sheet I created. The column on the far right was just to test the cross-sheet functionality. Otherwise, the sheet simulates what it would look like if your contact cell was in the same sheet as your sheet sending notifications.