That challenged me, because I would like to have it in the details component as well.
A bit ugly but this is how I solved, to get an age of a person based on his birth date.
I made a new field in the table, in which Birth date is an inputfield. The new field is an equation:
TRUNCATE(DATEDIFF(CURDATE(),{Birth date})/365,0)
I am sure you have a better solution, it is ugly because not every year has 365 days.
Hi @Peter,
You could use the Tadabase Date Utilities pipe to get the age.
Step 1) Install the Date Utilities Pipe
Step 2) Add a number field if you want to display just the years, add a text field if you want to display a detailed age with years, months, and days.
Step 3) Create a table rule and add the pipe. The only parameter you need to include is the Datetime parameter and set it to the date field you want to get the age of.
Step 4) Set the number field or text field accordingly.
It works beautifully, thanks
Thank you for these very clear instructions, Tim! Followup question. I have imported a bunch of records, each of which has a DOB value. I’d like to calculate the Age for all of these and the only way I can see to do it is to edit each DOB value individually and set it to itself. Is there a way to trigger the pipe for all records at once?
Hi @WilliamPorter,
Scheduled tasks!
Pipes can run in a scheduled task giving you the ability to process an entire data table.
Thanks, Tim. I created a scheduled task and ran it. Unfortunately, I made a dumb mistake and forgot to look at my data BEFORE running the task. All the Age values have now been set — but I don’t know if it’s because of the task or whether something happened magically overnight that caused them all to be processed for some other reason. (It’s still close enough to Christmas that I’m willing to believe in things happening magically overnight.)
Does a scheduled task target ALL the records in the table? Is that just the way these work?
Yes, scheduled tasks will trigger on every record in a data table that meets the task condition, or all records in a data table if no condition is set.
Displaying age by simply formatting a DOB field is brilliant and works great. But it returns a value including the word “Years”. Is it possible to show simply the number value, without “Years”? If the column is labeled “Age”, and the values are all integers, users will know that the numbers represent years.
We have a minor update being rolled out soon that will add custom classes to each part of the field.
You’ll then be able to simply add this CSS to the page:
.tb-age-year-text {
display: none;
}
I’ll confirm when this is live.
@tim.young I have a curveball on the original question - how would you calculate age in a custom component table?
Impressive! thank you
@tim.young is it possible to style it like a standard table?
I’d like to do as many of the following as possible:
I’ll probably need to create a new equation field for this in the data table, but that’s fine. Then I can use that field to solve #1.
For context, this is a deal pipeline and the purpose is to track time on the pipeline. So if there is another solution that will achieve the results, that’s fine too.
Are you looking at simply displaying the values dynamically in a custom component, or doing this on the database level?
For 1 and 2, you can create your own helper (with code). I’ll write up something simple and explain, but it’s quite complex.
For 3 and 4 you might need to address this at the database with custom fields, but you can also just display it in the app. Let me know and we’ll be delighted to help.
I’ll try and break it down, but you can first just copy paste.
Paste this code into the Javascript of the page.
Handlebars.registerHelper("james_custom_2", function(application_date, closing_date, status) {
if (status == "Won") {
var application_date = convert_sql_to_js(application_date);
var closing_date = convert_sql_to_js(closing_date);
var totalDays = getDifference(application_date, closing_date)
return '<span style="color:green;"> 🥂' + totalDays + ' 🚀</span>';
} else {
var application_date = convert_sql_to_js(application_date);
var closing_date = new Date();
var totalDays = getDifference(application_date, closing_date)
if(totalDays > 850) {
return '<span style="color:red;">' + totalDays + '</span>';
} else {
return '<span style="color:green;">' + totalDays + '</span>';
}
}
});
function getDifference(date1, date2) {
var Difference_In_Time = date2.getTime() - date1.getTime();
var Difference_In_Days = Math.floor(Difference_In_Time / (1000 * 3600 * 24));
return Difference_In_Days
}
function convert_sql_to_js (date) {
var date = date.split("-");
return jsDate = new Date(date[0], date[1] - 1, date[2].substr(0,2));
}
Next inside the Custom Component, paste this but be sure to update the field IDs everything accordingly.
{{#each records}}
<strong>Application Date: </strong> {{field_34}}
<br>
<strong>Closing Date: </strong> {{field_37}}
<br>
<strong>Status: </strong> {{field_35}}
<br>
<strong>Total: </strong> {{{ james_custom_2 field_34 field_37 field_35 }}}
<hr>
{{/each}}
The key here is that we are using 3 curly braces since we’re responding with HTML.
I’ll try and explain the code a bit:
At the bottom we have 2 functions. One to get the difference in time between 2 dates we pass it. The second, to cover the date from SQL to Javascript.
We created a custom Helper called “james_custom_2” (call it whatever you wish, but be sure to update the custom component to match.
In this custom helper we pass 3 values, the application date, closing date and status. In the rest of the code we first check is the ‘status’ is equal to Won, if yes, it runs its own code. Otherwise, we find the difference and return the values.
I hope that helps.
Let me know if I can help further and I’d be happy to do so.
Regards
Add this CSS to the page and it will not be visible:
.tb-age-year-text {
display: none;
}
That does the trick, Moe. Excellent. Thanks!
@moe -
Holy Cow! I never knew the custom components were built this dynamically. I am impressed (again).
Adam
The custom component is truly a powerful component that can achieve nearly anything imaginable when it comes to outputting the data. It might however require some coding. But HandlebarsJS is pretty easy.