4 Replies Latest reply on Apr 5, 2016 10:30 AM by ekjonaas

    Creating a Rental Inventory


      I am attempting to make an equipment rental inventory that will aid users with scheduling.  What I've managed to do so far is this:


      Users can select equipment in a portal that is then added to a customer's invoice.  In the same row, it shows the amount of that equipment that we have in stock so for instance if we get a request for 5 pieces of one equipment type and the total shows we only have 4 in stock, we know that we have to amend the order.  As each order is entered into the system, the quantity on hand is updated by subtracting the sum how many pieces of a certain type of equipment went out from the total of our inventory for that given item.  Tables are set up as: Equipment and Invoice tables both connect to an Invoice Line Item table from where I get the quantity for each piece of equipment on each rental.


      The problem is that this doesn't take time into account; it's as if every single order that gets entered is for the present.  I want to have this only to show what quantity we'll have on hand during the given rental period.  For example, say someone is renting from Monday to Tuesday, another from Tuesday to Thursday, and we get a request for something from Wednesday to Thursday.  Right now it's counting all those requests against our total stock when I want it to only look at the one from Tuesday to Thursday.  I managed to get close using a really convoluted method that's probably not what I should be doing; I created a portal that filters out only the invoice requests that overlap with the current request to be scheduled.  Inside that filtered portal are the quantities of equipment that go out for each.  I can't find a way to take the sum of only those quantities in the filtered portal and plug that into my calculation that figures out how much stock we'll have on hand.


      Am I even going about this the right way or is there a much more efficient way?


      Any thoughts would be much appreciated.


        • 1. Re: Creating a Rental Inventory

          You will need a table or entry to track all products instock per day. The number available is dependent on the start and stop date. There are a few ways to do this.


          Consider a table with one record per day and fields for the number on hand each day. Once the start and stop dates are selected you can limit the number ordered to the number available. You would need to find the lowest number in stock for the range of dates.


          There are few very experience rental solution people here and they may have better suggestions. Good systems can account for un retruned rentals and other anomolies in the system, such as equipment removed for repair and its next inservice date.

          • 2. Re: Creating a Rental Inventory

            One thing you certainly should do is build separate tables for equipment type (say, iMac computer—one record describing the make/model/etc) and specific individual items (Assets) of that type (say, you have five iMacs—so five records linked to the single Type record).

            You would also need each specific rental agreement to be in another table, and a rental line item table, so that an agreement can be for rent of more than one item at a time. The rental agreement would link to the client. Each rental line item would link to the rental agreement, but also to a specific asset.

            Scheduling could be managed in a further table, as proposed by bigtom.

            • 3. Re: Creating a Rental Inventory

              Rental inventories can have forecasting if you are reserving items for future pickup.  Tracking availability - current or in the future - needs to account for 11 different scenarios (see image).






              The attached file uses a transaction table to determine what is available for a given rental period.


              It may be helpful to take a future order in another table (e.g. a "Sales Order" or "Rental Order").  Also, if you need to pick and pack the items then it may be helpful to track those in separate tables was well.  You could continue to track it in an Invoice table and keep future invoices open.  Lots of options.

              • 4. Re: Creating a Rental Inventory

                Wow, thanks so much for the insights.  I'm going to get to work using those tips.