Forum Discussion

WarrenBarbour's avatar
WarrenBarbour
Qrew Member
5 years ago

Equipment Rental App - Utilization Rates

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
------------------------------
App builders
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    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 Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • WarrenBarbour's avatar
      WarrenBarbour
      Qrew Member
      5 years ago
      Thanks so much Mark! As always from you, fantastic solution!

      ------------------------------
      Warren Barbour
      ------------------------------
      • WarrenBarbour's avatar
        WarrenBarbour
        Qrew Member
        5 years ago
        Hi Mark,
        I tried to work through a number of ways in an effort to determine "# days rented out M0", without much success. I had tried a series of arguments that looked to check the month/yr values of current month to the Start Date and End Date of each rental period, and then make a series of calculations to count either days between Start Date and End Date, or days between start of month to End Date, or lastly, days from Start Date to end of month. Seems like there must be a better way. Can you offer any further guidance?



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