Turning a non-numeric text field number into a number field?

[itoldusoandso]

This is follow up on the response to issue mentioned in the other post and I didn’t want to flood it with unrelated stuff.

So My question - is there a way to turn a non-numeric field into number field.

In my case, the lookup creates apparently an array and is non-numeric. It is actually in my case ALWAYS going to be just one number. There is never going to be a comma, never space, never going to be two separate numbers. The number can range from 0 to 100 or get even negative (it’s for quantity).

Is there any function to turn this back into formula field?
Unless using some kind of SWITCH formula or substitute regex formula or some madness like this?

I tried VALUE but that seems only turns written text number into a digits number.

But I need to turn a text field like this:
9
into this:
9

This was the issue;

List your top Airtable feature requests here!

Wish the Automations error reporting was a bit more chatty. If it was ChatGPT giving me suggestions on what may be wrong, it would be more helpful. Got this error suddenly but there doesn’t seem to be any further explanation: Airtable error evaluating conditional group formula field. The underlying condition is simply evaluating the formula numeric field, if it is zero or not. The field shows zero (0) all the time, before the automation is triggered and even after that (for this specific reco…

[RussellBishop]

Instead of a lookup (which returns an array), use a roll up with the formula VALUE(ARRAYUNIQUE(values) & “”)

This turns the array into a string (unique is actually interchangeable here), then turns that string into a number.

[Kuovonne]

@itoldusoandso

In my case, the lookup creates apparently an array and is non-numeric. It is actually in my case ALWAYS going to be just one number. There is never going to be a comma, never space, never going to be two separate numbers. The number can range from 0 to 100 or get even negative (it’s for quantity).

Is there any function to turn this back into formula field?

Why don’t you use a rollup instead of a lookup? Then you can use a formula like MAX(values). Since there is only one number, you will get a single number as a number (versus an array of numbers, which might have only one element in the array).

[itoldusoandso]

Makes sense. Yes.

I only used Lookup all the time because there was no issue up to now when I come across this situation. Being non technical background, I would naturally think, if I am looking up one field and that field is a numeric field, then, naturally, Airtable should interpret it as such and show the result of the lookup also as a number field.

If using a rollup in this case a correct way of doing it rather than lookup, then the help and error messages could be more descriptive. On the other hand, if it is sort of a work-around fix to a problem that Airtable didn’t address with the function, then I can’t imagine how the new users must feel frustrated with these types of issues.

Probably one of the reasons why the Airtable support team was overwhelmed with dealing with these types of small case tickets in the past, no wonder they culled it.

[Kuovonne]

@itoldusoandso

If using a rollup in this case a correct way of doing it rather than lookup

There are many possible ways of doing things. Based on what you’ve described, I think that a rollup is the most efficient solution.

@itoldusoandso

the help and error messages could be more descriptive

While I agree that help and error messages could be better, figuring out how to identify the error can be tricky. Some people may want to throw AI at the problem, but the risk of false reasons is pretty high given the limited training material that AI has and the wide range of possible causes for automation issues. I often prefer a slightly cryptic, but completely accurate error message to an error message with a cause/solution that has a significant chance of being incorrect.

@itoldusoandso

Probably one of the reasons why the Airtable support team was overwhelmed with dealing with these types of small case tickets in the past, no wonder they culled it.

Could be. Large enterprise customers probably have staff with Airtable knowledge that can deal with these small case issues internally (or build to avoid these issues in the first place). Smaller customers either have to figure it out on their own or hire a consultant.