Counting how many times a value appears in a table

[Jeffrey_Porter]

Background: I have a table that includes a list of activities each customer has done. So there unique value is a random “Event ID”, another column is “Customer ID” and a third column is called “Activity Type.”

I need to find a way to count how many times a Customer has done a certain Activity Type and then be able to use that formula to create a notification when a customer has their FIRST activity (first appears in the table.) The table is populated via API, so it’s not as simple as just knowing I did it. I found a Pivot table that can just give me the counts of Activity Type by Customer, but you can’t act on any of the information in that table. So essentially I need a way to find any time a new record is created that contains a unique Customer ID that does not exist yet in the table.

TL,DR: I need a way to answer the question “Tell me when a new client has their first activity”.

[Dimitris_Goudis]

Hey @Jeffrey_Porter,

welcome to Airtable community.

To do those calculations I recommend to change the structure of your base like this:

1. First of all create a unique table with client information
2. Create a separated table for activity information
3. Link each other with a link to field

→ To answer the question “How many times customer done the activity X” you need to add rollup field in customers table. In this rollup you will set a condition to of activity is the activity X you want to present. Then aggregate the with: COUNTALL(values)
→ To answer the question “Tell me when a new client has their first activity” you need

1. one new field in client table that will return the date and time when the client added the specific activity, here is the formula:

IF(REGEX_MATCH({activities},"activity X")=1,LAST_MODIFIED_TIME(activities),BLANK())

{activities} is the link to field between activities and customers tables

2. You will need an automation that will trigger when the activities field last is updated and it will copy the values from the formula you created and it will add in a simple date field. This field will have the response you are looking for

Of course we could recommend an other and more efficient way to run those calculations and of course a better base structure but this is a simple fast solution I could recommend.

For any further question please feel free to text back.

Yours sincerely,
Dimitris Goudis

[Jeffrey_Porter]

Thank you for getting back to me. Unfortunately, I’m unable to rebuild our current table / infrastructure. I do have a separate table of all customers, so I’m able to have a table of “All Activities” and a separate one of “All Customers” but we can’t manually link them - we would need that to happen automatically. When the “Activities” table is automatically populated, it does come in with both Activity ID and Customer ID, so if there’s a way to match Customer ID from that table and Customer ID from the customer table, and automatically link, this could work. thoughts?

[Ben_Young1]

It is totally possible to achieve that via simple automation.

Here’s the logic flow (as I imagine it).

1. Create an automation for when a new record is created in Activities.
2. When that record is created, perform a search of the Customers table.
3. Search the customers table using the Customer ID from the new record that was created in Activities.
4. Create an action to update the new record
5. Update the linked record field with the record ID of the record found in the search.

---

There are some things to keep in mind with this configuration.

Firstly, this relys on there always being an associated customer in the table that will match the customer ID from the new activity record.

Second, you need to create a backstop that will account for situations where the customer either (1) does not exist, or (2) dirty data entry means that customer IDs are incorrectly entered and cause a break in the link.

---

My recommendation is to create a conditional automation that says that if the search for a customer returns false, then fire off an email or Slack message to let you know that a new activity record was created, but did not return a customer search result based on the customer ID.

This will allow you to easily troubleshoot when it does occur.