BjornSkifs Posted June 12, 2012 Report Share Posted June 12, 2012 I have five dates and want to display the earliest in a calculated field. I though I could use the following: Min([@field:Date1],[@field:Date2])to pick one of two dates. It doesn't work. Any thoughts on how I should solve this?I'm very grateful for any suggestions. /Bjorn Quote Link to comment Share on other sites More sharing options...
1 PotatoMato Posted July 29, 2021 Report Share Posted July 29, 2021 Hello, just to update this post, you can also use a CASE WHEN formula. CASE WHEN [@field:1Date] < [@field:2Date] THEN '[@field:1Date]' WHEN [@field:2Date] < [@field:1Date] THEN '[@field:2Date]' ELSE 'No Results' END -Potato Quote Link to comment Share on other sites More sharing options...
0 MayMusic Posted June 12, 2012 Report Share Posted June 12, 2012 Hi Bjorn, You can have a java script on your page to get both dates and return the earlier one in another field: function whichdate() { var firstDate=new Date("[@field:FirstDateFieldName*]"); var SecondDate=new Date("[@field:SecondDateFieldName*]"); if (firstDate > SecondDate) { document.getElementById("Id of the field you would like to show this date on").value = SecondDate; } else { document.getElementById("Id of the field you would like to show this date on").value = firstDate; } } whichdate(); You need to replace all IDs in the script with valid field IDs. Cheers :!: :!: :!: Quote Link to comment Share on other sites More sharing options...
0 BjornSkifs Posted June 12, 2012 Author Report Share Posted June 12, 2012 Beautiful! Thank you so much for your help MayMusic! Quote Link to comment Share on other sites More sharing options...
0 BjornSkifs Posted June 13, 2012 Author Report Share Posted June 13, 2012 I still have a question regarding this. I don't get it to work. Having experimented a bit I realized that I'm doing something wrong with the field id. This is my code (in a HTML Block): <script> var FirstDate=new Date("[@calcfield:1]"); var SecondDate=new Date("[@calcfield:2]"); document.write(FirstDate); document.getElementById("EarliestDate").value = FirstDate; </script>Everything works as expected until the last line: I have EarliestDate as display only, but it shows up empty when I run the code, whereas FirstDate is nonempty.Obviously, I'm making some kind of silly mistake here, but I can't figure out what it is. Again, I would be really grateful if anybody could help me out. Thanks! Bjorn Quote Link to comment Share on other sites More sharing options...
0 MayMusic Posted June 13, 2012 Report Share Posted June 13, 2012 What is the URL of the page? Since the form element is display only there is no ID for the field to refer to. You need to change the form element to either hidden field or text field. If you would like to have it as disabled then you can use this line of the code to gray out the date text field: document.getElementById("EditRecordEarliestDate").disabled="true"; Quote Link to comment Share on other sites More sharing options...
0 BjornSkifs Posted June 14, 2012 Author Report Share Posted June 14, 2012 I tried with a text field, but it didn't work out either. I'm not comfortable with leaving the URL here so I opened a support ticket instead. I'll report back what the solution is. Thanks for your help MayMusic! Quote Link to comment Share on other sites More sharing options...
0 BjornSkifs Posted June 16, 2012 Author Report Share Posted June 16, 2012 Ok, after having changed to a text field, it turned out that I simply missed the red part in the code below: document.getElementById("EditRecordEarliestDate").value = FirstDate; Thanks to MayMusic and the people at Caspio who helped me! Quote Link to comment Share on other sites More sharing options...
0 BjornSkifs Posted July 12, 2012 Author Report Share Posted July 12, 2012 Just to report back how I did. It's probably not the most beautiful code, and may contain some redundancies, but maybe it can help somebody. After som experiementation I put this into an HTML block below the EarliestDate field. (Note that the variable names are a bit awkward. EarliestDate is both a javascript variable and a Caspio field.) I also added a section to present the date in Swedish date format, or, more accurately, Caspio support helped me with this. <script> var dates = [ new Date("[@calcfield:1]"), new Date("[@calcfield:2]"), new Date("[@calcfield:3]"), new Date("[@calcfield:4]"), new Date("[@field:Date5*]")]; var EarliestDate=''; for (i=0; i<5; i++) { if (isNaN(dates[i].getTime())) { dates[i]=''; } else { EarliestDate=dates[i]; } } for (i=0; i<5; i++) { if (dates[i]<EarliestDate && dates[i] !='') { EarliestDate=dates[i]; }; } var ar=(EarliestDate.getFullYear()).toString(); var man=(EarliestDate.getMonth()+1).toString(); var dag=(EarliestDate.getDate()).toString(); if (man<10) { man='0'+man; } if (dag<10) { dag='0'+dag; } if (EarliestDate == '') { document.getElementById("EditRecordEarliestDate").value = ''; } else { document.getElementById("EditRecordEarliestDate").value = ar+"-"+man+"-" +dag; } </script> Quote Link to comment Share on other sites More sharing options...
0 Kurumi Posted April 29, 2019 Report Share Posted April 29, 2019 You could try using a SQL query. For the latest entry, you can try MAX() function. For more information about the function, you may check this site: https://www.w3schools.com/sql/sql_min_max.asp Quote Link to comment Share on other sites More sharing options...
Question
BjornSkifs
I have five dates and want to display the earliest in a calculated field. I though I could use the following:
to pick one of two dates. It doesn't work. Any thoughts on how I should solve this?I'm very grateful for any suggestions.
/Bjorn
Link to comment
Share on other sites
9 answers to this question
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.