-
RoSBCAsked on May 25, 2020 at 12:42 PM
-
Sam_GReplied 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.
Guide: https://www.jotform.com/help/228-How-to-Integrate-Forms-with-Google-Sheets
I hope this helps.
Let us know if you need further assistance.
-
RoSBCReplied 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 SupportReplied 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. -
RoSBCReplied 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 SupportReplied 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:
- 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.
3. Configure the both Substring widgets as follows:
The one for the date:
The one for the time:
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).
Well, let's see if all that works. It does!
Final result with the integrated sheet:
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
-
RoSBCReplied 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 SupportReplied on July 27, 2020 at 12:11 PM
Sure, you can hide the additional fields in the field properties:
-
RoSBCReplied 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 SupportReplied 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:
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.
-
RoSBCReplied 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.
-
RoSBCReplied 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
-
RoSBCReplied 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_GReplied 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_GReplied 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:
I hope this helps.
Let us know if you need further assistance.
-
RoSBCReplied 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_GReplied on July 29, 2020 at 4:09 PM
Hi RoSBC,
You can clone the form by following below:
https://form.jotform.com/202103595108952
https://www.jotform.com/help/27-How-to-Clone-an-Existing-Form-from-Your-Account
Let us know if you need further assistance.
-
RoSBCReplied 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.
-
Ashwin JotForm SupportReplied 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.
-
RoSBCReplied 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 SupportReplied 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.
-
RoSBCReplied 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 LeadReplied 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:
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
-
RoSBCReplied 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 LeadReplied 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