I’m trying to build a small app to manage catering equipment and could use some inspo on logic / table structure, if anyone could spare a minute and braincell!
Basis is an inventory list containing items and item count (e.g. 100 champagne glasses, 150 cocktail glasses, etc.)
The idea is to “book” X amount of available items (e.g. 40 of 100 available champagne glasses) for a certain time period. For this time period, there are now only 60 champagne glasses left available.
Subsequent bookings can now only use those 60 glasses (if the timeframe for the new booking overlaps with the previously reserved timeframe).
So… I’d need to make new bookings only possible/valid if booked # of items <= available # of items for any day within the booked timeframe.
Existing tables so far:
- Items (listing inventory)
- Bookings (details of the single bookings including start/end date)
- Item_bookings (listing the items and # of items for every booking)
Would I need to add a column per day to the Items table x days into the future calculating availability for each day? That seems inelegant and unflexible (ideally, there would not be a limit to how far into the future bookings can be done)? Where else would I log the booked/available items, or could it be calculated on the fly given the desired timeframe and existing Item_bookings?
Any inspiration would be greatly appreciated.
Think of your Item_Bookings table as an allocation for a certain date. The Items table should NOT be adjusted until the event is completed. Until then you have a Quantity on Hand and an “Allocated Quantity”.
To compute availability of any certain item on a given date, calculate it from the Quantity on Hand - (All allocations for that item <= the Date to Check). You can get the allocations by date with an expression against your Item_bookings table.
I hope this helps!
1 Like
Thanks @WillowMobileSys !
Not sure what you mean by “Items table should NOT be adjusted until the event is completed”? The items table is a fixed inventory, as in “we own 100 champagne glasses”, and should only be changed if the actual inventory changes (as in “I dropped a rack of glasses and now we only own 75”).
If I understand your approach correctly, I think I am currently trying to achieve just that:
Take the maximum number of allocated glasses within [Date range] from Item_Bookings table, subtract that number from absolute number of available glasses from Items table, result is available number of glasses for [Date range].
BUT I have trouble figuring out how to do this for a date range, not just a specific date (check for overlap in date ranges, not just compare single date to other dates).
Currently trying to SELECT() all rows in Item_Bookings where at least one date between start and end date of the booking matches with at least one date from the date range of the “new” booking using COUNT(INTERSECT(List of dates in this new booking;list of dates in existing bookings)>0) as an argument in SELECT(), but the expression stubbornly refused to work (“both lists must be of the same type”).
Does this even sound like the right approach to the issue?
Ha! Sometimes starting over from scratch helps. No idea what didn’t work as everything seems to be exactly the same, but whatever.
Available items in timeframe:
[Items].[Total inventory] - SUM(SELECT(Item_bookings[Quantity];AND(COUNT(INTERSECT([_THISROW].[Dates_List];[Dates_List])) > 0;[Item] = [_THISROW].[Item])))
[Dates_List] is a VC listing all dates between start and end date of the booking.
Still needs some testing and probably some tweaking, but I think this should do the trick.
1 Like
Ahhh, I was thinking of disposable products. WOW, fancy catering!!
Now that I understand you are trying to account for return of items after an event, I need to think this through a little more. I’ll get back to you.
Actually, with just a couple seconds of thought. you really only need to consider the Start Date of any new Event that overlaps right? I assume you are attempting to verify you have/will have enough items at the start of the “currently being booked” event. Right?
2 Likes
Before you waste too much time on my behalf, check out the solution I think I found myself above (you know how sometimes when you explain a problem the solution kind of finds itself?)
https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Inventory-Resource-manegement-app-Inspiration-needed/m-p/875610/highlight/true#M263189
1 Like
@WillowMobileSys wrote:> > Actually, with just a couple seconds of thought. you really only need to consider the Start Date of any new Event that overlaps right? I assume you are attempting to verify you have/will have enough items at the start of the “currently being booked” event. Right?
Hmmh. Let’s say I have allocated 40 of my 100 glasses from March 10 to March 15.
A new booking for 80 glasses comes in that would be from March 5 to March 12 → Start date of new event is outside existing timeframe, BUT the end overlaps → I would not have enough glasses on March 10
So I think I’d need to consider all dates within the timeframes.
Also while writing I think the “solution” I just bragged about needs some serious work/corrections to atually give me the correct number of available items, but now that I have the “timeframe overlap check” via INTERSECT() working, the rest should be doable.
1 Like