Square POS Integration How do I connect Data Types by using Data Fields in the database?

[mango74]

I am in the process of integrating Square with my Bubble app. I know this might be a long shot, but I’m hoping someone will be able to help me with linking things in my database.

This is going to be a long explanation asking for help so thank you for bearing with me!

My question:
How can I link options and modifiers to Items and Items to categories so I can easily reference them in the database and on the front end of the Bubble app?

Set up
I’m having trouble connecting the catalog items I returned from Square via the API in the database. For example, linking “Latte” to the category “Coffee/Espresso Drinks”

I think it would be helpful to share an example of how Square works with a catalog so you can understand my logic in setting up my database the way I have.

Category: Coffee/Espresso Drinks
Item: Latte
Modifier: Milk (oat, almond, whole)
Modifier: Hot/Iced (hot, iced)
Modifier: Espresso options (triple shot, quad shot, add vanilla)
Options: Cup Size (small, medium, large)

Every Category has Items within it.
Category: Coffee/Espresso Drinks
Items: Latte, Drip Coffee, Americano

In the same way, each Item has modifiers and options linked to it.

Each modifier has an id called a modifier_list_id. Within each modifier_list_id there are modifier_id values, which are the names of each modifier.

For example:
modifier name: Milk
modifier_list_id: ABC123

modifier name: oat
modifier id: 1111

modifier name: almond
modifier id: 2222

The same applies to Options.

This is my database set up right now:
Data fields with “Test field” written next to it are because I haven’t figured out how to link the database field with the database type yet. I would like to link them in the database, and this is where I need help. I am currently use list of text test data fields so I know what is going on.

Data Type:
Category

Data fields:
category_id (text)
category_name (text)
items (list of Items)
items_names_as_text (list of texts) Test field

Data Type:
Item

Data fields:
category (Category)
item_description
item_id
item_image_url
item_name
item_price
item_modifiers (list of Item Modifiers)
item_options (list of Item Options)
item_modifiers_id_text (list of texts) Test field
item_options_id_text (list of texts) Test field

I am still setting up the next couple of Data Types and Data Fields. I don’t have it 100% figured out yet.

Data Type:
Item Modifiers

Data fields:
modifier_id (text)
modifier_list_id (text)
modifier_list_name (text)
modifier_name (text)
item (Item)
list_of_items (list of Item)

Data Type:
Item Options

Data fields:
option_id
option_name
option_value_id
option_value_name
item (Item)
list_of_items (list of Item)

Current workflow:

1. Get all Catalog categories

2. Recursive workflow to create a new row for each category

   1. In this workflow, use the category_id to retrieve all items that are in this category
   2. Update the data type “Category” data field “item_names_as_text with the names of all items (list of text)

3. Database Trigger fires when there is a change in the “Category” data type

4. This trigger schedules a recursive workflow that calls the Square API and creates a new row for each item in each category

5. When creating a new item, these data fields are written in the Item data type.

   1. category - linked to the Category data type with the bubble unique id
   2. item_name
   3. item_price
   4. item_image_url
   5. modifier_list_id_text - saves all modifier_list_id values
   6. item_option_id_text - saves all item_option_id values

Question:
How can I link options and modifiers to Items and Items to categories so I can easily reference them in the database and on the front end of the Bubble app?

Thank you!