Help

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

Re: return field name from the MAX of fields in a row?

Solved
Jump to Solution
828 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Justin_Hall
5 - Automation Enthusiast
5 - Automation Enthusiast

Let's say we have a row with a product & prices for that product at four regional stores:

Chocolate Bar
Store A: $1
Store B: $3
Store C: $2
Store 😧 $5

I'd like a formula that would look across the store prices, determine the highest price, and then return the name of that field. I want a function that would return "Store D" in the example above.

MAX(Store A, Store B, Store C, Store D) will help me identify the highest price, but it returns the $5 value, not the field name Store D. How do I get something like MAX returning the winning field name?

Thanks!

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

I'm guessing that you come from a spreadsheet background.

As you add more stores, your table will get wider. However, when designing a database for this type of data, it would be better to structure the data with linked tables. One table for [Products], another for [Stores], and a junction table for [Prices]. Then you could do a set of back-and-forth rollups to determine the max price and the store with the max price. The system would scale to lots of stores without having to add any fields or edit formulas.

 

But that's not what you asked. I think you want something like this, even though I still recommend having a different schema setup instead.

kuovonne_0-1699038670978.png

Here are the formula fields:

kuovonne_1-1699038709256.png

kuovonne_2-1699038728460.png

 

 

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

I'm guessing that you come from a spreadsheet background.

As you add more stores, your table will get wider. However, when designing a database for this type of data, it would be better to structure the data with linked tables. One table for [Products], another for [Stores], and a junction table for [Prices]. Then you could do a set of back-and-forth rollups to determine the max price and the store with the max price. The system would scale to lots of stores without having to add any fields or edit formulas.

 

But that's not what you asked. I think you want something like this, even though I still recommend having a different schema setup instead.

kuovonne_0-1699038670978.png

Here are the formula fields:

kuovonne_1-1699038709256.png

kuovonne_2-1699038728460.png

 

 

Thanks Kuovonne!  I don't need this to scale for this particular case. Your pair of formulas works exactly as I'd like for the few fields I'm running this on. That's another time you've helped me with AirTable. Appreciate it!