Calculations with 2 Collections


I’m trying hard to use information from 2 different collections to get to a specific result, without luck.

The goal : Compare “shopping list” prices.

How :
- Step 1.Create Order List (Table 3) by choosing items (from Table 1)
- Step 2.Update quantity for each items (in Table 3)
- Step 3.Display Total Price per Order List (Table 3) for each supplier (from Table 2)

Where I’m blocked it’s at Step #3. As I would like to calculate the total price per item and then make a total price per supplier.

*** Table = Collection


Any help would be appreciated, thanks :slight_smile:

Adalo is not like a relational database. You need to add the supplier and the price to the orderlist collection, if you want to calculate with it.

Thank you!

Would you know if I could get around that problem with AirTable or Zapier/Google Sheets?

As far as I know: In Airtable you can also only calculate within the same table. So you need to have a relation and a lookup field. Google Sheets would probably work but from my POV it’s just to slow.
But why don’t you create a collection for orders like

  • Item Name
  • Format
  • Quantity
  • Supplier
  • Price ??

Thanks a lot karimoo :slight_smile: I understand what you mean, I guess I can try something like that as a temporary solution to showcase my MVP to potential customers.

The problem with that solution is that in my app there will be something like :
10-20 suppliers
150 differents combination of items/formats
Prices that change weekly

The changes for suppliers etc you mention is what I would call Master Data where you have the number of changes in the table model of your spreadsheet anyway. The Order table I suggested has the transactional data which scale with the number of orders independent from changes in Master Data.

Do you need the user to be able to pay for the items or just display the total(s) and submit one order? From what I understand, Adalo is not able to submit payment to two different vendors from a single order. On the other hand, if you don’t need the user to submit a payment, Here’s what I would do:

From what I can see, table one is redundant since you already provide the item name and format information in table 2.

You will need to create four databases for this to work:

  1. Users
  2. Product Items
  3. Order Items
  4. Orders

They will then need to be set up with the following relationships:
(keep in mind the Bold items below will change based on what you name your databases)

Users DB:

  • Relationship to Order Items:
    • An Order Item can only have one User. A User can have multiple Order Items.
  • Relationship to an Order:
    • An Order can only have one User. A User can have multiple Orders.

Product Items DB:

  • No set Relationship.
  • When a user clicks the add to cart button, it creates an Order Item.

Order Items DB:

  • Relationship to User:
    • A User can have multiple Order Items. An Order Item belongs to one User
  • Relationship to Order:
    • An Order can have multiple Order Items. An Order Item belongs to one Order.

Orders DB:

  • Relationship to User:
    • A User can have multiple Orders. An Order belongs to one User.
  • Relationship to Order Items:
    • An Order Item can only have one Order. An Order can have multiple Order Items.

You also need an “Add to Cart” button that creates a new Order Item. It should be set up like this:

Once your databases are set up, you will need to have a way for the user to “start and order” This will create a new order for the user and allow any items that have been created as an Order Item to be added to the current order. Without this function, your cart and checkout will pull any and all items from the user’s Order Items collection.

You then need to create a “Cart” page that will show a list of all of the items added to the cart. Where it gets tricky in your situation is if you want to be able to add multiple items to your “Cart” that shows a combined total based on the quantity. When an order item is created, it creates a new independent record within the collection and each time the add to cart button is clicked, it will create a new order item and not just update the quantity of the already added item. This is where conditional logic would be really helpful but not a feature that Adalo currently has.

You could technically add in a + or - quantity button on the cart page once the item has been created. This may get a bit confusing for the user however if they happen to hit the add to cart button multiple times to add multiple items. The + and - buttons would only affect the current item. They would have to delete any unwanted items manually.

Requiring the user to update quantities from the cart page would allow you to do the basic calculation to display the total price of the item based on quantity on the cart page. Just be sure you also set up an action to update the order item’s total price in the collection when the plus or minus button is pressed so it will properly calculate the vendor’s total on the checkout page.

On the “Checkout” page, You would need to add in separate custom lists for each vendor you have and filter the visibility based on the Vendor’s name. This is where you can display the total (Sum of the order item’s total) on a per/vendor basis and allow the user to submit their “order”.

If anyone has a better way of achieving any of the goals I have laid out above, I would love to hear any suggestions. This is simply based on my current understanding of how Adalo works and working within their limitations.

1 Like

It looks like I might have missed the mark a bit of what you are trying to achieve after re-reading your initial post… I totally missed the comparison part.

Do you need a user to be able to click on one item, say a 2" nail, and have it auto show all of the vendors that have a 2" nail available along with their price? Then be able to continue adding items until their list is complete so they can choose the vendor with the lowest pricing?

I think you could achieve your goal by requiring your user to go into each vendor’s items page and add the items on their list to their cart but this would require them to visit all of the vendors they are interested in purchasing from and add each item multiple times. Once the items have been created into order items from each vendor, you could then display each vendor’s total.

You might also be able to achieve your goal by creating a massive line item that has all of the vendor’s information for a single item in one line. This would require a lot of filtering rules but it might be possible.

1 Like

Wow thank you so much for taking the time to help me. I really appreciate @karimoo @ctboita

Like you mentionned, yes the goal is really to compare total prices by Order List for each suppliers. Because of minimum order price set by the vendors, the users can only order from one vendor per week.

I made a really basic mock ( w/ hard-coded text) :

Step 1.Create Order List (Table 3) by choosing items (from Table 1)

Step 2.Update quantity for each items (in Table 3)

Step 3.Display Total Price per Order List (Table 3) for each supplier (from Table 2)

If needed the user can click on a supplier to see the details per item.

Have you tried adding and changing quantities from two different users? I think without requiring the user to create an “Order Item” multiple users will be updating the same quantity record inside the collection.

Also, you can program the add to cart button to automatically update the quantity field without having to have the update quantities button below… or does that take you to the next screen where you can change the quantities?

I’d suggest having a way to reduce the quantity of an item as well as delete it from the order.

Those are all things I can do and the flow is meant like that :slight_smile:

My problem remains where as I need to calculate Total Price per Supplier (Step 3).

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.