I am organizing our rental sheets. We have around 10 pieces of equipment that we rent. Each item has a unique rental ID number When we get a rental request we enter the the dates in a spread sheet. I am now working on getting this setup in an Appsheet calendar view. I have it working properly with displaying the dates in a calendar view.
Now I would like to setup that it shows all the dates that the equipment is available. Basically I want to reverse my data, The dates that are scheduled would now be empty.
Any suggestions??
Thank you in advance.
To display the availability of your rental equipment in AppSheet, start by creating a new table, such as CalendarDates, which contains a column for all dates over a specified period (e.g., the next year) and a column for the equipment IDs. This table will list every date and associate it with each piece of equipment you manage.
Next, in AppSheet, add this CalendarDates table to your app. Create a virtual column named Available in this table with an AppFormula that checks whether each date is part of a scheduled rental period. For instance, the formula could be NOT(IN([Date], SELECT(RentalRequests[RentalDate], [EquipmentID] = [_THISROW].[EquipmentID]))), which will mark the date as available if it’s not within any rental period for the specific equipment.
Finally, set up a new calendar view using the CalendarDates table. Configure this view to only display dates where the Available column is TRUE. This will effectively reverse your current data, showing only the dates when each piece of equipment is available, thus helping you easily manage and visualize your rental inventory.