Date Calculations - Age of an asset

[james]

@tim.young is it possible to style it like a standard table?

I’d like to do as many of the following as possible:

1. Fix the output to days only (no months) and remove “days ago” so the column only contains a number
2. Turn the text red if > 30 days
3. Stop counting and retain the final value if a record drop down field is marked “Lost” or “Hold”
4. If the deal is marked “Won” stop counting and set the final value to the difference between “Application Date” and “Close Date”

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.

[moe]

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.

[moe]

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:

1. 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.

2. We created a custom Helper called “james_custom_2” (call it whatever you wish, but be sure to update the custom component to match.

3. 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

[moe]

@WilliamPorter

Add this CSS to the page and it will not be visible:

 .tb-age-year-text {
    display: none;
}

[WilliamPorter]

That does the trick, Moe. Excellent. Thanks!

[SafetyUniversity]

@moe -

Holy Cow! I never knew the custom components were built this dynamically. I am impressed (again).

Adam

[moe]

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.