Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Pull highest number of a comma separated text field

Topic Labels: Formulas
Solved
Jump to Solution
464 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi All,

I have a field which is pulling in from JIRA and displays like this:

154, 152, 157, 155

There could be more or less numbers.

What I need is to create a new field which is the highest number in this field. (So it will display 157 in this case).

The issue is the rollup feature is displaying 0. I assume this is because this is being viewed as text.

Any ideas how to solve this?

Cheers

Simon

1 Solution

Accepted Solutions

Thank you for the screen captures and the description of how you are getting the values. Since your numbers are left aligned, I bet your numbers are actually text strings that look like numbers.

In your formula field that pulls out the number, wrap the formula in VALUE() to convert the text string into an actual number. The number should now be right aligned.

Then in your other table, use a rollup formula with the MAX(values) aggregation formula.

See Solution in Thread

5 Replies 5

Hi Simon. Maybe something like this…

IF({field name}, MAX({field name}), 0)

Welcome to the Airtable community!

Rollup fields only work in conjunction with linked record fields.

Do the numbers from JIRA get sent as the comma separated list into a single field? If so, Airtable treats it as text. You can convert a text string that looks like a number into a number using VALUE() but it only works for one number.

Are there always four numbers, or could there sometimes be more or less? Is there a maximum number of numbers?

Can you post screen shots of all the relevant fields, including the field populated by JIRA, any linked record fields, and your rollup/formula field?

Hi Kuovonne,

The fields are being pulled through from JIRA as single values but pulled into a comma separated list as a result of grouping them in Airtable.
There could be any number of these numbers, not just four.

The values are being pulled in like this. I use a formula to pull out the number from 20220526-PP-R151-…
to look like: 151 as I only care about this part.

I then group various JIRA ID together in another table which creates a view like:
FeatureA - 141,142,143 (etc)

I need to then just need to print the highest of these values.

Untitled

Untitled2

Sorry this is very difficult to explain.

Thank you for the screen captures and the description of how you are getting the values. Since your numbers are left aligned, I bet your numbers are actually text strings that look like numbers.

In your formula field that pulls out the number, wrap the formula in VALUE() to convert the text string into an actual number. The number should now be right aligned.

Then in your other table, use a rollup formula with the MAX(values) aggregation formula.

Amazing! thank you. Its always so simple when you know.