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.