Help

Pull highest number of a comma separated text field

Topic Labels: Formulas
Solved
Jump to Solution
1297 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_Havil
5 - Automation Enthusiast
5 - Automation Enthusiast

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
kuovonne
18 - Pluto
18 - Pluto

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
augmented
10 - Mercury
10 - Mercury

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.

kuovonne
18 - Pluto
18 - Pluto

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.