Booking items shown as booked to avoid double booking

What base structure do you have?

It’s one table with the projects that links to another with the orders.
From those orders the items in the invetory table are chosen through a link.
Everything gets the dates from the projects dates.

It’s a basic setup, but the trick is to connect the amount of items in the inventory table to the amount booked in the order table on the specific dates.

Airtable doesn’t offer much in the way of data validation or process interruption, so I don’t think you could get it actually to block the second booking. What you can do, however, is build error checking routines into the base and have them ‘alert’ — typically by displaying an error message flanked by eye-catching emoji, although the new conditional color tagging can be used as well — if an error condition is encountered.

For instance, in this case one approach might be whenever you book a rental, a field in the table reflects the booked date. Through a linked field, elsewhere in your base you have a table where all booked dates are rolled up into one long string using ARRAYJOIN(); a second rollup exists as well, using ARRAYUNIQUE().* In this error-checking table, you have a formula field with the formula LEN(JoinArray)!=LEN(UniqueArray). Finally, that error field is returned to the order table through a lookup field.

Now, when a duplicate booking is made, LEN(UniqueArray)<LEN(JoinArray) (because ARRAYUNIQUE() removes the duplicate date. In your order table, you have a {Warning} field with the formula

IF({DupeError},'🔥🔥🔥🔥 Duplicate Booking! 🔥🔥🔥🔥',BLANK())

This will at least flag the problem booking. (In this instance, all records will show the error, so the assumption is whatever record was being modified when the error appeared needs correcting.)

You can see examples of such an error warning system in my Wardrobe Manager base in Airtable Universe. Since we’re looking for duplicate date entries, you can find example code in the demo bases I wrote illustrating row duplication detection — although I suspect the system I outlined here is superior to the one I initially implemented, so I may revise the latter bases shortly.

I know my description is kind of breathless and undoubtedly hard to follow, but I wasn’t certain enough of your design to try to slap together some example code, not wanting to be even more confusing.
. __________

• You may have to play around some with the separator value in ARRAYJOIN() — by default it’s a comma without spaces on either side — to get it to match ARRAYUNIQUE()'s separator, which I think is a comma followed by a space.

Actually, the algorithm in the Duplicate Detection Demo base is largely the one I described; I guess I already had that brainstorm once and modified the base. In fact, the one in the base is an improvement over the one I described in my reply in that it merely compares counts of all entries and all unique entries, thereby bypassing some of the concerns I had about parsing and punctuation of comparison strings.

Thanks for all your input!
I’ll have a look at the algorithm in the demo!

So, I ran in to the next problem…
Some projects run over a time period of several days. They are booked with a start date and an end date.
That means that the dates can be different and still double booked…
One way to get around it is we somehow can generate all the dates in a time period.

There is also the problem that we have a couple of hundred items which vary from one to hundreds of pieces each…

Our order lines are already up to a hundred or so, and we’re just trying everything out still…
The data amount gets massive after a while so it’s not really an option with a solution that requires manual input.

I’m thinking that there might be another way instead…
Have been trying different things but I only get jammed…

I’m trying to find a way to keep track of how many items we have in stock at a specific date.
If we can get that number to change at every start and stop date of an order then it would be easy to set a notification if that gets below 0.

Hi

What I’m going to say here may not be very helpful - but…

I’m not really sure that this is a very Airtable friendly use-case. Considerable logic will be required to determine, on data entry, whether any of the days of the new booking period would cause an item to be overbooked. I haven’t actually built a booking system myself yet but I would imagine this requires a record for each day of a booking for each item so that totals for each date can be compare to stock - and these dated records would need to be generated by the database automatically.

When you try to make a new booking this has to validate against existing bookings in real time and only allow the booking to be made if there is product available.

I’m sure some of this could be achieved through custom code in the API and/or tools like Zapier but I can’t see how suitable validation could be done real time.

Thanks for the reply!

Yes, you are probably right.
The thing is that for the rest of the booking process and the planning around everything Airtable has proven to be very useful and suits us very well.
That’s why I’m really trying to find some way to solve this :slightly_smiling_face:

It doesn’t necessary have to block double booking being done. A warning of the kind that @W_Vann_Hall proposed is perfectly fine.
I’m experimenting a bit with a simplified version. Feel free to check it in and join the discussion!:

While I don’t think it’s entirely applicable — I fudge a bit by declaring, by fiat, a garment can only have a single active ‘out’ (essentially, a trouble ticket) opened at any one time — you might want to look at the [out] table and associated documentation* in Wardrobe Manager, referenced above. Before I decided to take the easy way out, I started work on a system that would check for blocking against multi-day assignments; if any of that code exists in earlier versions or my notes, I’ll make it available to you.
. __________

• There’s a 40-page Users Guide included as an attachment in the [documentation] table.

Thanks for your help!

I’m still not there yet…

The latest thoughts are that it would be great to automatically create a table with dates as a primary field.
On these dates are noted where a specific number of items are booked.
When a new booking is made the numbers are added to that table and a warning goes out when that number exceeds what we have in stock.

Does someone have an idea how to do this?

hi! as this is about 4 month old i wanted to ask if you made any progress? because we are building a pretty similar system to track our video equipment as well … and im stmbling over a lot of things … starting with how to create a booking in the first place. i mean with a simple interface and not with a lot of manual data entry and stuff …
maybe you can lighten my confusion, if this is possible at all … or if this would have to be handled by some external application that uses airtable in the background? or if airtable is the wrong approach in the first place

thanks, cheers, arne

Take a look at the solution proposed in this reply and the base it references. It should give you the framework for a booking system you can modify for your own needs.

That said, I’m not sure exactly what you mean by “not with a lot of manual data entry and stuff…” If you’re referring to information pertaining to the video equipment itself, that would ordinarily reside in the [Equipment] table (or whatever you call it), where it would either have to be manually entered once or imported from an existing system. If you mean information on the customer/client/member booking the equipment, the answer is the same, except the data would be stored in the [customer/client/member] table, depending what sort of organization you describe. If you mean information about the booking itself, the base described in that link officially requires only two additional pieces of data to create a booking: the {StartDate} and the {EndDate}; everything else is optional.

I’d be happy to discuss how best to grow the example base into a full-fledged application; if you’re interested, PM me (click on my name and then select ‘message’).