Using Count to Calculate # of transactions

[Lost-Merovingia]

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)?

[Kamille_Parks]

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.

[Lost-Merovingia]

Yeah … stays is empty. How do I link Guests to Stays?

[Kamille_Parks]

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.

[Lost-Merovingia]

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?

[Kamille_Parks]

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.

[Lost-Merovingia]

I’m hestitant to share screenshots, honestly. Cause my guests info are in there. Can I DM you a link to the base?

[Lost-Merovingia]

Also … I’m subscribing to your podcast right now!

[Kamille_Parks]

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

[Kamille_Parks]

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}.