Bridge Add columns based on values within multi-picklist
Overview: We are managing the schedules of providers / resources in Smartsheet where each provider may be licensed in a variety of different states (Alabama, Alaska, Arizona, etc.).
These providers / resources currently enter their shift availability into a column with their name within a Centralized Availability Tracker. Each row represents a day / shift slot.
Datamesh then maps the provider's availability from the Centralized Availability Tracker to each state's schedule, so our coordinator can determine which shifts to assign each provider.
As our company grows and extends to more states and adds more providers to existing states, I'm looking to scale this approach in a way where we can leverage Bridge to dynamically add these provider columns to each existing state schedule, eliminating these tedious steps below.
Workflow: Our workflow is as follows whenever we onboard a new state and / or new providers:
- Submit intake via intake form. Control Center creates state-specific schedule
- Manually add new (checkbox) column for each new provider with their name as the title to the Centralized Availability Tracker
- Manually add new (checkbox) column for each new provider with their name as the title to each state schedule in which they're licensed.
- Manually create provider Smartsheet report (data source = Centralized Availability Tracker) where provider enters their availability. I'm assuming it's not possible to automatically create new reports via Bridge, but if it is possible I'd be excited to hear a solution.
Potential Solution: My thought is to leverage a simple sheet where we have the provider enter their name and all the states in which they're licensed in a 2nd column. Updating the "Assigned State Schedule(s)" value would trigger Bridge to Add Column to each state schedule based on the provider's name where it doesn't exist.
Roadblock: The problem I'm running into is I can't figure out how to parse the multi-select values from the array, in order to match against state abbreviation (AK, AL, AZ, etc.). When attempting to use the Find Text function, Bridge is unable to complete and stays in the hourglass status. Would appreciate any guidance on how to execute this.
Best Answer
-
I've thought of a way to bypass this error, and allow you to have one Parent workflow with all the Child Workflow Modules below it.
In each of your Child Workflows, add the "Get Sheet" and "Array Management: Extract Field from Array" modules to get all the Column Names in the sheet before looping through to add the new column if there's a multi-select match.
This way you can add in a CONTAINS Junction to see if the array of column names in this sheet contains the name of the new column already:
If there's a column already in the sheet with that person's name, the child workflow will have a successful result (result of nothing) which will prevent it from ending in an error.
I've added in two fail states just so they appear in my run log and I can keep track of how the workflow is going through each module.
Here are the pieces broken down:
Get Sheet
Copy the Data Reference to the Columns array.
Array Management: Extract Field from Array
Uses the Column Array reference to grab the title key.
Get the data reference to this new array of ONLY the column titles.
CONTAINS Junction
Look through the new Column Array as the Values to search, and use the runtime.entities.Name that was pulled from your Parent workflow to check if that name exists as a Content in the Array (or whatever you're using as the new column name).
The SUCCESS state (the column name already exists in the array) is just a plain state with a title for my run log. This will cause the workflow to end in SUCCESS but to take no action.
The FAIL state (no column exists) is the MATCH junction that we already had set up, where if there is no column and if the value (e.g. "AZ") exists in the multi-select array, it adds the column. Otherwise, even if the column does not exist, if there is no matching multi-select value it does nothing.
Let me know if this makes sense and will work for you.
Cheers,
Genevieve
Answers
-
What I would do in this instance is have one Parent workflow that grabs the row data and parses out the multi-select cell with "Split Text". Then I would create a Child Workflow that uses the Array created from the Parent workflow to loop through the individual values and create the column if the correct value was found.
I've added a "Split Text" module to separate out the values found in the Run Log from the multi-select cell (from your Get Row module). The delimiter is a comma.
Then you can grab the Data Reference from this Array to loop through Child Workflows as the "Number of Runs".
You may want to add the Provider Name that was retrieved in the Get Row module as a "Child entity values" to push through to the Child workflows (so you can use it as the Column Name!)
Then in the Child Workflow for a specific value, you can use a Match Conditional Junction to match the runtime data to the value you're looking for, since the runtime data will only loop through one of the arrays at a time.
Just an idea! 🙂 Let me know if this will work for you.
Cheers,
Genevieve
-
@Genevieve P. - this is really creative and got me 95% the way there.
Scenario 1: When there is 1 state schedule being added I don't experience any issues.
Scenario 2: When attempting to update (existing row) a Provider with another state allocation, I get the below error.
Scenario 3: When attempting to add (new row) a Provider with multiple states listed I get the below error. The first child workflow gets processed successfully, every subsequent child workflow fails.
This will be a common scenario, hoping you can help me find the correct solution so that all subsequent child workflows run successfully.
Error: "Child workflow execution failed: INVALID_DATA failed to execute extension module: Column titles must be unique"
-
I'll keep testing with this!
What do you have for the Column Name field in your Child Workflows going to the separate sheets? Can I also confirm that you have different sheets associated with each of your child workflows?
Getting this error message for the Sheet that already had their data (e.g. the NY sheet and child workflow) would be expected, as you want the workflow to stop instead of adding a duplicate column. However I may have completely blanked about the fact that this error would pause the workflow for the other values instead of skip over it!
-
Hey @Genevieve P. -
I confirmed when stacking the child workflows in a single parent stops the workflow if there's an issue (e.g. existing column with the same name), preventing subsequent children to run.
Yes, different destination sheets are associated with each child workflow.
What ended up working is by having a separate parent / child workflow for each state schedule. Your original idea was preferred though because it reduces the amount of build and maintenance.
-
I've thought of a way to bypass this error, and allow you to have one Parent workflow with all the Child Workflow Modules below it.
In each of your Child Workflows, add the "Get Sheet" and "Array Management: Extract Field from Array" modules to get all the Column Names in the sheet before looping through to add the new column if there's a multi-select match.
This way you can add in a CONTAINS Junction to see if the array of column names in this sheet contains the name of the new column already:
If there's a column already in the sheet with that person's name, the child workflow will have a successful result (result of nothing) which will prevent it from ending in an error.
I've added in two fail states just so they appear in my run log and I can keep track of how the workflow is going through each module.
Here are the pieces broken down:
Get Sheet
Copy the Data Reference to the Columns array.
Array Management: Extract Field from Array
Uses the Column Array reference to grab the title key.
Get the data reference to this new array of ONLY the column titles.
CONTAINS Junction
Look through the new Column Array as the Values to search, and use the runtime.entities.Name that was pulled from your Parent workflow to check if that name exists as a Content in the Array (or whatever you're using as the new column name).
The SUCCESS state (the column name already exists in the array) is just a plain state with a title for my run log. This will cause the workflow to end in SUCCESS but to take no action.
The FAIL state (no column exists) is the MATCH junction that we already had set up, where if there is no column and if the value (e.g. "AZ") exists in the multi-select array, it adds the column. Otherwise, even if the column does not exist, if there is no matching multi-select value it does nothing.
Let me know if this makes sense and will work for you.
Cheers,
Genevieve
-
@Genevieve P. - this suggestion fixed the child workflow stacking issue and addresses my solution need perfectly. Thank you!
Zeb
-
Awesome, I'm glad to hear it helped! 🙂
