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!!