Show available inventory items within selected timeframe

As a database user, you pretty often need to query inventory items that are available during a given timeframe. For example, you created a database to handle helicopters bookings. Each helicopter may be considered an inventory item that can be hired by a customer for a certain period of time. Ideally, when a new booking is made, only available helicopters should be brought to you by the system, but due to TeamDesk limitation there is no way to pass booking dates to a record picker for displaying available helicopters only. As a workaround, you may create a special dashboard that will display available helicopters for a given timeframe and use this dashboard for creating new booking records.

Please follow these steps to implement the process:

Step 1: (for example, there are “Bookings” and “Inventory Items” tables in your database)

– Create a User Property Table. It allows keeping the last timeframe entered by a user.

– Add the Start Date&Time and End Date&Time columns to this table to store a particular timeframe.

– Add users to the User Property Table. If a user is not added there, he/she won’t be able to change the date range.

Step 2: Create a “Filter” table view in the User Property Table.

Set “User is the Current user” criteria in the “Filter” section and include the Start Date and End Date columns only.

Step 3: Create a “Change Filter” custom button.

“Change Filter” button settings are displayed on the screenshot.

Step 4: Create a new “Number of bookings” summary column in the “Inventory Items” table and set the following formula in the Filter section:

[Start Date&Time]<=User[End Date&Time] and [End Date&Time]>=User[Start Date&Time]

and use “Aggregate: # of records” function.

If the “Number of bookings” summary column brings 0 value, then a helicopter is available.

Step 5: Create “Available Items” table view in the Inventory Items table.

This view should use the following condition in the “Filter” section:

Number of Bookings is 0

Step 6: Customize the Dashboard for “Bookings” table and place the “Filter” view and “Available Items” view on this dashboard.

You can check the above-mentioned adjustment in the Example: Time Dependent Inventory database.