Booking items shown as booked to avoid double booking

[Bokning_SoundLe]

We’re building a project management with Airtable and it works really great handling most of our workflow.
A part of it is a rental booking system with an inventory and booking orders on specified items.
I’m having a hard time figuring out how to get the items noted as booked on specific dates so that it shows if you try to book the same item on the same date.
Has anyone experience from the same kind of setup?

[Elias_Gomez_Sai]

What base structure do you have?

[Info_SoundLevel]

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.

[W_Vann_Hall]

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.

[W_Vann_Hall]

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.

Airtable

Duplicate detection demo - Airtable

Explore the "Duplicate detection demo" base on Airtable.

[Bokning_SoundLe]

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

[Bokning_SoundLe]

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.

[Julian_Kirkness]

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.

[Bokning_SoundLe]

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!:

Airtable

Datecheck test base - Airtable

Explore the "Datecheck test base" base on Airtable.

[W_Vann_Hall]

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.