Working With Calculated Age From Date Of Birth

[clicknip]

I have a table that includes the person's date of birth (DOB). I am able to create a virtual field in my Report DataPage that calculates the age from the DOB and display it. I also need to be able to search on age. How can I do searches such as:

 

1. Age is equal to x

2. Age is greater than x (minimum age is x)

3. Age is greater than minimum x and less than maximum y

 

Thank you for any help on this. The application has to do with booking models, where age is an important factor.

[MayMusic]

Do you use a calculated field to show the age? Or you have a java script?

 

You need to save the age in a field so you can search on it !!!

[clicknip]

I'm using a calculated field. I thought I saw something about searching on a calculated field, but yes, it makes sense to have to save it. How would I do that? I haven't figured out how to do that kind of thing in Caspio yet.

[Jan]

Hello clicknip,

 

I think, you can use a Java Script code on your Search Page.

I have written the code for "Age is greater than x (minimum age is x)" condition, but it can be easy edited.

 

Please follow these steps:

1) On the "Select Search Fields" step, add the field that contains birthday (I have named it "birthday").

2) On the "Configure Search Fields" step: 

2a) Add a virtual field (in my case, it is Virtual1).

2b) Select the "birthday" field:

2b*) In the "Form element" dropdown, select the "Hidden" item;

2b**) In the "Comparison type" dropdown, select the "Less than or Equal" item;

2b***) In the "Precision" dropdown, select the "Year" item.

2c) Add a Header&Footer element, select the "Footer", click the Source" button and enter the following code:

<SCRIPT LANGUAGE="JavaScript"> 
  function setDate()
  {
   var v_Age = document.getElementById("cbParamVirtual1").value; 
   var nDate = new Date(); 
   var current_year = nDate.getFullYear();
   var calculated_year = current_year - v_Age;
   var birth_year = new Date (nDate.setYear(calculated_year));
   var d_month = birth_year.getMonth() + 1;
   if (d_month<10) {d_month = "0" + d_month;}
   var d_day = birth_year.getDate();
   if (d_day<10) {d_day = "0" + d_day;}
   var d_year = birth_year.getFullYear();
   var v_Result = d_month + "/" +  d_day + "/" + d_year;
   document.getElementById("Value2_1").value = v_Result;
  }
  
  document.getElementById("caspioform").cbParamVirtual1.onchange=setDate;
</SCRIPT>

Please enter correct name of the "birthday" field instead of "Value2_1" in the line:

document.getElementById("Value2_1").value = v_Result;

 

Rule for the name is:

-   ValueX_Y

-   X is the form element order, which starts at 1 and increments based on the order of the element in the form.

 

Please note, if there is more than one Virtual field, insert the your Virtual field instead of "cbParamVirtual1" variable in lines:

var v_Age = document.getElementById("cbParamVirtual1").value; 

document.getElementById("caspioform").cbParamVirtual1.onchange=setDate;

 

For example, if the name of the field is Virtual3, then enter cbParamVirtual3 in code.

 

I hope, it helps.

[FinTheHuman]

Hello,

Just wanted to inform you about the new feature in Caspio.
Check this documentation in Caspio’s Help center: https://howto.caspio.com/release-notes/caspio-bridge-13-0/
This release includes a new feature of Calculated value in submission form option that allows you to use a calculated field in one of your field that is saved in your table. 
However, this feature only available in the ‘Submission DataPage’.
You can also check this documentation about calculated value: https://howto.caspio.com/datapages/datapage-components/calculated-values/ 

[ManokNaPula]

I suggest you create a field first for the Age so you won't have to do the Javasctipt anymore.

When submitting a new Data for a new User per se, you can create a Calculated Value for the Age (that will be saved to the table)

The formula would go like this: 

DateDiff(year, [@field:DOB], GetUTCDate())

 

And from your search form, you can create the criteria depending on the logic you want. 

 

All of your requirements perfectly fits with Caspio. This can be done simply without any code.

 

Let me know if this works for you =) 

[ManokNaPula]

Hey there again. Just to improve my previous comment about calculation for the "Age", I had some observations regarding DateDiff year.

24 minutes ago, Scarlet said:

Just to add to the previous comment, I just noticed this recently,

When a Field for Birthdate is equal to, say December 2018, and it is now January 2019, using "Datediff(Year,[@field:Birthday],GetUTCDate())" as a formula would give me a "1 year" as result, when in fact it should not. I guess it's because I used "Year" as the DateDiff option.

What I did was "Datediff(Month,[@field:Birthday],GetUTCDate())/12", it did somehow responds to the age correctly, but when my birth month comes, it adds to my age. I can accept that tho.

So just in case someone encounter the same problem, I hope this helps!  

Just sharing 

[Kurumi]

Hi - you may check this new post for other formula:

 

[Wikiwi]

You can also get the age from date of birth using triggers/task. the forum post below tackles this kind of use case.

 

[RuisiHansamu]

You may also want to check on this similar forum post: