How to read and store multiple rows of data using Bridge?

Brian_Richardson

Hi all, I'm hoping that someone has solved for this, or that I'm being blind, but I can't see a way to easily read a array of objects using Bridge, where those objects contain multiple fields, and then loop through those objects.

I have an example below that I was able to make work by combining columns and extracting from the array, but that's not going to work for anything more complex. Surely there's a way to read a set of objects, each containing multiple fields/values, loop through that set, and do something with each object like inserting them as a row in a sheet with the values in different columns? I know child workflows are used to loop, but in my example below you'll see that they don't seem to work with an array of objects that contain multiple values in each object or more nested arrays.

My example is Gitlab. I have a Bridge workflow defined that runs through the following steps:

1) Search Sheet (Smartsheet) for rows that have a status update and epic ID defined in two columns

2) Get a list of objects from the search that looks like this

Smartsheet: Search Sheet

--Output: Object

----data: Array[2]

------0: Object

----------Status: 'my status'

----------Epic: '123456'

------1: Objects

----------Status: 'my status 2'

----------Epic: '67890'

3) If I then try to run a child workflow and reference the data: Array[2] in Number of Runs field, I get an error

I'm pretty sure this is because the child workflow can't reference an array that has multiple fields in each object.

To get around this, I used a sheet formula to combine the epic ID and status into a single field. Then I used Array: Extract Field to pull that field out of the Search results to arrive at an array that looks like this:

Array Management: Extract Field from Array

Output: Object

results: Array[2]

0: '123456-my status'

1: '67890-my status 2'

The child workflow was able to reference this Array for the Number of Runs fields, pretty sure that's because it's just a single item for each object in this array.

I can't be doing this for large objects with multiple values and further nested arrays though, which is how most info comes back from other tool's APIs (and even Smartsheet API).

Anyone have any ideas or examples of how you've successfully looped through a data set of multiple objects with multiple nested objects and arrays in each?

Thanks for any tips and help in advance!

Samuel Mueller

It was a nested array already. So I did a getsheet, then in the extract field from array, I referenced the rows array (162) and I extracted cells to get a new array of all the cells data.

So then I got a new array - which is what I wanted to loop so I put the new array json reference in the child workflow number of runs: {{states.startstate.arraymgmt.extract_from_array.results}}

Samuel Mueller

When I get that Error I have to delete the child workflow (just in the parent workflow) and create a new one. Not sure if that's your issue or not though..

Brian_Richardson

Thanks- I can try again but I did already try deleting the parent and child workflows entirely and setting them up again, but no success, same error.

Have you been able to successfully loop through a set of JSON objects with multiple fields in each object?

Samuel Mueller

Yes - for example I have a workflow that loops this array: So in the child workflow for each object, all three fields (phase, project name, project number) appear in runtime.

This is what I'm putting into number of runs in the child workflow: {{states.startstate.arraymgmt.extract_from_array.results}} - so it loops 162 times and in each loop I create a new array with the information I want. I use Global data to store the array and manipulate the final product in the parent workflow.

Child workflow I'm referring to (where I put the reference for number of runs): Then I choose which workflow I want to run (where I create the new array).

This child workflow is in my "Parent" workflow.

Brian_Richardson

Ok interesting. So it IS possible 😀. It must be a nuance of how I have it setup. How did you extract multiple fields in the extract step?

Samuel Mueller

It was a nested array already. So I did a getsheet, then in the extract field from array, I referenced the rows array (162) and I extracted cells to get a new array of all the cells data.

So then I got a new array - which is what I wanted to loop so I put the new array json reference in the child workflow number of runs: {{states.startstate.arraymgmt.extract_from_array.results}}

Brian_Richardson

Aaaaaahhh. You just saved me I think. I didn't think about using the name of the array as the "field" to extract. I'll have to give it a try and see what I get, but I bet it will work for this case too. Thank you!

Brian_Richardson

Just to follow up, Bridge support got back to me today to say that the error I received was "a bug in the SDK". But, no info yet on how to get around that bug. I haven't had a chance to try your suggestions Samuel, but I'm hoping that may work.

Jon Barrow

This thread has been so helpful! I'm trying to loop through row values to create an array that will be used to define available values in a different column's dropdown list, and I'm a little stumped on the structure of the child workflow.

@Samuel Mueller, would you be willing to elaborate on how you created and populated the array in your child workflow?

Samuel Mueller

@Jon Barrow Here is an example of the child workflow. The junction is checking whether data exists. I use Store Data to create the array that can pass back and forth between workflows. every time the child runs it retrieves the data, manipulates it by adding object to the array, and then storing to new array over the Data I created.

So in the parent I used Utility Function Store Data to create a DataStore: and then in the child I retrieve the DataStore, manipulate retrieved data, and Store it again after I've done, and it iterates each time until the child workflow(in the parent) has completed. The junction is essentially to check whether it's the first run or not since you can't retrieve data if it doesn't exist an you get an error.

Child Workflow:

In the parent - this is my child workflow

Here is a bit more to try to help follow (in Parent). I have 2 child workflows because I'm creating 2 different arrays and updating columns in multiple sheets with those arrays. (This is not the same workflow as what was previously posted in this thread)

Happy to try to answer further questions if you have more.

Jon Barrow

Thanks @Samuel Mueller - that was tremendously helpful. I think the last bit remaining for me is manipulating the data for input into the update column module. When setting dropdown values programmatically I think the format is [0,1,2,"Value 0", "Value 1", "Value 2"].

How did you approach that? I understand "add object to array" can only be used for key / value pairs, not simple lists. Did you build a string instead?

Samuel Mueller

You would use the retrieve data results.. So my array was a contact list, but it should work the same:

Does this help?

Jon Barrow

Thanks @Samuel Mueller! I'm receiving an error in the update column module: "failed to execute module : Unable to parse request. The following error occurred: Field "null" was of unexpected type."

Below is how my stored data is structured - do you see any issues?

Edit: Oops, all I needed to do was extract field from array before updating the column. Works like a charm now!

Brian_Richardson

This is all such good stuff. Love the collaboration. Guys just a note though that I've found a limit of around 3000-4000 objects for the child to parse before it just chokes and resets. So if you're trying to do this with a larger data set, Bridge isn't the best for that.

Samuel Mueller

@Brian_Richardson that's frustrating! Thanks for the note.

abrae005

I have a similar question, but I'm trying to do a search to find rows where one column matches (a date), and then update 4 or 5 other columns in that row.

Use case - calling a public API to determine where specific ships will be on a particular date, storing that in Smartsheet.

So, for Ship1, it may be 3/25 - Port Canaveral, 3/26 - Tortola, 3/27 - Nassau, while Ship2 is 3/25 - New Orleans, 3/26 - Nassau, 3/29 - New Orleans.

There is a separate API call for each ship. Using some of the code that Brian had provided in a different thread, I was able to parse the API response into an array, and then I was able to pass that to a child workflow that will search the sheet, find the row that has that date, and update 1 column.

The problem is that there will be 3-8 API calls, and each will return 3-10 date/location values, which is 80 calls to the child workflow, 80 search sheet functions, and 80 update row functions. That is a lot, and can be slow. I'm resigned to the fact that I'll probably have to have multiple calls to the child workflow, but I'd love to at least combine all my data, so that the process will only have to do the Search Sheet/Update Row once per row, rather than once per cell.