Help with linked fields and automating to show top clients per employee

[Caitlyn10]

Hi everyone, I need to create a single field that automatically shows the top 3 clients for each employee, based on the total number of bookings (records) they have assigned to them. Ideally the field would be dynamic and will adjust automatically. We are using this field to integrate with external platform, Glide.

We have separate tables for clients, employees and bookings, and all utilise linked records for other purposes so we are very familiar with them. I'm just unsure about how to pull this data automatically, and if it can be done as a multiple linked-record field or if it would only work as a text formula.

I understand a lookup field can be used to show the first or last 3 items based on the lookup source, however I can't sort this to show the highest ranked. Example below. I really need to show the top 3 clients for each employee, and be able to easily click into their details like a linked-record allows.

Any ideas you have would be much appreciated.

[ScottWorld]

Yeah, it's one of the unfortunate tragedies in Airtable that they give us the ability to limit the number of records that we can see in a lookup field, yet that option is essentially useless because they don't give us any automatic way of SORTING the linked record values!

To start with, be sure to send this in as a feature request to support@airtable.com. It probably won't do any good because I've been emailing them about this for 6 years, but at least we can get one more voice thrown into the mix!

Maybe other people have discovered some clever trick or workaround to solve this dilemma, but the only solution that I can think of off the top of my head is that you would use an automation to re-input the same exact values in your linked record field, but make sure that they are in their new "proper order" (from highest to lowest).

You can do this with an Airtable automation by finding all the linked records in the linked table, then sorting the records, and then overwriting the original linked record field with the records that you just found & sorted.

The big problem here is that you would need to continually rerun this automation over & over again whenever you need to get the top 3 ranking values again.

Another problem is that Airtable has limits on how many records you can find & sort, so if you need to go beyond Airtable's limits, you can use Make's advanced automations for Airtable to do this same thing, because they have no limits over there.

However, maybe somebody else has a better solution for this.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Caitlyn10]

Hi @ScottWorld thanks for your response. Would your workaround solution be feasible with Zapier too? All our automation runs through Zapier so would be best to use it. Perhaps I could add a trigger there to only run the automation once a week or something so it's not too overloaded and then our employees have a regular reference for rank updates.

Back to Airtable -- is it possible to show the top 3 clients with a formula field instead of linked records? I'm wondering if there's a workaround with that option at all?

[ScottWorld]

@Caitlyn10 

I'm not sure about either of those questions.

Zapier is extremely limited compared to Make, so I don't know if they allow you to (1) sort found records or (2) sort the contents of an array, which are the 2 different ways that you could accomplish this in Make.

Regarding using an Airtable formula field, I can't immediately think of a way to do it, but that doesn't mean it's not possible! I actually demonstrate how to get the TOP VALUE using a formula field in this Airtable podcast episode, but I'm not sure how to extrapolate from there to get the 2nd and 3rd values. There might be a way to do it, though.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo]

Doable, we'd just need to run two automations every time a booking is created, and your rankings would be kept up to date in real time!  How you want the data displayed will affect the setup though, and so I'm going to assume you're okay with displaying the top 3 clients per employee in a lookup field in descending order like so:

You could also set it up so that it shows each client in its own cell with the count of bookings too, but that's more work to set up so I figured I'd just pass you this first

---

To get this to work you'll first need to create a third table where each record represents a link between a Client and an Employee (let's call that 'Clients <> Employees'), and then you could use a Count field to get the number of bookings, then sort by that:

After that, you'd
1. Create a formula field in Bookings to output the text '[CLIENT] <> [EMPLOYEE]'

2. Create an automation that would trigger whenever a new Booking record was created with the 'Client' and 'Employee' fields filled in, and make it paste into the linked field to 'Clients <> Employees', which helps you populate the data
3. Create another automation that will trigger whenever the 'Bookings' field in 'Clients <> Employees' is updated, and its actions will be
  - Find all the records in 'Clients <> Employees' that are linked to this Employee via the lookup field
  - Sort that list by the Count field
  - Update the record in 'Employee' with that sorted list
4. Make the lookup field in Employees display the First 3

And done!

Link to base

I've set up the automations and fields in the base above, and you can duplicate it into your own workspace to test it out!

[ScottWorld]

That is vastly overcomplicating the solution, but it does go to show that there are many different ways of creating the automation that I outlined above.

You don’t actually need to go through all of those steps nor do you need a junction table. You can just find the linked records, sort them, and update the linked record field.

However, as I said earlier, the solution requires automations to re-order the linked record values.

- ScottWorld, Expert Airtable Consultant

[Caitlyn10]

Thanks @TheTimeSavingCo. That does look quite complicated, but I really appreciate the detailed response and example base. @ScottWorld mentioned it may be able to work without a junction table, do you have any suggestions on that?

I don't want to complicate my base with extra tables. I already have separate tables for clients, employees, and bookings. They all have other linked records between them.