Jump to content

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


Recommended Posts

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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>

 

Link to post
Share on other sites
  • 1 year later...

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...