# Best way to create a calculated field

**URL:** <https://www.five.org/t/best-way-to-create-a-calculated-field/292>\
**Category:** How do I?\
**Created:** [December 7, 2023, 11:08pm UTC](https://www.five.org/t/best-way-to-create-a-calculated-field/292 "2023-12-07T23:08:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![leocarr](https://avatars.discourse-cdn.com/v4/letter/l/71e660/32.png) [@leocarr](https://www.five.org/u/leocarr)\
**Post date:** [December 7, 2023, 11:08pm UTC](https://www.five.org/t/best-way-to-create-a-calculated-field/292/1 "2023-12-07T23:08:08Z")

</div>

I want to create a calculated field within a table. I’ve tried to use a query to do this but it doesn’t work. I think I’m using the wrong function to get the date / year from the date. The error message suggests I read the user manual but I honestly do not know where to find the manual

SELECT  
`Participant`.`firstname`,  
`Participant`.`lastname`,  
`Participant`.`gender`,  
`Participant`.`DOB`,  
YEAR(GETDATE())-YEAR(`Participant`.`DOB`)-5 AS `Participant`.`schoolyear`,  
`Participant`.`house`  
FROM  
`Participant`

---

<div class="post-metadata">

**Author:** ![Pranoy](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/pranoy/32/281_2.png) [@Pranoy](https://www.five.org/u/Pranoy)\
**Post date:** [December 8, 2023, 1:06am UTC](https://www.five.org/t/best-way-to-create-a-calculated-field/292/2 "2023-12-08T01:06:43Z")

</div>

Hi leocarr,

If you’re interested in creating a calculated field, I recommend checking out this blog: [Calculating Field Values In Five | Five](https://five.co/blog/calculating-field-values-in-five/).

It provides a guide on writing a custom JavaScript function that can perform calculations and be integrated with fields.

---

<div class="post-metadata">

**Author:** ![leocarr](https://avatars.discourse-cdn.com/v4/letter/l/71e660/32.png) [@leocarr](https://www.five.org/u/leocarr)\
**Post date:** [December 8, 2023, 1:21am UTC](https://www.five.org/t/best-way-to-create-a-calculated-field/292/3 "2023-12-08T01:21:37Z")

</div>

Is there anyway to do this in SQL? I am a high school teacher, teaching this software to a info systems class. They are not familiar with javascript and when I previously taught Access we could do this using SQL.

---

<div class="post-metadata">

**Author:** ![mark](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/mark/32/300_2.png) [@mark](https://www.five.org/u/mark)\
**Post date:** [December 9, 2023, 6:54pm UTC](https://www.five.org/t/best-way-to-create-a-calculated-field/292/4 "2023-12-09T18:54:28Z")

</div>

Hi,

It can be done in SQL as follows:

SELECT  
FirstName,  
LastName,  
Gender,  
DOB,  
YEAR(NOW()) - YEAR(DOB) - 5 AS SchoolYear,  
House  
FROM  
Participant

The issue with your SQL is the use of GETDATE() which isn’t available in MySQL. I have replaced this with NOW()

I have set up a sample application for you with the table, a standard form, the above query, a data view and a form using the query as the data source which I am happy to share with you. Please let me know which version of Five you are running which you can get by clicking on the profile button in the top right hand corner and then clicking on the Five logo.

You will be glad to know there is not one line of JavaScript in the applcation!

Best regards

Mark