vanderLeest Posted January 25, 2018 Report Share Posted January 25, 2018 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. Quote Link to comment Share on other sites More sharing options...
MayMusic Posted January 25, 2018 Report Share Posted January 25, 2018 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. Quote Link to comment Share on other sites More sharing options...
vanderLeest Posted January 28, 2018 Author Report Share Posted January 28, 2018 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> Quote Link to comment Share on other sites More sharing options...
cheonsa Posted October 6, 2019 Report Share Posted October 6, 2019 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 Quote Link to comment Share on other sites More sharing options...
vanderLeest Posted October 6, 2019 Author Report Share Posted October 6, 2019 Thanks for the tip Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.