How to setup Bridge Junction for Update Row w/ multiple matches

Zeb Loewenstein
Zeb Loewenstein ✭✭✭✭
edited 01/25/23 in API & Developers

I am trying to leverage Bridge to update all cells of a column where there is a matched ID between a source sheet and a destination sheet. In this scenario there would be multiple matches in the destination sheet.

I'm struggling to determine how the Bridge workflow would identify which rows in the destination sheet to update. I've tried using both 'Get Row' (for the source sheet information) and 'Get Sheet' (for the destination sheet information), then pair possible data references for matches against the updated row (source).

  1. Which conditional junction would you use and how would you set up the criteria / states?
  2. Is Bridge limited to only updating the 1st found match, or is it possible for an Update Row to be executed for multiple matches in a single run?

Thanks for the help,

Zeb

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Zeb Loewenstein

    Can you clarify what your ideal output scenario would be?

    If there are multiple matches in the destination sheet, do you want duplicate data entered into those rows?

    For example, if this is your source sheet:

    A - Apple
    B - Banana
    

    And this is your destination:

    A - 
    A - 
    A - 
    

    Do you want it to output:

    A - Apple
    A - Apple
    A - Apple
    


    Side note: as much as I love Bridge and am happy to think through options here, do you have access to Data Mesh? If you do, I would simply set up a workflow in Data Mesh to achieve your goals. This will map data from one Smartsheet sheet to another based on a key matching value.

    You could also use a formula (INDEX(MATCH combination), which would repeat the output in the destination based on the first matching content in the source sheet.

    Cheers,

    Genevieve

  • Zeb Loewenstein
    Zeb Loewenstein ✭✭✭✭

    @Genevieve P. I hadn't considered DataMesh for this and is a great suggestion; sending Index / Match formulas discretely within the Bridge workflow also fixes.

    Thank you!

    Zeb

  • Zeb Loewenstein
    Zeb Loewenstein ✭✭✭✭

    @Genevieve P. I can clarify with another example, specifically one where DataMesh doesn't work because in my use case I'm trying to update rows in a downstream sheet which meet a couple different conditions.

    When our intake (source) sheet updates a Resource Type's column (e.g. Project Manager) I want to perform an Update Row for the Resource's Name when it finds a row that meets two different pieces of information based on our Status:

    1. Intake ID # (exists in both the source and destination sheet)
    2. Role (exists in only the destination sheet)

    For example: when Status is = "Assignment Needed", and the Role Type column "Project Manager" changes to a different value, I'm trying to code Bridge to update the Resource Name column in the downstream sheet for where it matches both the Intake ID # and where Role contains "PM". I would then have different workflows set up for each of the other roles. Before a project gets started there may be several changes to each different resource type, so having the Update Row function work like this would avoid unnecessary rows from being created.


    Trying to implement the above structure I get stuck on the following Equals junction:

    First Value: {{states.Get Row.smartsheet.get_row.row.cells.Intake ID #.displayValue}}

    • Intended to pull the Intake ID # from the source sheet

    Second Value: {{states.Assignment Needed.smartsheet.get_sheet.sheet.rows.cells.Intake ID #.displayValue}}

    • Intended to pull the Intake ID # from the destination sheet


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Zeb Loewenstein

    With Bridge, I would potentially suggest going the more programmatic route and try making a HTTP Call to bulk update rows, or create a Javascript module that handles the bulk update of rows.

    I don't have a direct example I can show you at this time for that route, however there is a way you can do this using Smartsheet Formulas directly in the sheet in the meantime.

    An INDEX(MATCH formula can index your source sheet then bring back a value in the correct column based on the ID and what value is in your Role cell.

    For example, if this is my source sheet:

    Then in my Destination Sheet, I would use this formula:

    =IFERROR(INDEX({All Columns}, MATCH([Intake ID #]@row, {Intake ID}, 0), IF(Role@row = "Project Manager", 2, IF(Role@row = "Business Analyst", 3, IF(Role@row = "CSM", 4)))), "")

    The MATCH is finding the Matching Intake ID across sheets. Then the IF statement at the end is finding the correct column number (in my sheet I only have 6 columns in the first {all columns} range). For your sheet, I would suggest adding a helper column right next to your "Project Manager" column to bring the ID closer, so your {all columns} range doesn't reference the columns in between them.

    I hope that helps for now!

    Cheers,

    Genevieve