VALUE() function is broken

[ScottWorld]

It has come to light that the VALUE() function is broken.

The VALUE() function is supposed to extract numbers from a string. It’s designed to be used as a simple filtering tool to remove non-numbers from a field. This is how it is documented on the formula field reference page: Formula field reference – Airtable Support

Instead, the VALUE() function does at least 2 completely different things:

1. The VALUE() function converts certain letters into numbers, and then multiplies those numbers by the closest numbers that it can find! This issue is documented here: Value Formula Returning Numbers not Found in Cell - #2 by CT3

2. The VALUE() function interprets certain symbols as operators to perform math on all the extracted numbers from a string. This issue is documented here: Interesting discovery: The VALUE() function performs math

We do not know what the complete list of letters & symbols are that cause the VALUE() function to fail, so there is no telling how many different ways this function can fail.

In my opinion, everybody should avoid the VALUE() function. Or at the very least, proceed cautiously when using it.

This comes right on the heels of me losing a lucrative Airtable client because Airtable has not fixed its mobile views. My client’s website depended heavily on Airtable-embedded views — and they charged their clients to access those pages — BUT most of their clients used mobile devices to browse their website (as do the majority of people using the Internet).

But, despite many posts in the forums about this for several years, Airtable still does not offer grouping, filtering, sorting, and searching on any mobile views. So it rendered his website unusable to his clients.

My client even emailed Airtable Support about this, and their response was: “We can see how fully-functional mobile views could be helpful. Thanks for the feedback.”

Airtable thinks they’re filling a bucket with tons of customers, but they’re not paying attention to the hole at the bottom of the bucket which is leaking customers & causing people who were once fans of the product to badmouth the product instead.

[kuovonne]

ScottWorld:

It’s designed to be used as a simple filtering tool to remove non-numbers from a field.

I agree that the behavior of VALUE() is not well understood, and poorly documented. However, the documentation doesn’t actually say that the function simply removes non-numbers from a field. The documentation states that the function …

Converts the text string to a number.

The function does indeed convert a text string to a number. It might not convert the text string to the expected number, but that is different from saying that there is a complete mismatch between the documentation and the behavior.

This is a case where a good technical writer could make a difference. A technical writer could have noticed that this documentation didn’t cover any edge cases, and could have gone back to the developer and ask for more details on how the function converts the text string to a number. But, maybe the technical writer wasn’t available at the time or maybe the technical writer was busy with other things at the time and made a judgment call to not investigate this further.

[ScottWorld]

kuovonne:

The function does indeed convert a text string to a number. It might not convert the text string to the expected number, but that is different from saying that there is a complete mismatch between the documentation and the behavior.

Haha, this is so true! :stuck_out_tongue_closed_eyes:

kuovonne:

This is a case where a good technical writer could make a difference

Yes, I’ve long had issues with the technical writing in Airtable’s support articles, but particularly the formula field reference article.

The majority of the examples that they provide do not have any context, because they only give the outputs of the functions, but they don’t show what the inputs need to be to get those outputs. In other words, there is no context for new users to understand the examples.

For example, when someone types in WEEKNUM({Date}) into one of their formula fields, they will not typically get the number 46 as a result. Airtable should let the users know what {Date} they used in the example.

That’s an easy-to-understand example, but this same problem is repeated across most of the examples in that article, and some of the examples might not be as easy for people to understand as WEEKNUM().

Users need to understand what inputs go INTO the formula in order to get the example output in that article.

Apple does a great job with their FileMaker reference — you can see all of their functions listed on this page, and every function example shows you both the INPUTS & OUTPUTS of each function:

https://help.claris.com/en/pro-help/content/functions-reference.html

[kuovonne]

Airtable’s formula field reference differs from many other references in that all functions are documented on a single page. Many other references have a single page per function. When there is a single page per function, the functions tend to be documented in more depth.

I think that this is a design tradeoff. Documenting all of the functions in a single page is faster to create and makes it easier to learn all of the functions, but there isn’t space to include all the details on each function. It is possible to document the functions both ways, but then you are still talking about tradeoffs in the technical writer’s time and resources.

Overall, I think that the formula field reference (and the support website in general) is a much underused resource by most users. While improved documentation will probably help power users, it probably won’t do much for the average users who may not even know what documentation exists.

I actually don’t have a problem with the lack of inputs in most of the formula field reference. I tend to use the formula field reference as a starting point, and then actually run the formula to get any finer details.

[Simon_H]

Just learned this lesson the hard way.
Tried using the value function to extract the numeric portion of a string field containing an amount with the currency abbreviation i.e 1.23 USD.
Results got weird when calling the function on amounts in GBP

[WilliamPorter]

CORRIGENDUM: Kuovonne corrected my claim that Value() always returns an integer. She is right. Read her comment here. I respond to her post thanking her first for the correction but insisting that the Value() function is not just confusing but confused.

I’ve kept my post here. Struck through some of the worst mistakes.

.

I agree with Justin Barrett’s comment that the VALUE() function is not broken. I would say however that the VALUE() function is lame.

I agree with Kuovonne that the documentation of the functions is poor. One might think – given Airtable’s market – that exceptionally good documentation would be a high priority, but never mind that. Here’s my improved explanation for VALUE:

The VALUE() function attempts to find, extract and in some cases convert a number value inside a text string. It always returns an integer value. If no numeric value is found, or if the source string is empty, the VALUE() function returns 0.

I believe that is the basic, true description of the function. But it desperately needs a number of notes or, as I would prefer to call them, “caveats”. Here are the key ones:

1. VALUE() returns only integer values. It will round as necessary.
2. VALUE() does not simply filter out non-numeric characters. It looks for number values, including decimal values. So, VALUE(“14.7 lbs”) returns 14.7, NOT 147.
3. If the source string contains more than one distinct numeric value (e.g. “11.2 gallons, 47 bushels”), VALUE() will extract an integer value only from the first distinct numeric value. In “11.2 gallons, 47 bushels”, it will find “11.2”, regard it as a distinct value and then round it to the nearest integer, returning 11. The fact that “11.2” is a decimal value distinguishes it. “11.0” will also be regarded as distinct. On the other hand, “11 gallons, 47 bushels” returns “1147”, apparently because nothing about “11” marks it off as a complete, distinct number in itself.
4. At least in the USA, a period/dot may be regarded as a decimal delimiter, VALUE(“14.7”) returns 15; but a comma is regarded as an alpha value and ignored: VALUE(“14,7”) returns 147.
5. Some text strings inside the source value being parsed will be interpreted as units and a conversion will occur to the base unit. This is especially true if the source string contains “TB”, “GB”, “MB”, and in some cases “m” may be interpreted as “million”, “b” may be interpreted as “billion”. There seems to be no complete or authoritative list of the units that the function recognizes and both context and case seem to make a difference. Have fun discovering what parts of your source string produce surprising results!

Okay, gets a little snarky there at the end. But it’s a first draft.

I cannot see what regularly-recurring problem VALUE() is there to solve. If you wanted to convert terabytes to bytes, you could do that easily with simple arithmetic. If you just want to extract numerals from a string value, use REGEX_REPLACE.

William

[kuovonne]

WilliamPorterTech:

VALUE() returns only integer values. It will round as necessary.

This is incorrect. You need to change the formatting in the field configuration to show the decimal places. The VALUE() function is not doing the rounding, the field configuration settings is doing the rounding for display purposes only.

WilliamPorterTech:

I cannot see what regularly-recurring problem VALUE() is there to solve. If you wanted to convert terabytes to bytes, you could do that easily with simple arithmetic. If you just want to extract numerals from a string value, use REGEX_REPLACE.

We need VALUE() to convert text strings to numbers. You can use arithmetic to convert terabytes to bytes, but you still need to convert the text string to a number before you can do any math. Plus, the VALUE() function predates regular expression functions.

I agree that the “extra” conversions that VALUE() performs is confusing and poorly documented. I just did a test and watched VALUE() try to convert “7tb” and it gave me 7000000000000, before changing its mind and giving me 7. Meanwhile, “7TB” turned into 7696581394432. Crazy. But we definitely need a function that converts a text string to a number, preferably one that is easier to understand.

[WilliamPorter]

WilliamPorterTech:

VALUE() returns only integer values. It will round as necessary.

This is incorrect. You need to change the formatting in the field configuration to show the decimal places. The VALUE() function is not doing the rounding, the field configuration settings is doing the rounding for display purposes only.

Ah, THANK YOU Kuovonne for correcting me on this. I will go back and edit my post (in hope of not misleading anybody else).

.

Still, I’m not willing to withdraw my comment that I can’t see the point of this function as it currently works. I would absolutely see the point if VALUE() in Airtable worked more predictably like GetAsNumber() function in FileMaker. The problem is that Value() is a function that contains something I don’t think anybody should want to have in a math function – the element of surprise! If you want to convert terabytes to gigabytes, do the math. If you want a surprise, use a random number function.

Actually there are two different use cases that in FileMaker are served by two different functions.

GetAsNumber() in FileMaker is used to coerce a string that (usually) looks like a number, into a true number data type. (FileMaker is loosely typed but there reasons you’d want to do this, including sorting.) Sometimes the string looks simply like a number: say “5.27”. In normal uses, it appears that Value() in Airtable does just the same thing, and yes, I see the use in that.

The other use case is when you want to take a string that contains both numeric and alpha characters and extract from them just the numeric. My go-to example of that is pulling the street number from a full street address:

Value("42 Pike St") => 42
GetasNumber ("42 Pike St") = 42 [in FileMaker]

In that case, though, in FileMaker, I wouldn’t use GetAsNumber: I’d use the Filter() function and provide the ten numerals “0123456789” as the filter table; maybe I’d include “.” in there as well. Works fine, and it’s easier to learn and use than using Regex functions in Airtable.

.

For me, the unpredictable consequences of having strings like “GB” or “b” in the source string, make the function unusable – and not just because it’s not properly documented.

It simply isn’t clear to me what this function really wants to do when it grows up: convert text to number values? convert certain kinds of number values to equivalent values based on different units? Which is it?

I think that’s more than a documentation error. I think its a bit of confusion about the purpose of the function.

William