Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet

  • RoSBC
    Asked on May 25, 2020 at 12:42 PM
    Also with the Appointment field is it possible for it to report Date and time in different fields into spreadsheet?
  • Sam_G
    Replied on May 25, 2020 at 12:55 PM

    Hi RoSBC,

    I have cloned your form and checked the integration for the Google spreadsheet.

    The date and time are in separate fields.

    Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet Image 10

    Guide: https://www.jotform.com/help/228-How-to-Integrate-Forms-with-Google-Sheets

    Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet Image 21

    I hope this helps.

    Let us know if you need further assistance.


  • RoSBC
    Replied on July 25, 2020 at 7:30 AM

    I have just returned to this question. As far as I can see with my view I do not get the date and time separated as in your example.

    It certainly does not appear as such in Excel reports. The date and time fields do not appear.

  • Amin JotForm Support
    Replied on July 25, 2020 at 9:30 AM

    Could you please allow me some time to closely look into this?

    I promise I will be right back the soonest I can.

    Your patience is most appreciated.

  • RoSBC
    Replied on July 25, 2020 at 10:02 AM

    I realise that I have deleted the original form. Here is the current form. The problem is the same.

    https://eu.jotform.com/build/201712789401352

  • Amin JotForm Support
    Replied on July 25, 2020 at 10:14 AM

    A pretty neat workaround to achieve what you're after goes as this:

    1. Add 4 new fields/widgets to your form as follows:

    1595685156Screenshot 2020 07 25 The Easi Screenshot 10

     - Calculation widget (bridge); this is just used to trigger the conditional logic we will create later on.

    - A normal Short Entry text field (bridge2); we will populate it with the entire appointment string.

    - A Substring widget; we will use to subtract date from the text field above (bridge2).

    - Another Substring widget; we will use to subtract time from the text field above (bridge2).

    2. Create the conditional logic below. It's pretty self-explanatory.

    1595685079Screenshot 2020 07 25 The Easi Screenshot 21

    3. Configure the both Substring widgets as follows:

    The one for the date:

    1595685313Screenshot 2020 07 25 The Easi Screenshot 32

    The one for the time:

    1595691857Screenshot 2020 07 25 The Easi Screenshot 43

    Note that (#input_9) is the field ID for the bridge2 field. It may vary in your form. To get yours, pay this guide a visit.

    You may even hide these two fields and pass their values to simple Short Entry fields to give them labels to be properly shown under the Sheet/Excel with column labels (Date/Time).

    1595685834Screenshot 2020 07 25 The Easi Screenshot 54

    1595685848Screenshot 2020 07 25 The Easi Screenshot 65

    Well, let's see if all that works. It does!

    1595686151Screenshot 2020 07 25 Form(2) Screenshot 76

    Final result with the integrated sheet:

    1595686063screenshot docs Screenshot 87

    Of course all these fields will be hidden on the form. Meaning, they will not be shown on the form. We just use them to run in the back-end of your form. Also, you may hide all unwanted columns within the Sheet with no issues at all.

    My demo form: https://form.jotform.com/202064346299054

  • RoSBC
    Replied on July 27, 2020 at 10:36 AM

    Thanks I will tackle it tomorrow.

    Am I able to hide the extra fields? It would confuse my members if the form had extra bits. Sometimes filling in the form as it is, causes some problems!!

  • tomiko_ Jotform Support
    Replied on July 27, 2020 at 12:11 PM

    Sure, you can hide the additional fields in the field properties:

    1595866279Kapture 2020 07 27 at 20 Screenshot 10

  • RoSBC
    Replied on July 28, 2020 at 9:44 AM

    Hi

    This is my attempt at the workaround, but I had problems.

    Firstly the field details do not appear on any of my forms. I am unable to scroll further down as there appears to be no information there.

    Do I need to put extra fields in for Date and Time? If so, where

    This is the form I have been working on.

    https://form.jotform.com/201805122127341

    Any help much appreciated.

  • Ashwin JotForm Support
    Replied on July 28, 2020 at 11:10 AM

    Firstly the field details do not appear on any of my forms.

    Do you mean to say that you are unable to go to field's property window to hide it? Please check the screenshot below on how to go to field's property window:

    1595948899property Screenshot 10

    Do I need to put extra fields in for Date and Time? If so, where

    If you are following my colleague @Amin_N, you can add the date and time field in any location of your form and hide it. 

    Hope this helps.

    Do get back to us if you have any questions. 

  • RoSBC
    Replied on July 29, 2020 at 6:27 AM

    The example is moving all over the place and when I try to click the down arrow for Field details it dissapears. It is no help at all.

    I cannot move further down the page to see Field Details. There is nothing there.

  • RoSBC
    Replied on July 29, 2020 at 6:48 AM

    I have had another go as I have found the field details

    Please could you look at my file to see where I have gone wrong.

    https://eu.jotform.com/build/201712789401352

    Thanks

  • RoSBC
    Replied on July 29, 2020 at 7:23 AM

    I have cracked it!

    But because the number of letters in a month varies from month to month, the splitting of the time from the date is in a different place each month.

    How can this be sorted?


  • Sam_G
    Replied on July 29, 2020 at 9:57 AM

    Hi RoSBC,


    Let me work on this and will get back to you with an update.

  • Sam_G
    Replied on July 29, 2020 at 1:26 PM

    Hi RoSBC,

    Thank you for your patience.

    You can clone my form.

    https://form.jotform.com/202103595108952

    It should show like this in Google Sheets:

    Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet Image 10

    I hope this helps.

    Let us know if you need further assistance.

  • RoSBC
    Replied on July 29, 2020 at 2:57 PM

    Thanks, but how do I clone the form. It just opens as a form to fill in with no menus etc around it.

  • Sam_G
    Replied on July 29, 2020 at 4:09 PM

    Hi RoSBC,

    You can clone the form by following below:

    https://form.jotform.com/202103595108952

    Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet Image 10

    https://www.jotform.com/help/27-How-to-Clone-an-Existing-Form-from-Your-Account

    Let us know if you need further assistance.


  • RoSBC
    Replied on July 30, 2020 at 2:48 AM

    I do clone, but you have changed things recently!

    However on doing the above

    I get this message.

    Unauthorized request. You do not have access to this form.
  • Ashwin JotForm Support
    Replied on July 30, 2020 at 4:08 AM

    I have gone ahead and cloned my colleague's form in your account. You should be able to see form "Clone of Clone of Rhos on Sea Bowling Club" in your account. 

    Hope this helps.

    Do get back to us if you have any questions. 

  • RoSBC
    Replied on July 30, 2020 at 5:58 AM

    Thanks

    I still have the same problem with the splitting of date and time, because it is a fixed length it does not take into account the number of letters in the month! This varies from month to month. Look back to posting of mine above Answered on July 29, 2020 at 07:23 AM

    Is it possible to separate/split up the IDs as I notice When has #input_4_date, #input_4_duration, #input_4_time zone. These would not be fixed lengths.

  • Ashwin JotForm Support
    Replied on July 30, 2020 at 7:35 AM

    Unfortunately it is not possible to split the value of the appointment field with the ids. Splitting the values based for months and different days of week will be challenging. Though we can try to add condition to first look for months and days but the same issue will be for different time slots as well.

    Please note that there is no built-in feature to achieve this. 

  • RoSBC
    Replied on July 30, 2020 at 1:21 PM

    It seems a shame that whoever built the Form (for Zoom) that did not think about this during the initial setting up

    Thanks for your help. I will now abandon the idea and we will just cope as we had been doing.

    Disappointed.


  • Welvin Support Team Lead
    Replied on July 30, 2020 at 2:46 PM

    We apologize for the inconvenience.

    I have escalated this ticket to our developers as a feature request for separating the entry of the new Appointment field into different cells in Excel Report and Google Spreadsheet. We cannot have you an ETA of this request, but as soon as we have an update, you will be notified here.

    I have noted the elements as follows:

    ~Day

    ~Date

    ~Duration

    ~Timezone

    You've mentioned Zoom integration. The Appointment field is optimized for Zoom. You can see the date and duration are two separate options on the dropdown:

    Appointment Field: the option save the elements to different cells in Excel Report and Google Spreadsheet Image 1 Screenshot 20

    Do you need anything else from the appointment field? I mean, do you need every piece of the appointment field to be mapped on Zoom fields? Please clarify. We might create a separate ticket for this.

    Thanks

  • RoSBC
    Replied on July 31, 2020 at 3:51 PM

    no. What I was meaning was that I used the Zoom template to create my forms.

    All I need are the Day, Date and Duration.

    I am using the form for booking times on a Bowling Green. I use the excel sheet and sort to find that day's booking, then I paste it into a word document, as I have to keep a daily record. It is to do with track and trace with Covid 19.

    I would love it to do the page each day automatically without having to fudge it!

    I have mentioned this before in another question to Support and it was supposed to have been sent as a suggestion.

    Q:

    Collection form for appointments booked to go to a pdf/spreadsheet

    If Zoom integration can split the When field up why can't it be done simply in Excel?

    What is different?

  • Welvin Support Team Lead
    Replied on July 31, 2020 at 5:07 PM

    Technically, you do not need to separate things out in the form. All you have to do is to add the Appointment field and map it appropriately to your Zoom fields. The field is already optimized for Zoom integration.

    As for the external sheet/report/integration, it is not yet possible hence the request ticket that I've raised. We'll keep you posted on that.

    As for your previous question, I see my colleagues have answered there.

    Here's that ticket: https://www.jotform.com/answers/2342543.

    Based on the description, I believe our calendar report is best suited for your goal. However, the appointment field is not yet optimized for the calendar report. I'll create a new request ticket about it through this link: https://www.jotform.com/answers/2485080.

    Thanks