Linked fields - formula/strategy to identify and show the highest-ranking linked record

[Heather_Kleinsc]

Is there a straightforward way to handle this?

I have two tables:

• contacts
• email addresses

There's a field linking them. Each contact may contain multiple email addresses. 

What I want is to have a field in the contacts table that displays one email address - but not just the first or last record, I want the best address for that contact.

I've created a formula field in the email addresses table that assigns a score based on various criteria. Ideally, a 'top email address' field (in contacts table) would identify the highest-scoring address within the addresses linked to that contact and display only that one.

---

I realize I could sort the email addresses table and then have a lookup field take the first one. For several reasons, I'd prefer to not have to change the sort order.

Any other suggestions come to mind?

[Sistema_Aotearo]

Might have a solution, but haven't tested it yet!

Airtable recently added a feature where you can convert a formula to output as a "Single Select" field.
If you convert your email scores into a single select type, you can organise the order of options.

When using a lookup field on the contacts table, it should be ordered by what you've set in the formula's setting. Then choosing "First" would only display the top one.

That's as far as I've gotten theoretically. Let me know if this makes any sense haha!

[Heather_Kleinsc]

Thanks for the suggestion, @Sistema_Aotearo! I hadn't heard of the new feature yet 🙂

Unfortunately for this purpose, it didn't work 😞  I think the problem is that when the first-listed record has a low-ranked score, the record itself still occurs first, even though its single-select option is lower than that of the later record with a higher single-select option

[TheTimeSavingCo]

I've set up a base for you here that I think does what you're looking for:

To recreate it:
1. In the "Contacts" table, create a rollup field on the "Number" value from "Email Addresses" with the formula "MAX(values)"
- This will give us the highest number value of the linked emails
2. In "Email Addresses", create a lookup field to display the value from the lookup field from Step 1
3. In "Email Addresses", use a formula field to check the "Number" value against the lookup field value from Step 2, thus identifying the record with the highest "Number" value
4. In "Contacts", create a lookup field with a conditional that will only display the value from the record with the highest "Number" value

Kinda complicated to set up, but works fine, and you can duplicate the base to your own workspace to play around with it!

[Heather_Kleinsc]

Thank you for all your help on this Adam!