Jump to content
  • 0

Separate Two Dates From One Record For Calendar Page




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.


Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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>


    enableTime: true,
    dateFormat: "m/d/Y H:i"


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.


    enableTime: true,
    dateFormat: "m/d/Y H:i",
    altInput: true,
    altFormat: "F j, Y H:i"
Link to comment
Share on other sites

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.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...