Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Single select based on 2 different fields

Topic Labels: Formulas
Solved
Jump to Solution
315 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Megalife
4 - Data Explorer
4 - Data Explorer

Hi guys,

Hey, guys,

Please help me solve a problem - I have a table of tennis matches, which has fields Player 1 and Player 2, which are linked to another table Players. In the matches table next to the Player 1 and Player 2 fields, I want to add a single select field Winner, which will have only 2 values: Player 1 or Player 2.

Is there a formula that will allow me to create a single select from the values of the neighboring fields?

Thanks.

2 Solutions

Accepted Solutions
EvreuxPendragon
4 - Data Explorer
4 - Data Explorer

Hello!

You don't need a formula to make this work actually. Assuming you have an identifier, username or fullname in your player records:

  1. Go to your Matches table.
  2. Add two Lookup columns.
  3. Set the Source of each lookup to Player 1 and Player 2.
  4. Set the Field Lookup to your Id/username/fullname.
  5. Now add your Winner column with Linked Record type.
  6. Enable Filter record selection by a condition.
  7. Set two conditions with the following sentence: Where [Username] is [Lookup from Player 1] or [Username] is [Lookup from Player 2]. To enable the selection of lookups, click on the gear icon next to each condition and click Dynamic Condition.
  8. Click Save.

Here are some screenshots for the configuration of the lookup fields:

EvreuxPendragon_0-1726526766485.png

 

The Matches table:

EvreuxPendragon_1-1726526824143.png

 

The Winner field configuration:

EvreuxPendragon_2-1726526847040.png

 

Final output:

EvreuxPendragon_3-1726526868179.png

 

See Solution in Thread

TheTimeSavingCo
18 - Pluto
18 - Pluto

I don't think creating a single select via a formula like that is possible I'm afraid, and I think the best we can do is something like this:

Screenshot 2024-09-17 at 9.26.45 AM.png

SWITCH(
  Winner, 
  "Player 1" , {Player 1},
  "Player 2", {Player 2}
)

 ---
@EvreuxPendragonthat's a really neat solution!

See Solution in Thread

3 Replies 3
EvreuxPendragon
4 - Data Explorer
4 - Data Explorer

Hello!

You don't need a formula to make this work actually. Assuming you have an identifier, username or fullname in your player records:

  1. Go to your Matches table.
  2. Add two Lookup columns.
  3. Set the Source of each lookup to Player 1 and Player 2.
  4. Set the Field Lookup to your Id/username/fullname.
  5. Now add your Winner column with Linked Record type.
  6. Enable Filter record selection by a condition.
  7. Set two conditions with the following sentence: Where [Username] is [Lookup from Player 1] or [Username] is [Lookup from Player 2]. To enable the selection of lookups, click on the gear icon next to each condition and click Dynamic Condition.
  8. Click Save.

Here are some screenshots for the configuration of the lookup fields:

EvreuxPendragon_0-1726526766485.png

 

The Matches table:

EvreuxPendragon_1-1726526824143.png

 

The Winner field configuration:

EvreuxPendragon_2-1726526847040.png

 

Final output:

EvreuxPendragon_3-1726526868179.png

 

TheTimeSavingCo
18 - Pluto
18 - Pluto

I don't think creating a single select via a formula like that is possible I'm afraid, and I think the best we can do is something like this:

Screenshot 2024-09-17 at 9.26.45 AM.png

SWITCH(
  Winner, 
  "Player 1" , {Player 1},
  "Player 2", {Player 2}
)

 ---
@EvreuxPendragonthat's a really neat solution!

Megalife
4 - Data Explorer
4 - Data Explorer

Thank you all guys,

You've been a big help. I'm just surprised I didn't think of this solution myself 😊