I’m trying to set up a hire system that tracks when an item is on loan or when it’s available. Is there a way to restrict selection of an item if it is ‘on loan’ for that date?
Hire/lease products
+21Welcome to the community, Jes! :grinning_face_with_big_eyes: What exactly do you mean by “that date”? Do you mean the same date that you’re checking for the item’s availability, or some future date? If it’s the same date—you want to see if it’s available to loan out now—that’s fairly easily done (more below). Limiting its selection for a future date is a bit trickier. The setup for such a system isn’t coming readily to mind, so I can’t say for sure whether or not it can be done. If anyone knows of a way, chime in!
For an instant check, here’s how to set it up. Here’s a sample table I made for tracking loaned items:
Here’s the formula for the {Available} field:
IF({Checked Out}, IF({Checked In}, "✅", "❌"))
The {Item} field links to an [Items] table, and is set to only select from a view of items that are marked as available.
That availability check begins when the various {Available} status emojis are rolled up for each item in {Status Rollup}, using the aggregation formula: ARRAYJOIN(values, ""). The {Available?} formula field then determine’s the item’s availability to loan by searching for any :x: ’s in that rollup:
IF(FIND("❌", {Status Rollup}), "❌", "✅")
This field is then used to drive a filter in an “Available Items” view, which in turn controls the item selection limitation in [Loans]. As you can see, I can pick either Book 2 or Book 3 in a new loan record, but not Book 1 because it’s currently out.
Welcome to the community, Jes! :grinning_face_with_big_eyes: What exactly do you mean by “that date”? Do you mean the same date that you’re checking for the item’s availability, or some future date? If it’s the same date—you want to see if it’s available to loan out now—that’s fairly easily done (more below). Limiting its selection for a future date is a bit trickier. The setup for such a system isn’t coming readily to mind, so I can’t say for sure whether or not it can be done. If anyone knows of a way, chime in!
For an instant check, here’s how to set it up. Here’s a sample table I made for tracking loaned items:
Here’s the formula for the {Available} field:
IF({Checked Out}, IF({Checked In}, "✅", "❌"))
The {Item} field links to an [Items] table, and is set to only select from a view of items that are marked as available.
That availability check begins when the various {Available} status emojis are rolled up for each item in {Status Rollup}, using the aggregation formula: ARRAYJOIN(values, ""). The {Available?} formula field then determine’s the item’s availability to loan by searching for any :x: ’s in that rollup:
IF(FIND("❌", {Status Rollup}), "❌", "✅")
This field is then used to drive a filter in an “Available Items” view, which in turn controls the item selection limitation in [Loans]. As you can see, I can pick either Book 2 or Book 3 in a new loan record, but not Book 1 because it’s currently out.
Hi Jason,
Thanks so much! This is super helpful. I’m hoping to also be able to do it for a future date. So that when a product is getting hired out for the future my staff can know whether it’s available for the dates. So someone needs to hire out a particular type of product “A” for the dates of 15/9 - 30/9 I’m hoping I can make it that when they go to hire “A1” for those future dates they will get a warning if it’s already booked for those dates. If that makes sense?
+21Hi Jason,
Thanks so much! This is super helpful. I’m hoping to also be able to do it for a future date. So that when a product is getting hired out for the future my staff can know whether it’s available for the dates. So someone needs to hire out a particular type of product “A” for the dates of 15/9 - 30/9 I’m hoping I can make it that when they go to hire “A1” for those future dates they will get a warning if it’s already booked for those dates. If that makes sense?
Yes, that makes sense. And I’ve got most of the setup worked out in a test base, though I don’t have time to wrap it up tonight. I’ll try to get it posted tomorrow, most likely as a base that you can download and play with to see how it works from the inside.
+21That’s amazing thank you so much!
Here’s a base to play with:
As you add new rental records to this base, any past rentals of the same item will compare themselves to this newest record, and any conflicting rentals will be marked in the “Conflict” field. Note that when checking for conflicts, this system allows an item to be returned and re-rented the same day. If same-day turnaround isn’t possible with your system, you’ll need to modify the logic in the {Conflict Marker} formula field to account for this.
The key to this setup is that each record only compares itself with the most recently-added record for a given item. So if you happen to change the rental dates of older records, they will still only compare themselves against that same item’s most recently-added record, not against every record in the base. Airtable currently doesn’t offer a way to iterate through an arbitrarily-sized collection of things, so comparing every record against every other record isn’t possible.
Hi. I’m also looking for a table to control rental inventory. I downloaded your template and it was the best I’ve found so far.
However, it can rent the same equipment to more than 1 client at the same time.
This was intriguing and I could not fix this. I’ve already asked airtable to publish a complete template like the excel below.
Excel Inventory Template for Rental Business - Track Rental Inventory
Free Rental Inventory Management. Track rental assets or rental equipment with free Excel template. Calculates stock on hand & available future inventory.
I just wanted this table on Google spreadsheet or airtable but I simply didn’t find and I don’t have knowledge to build one.
Thank you
+5Here’s a base to play with:
As you add new rental records to this base, any past rentals of the same item will compare themselves to this newest record, and any conflicting rentals will be marked in the “Conflict” field. Note that when checking for conflicts, this system allows an item to be returned and re-rented the same day. If same-day turnaround isn’t possible with your system, you’ll need to modify the logic in the {Conflict Marker} formula field to account for this.
The key to this setup is that each record only compares itself with the most recently-added record for a given item. So if you happen to change the rental dates of older records, they will still only compare themselves against that same item’s most recently-added record, not against every record in the base. Airtable currently doesn’t offer a way to iterate through an arbitrarily-sized collection of things, so comparing every record against every other record isn’t possible.
Hello! Im looking at your Rentals w/conflict test airtable base and considering using it for my organizations simple equipment check out system… We basically have a handful of cameras and video eq we would like to check out to our youth participants… Im still learning airtable and wondering if there is a way to modify your base to check for conflict on rental records with multiple items vs. a single item?
I’m looking for a way to create future reservations with a handful of items each and for them not to be double booked. Does that make sense? any help is greatly appreciated! thanks!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.