Count Unique check-in dates

Topic Labels: Formulas
1247 1
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

Hi all,

I’m using Airtable as a reservations automation platform for my short-term rentals.
In 1 table (Bookings) i have all the bookings with their relevant information (check-in, check-out, # of guests, …etc). This table is linked to a second table called “Guests”, which summarises everything for this guest, including the booking this guest has made, unique phone numbers, unique emails, how much they’ve spent so far…etc

In the “Guests” table, i am trying to create a field which counts the number of unique reservations (based on check-in date), this guest has made.

I currently have the following set up in the “Guests” table:
Screen Shot 2019-07-27 at 11.24.34 copy.png

Right now, the formula in “Returning guest” is showing “Returning” if the “# of reservations” field is larger than 1. This is not completely accurate because a guest can have multiple reservations on the same day but for multiple properties, which does not make him a Returning guest.

Ideally i would like to have the formula compare the number of dates in “All check-ins”, and “unique check-ins”, and if they are not the same, then i can show the guest as a “Returning Guest”.

The simple solution here would be to have a Count Unique formula, however as this does not exist, i’m trying to find another way to do this.

Would appreciate any and all help!

1 Reply 1

I suggest using two rollup fields, one replacing your current {all check-ins} lookup field. One rollup will use values as the aggregation formula, which would simply collect all dates. The other would use ARRAYUNIQUE(values), which would remove matching dates, leaving only those that are unique. Then you could use a formula to compare the count between the two rollups.