List vs List query

Hi Everyone,
I have a problem with a query.
I have two databases:
Database PRODUCT:
Product_name – text,
Product_description – text,
Product_quantity – number,
Product_price – number
Product_image – image
Database BASKET:
Product – Product,
Basket_quantity – number
Checked_out – yes/no
A user can select different products and when selected they are added to the user’s BASKET database.
The view of the basket is a repeating group filtered by the products selected by the current user and Checked_out is false.
When a user wants to check out (continue to payment), the products in the basket(repeating group) are set to true and the quantity he selected for each product (basket_quantity) should be removed from the product stock (product_quantity).
The aim here is to find all the basket_quantity of all the items in the basket and to find the corresponding products in the PRODUCT database and substract the basket_quantity to the product_quantity.
Can anyone help with this please?

I think you will need to filter your “Quantity Available” calculation to match it with the “This Product” being processed.

Hi @NigelG, I have been trying what you have said but with a poor result.
Could you have a look please?

Yes, it is a little tricky, as you need to search for the Basket item to get the Quantity. As that is a list you lose the “This Product” from the next level up.

Anyway, I think this probably does it.

So go through all the Products from the Basket RG.

For each one, update the Quantity by the sum of the baskets for this user that have this product.

1 Like

Hi @NigelG, thank you very much for your time and help. Everything is working perfectly.

1 Like