Complicated lookup field's Type causes formula to fail

[jethroc]

I have two tables Experiments and Readings.

Each experiment is linked to two or more reading records. Each reading has a DateTime field, and some readings such as pH and temperature. The pH and temperature readings are number fields.

I want to use the first and last sets of readings in the Experiments table in some numeric formulas.

In Experiments table I use a rollup field with MIN(values) to find the earliest DateTime in the linked readings. In the Readings table I use a lookup field and copy the {First DateTime} reading back into the readings table. So that all the Readings for a specific Experiment contain a field which is set to {First DateTime}.

Then still in Readings, I have a field {First Temperature} with the formula:

IF(DateTime={First DateTime},Temperature, '')

Then back in Experiments I have a corresponding field {First Temperature Reading} that looks up the result in the {First Temperature} field. 

This all appears to work correctly so far. In the {First Temperature Reading} field, I have the first temperature reading taken for each experiment.

In another field {Temperature Difference} I want to calculate the number of degrees above a fixed temperature with a formula like this:

{First Temperature Reading} - 20

This displays an error and all values are 1.0 (following the results format I set). However the Preview result is "true", so 1=true. Removing the "-20" I can not set the result format and get the message "Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date."

Tracing it back, my temperature reading stopped being a number in {First Temperature} when I used the formula:

IF(DateTime={First DateTime},Temperature, '')

My assumption is that because the formula returns two different types, a number, and whatever '' is, therefore the fields type is unclear, and this unclear status is kept all through the transformations until it arrives in my formula in the Experiments table.

Nothing I do can convert this back to a number.

So I feel slightly trapped. I've got the value from the Readings field that I want to manipulate in the Experiments field, but I can't use it.

Is there some other way I can get the value I want to work with that doesn't change it's type, or is there a formula to force it back to being a number?

[TheTimeSavingCo]

Hm, could you share a read only invite link to an example base with this issue so I can take a look at it for you?  I tried to replicate it but wasn't able to:

Link to base

 

[jethroc]

Hi,

https://airtable.com/apppjm1dbpSM2JxEZ/shrj5isDBs9RJMEbC

Sorry for not thinking of that earlier!

[Alexey_Gusev · Accepted solution]

Hi,
try to:
1) remove ''  (just close bracket after Temperature)
2) use VALUE({some_result}) for explicit conversion to number

[jethroc]

Hi Alexey,

Thank you very much! Suggestion (1) fixed it.

I tried (2) as well, without (1), and all that produced was an error, which is confusing.

 

   

[Alexey_Gusev]

Hi,
glad it helped you! It was just a quick guessing, like 'try 1, OR try 2'
If after (1) it is OK, then just ignore (2)
For your info, about 2, I suppose {First temp} you using is lookup. All lookups are arrays, even when empty or single-value. I think, Airtable math expressions can use a single value array as number (and it surprised me), but VALUE function - not, It needs string.
Array can be converted to string by CONCATENATE({Field}) or {Field}&''. So, VALUE({Field}&'') will do the job. Maybe ))
But if it works without extra functions, I think you should leave it 'as is'.

[jethroc]

Thank you. It's good to understand better how arrays and VALUE work. 

On (1) I had tried, removing '' from the formula but had left the trailing comma so that the function ended ,) which it didn't like. I didn't think to try dropping the last argument altogether! New trick unlocked.