EQ reservation system with Airtable?

[OTF_Admin]

Hello Airtable Community! I have a question about creating an EQ reservation/checkout system in airtable. My nonprofit already uses airtable for keeping track of our film production equipment but lately we have started checking out filmmaking equipment to our youth participants. I was able to look at various templates and create a rudimentary checkout-checkin system in our eq base, but as of right now it doesn’t allow us to create EQ reservations for future dates.

I would like to be able to make EQ reservations (with multiple EQ items) for the future and see what EQ is “available” during a specific date range. So I would need these future reservations to be able to look and see what else is reserved or checked out in any given date range…

Im looking for ideas on how this could be possible, I have done a bit of research but haven’t found examples of what I am trying to do. Any help is greatly appreciated! thank you very much!

here is a link to a simplified test base version of our EQ base as an example for how we have it set up:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[Kamille_Parks]

I’ve written two scripts which may be of use to you. They both let people reserve something for a period of time, and only letting people reserve that something when it is available.

The first one:

Asset Reservations: No Double-Booking Scripting app

Use Case: You have a table of assets (library books, conference rooms, etc.) that you want people to reserve, but don’t want people to be able to reserve an individual asset at the same time it has already been reserved. Solution: No-Conflict Asset Reservations script by Kamille. This script allows you to schedule a new asset reservation in a form-like interface by setting a date range, then selecting from a list of assets which are un-reserved for that time period. [Screen Shot 2020-03-20 at…

I believe this should do exactly what you need: Ask who is making the reservation, ask for the date range of the reservation, allow someone to pick from a list of equipment only if the equipment hasn’t already been reserved for that date period.

My other script:

No-Conflict Appointment Scheduler Scripting app

Use Case: You have a table of stylists, therapists, technicians, etc. (“agents”) who perform any number of services, and customers are supposed to schedule an appointment with an agent to perform a service but only when that agent is available. While you think the No-Conflict Asset Reservations script is great, it doesn’t take into account your need to schedule an agent for more than one appointment per day. Solution: No-Conflict Appointment script by Kamille. An improvement upon my previous…

It more or less does the same thing, but is framed more as an “appointment scheduler” where people are reserving something for a period of hours and not a period of days. It is structured in a way that gives it a couple features/steps I don’t think you need or want, but I’m linking it here just in case there’s something in it you want incorporated that the first script doesn’t have.

[OTF_Admin]

Thank you so much for your response @Kamille_Parks! I actually saw your script during my research and considered using it. The problem that i couldn’t figure out was how to reserve multiple items with out having to complete the script for each one individually. rather than reserving a single room or book or appointment, i am needing to reserve sometimes 5-10 pieces of equipment for each youth filmmaker for a given timeframe/reservation. Like Camera, lens, audio recorder, tripod, lights, etc. Do you have any ideas about that? Thanks!

[Kamille_Parks]

Right now the “select a record” inputs available to the Scripting Block and the Custom Blocks are only “single select”. I do have an idea though:

There could be an input field where the user is asked “how many assets are being reserved?” and then the script could create a loop showing a record-select input and have it run that number of times.

Or you could “hardcode” a maximum amount of items someone could reserve at a time. If you say the limit is 10, well the loop could run no more than 10 times, and on the 10th time tell the user “You’ve reached the limit”.

Lastly, just so you’re aware, with using scripts, they can’t really be shared by people who aren’t collaborators in that base. Until runable scripts can be publicly shared, either you or someone else on your team would have to run the script.

[OTF_Admin]

Hmmm Ok i’ll try to mess around with that and see what happens. thanks for the ideas!

[Kamille_Parks]

Both suggestions would require replacement of let selectedAsset = ...

If I were to write it, I would replace that variable with an empty array like so let selctedAssets = []. Personally, I’d go with the second option (set a maximum number of assets which can be reserved). Following that empty array would be a loop, something like:

for (var i = 0; i < maxAssets; i++) { 
    let asset = await input.recordAsync("Requested " + BaseSpecificNames.assetField + ":", availableAssets);
    selectedAssets.push({id: asset.id})
}

Then of course in the await reservationsTable.createRecordAsync() replace [{id: selectedAsset.id}] with selectedAssets.

This isn’t perfect. Ideally, with each run of the loop availableAssets would re-filter to exclude the asset a person just clicked so people aren’t double-booking within the script itself. There’s some more error handling that would need to be incorporated with this new set up. I may tinker with this later today if I have the time.

[OTF_Admin]

OK thanks for the details, let me know if you tinker with it again and learn anything new : )

I am also wondering if there is a way to do this without scripts?

[Aron]

Hi OTF!

Aron from Airtable here. I quickly perused the base (seemed great!). The only thing that caught my eye was the “serial number” – you can use the barcode field to make it easier to find a piece of equipment (especially on mobile) by scanning the barcode.

I showed off what the barcode field can do in a recent webinar (recording here - the part on barcode is around 25 minutes in).

Hope that helps!

Best
Aron

Want to learn Airtable? Join me for a webinar at airtable.com/webinar

[OTF_Admin]

Hey Aron! Thanks for your response :slightly_smiling_face: I love your idea about scanning barcodes however we aren’t set up to do that yet, we don’t have barcodes on all our items… One day that would be great!

My coworker said that she attended one of your webtrainings this week and said you were super informative and helpful! I’m wondering if you have any ideas for how to take the base I have set up and include the ability to make future reservations for equipment. right now ‘checkouts’ only works if you are checking out eq in the moment… I would like the ability to schedule to check out eq in the future as well. Any ideas or help is greatly appreciated! thanks!

[Kamille_Parks]

Well I figured out how to reserve more than one asset, without forcing a maximum asset limit (you know, other than the number of records available).

The approach is different than what I described above. Basically I have a variable shouldContinue set to true and a loop running until that value is changed to false. Each time the loop run it has a new record picker, where the selected record is added to the selectedAssets array and removed from the unselectedAssets array (that way you can’t reserve the same record twice during this process). Then there are two buttons asking whether the loop should run again (select another asset) or should it end. If you click the “done” button shouldContinue is set to false and the process ends.

Then, like in the original script, it asks for confirmation that all the details are correct before adding the new reservation, now with multiple assets assigned to it.

Some other bits needed adjustment, like how unavailebleAssets and availableAssets are calculated as well.

Full code:

github.com

thekamillionaire/airtableScripts/blob/master/No-Conflict Asset Reservations (Multi-asset Edition)

/*
Script: No-Conflict Asset Reservations (Reserve Multiple at a Time!)
Author: Kamille Parks

This script allows you to reserve multiple assets for a specified time period, but only between dates when 
there are no existing reservations for that asset. Follow the prompts to select a time period, and then selct 
from a auto-filtered list of the assets available for that time period. You'll never have to worry about 
double-booking an asset again!

Note: There is another version of the script which allows someone to only reserve one asset at a time available.

*/

// Begin BASE SPECIFIC NAMES Section (adjust these values to fit your base!)

const BaseSpecificNames = {
    // Reservations Table
    reservationsTable: "Reservations", // name of the [RESERVATIONS] table
    assetField: "Room", // name of the link-type field connecting to the [ASSETS] table
    startField: "Start Date",

This file has been truncated. show original