Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 23, 2023 05:45 AM
I'm trying to automate the linking of one record (a website enquiry) to another record (an adviser - in another table) i'm struggling to make sure I only link one enquiry to one adviser.
I have a formula field {Time Since Last Lead} I was hoping to either create a view with just the record with the highest value in this field or just find the record with the highest value in the automation but i'm stuck.
Jun 23, 2023 07:46 AM
Are you trying to link the new enquiry with the advisor who has gone the longest without getting a lead?
---
Here is one no-code option:
Have a control table with a single control record that is linked to all advisors, with a system of back and forth rollups that identifies the record you want. In the control table, have a rollup field that rolls up the maximum value of the {Time Since Last Lead}. Use the formula MAX(values). Then, back in the [Advisors] table, have rollup of the rollup to see the max time since last lead for all advisors, Each advisor record compares its own {Time Since Last Lead} with the overall max time to see if it is the largest. You can then bring the chosen advisor back to the control record using yet a conditional rollup. Finally, use the advisor in the last rollup in your automation.
Note that this system will work best with a small number of advisors (less than ~20), but I highly discourage if it you have a large number of advisors (~over 500). (These number estimates are not based it testing, just a gut feel.)
If two advisors end up with the exact same {Time Since Last Lead}, you can also have a problem because they will advance at the same time. However, creative use of formulas can pick just one of the two.
---
Here is another no-code option.
Have an editable number field representing the queue place. Start by manually entering the queue values. The person with the longest time would be 1, and the first person to be picked. Have a formula field that calculates the "next queue place". For example, if there are 10 advisors: IF( {queue} = 1, 10, {queue) - 1).
When an enquiry is received, do a find records to find the advisor where queue = 1. Link this advisor to your enquiry. Then do a find records action to get all the advisors, and use a repeating group on the found records where you update the editable {queue} with the {next queue place} for each advisor.
This method can also be adapted if an advisor gets a new lead that isn't from the enquiry form. In this case, update the triggering record to be in the last queue place instead of its normal {next queue place}. In the "Find Records" action, instead of finding all advisors, find only advisors where the {queue} is greater than the triggering advisor's {queue}. Update those records with their {next queue place}. (Identifying the proper trigger for this automation may be a bit tricky since you cannot watch the link to the enquiry as a trigger.)
Note that this method will only work with 100 or fewer advisors because the "Find Records" action can return a maximum of 100 records.
---
If you don't want to use helper fields, you will need to use scripting.
Sorry if you this is a bit hard to understand. I am away from a computer.
Jun 23, 2023 07:53 AM
That is what i'm trying to do but some advisers may need to be set as unavailable with a status too. Feels like I do need a script maybe.