Parse JSON File via Formula

[Karlstens]

Hi All,

I’m parsing JSON files into Airtable Long Text fields, and then setting up Formula Fields to relay the contents of that JSON. For example, the JSON data looks similar to this;

{"NAME":"Colour","VAL":"Red","ID":"1"},
{"NAME":"Shape","VAL":"Square","ID":"2"},
{"NAME":"Size","VAL":"500","ID":"3"},
{"NAME":"Scale","VAL":"2","ID":"4"}

At the moment, I’m using a fairly hacky method to display the data in a formula field, for example;

ABS(SUBSTITUTE(
RIGHT(MID({Log Content},FIND("Size", {Log Content}),17),4)
, '"',''))

But this is buggy as. Obviously if Size is more or less than 3 digits, the output fails or works simply by chance.

What would be the correct way to write a formula that retrieves the entire value between the inverted commas, regardless of the length?

[ScottWorld]

kuovonne:

I recommend wrapping the digits in VALUE() instead, because it was designed to convert text to number. It can also preserve negative numbers.

Actually, Airtable’s VALUE() function is extremely problematic because it is actually designed to perform a large variety of mathematical functions & mathematical substitutions. This function causes way more trouble than it is worth. It’s too bad that the Airtable employees didn’t create a REAL text-to-number function for us to use.

[kuovonne]

ScottWorld:

Airtable’s VALUE() function is extremely problematic because it is actually designed to perform a large variety of mathematical functions & mathematical substitutions.

Yeah. But if you have a well crafted REGEX pattern, you can be sure of grabbing only digits and avoiding having VALUE() do weird math.

[Karlstens]

I find that ABS() triggers the integer/decimal needed to allow numeric formatting. I’ll read up on Value, but suspect they’re both same-same for what I’m needing.

One other technique I’m also using is Substitute() on the JSON data to remove ‘ apostrophe characters, because I will go cross-eyed trying to regex data that also contains apostrophes :rofl:

[kuovonne]

Karlstens:

suspect they’re both same-same for what I’m needing.

Yeah. There are multiple ways of doing things with code. They both work for this particular use case, for now. But I think it is useful to know the differences between the functions.

ABS() returns the absolute value of a number. Basically that means it turns negative numbers positive. So you can never get a negative number from ABS().

The fact that ABS() does text to number conversion is an undocumented feature, which means that Airtable could change that behavior without notice.

On the other hand, VALUE() was specifically designed to convert text to a number, so it can return negative numbers, and it’s ability to convert text a number is not at risk of suddenly disappearing.

It is true that VALUE() does some very strange computations in some cases, but you can avoid that if you ensure that the text being passed in contains only digits, an optional negative sign at the beginning, and a single, optional decimal point.