Finding next booking after this booking, for the same property

[Karl_at_Easy_La]

I'm trying to migrate some data I have in Google Sheets, to Airtable.

I have a table, which is full of reservations/bookings. Each booking has a checkin and checkout date, and is allocated to a property. A booking also has a Status.

I would like for each row (booking) to also have a field, Next Booking Reference. In Google Sheets, I had a formula which could find the Booking Reference, for the next booking after this one. I've created this shared view to serve as an example:

https://airtable.com/appV8ivwvEiMnsmNf/shrUwgmVMY4O41Ki0

In this example, Booking 1 (Karl), the Next Booking Reference would be Booking 9 (George).  

The conditions for this are:

• Booking has the closest greater checkin date to current record's checkin.
• Booking is not cancelled OR black
• Booking has the same property as current record.

Is anything like this possible with Airtable?

Regards,

Karl 

[pressGO_design]

You can use calendar or grid views that filter for property name and check-in date is on or after today.

Wondering why the properties aren’t linked records? I mean, there might be a reason why they’re not, but you can do more with linked records than with single-select fields. 

[Karl_at_Easy_La]

Im not looking get this information visually, but rather have this set at a record level, in a field - and automatically not via a manual process, where you lookup this information and then fill in the field..

My ideal scenario would be that Bookings table would have a list of Bookings. Each booking has an ID, which is the booking reference (primary key).  Then, each booking also has the 'NextBookingID'.  This would be set to the booking reference of the next booking. This NextBookingID would be a linked relationship to Bookings.ID, however the issue is filling this NextBookingID. My scenario is how to have a way in airtable, when I can easily find and set the NextBookingID, ideally automatically. You can assume an ideal database schema, as my intention is to model my Google Sheets into Airtable.  Again, similar to my other question - I would like to avoid custom Javascript coding as much as possible, to keep it simple.

In google sheets, you can do this using Formulas, filters, and Array Formulas to find and fill in the value of a cell with the Booking Reference of the booking which comes right after.  

[pressGO_design]

I understand. I am not sure that you are going to be able to bring in the nextBookingID into the Booking record without using a script to loop through all the records in the Booking table.

What you can do in Airtable is house the nextBookingID data in the Property table and then use Automations to update the information as new Bookings come in or as the status of existing Bookings change.

In this demo base  I am using a formula to calculate the number of days until checkin for each Booking. I then rollup that value into the Properties table using the MIN() function and filter for <0, notCancelled, notBlack.

When the nextCheckin field changes in the Properties table, there is an Automation that finds the Booking record in the Booking table and updates the nextBookingID to reflect that. In the video attached, I created a Test Booking that precedes Sarah's reservation at Cikku's Motel. When I change the status of that Test Booking the nextBookingID updates in the Properties table.

[Karl_at_Easy_La]

Thanks for the detailed explanation and video - appreciated. The video is without sound/voice, not sure if it is like that, or something is wrong from the video or my side.

Your solution would work to find the next upcoming booking, for a property. In my case what I am looking for, is to find which is the booking upcoming, after a certain booking. A booking has a link to a property, but does not have a link to the next booking automatically.  Similar to my other post about bank transactions, I assume this has to be something which can only be done with some custom script, based on some trigger.  I was hoping like in Google Sheets, it can be done with some advanced formulas / filters, without any actual javascript code - but it does not seem to be the case.  

Thanks a lot for help, much appreciated!