How do you update a list of data type in a backend workflow?

[mango74]

I am using backend workflows to iterate over an API response to create new rows in the database.

I would like to update one database data type with information from another data type. I’ve been able to do this when searching for one value in the second database but I can’t wrap my mind around the logic when I need to search for more than one value.

Data Type: Item

Data Fields:
list_of_option_id (list of Text)
list of Options (list of Options)

Data Type: Option

Data Fields:
option_id (text)

I would like to update Item > list of Options
when the
Option > option_id is a value in the Item’s list_of_option_id

Backend Workflow:

Create a row in the Options database:

Create a row in the Item database:

In the API workflow where I create an Item, this is what I’m doing for the data field list of Options:

I’ve also tried “contains” but neither of these return the bubble id for the Options row to the corresponding Items database.

It’s not a race conditions issue as I create options first and don’t move onto the next step of creating items until the final option is created.

There are no current privacy rules for any of the data types.

Any help would be greatly appreciated!

[alan8]

I think best way of tackling this would be to use the advanced filter.

This would mean you can test for the presence of the single item within the list. This would also be server side, which would be faster and more efficient. But be warned, they can get messy and end up being really slow. You may end up daisy chaining some searches together.

You can set up the advanced filter in the constraint in the query - it is at the bottom of the list when you create the filter against the search.

[mango74]

Hey Alan,

Thanks so much for your response!

I’ve shared images of the two databases I am trying to connect and the backend workflow using the advanced filter below. Would you mind checking my advanced filter expression and letting me know where I might be going wrong?

This is what my database looks like currently:

Item Options

Items

• The list of option id is type text, and the value is a string of text like this: “ZMIH5JWRP74OCRSXEWYTIUFT, M5IQ4KUZVPLQVG6DOSOKSSGV”
  
  

I am trying to link the Item Option to the Item database via the list of options data field.

This is the backend workflow step where I try to write this value to the database.
First search constraint is empty:

This is the filtered constraint:

The expression I am trying to write says something like this:

Do a search for all Item Options where the Item Options option_id is also found in the data type Item’s list of option id, and if it is found add the Item Option to the Item’s data field list of options.

For example, the Item Latte has two option ids. “Hot/Iced” and “Cup Size”. So, I want the list of options field for Latte to be linked to the Item Options “Hot/Iced” and “Cup Size”.

Thank you again for your help!

[alan8]

Yeah I think you are nearly there.

If I am understanding this correctly you want to change the perspective of the advanced filter to be looking for the occurrence of the option id inside the list of the options ids

Currently you have sort of have it the other way around.

But I am slightly confused by what you are actually trying to achieve. You are creating a new thing, and you want to add in any occurrences of those items in another list?

[mango74]

I think you’re right and I think I found the problem but I’m unsure how to fix it.

More context:
I’m integrating my Bubble app with Square POS to build an ordering and loyalty app. These steps are pulling in the Square catalog and writing it to the database.

I’m linking various data types together to make it easier to display catalog Items and their associated options/modifiers on the front end.

My current workflow looks like this:
Press button on the front end to start the process of getting the Square catalog
API request to Square to get Options
Backend workflow iterates through all options until finished then schedules the next backend workflow to get Option Values.
As this step iterates through each option value it does a search for Options where the option_id = the option_id associated with the value and adds the option value to a list of options values in the data type Options.

It sounds more complicated than it is and a visual might help. If you’d like to see the app I’d be happy to share it with you.

The problem
I am building a plugin for each API request to Square. Because of the plugin limitations I have to receive each value I want returned from the API response as a list of text.

This means the value I receive in the returned values “option_id” is a list of text and it is returned as a string.

This means it is written into my Bubble database like this: “ZMIH5JWRP74OCRSXEWYTIUFT, M5IQ4KUZVPLQVG6DOSOKSSGV”

When I do a search for any Options containing this option id there aren’t any. Because the thing I’m searching for is two option ids put together separated by a comma.

I need the value to be written to the database as a true list of texts separated by a comma, like this: “ZMIH5JWRP74OCRSXEWYTIUFT , M5IQ4KUZVPLQVG6DOSOKSSGV”

I’ve manually tested this approach and it does work when formatted like a true list of texts.

That said, do you know how to extract or reformat a value I receive in a response? The ids can be different character lengths and Items can have 1+ associated option ids.

Essentially, do you know of a way to take this value “ZMIH5JWRP74OCRSXEWYTIUFT, M5IQ4KUZVPLQVG6DOSOKSSGV”

and turn it into this:
“ZMIH5JWRP74OCRSXEWYTIUFT , M5IQ4KUZVPLQVG6DOSOKSSGV”

Here’s an example of this in the database. The correct format is in the red box.

[alan8]

Have you set the list of option id to be a list in the set up of the table (Data Types tabs in the database)

[mango74]

Yes, “list of option id” is a list of text.

I am trying to use the splitby expression when creating the entry for the database, and it is splitting it, but it is written as a string in the database.

[alan8]

You might be over thinking it. Why are you splitting the input? Where is that coming from? Are you receiving an array from somewhere?

But it looks like you are missing the last step - which item from the list of split items do you want to add to the list in the table? You need to name that item (i.e. item#1) at the end of your expression in yellow.

And from there, if you want to add to the list in the database, just add to the list and bubble will do the rest for you .

Data (things) | Bubble Docs

Adds an item to the existing list. The type of thing should be of the same type as the field. If the item is already in the list, it will not be added.

[mango74]

I receive the input as a list of text from the plugin I’m building. The value is from Square’s API response and is formatted as a string like this: “ZMIH5JWRP74OCRSXEWYTIUFT, M5IQ4KUZVPLQVG6DOSOKSSGV”

The response contains all option ids in the Square Catalog returned as a string.

I decided to split the input because when I did a search with an advanced filter I wasn’t able to get any results. I believe this was because I was searching for option id “ZMIH5JWRP74OCRSXEWYTIUFT” in the string “ZMIH5JWRP74OCRSXEWYTIUFT, M5IQ4KUZVPLQVG6DOSOKSSGV”. I don’t think Bubble recognizes a value or part of a value as a match so I split the string into separate ids.

After splitting the input, I have been able to add one value and only one value to the data type Item > list of options field.

I would like to add every item that matches to the data type Item list of options

For example, a Latte can be “hot/iced” and has a “cup size”. So I need both of these Options to be linked to the Item “Latte.”

Whereas, Toast does not have a “cup size” nor can it be “hot/iced” so it will remain blank.

Items:

Options:

If the Item’s list of option id (list of text) contains the Option’s option_id (text) I want to link this Option to Item’s list of options field.

I know it’s complex and I appreciate all of your help. The Square Catalog structure looks like a pyramid.

                          Category
                           Item
                      Options, Modifier List 
              option values            modifiers

Category: Espresso drinks
Item: Latte
Option: Cup size
option value: small, medium, large

Modifier List: Espresso drinks
modifiers: triple shot, add vanilla,

I am trying to set up the database in this way so that I can more easily access values in the front-end. I can do a search for a category once and get everything under it.

This is especially helpful for the actual ordering menu when the user is choosing how they want to customize their latte.

I manually added the options and modifiers to the Item latte in the database to get the image below so when I link the database this is what it should look like:

I’m almost there!

[alan8]

@mango74

After splitting the input, I have been able to add one value and only one value to the data type Item > list of options field.

OK, this helpful. So the bit you are missing here is that you need to convert that data to a list.

If it were me, I would change the worflow so that I parsed out and split the ID you receive in a comma separated list and create a List of those items. And then you can add those to the database in one hit rather than trying to manipulate the list on the fly and adding them to the database at that point.

[mango74]

Thank you for all of your help!

I finally figured it out what was happening.

When I split the input I thought any blank spaces would be removed when the value was added to the list. Instead, there was a space at the beginning of the second value. This is why I was only able to get the first value and not the second.

My search was looking for “ZMIH5JWRP74OCRSXEWYTIUFT"
and " M5IQ4KUZVPLQVG6DOSOKSSGV”

As soon as I added "each item:trimmed to the list under list of option id, everything fell into place.

I use an advanced filter to search if the values in the trimmed list are found in Item Options and if they are, I add them as a list to this entry in the database.

In summary, a space in my list value was the issue. Thank you again! I wouldn’t have gotten this far without your help.