Creating custom view with field derived from a database field

[munhin]

Hi,

I have a Date-Of-Birth (DOB) field in the database and I hope to add extra field in the client view to show the Age, that is computed using PHP from this DOB field.
Is there an easy way from within Formtools admin page, to create this kind of derived field?
The api ft_api_show_submissions() looks like something I can use, but I'm not sure how to go about it. Any advice?

Many Thanks,
Mun Hin

[Ben]

Hi Mun,

Neat! Sure, you could do this using the Submission Pre-Parser module.

That module lets you execute your own custom code on the submission data right before it gets added to the database, and right before it's updated.

In your case, you could use it to examine the contents of the DOB field, calculate the age, then store the age in a new field in the database (you could just create it in the Edit Form -> Database tab - you wouldn't need to actually add it to your original form).

The actual code would depend on the format that the DOB field has. Note: that field has to be consistently entered. If it's just a text input field in your form, this won't work: the code would require a format like YYYY/MM/DD or something like that.

Maybe give the Submission Pre-Parser module documentation a look over to get an idea of how it works, then post me back if you need a hand with the PHP.
http://modules.formtools.org/submission_pre_parser/

- Ben

[munhin]

Hi Ben,

Thanks for the advice! Managed to get it to work using Submission Pre-parser module .
Both my DOB (DD/MM/YYYY) and Age field are in textfield format. My code is as below, for those interested.

PHP Code:

if ( (isset($_POST["dob"]) && !empty($_POST["dob"])) )
{
  $dob = floor((time() - strtotime($_POST["dob"]))/ 31556926);
  $_POST["Age"] = "{$dob}";
} 


A preferred approach will be not to modify the database but just create a view that derives the age from the DOB column in database. Feature suggestion: A module to perform a "Display pre-parser" to display any derived info from the database will be very useful.

Thanks again & Best Regards,
Mun Hin

[Ben]

Nice idea! And I'm glad you got it working.

- Ben