Glider, I´m having a bad time trying to solve this, already tried different approaches (query formula is one), and I´ve not achieved my requirement please help!
Let me give you a bit of context. I have three tables:
• Item Type: Stores the available categories or models of the items.
• Items: Stores the actual items in stock (each one of them has an atribute “item type”
• Reservations: Stores bookings to use a certain amount of a particular or multiple types of items within a date range.
I would like to generate a View/search table that contains the amount of available type of items given a start (SD) and end (ED) date that the user states for a posible reservation, so the user can add available items to reservation.
The table would look like this
Type: The type of item.
Amount available: Item[Item_type == This row type].count - Reservations[SD<current_posible_reservation.ED && ED>current_posible_reservation.SD && item_type cointains This row type].thisrow_item_type.count
This way, the customer can preview which types of items are available for reservation.
Think would be quite similar to a clasic car rental, like this.