Trouble with understanding Workflow

marihorton

I work for a company that runs clinical trials; I want to create a way to send out vendor performance surveys to Clinical Trial Managers (CTMs) who work with the vendors on different studies. Each CTM is responsible for one or more studies, and some vendors are repeated in different studies. The CTM needs to take a survey for a vendor’s performance in one specific study, and they need to do that for all of the vendors in their study, or studies if they run multiple studies. So, there may be instances where the CTM takes two surveys, both for the same vendor, but for two different studies, in addition to other surveys for the other vendors in their studies. I want these surveys to send out quarterly and I also want to be able to track whether a CTM has completed all of the surveys they need. This is what I have so far:

CTM-Vendor Relationships Sheet which has a list of all studies, all vendors working on those studies, and the CTM responsible for overseeing all the vendors for each study.

Survey Responses sheet which contains all the forms (surveys) for each study/vendor combo. Each individual survey will be sent out to the corresponding CTM listed in the CTM-Vendor Relationships sheet. The Survey Responses sheet will also house all the responses from the surveys that are taken

Survey Status sheet will track the status of the surveys that are sent out each quarter. Hopefully I can get it to check if the survey has been sent, submitted, and if not, send a reminder a week after it was sent.

My issue is that I have no idea where to start for the workflow for this. My guess is that I need to go to the CTM-Vendor relationships sheet, start a workflow, and link that to the survey responses sheet so that the correct form (survey titled with study and vendor) is sent to the CTM associated, and then also make sure it is sent quarterly. I likely also need to link the survey responses sheet to the survey status sheet to be able to check the status. I am new to Smartsheet and would love any suggestions, steps, or advice on how to automate this!!

Michelle Choate 2

I do not think you need the survey status sheet. I think you can do it all in the "CTM-Vendor Relationships Sheet."

You might consider making the Study the Parent row, with the CTM in that row, and then a row for each vendor underneath. I would also add a Form Link column, a "Date Last Survey Sent" column for the parent row, and "Date Last Survey Completed" column. The parent row will only contain the Study Name. the CTM Contact, and the Form Link.

I would call the primary column Info or something similar, then have the next column be Vendor, then CTM, then Form Link, then Date Survey Last Sent, then Date Last Survey Completed. Then I would add a Study Name Column, that would be a formula looking at the primary row and be =PARENT(Info@row).

Then I would create a column formula that looks at the Survey Sheet with and INDEX/COLLECT that looks at the Survey Name, and the Vendor Name, with a MAX formula thrown in to get the latest survey date.

Then I would create an automation that sends out the survey to the CTM on a cycle (Send it with Some Columns (You only need to send the Study Name, and the Form Link). Then also create a Record a Date automation that records a date in the Date Survey Last Sent column, if CTM is not Blank (Since you will only be filling this info in on parent rows). Set the automations to run the same date.

ChristianFinke

Hey @marihorton

Here’s how I’d approach it:

Start with Your CTM-Vendor Relationships Sheet:

Set up an automation here to trigger the survey. Use the "Send an Alert" automation to email the form link to the right CTM based on the study/vendor combo. You can schedule it to run quarterly.
Link to Your Survey Responses Sheet:

On the Survey Responses sheet, use an INDEX/COLLECT formula to pull in the latest response for each vendor/study combo. That way, you’ll know if the survey’s been submitted.
Track Status on the Survey Status Sheet:

If you want to keep it separate, your Survey Status sheet can use formulas like COUNTIFS or INDEX/MATCH to check which surveys are still missing. You can also set reminders to go out if surveys aren’t completed after a week.
It sounds like a lot, but once you get it set up, Smartsheet automations will do most of the heavy lifting.

If you need help fine-tuning anything or have questions along the way, just see my signature!