Time & date fields

[AlanMac]

Hi all,

I'm working on a Submission Form that has a 'Shift Date' and 'Start time' and 'End Time' fields. I also need to calculate the number of hours between start & end time.
My problem is the Caspio calendar widget doesn't have a time picker. However after some research I came across an external JS library called flatpickr - there's been a few posts here about it. I understand about inserting the code in the footer, etc. but I'm not sure how this references my field? I can't seem to get it to work. Would someone who's used it successfully be kind enough to explain the steps in how to use it in a form?

 

[CoopperBackpack]

Hello @AlanMac,

I posted a code yesterday regarding the time picker

I have some questions about the workflow to provide the steps.

1) What are the data types of the fields? Is the 'Shift Date' the 'Date/Time' field? Are the 'Start Time' and 'End Time' Text(255) fields?

2) Do you want to add the time in AM/PM or 24-hour format?

3) Does the shift always start and end on the same date?

4) Do you need to store the number of hours between start & end time in the table? And if so, what is the format to store?

For example, the start time is 09:00 AM and the end time is 17:45 PM. Should 8.75 be stored? 
 

I am asking because it is possible to add a custom calendar that allows selecting date and time or just time.

 

[AlanMac]

 

Hi!

Many thanks for getting back to me!

I have two DateTime fields on my database table - Start_time and End_time. 

I think the simplest solution might be to have two input fields on my form - start time & end time - where user picks the the full date & time in each field. Will solve problem of a shift spanning two days. I think 24hr format would be fine. 

Yes, storing the shift length in hours would be useful too - exactly as you described, 8.75 - so number field I guess.

I tried placing that code snippet I found in the footer of a the page, disabled HTML, etc. and referenced the field name but when I previewed the screen, nothing.

Again, many thanks for your help. I was a programmer in a previous life but didn't have much or any experience of JavaScript/CSS/html etc.!

Alan

 

 

[CoopperBackpack]

Hi @AlanMac, 

I agree that it is simpler to have 2 Date/Time fields to select the date with time.

You are correct, the Duration field should have the Number data type.

You can test the following steps:

1) Disable the 'Calendar popup' checkbox for the Date/Time fields. 

 

2) In the Header section disable the HTML editor and add CDNs and styles.

I added styles for the mobile screens as well because the arrows to select hours/minutes by default are invisible on mobile.

In general, this custom calendar has descriptive CSS classes so it can be easily customized. 

 <!--  Flatpickr  -->
<script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">

<style>
.flatpickr-calendar {
    font-family: system-ui !important;
}
.flatpickr-months{
  background-color: #bfbfbf !important;
  padding-bottom: 10px !important;
}

@media (width <= 1024px) {
  .flatpickr-calendar .numInputWrapper span.arrowUp,
   .flatpickr-calendar .numInputWrapper span.arrowDown {
     opacity: 1 !important;
     pointer-events: auto !important;
}
}
</style>

3) In the Footer disable the HTML editor and add the JS code. In my example, the field names are Start_Time and End_Time. I reference these fields in the code in 2 variables. Please use your field names if they are different.  

In this example the US date format is used: MM/dd/yyyy (dateFormat: "m/d/Y H:i:S").

If you have the European Localization and the user inputs the date in dd/MM/yyyy format, then this date format should be applied in the code (dateFormat: "d/m/Y H:i:S")

<script>
document.addEventListener('DataPageReady', customCalendarHandler);

function customCalendarHandler(){

const startTimeField = document.querySelector('input[id*="InsertRecordStart_Time"]'); //replace the field name if needed
const endTimeField = document.querySelector('input[id*="InsertRecordEnd_Time"]'); // replace the field name if needed

startTimeField.flatpickr({
    dateFormat: "m/d/Y H:i:S",
    enableTime: true,
    time_24hr: true
});

endTimeField.flatpickr({
    dateFormat: "m/d/Y H:i:S",
    enableTime: true,
    time_24hr: true
});

}
</script>

4) The Duration field should be the Calculated value with the formula:

DateDiff(minute, [@field:Start_Time], [@field:End_Time]) / 60.0

Use your field names if they are different.  The Calculated value field can be hidden for the user (there is the 'Hide field' checkbox on the Advanced tab).

 

[AlanMac]

Thank you so much - I owe you a beer!

At work now, but I'll be trying it out when I get home tonight and will report back.

It's my first project with Caspio and I really like it, but I can't understand why they haven't integrated something as basic as this into the core functionality? It seems to be a recurring question on here.

Again, many thanks!

Alan

[AlanMac]

2 hours ago, CoopperBackpack said:

2) In the Header section disable the HTML editor and add CDNs and styles.

Sorry what's a CDN? It's just the links to jsdeliver?

I'm on the free plan at the moment until I can get my MVP up & running - can I use this feature?

[CoopperBackpack]

@AlanMac, 

Yes, by CDN I menat the external library. So, this solution should work on the Free plan since doesn`t require additional features, but just JavaScript and CSS.

[AlanMac]

It works perfectly! Thanks so much.

One more thing I'd like to add... to prevent the user selecting a date/time for End_time that is prior to Start_time. Otherwise I end up with the possibility of a minus value in my Duration field.

I'll take a look at the documentation for flatpickr and maybe I'll figure it out.  If you happen to know off the top of your head maybe you could let me know?

Anyway, thanks again for you help. I can push on with my project!

Alan

 

 

[CoopperBackpack]

Hi @AlanMac, I am glad that it works. 

I checked the flatpickr documentation and as I can see it allows disabling dates but not dates+time. I hope I didn`t miss anything.

Disabling dates and times requires a complex script since it should work for different scenarios. For example, the user can select the end time first, etc.

The easiest way is to prevent the submission of the incorrect duration value. For example:

If this works for you, you can test the updated version:

1) In the Header add a line of the code to enable the library for the pop-up messages:

<script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>

2) In the Footer add the code to validate the duration. It should be added before the </script> tag.

In the example, the error is displayed when the duration is less or equal to 0. The condition can be changed. 

document.addEventListener('BeforeFormSubmit', function(event) { 

    const durationField = document.querySelector('input[id*="InsertRecordDuration"]'); // use your field name
    let durationValue = parseFloat(durationField.value);
       if(durationValue <= 0){
             event.preventDefault();
             Swal.fire( '', 'The duration is incorrect. Check the Start and End date/time', 'error' ); //message can be customized
        }
})

 

If you have further questions, please let me know.

[Kronos]

Hi everyone,

Just leaving this here just in case it might help:

HowTo article: Separating Input Fields for Date/Time Parts

[AlanMac]

Hi @CoopperBackpack, thanks for getting back! I must not have enabled notifications so didn't realize you posted an answer - been pushing on with other parts of my project. I will try the code you posted this weekend and let you know how it worked. Have a great St Patrick's weeked!

[C5N0AL]

Hi @CoopperBackpack

Thank you for providing the quote for the date + time picker, that has helped eliminate more head scratching! 

I am currently trying to build a weekly timesheet where employees will complete the timesheet at the end of each week. Thus, I have 7 start time / finish time fields. I have implemented your code as explained and it works perfectly - but only for the first fields. Is there a way I can apply the code to all Start / Finish time fields?  I have attached a screenshot of the timesheet so far for reference.

Also, I have some options for Holiday / Absence to be selected in the job reference.  Is there a way that if these options are selected, the start/finish times does not need to be inputted?

I am a novice with this and only started using the 'software' yesterday so any guidance would be much appreciated. 

Many Thanks

Callum

[CoopperBackpack]

Hello @C5N0AL,

I am glad that the post was helpful.

1) As for the Holiday / Absence options, please check the so-called Rules described in this article https://howto.caspio.com/datapages/forms/conditional-forms/

You can set a condition to disable or hide the fields. For example: 

Please pay attention to the criteria (AND / OR). For your use case, the condition is: If Job_No is Holiday OR Absent, hide/disable fields.

 

2) The code should be modified to apply a calendar for multiple fields.

 

 

The changes are applied to the code in the Footer. The code in the Header is as suggested above.

For example, I have these fields:

 

The code:

<script>
document.addEventListener('DataPageReady', customCalendarHandler);

function customCalendarHandler(){

const dateFieldsIds = '#InsertRecordmonday_start_time, #InsertRecordmonday_finish_time, #InsertRecordtuesday_start_time, #InsertRecordtuesday_finish_time';

document.querySelectorAll(dateFieldsIds).forEach(field => {
     field.flatpickr({
         dateFormat: "m/d/Y H:i:S",
         enableTime: true,
         time_24hr: true
        });
    })
}
</script>

 

Please modify the 'dateFieldsIds' variable. It is a list of IDs of the Date/Time fields for which the calendar with time should be applied. It is a comma-separated list enclosed in single quotes.

If you have any further questions, please feel free to update this thread.