Help Needed for Developing Reports Section in E-commerce App

molinarodrigo93 · 2024-07-19T19:09:47.527Z

Hello Bubble community,

I need assistance with developing the reports section for my e-commerce app. So far, I have completed the purchase process with the following database structure:

Items

Restaurant
Name
Price

Items Cart

Restaurant
Items (List of items)
Order ID
Quantity
Subtotal

Order

Restaurant
Order ID (items cart)
ItemsCart (List of ItemsCart)
Total Price

I want to create a table that shows the quantity sold and the total revenue generated per product.

Here are my questions:

Do I need to make any modifications to my current database structure to achieve this?

I have tried various approaches but haven’t been able to achieve the desired results. Any guidance or examples would be greatly appreciated.

Thank you in advance for your help!

Zeroic · 2024-07-19T20:59:45.197Z

Hi! A couple of suggestions:

Items Cart should have the Quantity, Total and Item (not Items, since an individual item will be added). The Order table should have the Items Cart as a list.
You can remove the Total Price from Order since you already have it in the Items Cart as the sub-total, so storing the same info twice seems redundant.
You can remove Restaurant from Items Cart since you already have that in Items as well as the Order

Apart from that, you can achieve the per-product quantity and total revenue by Doing a search for Items Cart where the Item is the current item you want to search for. You’ll get a list of Item Carts that you can add over for the Quantity and Total

Let me know if this helps

molinarodrigo93 · 2024-07-20T00:57:42.504Z

Hello Zeroic,

Thank you very much for your response. It has been very helpful, but I still have a problem bringing the data to the table.

Let me tell you a bit more about how I designed this process:

Users can add items in the quantity they desire. Adding or removing an item triggers a workflow that adds or removes items from the cart.
Once in the cart, users can delete, add, or remove items.
Once the user has the desired order, they can execute the order.

If I do a “do a search for” “items cart” I will get incorrect data, as it includes not only order data but also data from carts that have been abandoned.

How can I bring to a table the quantity and total price per product of the orders and not all the carts?

Zeroic · 2024-07-21T15:16:41.165Z

Ah, in that case, you have 2 ways to do this

Create another data type called Cart, which has a list of Items Cart in it as a list (you’ll need Items Cart since it specifies the quantity and the Item); or
Create the Order whenever a user add a Item Cart and add a Status field for it. Filter by Status (the fields can be In cart, Pending payment, Completed and so on)

Let me know if this works

molinarodrigo93 · 2024-07-21T21:06:35.863Z

Thank you again, Zeroic. I opted for your second suggestion about adding a “status” field, and now, after a user clicks to generate an order, the items in the cart are updated to show “Completed” in the status field.

So far, this has helped me calculate the totals per product, but I still can’t get the table to correctly reflect the 3 columns. As you can see in the attached image, the table correctly shows columns 2 and 3, which are the units and revenue per product. However, column 1 should only say 1 item, for example, “Americano” or “Chocolate Ice Cream,” but it shows repeated items.

Captura de pantalla 2024-07-21 a la(s) 5.56.25 p. m.1792×808 51.4 KB

How could I solve this problem? I think the issue lies in how I’m calling the data rather than in the structure itself.

Thank you again.

Zeroic · 2024-07-22T17:30:07.759Z

Is your Item a list of things or a single item in your table?

molinarodrigo93 · 2024-07-24T21:55:58.094Z

Hi Zeroic,

Thanks again for your answer.

It’s a list of things, because it’s a “Items carts” type as you can see in the following image:

Captura de pantalla 2024-07-24 a la(s) 6.50.42 p. m.2110×680 110 KB

I can’t figure out how to match each product name with its total quantity and total value in the same table.

Perhaps I have to make changes to the data structure?

Thanks again for your help!

molinarodrigo93 · 2024-07-31T14:44:07.958Z

@Zeroic Hey, could you see my last post? I still struggling with this

Thankss!

Zeroic · 2024-07-31T20:42:21.927Z

Hey! Sorry, I wasn’t notified of this. Do you think you can find some time here to figure this out on a quick call?

molinarodrigo93 · 2024-08-01T20:37:05.761Z

@Zeroic I’ve already schedule a quick call for Monday.

Oh thank you so much!

system · 2024-09-27T19:10:44.443Z

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