Workflows and Conditions

Cristina Ayala

Hello,

I have a data collection sheet that stores a variety of sites data. I will do my best to explain the setup, as well as desired outcome.

This sheet contains two forms, (1) a data collection form used for new sites [requires 3 Point of Contact (POC) emails, site name, and various other fields that will not be referenced] and a (2) data collection request form used for sites who wish to have their original entry resent to themselves [Requires email, site name, and will automatically use a checkbox] . In order to make this process automated, I was trying to create a work flow that references site and emails.

The Data Collection Request sheet automatically uses a checkbox for a column (Requested). It also required the requestor input their email (Requestor Email). When creating the workflow, I want the Requestor Email field to reference at least 1 (at most 3) other columns that are submitted with the original form (IT POC, GM POC, and/or Leadership POC). If the email address submitted in the Data Collection Request form matches any of the emails in the other fields AND the site name, I would like to have this trigger that row to be sent in an email to the Requestor.

Is this possible? Any guidance is appreciated! Thank you!

**Edited**

Genevieve P.

Hi @Cristina Ayala

Are all of these details within the same row? If so, we could add a helper column to your sheet that uses a formula to check if the Requestor Email matches any other cells in its row, and checks the Site Number (I'm not sure what you wanted to compare this against).

Then you can use this helper column as a Condition Block in a workflow to filter if the notification is sent or not (perhaps in a Time Based workflow, such as once a week?)

If you wanted to send multiple rows to the Requestor if they have submitted multiple requests, then you could use a trigger for when Rows are Added or Changed, but then schedule it to send Daily to the Contact in the cell. This will group together all relevant rows in one email at the end of a day as a re-cap. See: Trigger frequent, change-based automations in batches

If I've misunderstood what you're looking to do, it would be helpful to see screen captures, but please block out sensitive data.

Cheers,

Genevieve

Cristina Ayala

Hello @Genevieve P.,

I appreciate your assistance! I did try to create a 'helper' column which was created as a Checkbox type (Gray circle). It is hidden on the form and set to Checked, so once a form is submitted I could use that field as a trigger in a workflow. However, I was unable to find a workflow that would allow me to reference a specific field and/or how to set the workflow outcome to email the most recent submission.

That being said, to ensure we are on the same page, here is another explanation of what I am looking for.

This sheet contains two forms. An initial submission form (highlighted in purple), and a confirmation receipt form (highlighted in orange). I am trying to find a way to automate users requesting access to previously submitted data without sharing all the data to all user, or making manual work. I would like the confirmation receipt form to trigger a workflow that will compare both the Site Name, and Email to previously submitted data. If the Site Name matches AND the email matches at least one of the emails in two separate fields (noted by the email names Confirmation-Email@sent-here.com), then the email would be sent to the email used in the form.

Genevieve, if the suggestions provided before align with this request, I will continue to look into this solution and how to implement it and appreciate any further guidance you have.

I appreciate your time, thanks again.

Cristina Ayala

Hello,

I am unsure if this request is something that can be done within Smartsheet. If anyone could follow up to verify this, that would be greatly appreciated.

Cristina

Genevieve P.

Hi @Cristina Ayala

My apologies for missing your follow-up above!

In looking at your example, the tricky thing here is that your Confirmation Receipt form is on a new row, not associated with the previous email addresses from the purple row. This means we can't use one row to trigger another row's data being sent out.

However, you could use a COUNTIFS formula in your PURPLE row to Check a Box if the COUNT of rows in this sheet that have the same Site Name and have the Primary email or the IT Email in the Confirmation Submission From email column.

To do so, we'd create 2 COUNTIFS statements (one checking one email column, the other checking the second email column) and add these COUNTIFS together. If the total Count is >0, this means that an orange Submission has come in that matches data in your Purple row.

=IF(COUNTIFS() + COUNTIFS() > 0, 1, 0)

=IF((COUNTIFS([Site Name]:[Site Name], [Site Name]@row, [Confirmation Submission From:]:[Confirmation Submission From:], [Primary Leadership Email]@row) + COUNTIFS([Site Name]:[Site Name], [Site Name]@row, [Confirmation Submission From:]:[Confirmation Submission From:], [IT POC Email]@row)) > 0, 1, 0)

You could then use this trigger on the purple row to send out to both emails (the one in the Primary Leadership Email column and the one in the IT POC Email column), since the request came from one of those emails.

If you didn't want one person to be CC'd, you would want 2 checkbox columns, one for each COUNTIFS:

=IF(COUNTIFS([Site Name]:[Site Name], [Site Name]@row, [Confirmation Submission From:]:[Confirmation Submission From:], [Primary Leadership Email]@row) > 0, 1, 0)

=IF(COUNTIFS([Site Name]:[Site Name], [Site Name]@row, [Confirmation Submission From:]:[Confirmation Submission From:], [IT POC Email]@row) > 0, 1, 0)

Then you would use one checkbox column to automate an email to the Leadership Email column, and the other checkbox as the trigger for the IT email column. Does that make sense?

Let me know if I'm still misunderstanding the end goal!

Cheers,

Genevieve