How to pick the earliest of several dates?

[BjornSkifs]

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

[PotatoMato]

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

[MayMusic]

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 :!: :!: :!:

[BjornSkifs]

Beautiful! Thank you so much for your help MayMusic!

[BjornSkifs]

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

[MayMusic]

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";

[BjornSkifs]

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!

[BjornSkifs]

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!

[BjornSkifs]

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>

[Kurumi]

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