Set End Date of Financial Year based on entered Contract start date

[vanderLeest]

I want to insert a the next 30th of June as a field value in a online form, based on the entered start date of a contract

e.g. is the start date is earlier than 30 June of the current year, the  value I need is 30 June this year, if the start date falls in July or later, is should be 30 June next year.

(I need this date to do further calculations for milestone periods and budgets)

I have tried to modify a few scripts dealing with related issues, but I cannot seem to make it work for this problem, I get either the wrong date format or no calculation/result at all

Any help sincerely appreciated.

[MayMusic]

In a calculated field on your report or details page you can have:

CASE WHEN 

[@field:Date] < CONVERT (DATETIME, '6/30/'+CAST (YEAR(getdate()) AS varchar) , 101)

THEN  '6/30/'+CAST (YEAR(getdate()) AS varchar)

ELSE  '6/30/'+CAST (YEAR(getdate())+1 AS varchar)

END

 

[@field:Date] needs to be selected from picker and is the entered date . Note since we need to use GETDATE() and it is a Nondeterministic function according to https://howto.caspio.com/tables-and-views/data-types/formula-fields/

I could not apply the formula in a Formula field in table design.

[vanderLeest]

Thanks for your quick answer, MayMusic

I sadly could not implement your suggestion as I need to calculate the  End Date of Financial Year on a submission form, and a Caspio submission form does not have a calculated field option.

I cobbled together (after much trial and error) a bit of javascript and added it to the Footer of the form:

<SCRIPT>

function setEndDateValue()
                { 
    var today = new Date();
    var entered = document.getElementById("InsertRecordStartDate").value;
    if (entered.length > 0) {
        today = new Date(Date.parse(entered));
        var mm = today.getMonth()+1;
        var yyyy = today.getFullYear();
}

if (mm > 6) {
       var yyyy = today.getFullYear()+1;
} else {
       var yyyy = today.getFullYear();
}

document.getElementById("InsertRecordMonths").value = mm;
document.getElementById("InsertRecordFinDate").value = '06' + '/' + '30' + '/' + yyyy;
}

document.getElementById("caspioform").onsubmit=setEndDateValue;

</SCRIPT>

 

[cheonsa]

Hi @vanderLeest,

In Caspio Release 13.0, we added new feature called Calculated Value. The Calculated Value form element generates dynamic calculations that automatically update as users interact with your form. It means, you can now perform calculations once the value has been entered in a field in a Submission Form or Details Page. You may refer to this link for more information: https://howto.caspio.com/datapages/datapage-components/calculated-values/

To help you with available formula, here is our function reference: https://howto.caspio.com/function-reference/

 

-kristina

[vanderLeest]

Thanks for the tip