Separate Two Dates From One Record For Calendar Page

[kpcollier]

Howdy.

My main table is Work_Order_Table. Customers call us for work to be done, and we create a record in this table to reflect that. After it is created, we assign a technician to it through Tech_Assign dropdown field. This technician updates the record with two date fields - a Measure Date and an Install Date. So, each record in the Work_Order_Table has 2 date fields.

I am trying to display these date fields separately on a Calendar DataPage. The problem is that you can only filter by one date field per record, so I cannot plot both dates on their respective day. 

To fix this, I have created a new table called Tech_Schedule_Table. The fields are Date_ID (autonumber), Event_Type (this will be either measure or install), Date (the actual date of the field), and Technician (the value of Tech_Assigned from Work_Order_Table). I am trying to create a trigger that will split these two dates into their own record on the Tech_Schedule_Table so that I can display them both correctly.

The problem I have run into... The records in Work_Order_Table have some unrelated fields that are updated multiple times a day. I wouldn't want the Install and Measure Date fields to add to the Tech_Schedule table every time that parent record is updated. Secondly, if the Technician needs to change one of the measure/install dates on the Work Order record, I don't want the old dates to show up as well - only the updated ones.

 

Here is what it looks like without splitting the date fields into their own record. Notice that the Install Date is on the wrong date.

 

This is the new table structure. If I need to add more fields, please let me know.

 

 

Here is the trigger I am working on. Need to enhance this so it can replace old dates with new ones and not insert again on unrelated updates to the parent record.

 

[Hastur]

Hello @kpcollier

Am i right that you insert the data into the Tech_Schedule_Table on Update action in  Work_Order_Table?
Also, as I understood, you want to insert the particular set of dates only once to avoid duplicates.

Looking forward to your reply.

[kpcollier]

Hey @Andrew,

Yes, you are correct. I am trying to do it on update, because the tech's do not get assigned until after the record was made. Also yes, if I could somehow have the trigger know if the parent record was updated, to delete the old dates and add the new ones. I wouldn't want dates that are no longer being used to show up.

[Hastur]

Hello @kpcollier

To implement such a workflow you need to adjust the trigger to be able to check whether the needed field was changed.
Check the print screens with examples here - https://dropmefiles.com/Qt9eW

 

[Kurumi]

Hi everyone - Just wanted to share this solution. If you want to save and select Date and Time in (1) Text Field instead of separating them into different fields. You may use an external JS library called flatpickr: https://flatpickr.js.org/getting-started/ 

To apply in the DataPage, follow these steps:

1. Insert Header and Footer. In the Footer, insert the code below. Make sure to disable first the HTML Editor in the Advanced Tab.

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

<!-- jQuery -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
 <!--  Flatpickr  -->
  <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

<script>

$("#DATETIMEFIELD").flatpickr({
    enableTime: true,
    dateFormat: "m/d/Y H:i"
});

</script>

To know the formats, you can check them here: https://flatpickr.js.org/formatting/

enableTime - you are enabling time to the picker
dateFormat - format of the date that will be saved in the table. Caspio only accepts MM/DD/YYYY format in the table. 

If you want to have a different display in the Text Field when selecting Date and Time, you can add altInput. altInput hides your original input and creates a new one.

Upon date selection, the original input will contain a m/d/Y H:i string, while the altInput will display the date in a more legible, customizable format.

Example:

$("#DATETIMEFIELD").flatpickr({
    enableTime: true,
    dateFormat: "m/d/Y H:i",
    altInput: true,
    altFormat: "F j, Y H:i"
});

[Kurumi]

Hi - Just to add, here's a new post to disable the dates in a custom calendar date picker: 

[Kurumi]

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the total worked hours based on Time In and Time Out fields.

Request:
Calculate the total working hours for the following time entries:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

The format should be HH:MM

Insert 'File a leave' if there are no values in:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

Return only the final result.

Result:

Change the 'Clock_In' and 'Clock_out' fields to your right field names.

 

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/