Create dynamic custom schedules based on selections in intake

Brian_Richardson

Hi all, I recently developed a cool solution to an issue that we had... how to build a custom schedule based on selections in an intake form. This solution leverages Bridge, but you could do the same thing using a third party integration tool.

THE GOAL

The process goal for this solution is:

1. Collect project requests using an intake form
2. Allow the requestor to specify what types of work need to be done (ie content creation, forum hosting, website uploads). This was for Marketing, but could be used for any type of work.
3. Produce a custom task schedule, based on the type of work selected. For example, if the requestor selected "Brand - Brand review" the custom task schedule would have tasks related to Brand review only.
4. Set the start date of the tasks to the start date from the request

THE SOLUTION SUMMARY

When someone submits a form and selects certain components for their task sheet, Bridge triggers. Bridge then copies a template sheet which contains all possible components and tasks. Then Bridge deletes all the task sections that don't match the components selected on the form.

The reason I copy a sheet and then delete sections, rather than copying over only selected rows, is because of Predecessors. When doing a row copy, the Predecessors are not copied over. However if doing a sheet copy, the Predecessors are kept intact.

THE SOLUTION DETAILS

I created an Intake sheet with the following columns:

• Project Name
• Requested Start Date
• Task Sheet Link
• Components: this column is a multiple-selection dropdown with the name of each type of work

I created a Task Template sheet with the following setup:

• A row at the top with the name "Requested Start Date" in the Primary column
• A column called "Row Type" with the column formula =IF(COUNT(ANCESTORS([Task Name]@row))=0,"Section Header","Task")
• A column called "Section" with the column formula =IF([Row Type]@row="Section Header",[Task Name]@row, INDEX(ANCESTORS([Task Name]@row),1))
• A set of tasks, grouped under parents, with each parent matching the name of a Component in the Intake Sheet. For example, one section of tasks was called "Brand-Brand review" to match to the component option, with subtasks indented underneath that outlined the steps and tasks needed to complete Forum setup. Note you could have multiple levels of hierarchy, the important thing is that the top parent names match the Component field options in the Intake form.
• I put the Predecessor for the first task of each section to be 1. That linked the first task in each section to the top row where I have the requested Start Date.

I created a Bridge Smartsheet Integration trigger to kick off a workflow when a row was added to the Intake Sheet.

I created a Bridge workflow to be the "child" workflow to do the deletions of each section of tasks from the copied template that doesn't match the selected components in intake. You need to create this "child" first, then the parent, because the parent calls the child in the last step. Note that this workflow won't do anything until you run the parent by triggering a new entry in your intake sheet.

1. Conditional Junction: Contains. This checks to see if the first section of copied tasks matches a component selected in the intake sheet. Name = Component Selected. Value = {{runtime.entities.components}}. Contents = {{runtime.data.cells.Task Name.value}}
2. true side of junction: State Name = Selected. Nothing else after this step on the true side.
3. false side of junction: State Name = Not Selected. Remaining steps fall under the false side of the junction
4. Search Sheet: this finds all row IDs for tasks in the section. Sheet = {{runtime.entities.sheedId}}. Row Filter 1 = Section Is Equal To {{runtime.data.cells.Task name.value}}
5. Array Mgmt: Extract Field From Array: this collects the row IDs into a single array. Array = {{states.Not Selected.smartsheet.search_sheet.sheet.rows}}. Key = id
6. Delete Row: The last step, this removes the entire section that was not selected in the Intake Sheet request. Sheet = {{runtime.entities.sheetID}}. Row ID={{states.Not Selected.arraymgmt.extract_from_array.results}}

Then finally I created the parent Bridge workflow with these steps:

1. Smartsheet: Get Row: returns the row that was sent by the trigger (the newly added row) from the Intake Sheet. This is to get all the detailed data from the row. Sheet = {{runtime.sheetID}}. Row ID={{runtime.event.id}}. Advanced Options = Include Row Permanent Link
2. State: Copy Template
3. HTTP Call: copies the template to a folder and renames the copy to match the requested project name. URL = https://api.smartsheet.com/2.0/sheets/your template sheet id/copy?include=attachments,cellLinks,data,discussions,filters,forms,ruleRecipients,rules. Method=POST. Headers= Authorization (your Bearer token) and Content-Type (application/json). Body = {"destinationType":"folder","destinationId":your desired destination folder id,"newName":"{{states.startstate.smartsheet.get_row.row.cells.Project Name.value}}"}
4. State: Update Copy with Start Date
5. Search Sheet: gets the row ID of the first row. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row Filters = Section Is Equal To "Requested Start Date". Advanced Options = Include Sheet Data
6. Update Row: sets the start date based on the request. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row ID = {{states.Update Copy with Start Date.smartsheet.search_sheet.sheet.rows.0.id}}. Cells Key 1 = Start. Cells Value 1 = {{states.startstate.smartsheet.get_row.row.cells.Requested Start Date.value||2023-01-01T08:00:00}}. (I used 1/1/23 as a default start date if no start date was given in the request)
7. State: Update Sheet Link
8. Update Row: posts a link to the newly created sheet, back into the Intake sheet for reference and easy navigation. Sheet = {{runtime.sheetID}}. Row ID = {{runtime.event.id}}. Advanced Cells 1 Column = "Task Sheet Link". Input Type = Hyperlink. Hyperlink Display Text = {{states.startstate.smartsheet.get_row.row.cells.Campaign Name Trimmed.value}} Task Sheet. Hyperlink Sheet ID = {{states.Copy Template.utilities.httpcall.result.id}}
9. State: Get Section Row IDs from Copy
10. Search Sheet: gets a list of all the section top header names. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row Filter 1 = Row Type Is Equal To Section Header. Advanced Options = Include Sheet Data
11. Child Workflow: Remove Sections. This step calls a child workflow which will parse each section header name and compare it to the list of selected components in the intake sheet request. If there's a match, the section of tasks is kept in place. If not matching, the section is deleted. Child Workflow Name = the name of the child workflow you created above. Number of Runs = {{states.Get Section Row IDs from Copy.smartsheet.search_sheet.sheet.rows}}. Child Entity Key 1 = components. Child Entity Value 1 = {{states.startstate.smartsheet.get_row.row.cells.Components.displayValue}}. Child Entity Key 2 = sheetID. Child Entity Value 2 = {{states.Copy Template.utilities.httpcall.result.id}}

Whew!!! I know it's a lot, but the end result here is that you'll get a sheet created for each request, with the task sections that were requested. Predecessors will be intact, the start date will be set, a link will be posted back to the request row, and now your team can move forward with doing the work!

Danielle Wilson

That's awesome @Brian_Richardson! Thank you for sharing!