Re: Dynamic Count based on Date in linked field

893 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi there. 

I have a table that has all of our customers and their start date. When they register to an event, it goes into a different table and these are linked to the customers table. I can count how many events they have registered to without a problem, however i'm finding an issue as:

I want to calculate how many events they have registered to after their start date (note they can register to events before they start), and I can't seem to find a workaround. 


2 Replies 2

Hm, I would recommend an automation that runs a script whenever a customer registers for an event, and the script would get this number for you and update the record as needed

The alternative would be the creation of a single record in a helper table, linking all of the `Customer` and `Event` records to it, and using an automation to make the proper links for you to get the number, then clearing the links.  Doable, but very fragile and prone to error

6 - Interface Innovator
6 - Interface Innovator

I have an idea that doesn't require an automation. 

In the registrations table, you'll need to add 2 fields: 

  1. A lookup field that points to the customer's start date
  2. A formula field that computes if the order date/registration date is on or after the Customer's start date. (for example: the following formula: {Order Date} >= {Start Date (from Customer's table)} will return 1 if the order date is actually after the start date.

After those two fields are added, you can add a rollup field to the Customer's table that just Sums up the values in the formula field from step 2. 

Screen Shot 2022-12-07 at 2.39.39 PM.png