Re: How to select a value, within a record with several values.?

2321 6
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, I need help please, consult, I need to know how to use in a formula only one term within a record.
example: within the field price m2 printed + cut I have two values, how do I select only one if I have two or more?
Is it possible? Thank you!


10 Replies 10

Welcome to the community, @Luciano_Augusto_Estr! :grinning_face_with_big_eyes: Unfortunately formulas are extremely limited in how they can process arrays. Currently there’s no way to arbitrarily pick a given item from the array; e.g. first, last, third, etc. The only functions that will return a single value from an array are the MIN() and MAX() functions, which will give you the smallest and largest values, respectively.

That said, there’s probably a way to do this using regular expressions, but I’m way too tired to go there right now.

Can you share more details on how you determine which value you want to extract? Is it always going to be the same position—e.g. first/last/third—or will it depend on other data values in the same record?

Welcome to the Airtable community!

It looks like you have a lookup field, which includes multiple values when there are multiple linked records.

Have you looked into using a rollup field instead? The formulas that Justin refer to work best in rollup fields. If you want the largest or smallest value, you can use the MAX(values) or MIN(values) as the formula for the rollup. If you want the first or last value (based on the order of the linked records), Justin is also correct that it can be returned using regular expressions.

Thanks Justin. Yes, the order of the values is always the same, what I need is that the final price of the product takes one of those two or three values, being a product that has two possible production variables.

Thanks, kuovonne. How does rollup field work? I never used it, would you have an example?
Thanks again.

Thanks for the follow-up, but my question isn’t so much about the order as it is about which value is chosen. You mentioned that it’s a product that has multiple production variables. What determines which of those variables is used in the final price? In other words, what part of your process logic is the part that says it’ll be price #1 vs price #2? Perhaps you could share more details about your use case to give us more of the big picture.

The support website describes the rollup field type. However, it sounds like you want more complex logic than just the largest/smallest/first/last value. Thus, while a rollup field might be part of how you calculate the value you want, it probably will not be the entire solution.

There you can see, when I add another item in the variables field, the formula does not work for me, because it is configured to take only one value, then the $ 1 is seen.
In short, what I need is for the formula to assign me the value that I want it to apply.
but maybe I’m wrong and there is another way …


That shows more of the problem, but we still don’t know how you choose which value to use in the other formulas.

How would you fill in the blanks in the following statements?

  • When there are two values, I want to use the ________ value (first? second?)
  • When there are three values, I want to use the ________ value (first? second? third?)

Will the response always be the same for every record?

When there are two values, I want to use the first value for one price and the second for another price, the logic would be the same with 3 or more values.
Maybe the fields and logic of my table are not well applied …