Equipment Rental App - Utilization Rates

Question

I'm working on early phase design for an app that will be used to manage equipment rental for a small operator. One of the key design requirements is for the app to track dollar and time utilization for each piece of equipment, across the entire rental inventory on a monthly basis. At the end of a given month, I need to be able to be able to report that summarizes the number of days each piece of equipment had been rented out of the total days in the month. My initial thoughts are to create an Equipment table, with a unique record for each individual piece of equipment in inventory, and a related Rentals table to track individual rental agreements, along with a third table to manage Customers. To simplify the solution at this point, each rental record will only have one piece of equipment, although a future many-many relationship could be in play. Rental periods will range from a minimum unit value of 1 day and 365 days.

The challenge I'm having in the design is that if a rental period for a piece of equipment spans across more than one given month, I can't seem to find a way to allocate x days for month 1, and y days for month 2. I don't want to anchor a duration of rental days to either the start or end date of a rental record. The only thing I can think of at this point is to build an automation to create/import a month-end snapshot against the rental table and post those month end views into a 4th Monthly Report Snapshots table simply to track rental days per piece of equipment.

As much as I'm sure this would be common for anything app used to track resource utilization spanning periods of time, I can't seem to find any related solutions. Any other thoughts out there?

------------------------------
Warren Barbour
------------------------------

MarkShnierYou · Answer

OK, here is another solution.Make a summary field on the Equipment for [# days rented out M0], where M0 means the current month.Then duplicate that field to be called [# days rented out M-1] ie month zero minus 1 ie last month and adjust the filters.Keep giong to about 24 of these so you have a two year history.Then if you want to not have those somewhat non user friendly field names, you could create a nightly process to run 24 saved table to table copies to copy these to another table were each record represent an equipment and the first of the month dates (key field in the format [Record ID# [f equipment] - YYYMM01. so then  it will easier to create proper report and nice charts. ​------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Sign up

Login to the community