Update multiple sheet column dropdowns from one source sheet using Bridge
We have a master project list that we reference to update project select dropdowns on 50+ sheets in our solution.
We'd like to use Bridge to update all of those dropdowns, but we haven't found a way to update multiple dropdowns with one workflow. The workflow works fine for updating one sheet (the circled portion below), but we keep encountering errors when we try to update another sheet dropdown. If we can't figure out another way, we'll have to create separate workflows for all 50+ sheets and include the Get Sheet and Array functions for every one of them.
Here's what we've tried:
- We added another Update Column module for the next sheet after the circled portion below.
- Result: The workflow ran through the circled portion with no issues, but acted as if the second Update Column module didn't exist. There were no errors; it just stopped after running through the circled portion.
- Then we tried a child workflow as shown below.
- Result: The child workflow ran, but when I tried to reference the array values, I received an error (see screenshot below). Will child workflows not identify references from parent workflows?
Child workflow error message:
Any insight is greatly appreciated!!
Tagging some Bridge friends 🙂
@Samuel Mueller, @Brian_Richardson, @MCorbin, @Genevieve P., @Kelly Moore
Answers
-
The excitement I get from being tagged in a Bridge post... 🤩
Hey @Julie Fortney!
Before I do a deep-dive on your second scenario, I believe we can fix this right from the first.
States keep Bridge workflows organized. If you have one State at the top and then add duplicate modules below (the exact same module with the same name... aka "Smartsheet: Update Column"), the workflow will only recognize one of those modules.
Go back to your first workflow and try adding a new State above your second Update Column. You can name it anything (e.g. "Second Update"). This should allow the workflow to see it as a new, unique action!
Let me know if that works for you or if you'd like to see screen capture examples.
Cheers,
Genevieve
-
Julie,
In my testing with Bridge, here is how I updated multiple sheet columns.
Trigger
State: SourceSheet -> Get Sheet -> Array management: Extract Field From Array
State: Destination Sheet -> Get Sheet -> Update Column
Create a new state for each sheet that the column dropdown needs to be updated.
I currently use this method to update several project tracking sheets without issues.
Let me know if you need additional information or explanation.
Frank Smith, PMP
Assistant Director | IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
-
Aha, I needed another State! I'm still not quite there yet, though:
The workflow is now willing to run my List Columns module for Sheet 2, but the second Update Column module still errors. Here's a screenshot of the workflow and error, followed by a screenshot of the module settings. Any ideas what might be going wrong?
Thank you!
-
Julie,
You are close.
Instead of List Columns in your state, you must Get Sheet for the sheet you want to update first. Then, you will update the column.
The null error is because you are not referring to a specific sheet.
I hope this helps.
Frank Smith, PMP
Assistant Director | IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
-
Like @Genevieve P. , I love being tagged for Bridge!
@Julie Fortney you are not going to want to setup 50 states and steps! You can use a child workflow. You'll need to get a list of sheet IDs... you can do that with a workspace or folder list, or have a report with all the sheets on it and scrape the report for those Ids, etc. I have to jump on a call but more to come when I get a chance.
-
So real quick, I'd set it up like this:
1) Get the list of sheet IDs. Maybe the easiest is to setup a report that pulls in all the sheets and has the column that you want to update. If you can get it to 1 row per sheet, that would be really useful. Then do Get Report to pull the report data into Bridge.
2) Call a Child Workflow and set the Number of Runs to be the rows object from your Get Report. That will spawn the child workflow once for each row of your report = each sheet and will hand the row object to the child workflow with all the data from that row. The row data from a report includes the original column id from the source sheet and also includes the sheet id of the source sheet for that row. Both of which you need.
3) Also in the Child Workflow call, put a Child Entity Value to capture the current list that you want to set the column to have as dropdown options. You should have gotten that already from earlier steps to extract the array of options. When you put a Child Entity Value in the Run Child Workflow step, it carries through to the child.
4) In the child workflow itself, you can then read the sheet ID and column ID from the {{runtime.data}} object, and the array of options from the {{runtime.entities}} object. Then do an Update Column to update the column on the sheet.
Each time you run this, it will pull your report that shows all 50 or however many sheets and update each one of those sheets with your new options!
If that's too high level let me know and I can take some screenshots or get more specific on the references.
-
@Brian_Richardson This is an intriguing idea! I'm going to try working through this setup and see how it goes.
Thanks - being new to Bridge is a bit painful, but I have a feeling these challenges will unlock a lot of new ideas, all thanks to my Community friends. 😀
-
Julie you're welcome to setup time too to walk through it together online. Ping me if you want to do that. My email is on my profile.
-
This is fantastic and a much cleaner set-up! Thanks for sharing, @Brian_Richardson
-
This is why I love the Smartsheet Community - question answered before I even notice that I've been tagged :-)
-
@Brian_Richardson Thank you, Brian! I think I'm very close to getting this. I sent you a request to connect on LinkedIn to discuss further when your schedule allows.
-
Nice solution! I hadn't considered a report. I was thinking about using a Get workspace for sheet Ids (I had made an assumption the sheets might all be in the same workspace) - but this probably would have required nested child flows to grab both the sheet IDs then the column ids/rows. Your solution is perfect.
-
Absolutely love seeing the bridge Q & As 🤩
