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

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!

This topic was automatically closed after 70 days. New replies are no longer allowed.