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.
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.
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.
Rental Tracking.fmp12.zip 70.5 K
Wow, thanks so much for the insights. I'm going to get to work using those tips.