Tips For Creating a DB To Handle Rental Inventory

[David Rosen]

For years I have been trying to create a FileMaker database that could handle rental inventory. Specifically, I'm looking to handle orders for a special event lighting company that rents lighting equipment, and then checks the equipment back in.

A user would input a Rental Begin Date and a Rental End Date. They would proceed to enter line items (lighting equipment) to fulfill the order. A user would need to have a "Conflicts" button that could run a script that would check for any potential conflicts with the order (e.g. does another sales rep have any of the same equipment already reserved or out on another order) Conflicts could occur via Rental Begin Dates OR Rental End Dates OR if a rental order is returned with items missing.

Any suggestions for the structure of how such a database would be set up? How could FM search for all orders that are "Confirmed/Reserved" AND "Out" AND "Missing/Damaged" for any date range in the future?

BTW, my programming skills in FM are limited. Extra explanation will be greatly appreciated!!!

Thanks in advance!

David

[dbruggmann]

Hello David

Unfortunately I don't have a solution out of the box. Just two suggestions where to look for it:

1) The problem you are confronting seems to me to be quite similar to the ones with a scheduling system. So I would take a look at the various free samples that exist

[Ender]

Scheduling systems can be pretty complex. The relationships with a date range are just part of the challenge. Scripting can also be pretty extensive.

Below is an ER Diagram showing a proposed structure for your system. There may be other entities and relationships needed, but hopefully it will give you a starting point. The idea is that when you are building an order, you would enter the date range and select the type of equipment you need, then perform a scripted find in Equipment-Date for equipment of the type specified for the date range, omitting any that are already used in another order (Not sure if this could be done through a relationship or not.) You can then grab the Equipment ID from any found Equipment-Date records, and use that for the order.

When a line item is added to an order, there will need to be some scripting to update the Equipment-Date records for that date range.

Hope that helps.