Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 17, 2021 12:43 PM
I have a small hotel, and I wanted to calculate the number of stays each guest had with us.
Is it possible that I can create a record like this and have the guest email and name as a variable for each column (see attached photo)?
Solved! Go to Solution.
Apr 18, 2021 02:12 PM
In that case you’d leave {guestEmail}
alone and set up an Automation to copy the value from {guestEmail}
into your existing Link To Another Record
-type field which I believe would be {Contacts}
.
This will link all future Reservations records for you. For the existing ones, you could simply copy the entire {guestEmail}
column and paste it into the {Contacts}
.
Apr 17, 2021 12:52 PM
Based on your screenshot, it looks like we’re seeing a table for [Guests]
, where each Guest
is linked to one/multiple records in a [Stays]
table. You are editing a Count
-type field, which automatically only gives you the number of [Stays]
records that are linked to each Guest
.
Unless your {Stays}
column is literally empty, it should already be counting the number of stays per guest.
If your {Stays}
column is empty, it means you haven’t actually linked any Guests
to Stays
, meaning your {Count}
field isn’t going to work.
Apr 18, 2021 09:33 AM
Yeah … stays is empty. How do I link Guests to Stays?
Apr 18, 2021 09:46 AM
When you click into the {Stays}
column there’s a plus symbol, click it and pick the appropriate [Stays]
record. Or do the same thing from the [Stays]
table.
Records in Airtable do not automatically link themselves together. You have to indicate which Guest
should be attached to a Stay
.
You likely have been entering people’s names in a regular text field in the [Stays]
table instead of filling out the Link to Another Record field.
Apr 18, 2021 09:48 AM
Wait … I have to do this manually? That’s not gonna happen … there’s WAYYYY too many records for that. Isn’t there a script or automation that can do this?
Apr 18, 2021 09:56 AM
Yes, probably. However, if you post more screenshots of your base setup, I can tell you whether or not you need a script to fix all your existing records.
It is possible that you just need to convert an existing field you have into a Link to Another Record
-type field, but this is only the case if you have a column in [Stays]
that matches the primary column of [Guests]
. For instance, if the first column of [Guests]
is their email address, and you have a column in the [Stays]
table that gives the guest’s email, then convert the [Stays]
{Email}
column into a Link to Another Record
-type field.
Apr 18, 2021 10:03 AM
I’m hestitant to share screenshots, honestly. Cause my guests info are in there. Can I DM you a link to the base?
Apr 18, 2021 10:04 AM
Also … I’m subscribing to your podcast right now!
Apr 18, 2021 10:06 AM
You can crop to just show the column headers. Your question is about Airtable fundamentals and I believe others would benefit from seeing how to correct this issue. If you really need to DM you can, but I will be answering the question here.
(Also thanks for the subscription!)
Apr 18, 2021 10:20 AM
In your [Reservations]
table you have a Long text
-type field for {guestEmail}
, and the primary field in your [Contacts]
table is their email address. So right-click {guestEmail}
, chose Customize field type
, then choose Link to Another Record
.
Adjust your {Count}
field to pull from that^ field and not your blank one. Then go ahead and delete your blank {Stays}
column, and from now on, actually link your records together by filling in {guestEmail}
.