Calculated Value Field- Estimated Age

[harrytruman]

Hi Everyone,

I'm having trouble writing a formula for a calculated value field.

Here's what I'm trying to do:

-Calculate the estimated age of person/record using their medical school graduation year

-Use a field called 'medicalSchoolGraduationYear' that contains the year they graduated

-Get current year

-Assume they were 22 years old at graduation

-Calculation: Current year - graduation year +22 For example, 2023- 2000+ 22= 45 years old

-Display calculated value (estimated age) in a field called 'estimatedAge'

-This would display anytime a record is viewed by a user, not only when it is saved.


Here's what Chat GPT suggests (does not work so far):

var graduationYear = parseInt(fields['graduation_year']); // Assuming 'graduation_year' is the name of the graduation year field
var currentYear = new Date().getFullYear();

if (graduationYear && graduationYear <= currentYear) {
fields['estimatedAge'] = currentYear - graduationYear + 22;
} else {
fields['estimatedAge'] = null; // Set the field to null if graduation year is missing or in the future
}


Any suggestions? Thanks so much!

[Kharg]

Variables uses $ in EspoCRM formulas

https://docs.espocrm.com/administration/formula/#variables

[Jakub Grufik]

Hello

Here you go:

ifThen(
    medicalSchoolGraduationYear != null,
    estimatedAge = (datetime\year(datetime\today()) - medicalSchoolGraduationYear) + 22
    );​

Put this into formula of the entity and then recalculate formula for all the records.

Please keep in mind that you have to create fields integers called "medicalSchoolGraduationYear" and "estimatedAge" where you will store those values.

[rabii]

Hey @harrytruman

Here is the code you need to make it work, one thing is i didn't understand how do you get the (22) is this a field in your database if yes then add the field to the age calculation in the formula below, please note that if you have a version which is older than 7.4 then you need to use the first code otherwise use the second code:

Code for Versions less then 7.4

// I assume the field is a date field therefore extract the year from the medicalSchoolGraduationYear like below
$medicalSchoolGraduationYear = datetime\year(medicalSchoolGraduationYear);
// Get the current year
$currentYear = datetime\year(datetime\today());
// calculate the age here
$age = ($currentYear - $graduationYear) + 22;

// Expression used in versions less than 7.4
ifThenElse(
  $medicalSchoolGraduationYear && $medicalSchoolGraduationYear < $currentYear,
  entity\setAttribute('estimatedAge', $age),
  entity\setAttribute('estimatedAge', null)
);​ 

Code for Version 7.4 and above

// I assume the field is a date field therefore extract the year from the medicalSchoolGraduationYear like below
$medicalSchoolGraduationYear = datetime\year(medicalSchoolGraduationYear);
// Get the current year
$currentYear = datetime\year(datetime\today());
// calculate the age here
$age = ($currentYear - $graduationYear) + 22;

// Expression used in version 7.4 and above
if ($medicalSchoolGraduationYear && $medicalSchoolGraduationYear < $currentYear) {
    entity\setAttribute('estimatedAge', $age);
} else {
    entity\setAttribute('estimatedAge', null);
}​ 

Hope this helps

[esforim]

Wow dual version.

btw Harry, you will need to use Workflow to have to update regularly. Many of my Age field don't get update until as I don't edit the record after their date of birth or the following years.

[harrytruman]

Thanks everyone! You guys are awesome. I just tried the formula from Jakub Grufik and it worked. It does require you to update and save the record but that's ok. Once a record is saved, the estimated age displays. Thanks again!!

[Jakub Grufik]

You are welcome man!