Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 21, 2021 01:07 PM
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!
Aug 21, 2021 11:34 PM
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?
Aug 22, 2021 04:52 PM
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.
Aug 23, 2021 04:55 PM
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.
Aug 23, 2021 04:58 PM
Thanks, kuovonne. How does rollup field work? I never used it, would you have an example?
Thanks again.
Aug 23, 2021 06:21 PM
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.
Aug 23, 2021 09:25 PM
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.
Aug 24, 2021 04:03 PM
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 …
Aug 24, 2021 10:43 PM
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?
Will the response always be the same for every record?
Aug 25, 2021 12:40 PM
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 …