Help

Dynamic Count based on Date in linked field

1276 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Penny_Penati
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

autumn
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