Best Of

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!

Here is a way to create reminders for Proof reviewers. This solution will:

• Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request.
• Send a reminder 5 days after the last reminder, if no one has responded to a Proof review.
• Reset when a new Proof version is created.

Some useful modifications include:

• Reminding any one who has not responded to a Proof review request.
• Changing the reminder frequency for urgent Proofs or conditions.

To create this solution, follow these steps:

1. Create the Proof Info columns. These will reveal information such as who has responded, whose response is pending, and status of the proof.

1. Create these columns

• Invite sent date.
  • Date column.
    • Will be filled with automation when invites are sent.
• Workdays since invite sent.
  • Formula
  • =IF([Invite sent date]@row = "", "", NETWORKDAY([Invite sent date]@row, TODAY()) - 1)
• Send proof review reminder.
  • Trigger to send reminder notification. In this case the reminded is sent after 5 days if no one has responded.
  • Formula
  • =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")
• Last reminder sent.
  • Date column.
  • Will be filled with automation when a reminder is sent.
• Workdays since reminder sent.
  • Formula
  • =IF([Last reminder sent]@row = "", "", NETWORKDAY([Last reminder sent]@row, TODAY()) - 1)

1. Create two automations

I hope this helps.

Neil Egsgard

Business Solutions Architect

Southern Alberta Institute of Technology

This little formula is useful for a range of solutions, like working out if a date is in daylight savings time or not. The examples below reference a date field named [Date].

The first part of the formula finds the date of the seventh day of the month. This is done so that the number of days from Sunday can be subtracted:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 )

The second part of the formula finds the number of days to subtract from that date:

- ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )

This part of the formula gets the date of the seventh day of the month again, and works out what day of the week it is. We subtract 1 from the result because the WEEKDAY() function returns values starting at Sunday = 1. So if the seventh day of the week just happens to be Sunday we don't want to subtract 1, we need to subtract 0 instead.

So the entire formula to find the date of the first Sunday of a month is:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )

This can be tweaked a little for applications like checking if a date is/was in daylight savings time. This example expects daylight savings time to start on the first Sunday in October and end on the first Sunday in April of the next year:

=IF( AND( [Date]@row >= DATE( YEAR( [Date]@row ), 10, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), 10, 7 ) ) - 1 ), [Date]@row < DATE( YEAR( [Date]@row ) + 1, 4, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row) + 1, 4, 7) ) - 1) ), 1, 0)

It checks to see if [Date] is greater than or equal to the first Sunday in October (month 10) in the same year as [Date] AND is less than the fist Sunday of April (month 4) in the following year. If that is true then it returns 1 (daylight savings), otherwise 0 (not daylight savings).

To find the date of the first day of the month for some other weekday a slight tweak is required on the second part of the formula:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - MOD( ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 ) + [Weekday], 7)

where [Weekday] contains:

• 0 for Sunday
• 1 for Monday
• 2 for Tuesday
• 3 for Wednesday
• 4 for Thursday
• 5 for Friday
• 6 for Saturday