Best way to create a calculated field

How do I?
1

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

2

Hi leocarr,

If you’re interested in creating a calculated field, I recommend checking out this blog: Calculating Field Values In Five | Five.

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

3

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.

4

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