Automation that finds similar records and updates based on maximum value across those records

Topic Labels: Automations
480 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Thanks in advance. I'd like to:

Upon creation of a new record, find all other records in the table (or a view) that also share the same value with the new record in two linked fields, look at the new record and all the similar records and identify which one has a maximum value in a specific field, update a field in all the records that are not the maximum record.

An example (ignore the real life use case), I'm recording phone calls between sales people and companies. I have a table for each of sales people, companies, and phone calls. Sales people create a record to log their phone calls, select the company they called, and indicate how many minutes they were on the call. They create multiple call records with multiple companies in a month, sometimes the same company multiple times. When a new call record is created, I want an automation to update a field by adding a simple "yes" value for all the call records that meet the criteria of being 1) the same sales person in the new call record, and 2) the same company in the new call record, and 3) the same month as the new call record, and (this is the part I can't figure out)... 4) are NOT the record with the maximum call length across all of the records that meet conditions 1-3. In short, I want to be able to have a maximum field in the call table that simply includes a "yes" for the record with the longest call length between each sales person and company I'm a given month. The purpose is to be able to identify only these longest calls at any given time. 

The conditional logic action function on automations doesn't seem to allow for a formula, only a specific value to be indicated. Is there a way to do this with automations? Or another solution people use to accomplish something similar?

Thanks for the insight and consideration.






1 Reply 1

Hmm, a possible workaround could involve a new table where each record represents one set of condition 1-3

So if Bob contacts Company A 5 times this month and 3 times next month, you'd have two records in this new table representing this

So each call record would be linked to the appropriate record in this new table, and you could use a rollup field with MAX(values) to get the longest call length that means conditions 1-3, does that make sense?

To automate this, you could have an automation that would trigger whenever a new call record gets created and link it to the appropriate record automatically for you