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.