Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 16, 2024 03:25 AM - edited Apr 16, 2024 03:32 AM
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, '')
Solved! Go to Solution.
Apr 16, 2024 10:03 AM
Hi,
try to:
1) remove '' (just close bracket after Temperature)
2) use VALUE({some_result}) for explicit conversion to number
Apr 16, 2024 06:04 AM
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
Apr 16, 2024 07:37 AM
Apr 16, 2024 10:03 AM
Hi,
try to:
1) remove '' (just close bracket after Temperature)
2) use VALUE({some_result}) for explicit conversion to number
Apr 16, 2024 06:02 PM
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.
Apr 16, 2024 06:54 PM
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'.
Apr 16, 2024 07:50 PM
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.