Hire/lease products

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?

Welcome to the community, Jes! :smiley: 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.

29%20PM

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.

27%20PM

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?

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.

That’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.