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:
-
Get all Catalog categories
-
Recursive workflow to create a new row for each category
- In this workflow, use the category_id to retrieve all items that are in this category
- Update the data type “Category” data field “item_names_as_text with the names of all items (list of text)
-
Database Trigger fires when there is a change in the “Category” data type
-
This trigger schedules a recursive workflow that calls the Square API and creates a new row for each item in each category
-
When creating a new item, these data fields are written in the Item data type.
- category - linked to the Category data type with the bubble unique id
- item_name
- item_price
- item_image_url
- modifier_list_id_text - saves all modifier_list_id values
- 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!