Using Count to Calculate # of transactions

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

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.

1 Like

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

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.

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?

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.

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

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

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

1 Like

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

1 Like

I understand thanks.

This is the contacts Table

This is the reservations Table

there are a lot of columns … would you like me to try to get them all?

I can’t change the {guestEmail}, cause it’s coming from the API … and it won’t populate automaticlly if I change it

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

1 Like

OMG!!! It worked! You are a God!

Steps:

  1. Create new column (forumla: guestEmail) which just copies that column in to this one
  2. I hid the first guestEmail (cause I know I’ll forget about the automation with the API one day, and edit it
  3. I already had a column that linked to the guest email in the contacts table, so I just copied the entire column and pasted it into the new column

Thanks for your help.

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.