Hi There,
I have a base that automatically date stamps when something moves through my content funnel. I would like to find a way to automatically count the number of date stamps for each month.
My leads are sorted by status: First Contact, Initial & Follow Up, CAM Meeting and Play Session. When I change a leads status it automatically puts a date into the below fields so I can see how many reached each point, each month.
I would like to have it so another field automatically counts how many date stamps there are in each month. For example, if I had 16 'First Contact leads and thus date stamps (as with the above image) in November, it automatically put it into the below table:
Any help would be appreciated, thanks!
Accepted Solutions
You’re going to need a series of helper fields or a script. The following solution doesn’t use scripts:
- For each date stamp field, you need a companion Formula field to convert the date to your desired format, in this case “MMM-YY” to get “Dec-21”. Each formula would follow this pattern:
IF({Date Field}, DATETIME_FORMAT({Date Field}, "MMM-YY"))
- Now create one Link to Another Record field for each date field all pointing to your Test table.
- Copy the values of each Formula field into the corresponding Link field. This can be made automatic by using an automation, described further below.
- In your Test table you will then be able to use Count fields to identify how many records hit a particular stage each month.
So to recap your first table will look like:
and your second table will look like:
Automation Set Up:
Trigger: When record is updated (select all 4 formula fields as the ones to watch)
Action Steps:
1. Update Record. Update the same record which triggered the automation by copying the values of the Formula fields into the Link fields. That would look like:
Notes: The Automation is designed to result in as few Automation runs as possible. Since you are only reporting by month, watching the Formula fields instead of the Date fields will make sure the Automation won’t refire if a date is changed from 12/1 to 12/2 since that’s still the same month. It also copies all Formula fields into all corresponding Link fields within the same action step so you don’t need to create one Automation per date field.
You’re going to need a series of helper fields or a script. The following solution doesn’t use scripts:
- For each date stamp field, you need a companion Formula field to convert the date to your desired format, in this case “MMM-YY” to get “Dec-21”. Each formula would follow this pattern:
IF({Date Field}, DATETIME_FORMAT({Date Field}, "MMM-YY"))
- Now create one Link to Another Record field for each date field all pointing to your Test table.
- Copy the values of each Formula field into the corresponding Link field. This can be made automatic by using an automation, described further below.
- In your Test table you will then be able to use Count fields to identify how many records hit a particular stage each month.
So to recap your first table will look like:
and your second table will look like:
Automation Set Up:
Trigger: When record is updated (select all 4 formula fields as the ones to watch)
Action Steps:
1. Update Record. Update the same record which triggered the automation by copying the values of the Formula fields into the Link fields. That would look like:
Notes: The Automation is designed to result in as few Automation runs as possible. Since you are only reporting by month, watching the Formula fields instead of the Date fields will make sure the Automation won’t refire if a date is changed from 12/1 to 12/2 since that’s still the same month. It also copies all Formula fields into all corresponding Link fields within the same action step so you don’t need to create one Automation per date field.
