Skip to main content

Hi,

I've created a formula to calculate my patients' current age based on their date of birth (Lookup field). When I look at the record in grid view, it gives me an error message, but when I check the formula for accuracy, the calculation preview gives me the correct calculation. I'm not sure why there is an error in the record in grid view if the formula is accurate when I check and edit it. HIPAA-protected info has been redacted in the screenshot below.

 

DATETIME_DIFF(TODAY(), {Child's Date of Birth (from Lookup) 2}, 'years') & ' years, ' & MOD(DATETIME_DIFF(TODAY(), DOB, 'months'), 12) & ' months'
 
Thanks so much!

Hey @msw_SLP!

I'm currently from my phone, so I cannot test much, but the issue is probably arising given that the {Child's Date of Birth (from Lookup) 2} field is a lookup field, and lookup fields in Airtable often return an array (even if there’s only one value). When the formula tries to do the calculation using an array, it can lead to errors.

Instead of having field {Child's Date of Birth (from Lookup) 2} as a lookup, please try having {Child's Date of Birth (from Lookup) 2}  as a rollup with the formula of the rollup being:

 

MAX(values)

 

Please let me know if this gets the trick done. I believe it should.

Mike, Consultant @ Automatic Nation

Yes, Lookups reveal an array. You can change the formula to arrayjoin({Child's DOB From Lookup})
Or change the Lookup field itself to Rollup with max(values)

Terms & ConditionsAccessibility statement