Update multiple column dropdowns on one sheet, from one source sheet using Bridge

Hello! Bridge newbie here.

I have successfully set up a Bridge to update the column dropdown for one column from one source sheet. I'd like to build off this bridge to update multiple columns dropdowns on the same sheet, using the one source sheet. Is this possible?

Here is what we have set up so far.

@Genevieve P. @Brian_Richardson Tagging some popular Bridge experts :)

Find more posts tagged with

Bridge

Accepted answers

All comments

jessica.smith

Hi @KatieMiller

If you add a new State, you can add another Update Column action underneath.

There are a few ways you can improve this Bridge flow, but would be difficult to get into the details via the Community chat. Happy to connect offline if you want any additional assistance here.

Also, since you have access to Bridge, its likely you also have access to DataShuttle. DataShuttle is a great way to update Column Dropdown options and might be a better approach here.

Brian_Richardson

Simplest to do is probably add another state and repeat the steps in that state, updating the other column. Repeat as needed. You don’t need to repeat the Get Sheet step as long as it includes all the various updates that you want to make.

NeilKY

@jessica.smith @Genevieve P. @Brian_Richardson

I have the exact same question above BUT, what if I wanted to update ALL the other columns on the same sheet? What changes would you need to make? If updating all isn't an option, any column with a hyphen "-" in its name would work too.

—

(I'm basically just trying to update 250+ columns in a single sheet with the below options. I do NOT want to do it manually :) )

Values: Satisfactory, Unsatisfactory, N/A

Restrict to allowed values only

My flow currently looks identical to this tutorial: https://help.smartsheet.com/articles/2482712-update-dropdown-columns

jessica.smith

@NeilKY

Hi Neil,

I'd probably try to set up a parent/child workflow relationship.

The first bridge flow (the parent) would be a "Get Sheet" function that you would use to get an array of all of the columns on the sheet. This would be followed by a "Run Child" step. You would tell it to run for each object in the columns array. (so if there are 250 columns, the child will run 250 times).

The "Run Child" step would point to your second bridge flow (the child). You would set this workflow up to make the desired update for each column.

Not sure if this description is confusing or has enough detail to get you started. Please free to reach out via email if you want to do a live session and set this up together.

Jess

NeilKY

@jessica.smith I believe my mistake will be simple but I just can't get it.

I tried setting it up as you suggested but can't understand how to pass on the column ID to the child, i suppose? I get the error, "workflow execution failed: Plugin Error INVALID_DATA : failed to execute extension module : Required parameter "Column ID" is not set."

Also, I don't know where to get the count of columns in the sheet from the run log. Is it the "columns: Array[17]' data reference in "Get sheet"?

Here's what the Parent looks like:

{F52667D0-E0D9-479E-978E-FE3E20BF249A}.png

and here's what the Child looks like:

{0FA73755-CA40-4574-9701-79AC2C520EAB}.png

The Column ID I used came from the PARENT in the "Get Sheet" Step. : Value is {{states.Get Sheet.smartsheet.get_sheet.sheet.columns}}

PS. The "Array Management: Extract Field From Array" comes from the tutorial, used to grab the dropdown values. It's not required and I don't reference it, but it was pretty neat so it's just in there. I specify the field values in the Child workflow in the "Update Column" Values section.

jessica.smith

@NeilKY

You should be able to do something like this.

For the Parent:

Copy the Data Reference for the column array in the run data and paste it into the "Number of Runs" field on the Run Child config.

IMPORTANT NOTE: When you are first setting this up, have your Get Sheet function only grab 1 column so that you don't trigger the child 250 during set-up and testing. You will remove the column filter before you run the live flow.

For the Child:

Trigger the child flow from the parent one time so you have some run history. Find the column ID ("Id") under the data and copy the data reference. Paste this into the Column ID field on the Update Column module.

Does that help?

Jess

NeilKY

@jessica.smith YES! That fixed it. I copied your actions and it works! I don't understand the concept very well though. Here's how I understand the process. Please correct me if I'm wrong?

Parent:
Use Get sheet to locate all columns on a sheet in parent.
Link child and parent using child workflow.
Set the child workflow to run 1 time to give the log files some data.
Run log in parent under Get Sheet it gives us the results. That includes the Sheet's column Array.
We use that Data Reference to tell the Child workflow how many times it needs to run by replacing the 1 in "Number of Runs" with the same Data Reference. (Bridge only interprets the Data Reference as a COUNT of rows because it's in the "Number of Rows" field. However, we could use that same data reference to get the details of each column if we wanted to)
Child:
Run Log displays the "Trigger" log, which is the parent data.
We grab the "ID" Data Reference because it tells us the Column's ID as it cycles through each one.
We will now use that column ID Reference to tell the "Update Column" Element to update that Column with the other details we place in the "Update Column" Element.

Is this all accurate? I wish I could compare it to farm animals to help someone like me understand it better, but writing it out seems to do the trick, sort of… (if it's right).

Thanks again!

PS. How do I get around the problem when the API tries to modify the "Primary Column" and it obviously fails because it can only handle text fields? Is this where the || comes in? I remember reading about it but can't find any info about it now :p

jessica.smith

@NeilKY I think you've got it!

For the primary column, you're right that you can't change the type of the primary column as it has to be TEXTNUMBER.

A few ideas for working around:

You can add a Conditional Junction to your Child workflow to check the column name or ID before making the update. If the name matches the name of your primary column the flow can take a "do nothing" path and move on to the next column.
You can change your "Update Column" action to a custom API call. As you've seen, when a pre-built module fails, both the child and the parent fail and stop running. When you use an API call, if the API call fails it will return an error code, but won't stop parent flow from moving on to the next column.
I can't remember if the "Get Sheet" function returns the array of columns in order of index or alphabetical, but I think its in order of index. If that is the case, since you are only working on a single sheet, you could just temporarily move your primary column to be the last column on the sheet when you trigger your flow. That way when it fails, it doesn't matter if it terminates the flow. This isn't a solution I'd recommend for like a permanent automation that you intend to run again and again, but for this use case, a quick and dirty method like this might be fine.

The || can be used if you need to tell Bridge what to insert in place of the relative reference if the value is blank. Bridge will insert the text that is to the right of the ||. If you just insert ||}, Bridge will return an empty field. If you leave off the ||, Bridge will insert the literal json text of the relative reference into the field.

Jess

NeilKY

@jessica.smith Thanks! I successfully used #3 above. I'll create a junction if I ever need this to be a permanent solution but you're right that #3 was great.

Thanks for your help in not only fixing my problem, but helping me understand it better!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.