See time and data when submission was edited.

Hello,

We allow visitors to return and edit their form.

We want to know the date and time they did so.

We have a date/time field called

"Thank you for your edits. Please enter the current date and time."

However, if they make a mistake, that confuses our data.

Is there a way we can automatically capture the date of their submission? We would want that to be updated each time they edit, not just capture the time of the original form completion. (We already have a hideen "Date" field at the top, and my understanding is that it captures the date/time of the initial submission but is not updated after that. Is that correct?)

Thanks!

Hi there,

Thanks for reaching out to us!

This is possible as follows:

1) Add the Get Form Page URL widget.

2) Add a Date Picket and set Time to CURRENT.

3) Create the condition below.

4) Done! When the user finished editing, you will be successfully able to see the exact date and time when the submission was edited as shown below.

Related guide: How-to-show-or-hide-fields-base-on-users-answer

I hope I have been of any help.

Thank you for the help. This has been on the back burner while we focused on other things. Eager to get it cleared up now.

Created this sandbox form for experimenting https://www.jotform.com/build/213107489534054/publish

Integrated into this google sheet: https://docs.google.com/spreadsheets/d/16PNXcI45WfxG8JtjmTMQMVhjrScRKIeovnoEqm2r9b8/edit?usp=sharing

As you will see, I changed the date format for submissions so it matches Date of Edit field.

Testing

I created a response with name = 31a

Edited it and changed name to 31b when I did

I see row 5 of the sheet with the changed name and the Edit Date being two minutes after Submission Date. That is all fine.

Questions. Lots of questions.

1. I understand that editing the form edits the existing submission row on the google sheet and adds the Edit date.
2. Is my understanding correct that Jotform will only add rows to the Google sheet until it hits 10k rows? Is there any way for us to increase that on our account or forms? Since we expect MANY more submissions than that, we will need to constantly clear out submissions from the Google sheet. (Yes, we are working on integrating into a MySQL database, but that will take some time, and we need to figure out our interim solution).
3. I also understand that data from an edited submission will only appear on the Google sheet if the original (or prior edit) submission is still on the Google sheet. If we cleared out the original submission, then the edited version will not appear on the Google sheet. Is that correct?
4. The visitor might edit several times. Is there a way for us to get a row on the google sheet for each edit (and the edit time for each), instead of having the existing submission row get updated?
5. If we can do #4, then will that take care of the problem discussed in #3? (So we would get a row for an edited submission, even if the original had already been removed from the Google sheet?
6. I assume that editing a form does not update the Unique ID field, which is set to auto-increment. Right?
7. You instructed us to set the TIME field in the Date of Edit element to Current. That works. However, we do not want to risk visitors editing it. I assumed we could prevent that by setting the element to "Read-only." However, when I tested that, I discovered that it prevents the visitor from changing the date or time, but it lets them change AM/PM. Note on row 5 of the chart. YIKES! That sounds like a bug.
8. You instructed us to use the Conditional logic to Show the Date of Edit field if the Form Page URL contains "Edit." However, we would just as soon not show the Date of Edit field to the visitor. I therefore (i) disabled the conditional logic thing you suggested, and (ii) changed the Edit Date element to Hidden. That seemed like it should work, but it caused problems. It hid the Edit Date field from the form as expected, but it showed the Date of Edit in the google sheet with only a time (row 8) and no date. When I copied the formatting from the rows above it down to that row, it shows the date 12-30-1899, which I guess Google treats as day 0. I made another submission (row 9), and you can see the time with no date. What do I need to do differently so that the visitor does not see the Date of Edit field and the google sheet shows the correct edit date, as well as the edit time?
9. Odd: one time when I clicked the Edit Form button in the thank you page, it took a long time and then I got this message: Form failedForm engine failed - #8j1h17f1. I reloaded the page, and it worked. Maybe just a transient glitch?
10. I tested this several times. Although I disabled the conditional and changed the Edit Date element to hidden, when I click the Edit link for earlier submissions, the visitor still sees the Edit date field. When someone clicks Edit Form, shouldn't they see the form with the most recent edits? I further tested by adding a new field called City. When I click the Edit Field link for older submissions, that new City field appears (that's good), but the Edit Date field is not hidden. Odd. I would expect ALL changes to the form (new fields, edited fields, relocated fields, fields with changed rules, hidden/not-hidden, etc) to app appear in the latest version when someone clicks Edit Field. Please advise.

Thank you very much!

Robert

Hi Robert,

1. & 2.
I understand that editing the form edits the existing submission row on the google sheet and adds the Edit date.
Is my understanding correct that Jotform will only add rows to the Google sheet until it hits 10k rows? Is there any way for us to increase that on our account or forms? Since we expect MANY more submissions than that, we will need to constantly clear out submissions from the Google sheet. (Yes, we are working on integrating into a MySQL database, but that will take some time, and we need to figure out our interim solution).

That's correct, and I'm afraid that there is no way to increase the limit.
It's possible to use scripts with Google Sheets, and as a workaround, I could think that you can create an automatized script that back-ups your data at specific times, and then deletes the entries when you hit a certain amount of rows. Here's some information I found on this:
https://gist.github.com/abhijeetchopra/99a11fb6016a70287112
https://groups.google.com/g/acra-discuss/c/Dv6hg-FakJE?pli=1

However, I have not tested this myself, so I cannot guarantee that this will work with the integration; there is a possibility that using the scripts might conflict with the integration pushing data to Google Sheets.

3. I also understand that data from an edited submission will only appear on the Google sheet if the original (or prior edit) submission is still on the Google sheet. If we cleared out the original submission, then the edited version will not appear on the Google sheet. Is that correct?

This is also correct. I tested this, and if the original submission is deleted or cleared from the sheet, the edited data is not uploaded to the sheets.

4. The visitor might edit several times. Is there a way for us to get a row on the google sheet for each edit (and the edit time for each), instead of having the existing submission row get updated?

If you mean by adding each edit on a new row, I'm afraid that is not possible. In this case, you would need to ask the user to make a new submission each time, instead of editing the existing submission.

5. If we can do #4, then will that take care of the problem discussed in #3? (So we would get a row for an edited submission, even if the original had already been removed from the Google sheet?

If that would be possible, it would take care of the issue regarding the deleted original record.
However, unfortunately I was unable to come up with any workarounds, other than submitting the form newly each time.

Not the perfect solution, but it is possible to prefill forms. I'm wondering if it would be possible to create a feasible solution where on the first time when the user fills out the form, a second prefilled form is created, and when the user "edits" the form, they will actually submit a new instance of the same form, that has the fields prefilled.
Here is our guide to Prefills:
What-is-jotform-prefill-and-how-does-it-work

Overall, the Google Sheets is not the best solution for managing large amounts of data, but I understand that it is a temporary solution for you. I do not know your use case more in-depth, but is there a specific reason you want to use Google Sheets? Are you accessing the sheets from somewhere else?
I was thinking that if you are only saving the data, would it be possible to keep and manage the data in Jotform Tables instead?

I'll answer to the rest of the questions in my next comment, so that this one doesn't become overly long.

6. I assume that editing a form does not update the Unique ID field, which is set to auto-increment. Right?

That is correct, editing the form will keep the same Unique ID that was created when the form was submitted.

7. You instructed us to set the TIME field in the Date of Edit element to Current. That works. However, we do not want to risk visitors editing it. I assumed we could prevent that by setting the element to "Read-only." However, when I tested that, I discovered that it prevents the visitor from changing the date or time, but it lets them change AM/PM. Note on row 5 of the chart. YIKES! That sounds like a bug.

I tested the Date field on a different form, and the read-only option makes all of the fields disabled.
Could you confirm if this behavior is still occurring on the form, where the AM/PM is clickable even when the field is set to read-only?

8. You instructed us to use the Conditional logic to Show the Date of Edit field if the Form Page URL contains "Edit." However, we would just as soon not show the Date of Edit field to the visitor. I therefore (i) disabled the conditional logic thing you suggested, and (ii) changed the Edit Date element to Hidden. That seemed like it should work, but it caused problems. It hid the Edit Date field from the form as expected, but it showed the Date of Edit in the google sheet with only a time (row 8) and no date. When I copied the formatting from the rows above it down to that row, it shows the date 12-30-1899, which I guess Google treats as day 0. I made another submission (row 9), and you can see the time with no date. What do I need to do differently so that the visitor does not see the Date of Edit field and the google sheet shows the correct edit date, as well as the edit time?

It looks that your form is currently clearing all hidden values when the form is submitted. This caused the hidden Date field to empty its values, and thus shown as 0 in the Google Sheets. You can change this setting from the Form Settings, and after that, the hidden field should work.

Set the Clear Hidden Field Values to Don't Clear:

9.Odd: one time when I clicked the Edit Form button in the thank you page, it took a long time and then I got this message: Form failedForm engine failed - #8j1h17f1. I reloaded the page, and it worked. Maybe just a transient glitch?

This error might happen if the form fails to load its cache correctly. This is not a common error, but as your form is quite heavy, it can happen very seldomly. If this would occur again, you can fix the issue by clearing your form cache:
How-to-clear-your-form-cache

10. I tested this several times. Although I disabled the conditional and changed the Edit Date element to hidden, when I click the Edit link for earlier submissions, the visitor still sees the Edit date field. When someone clicks Edit Form, shouldn't they see the form with the most recent edits? I further tested by adding a new field called City. When I click the Edit Field link for older submissions, that new City field appears (that's good), but the Edit Date field is not hidden. Odd. I would expect ALL changes to the form (new fields, edited fields, relocated fields, fields with changed rules, hidden/not-hidden, etc) to app appear in the latest version when someone clicks Edit Field. Please advise.

In the edit mode, hidden fields are shown if you are logged in as the owner of the form - this is because the system considers that as the owner, you might want to make edits on all of the fields. Please log out to test this, and the hidden fields should not show.

Hello Laura

I am racing deadlines on projects, so slow to respond to all this. Let me address these one at a time as I can (and as certain ones are most urgent).

This uses the same sample form as above.

(8)

I have a Get Form Page URL Widget and a Date of Edit element.

This may be THREE questions on the same element.

(A)

Why is the Conditional greyed out?

(B)

You can see that if the Get Page URL contains edit, then it should show Date of Edit.

It does. The Date of Edit field is set to time = Current Date; Default Time = Current; and Read-Only.

The problem is that the Edit Date field is not updated when I edit the form.

(C)

I see the "Current " choice for Default time. How do I set the current date to be the default date?

(D)

If we a Condition so that if the URL contains "Edit," then Date Picker will display.

That sets the current time and (after you guide me on (C), the current date.

But when someone comes back to edit the form again, will that date and time be updated?

Thanks!

Thanks!

Replies to the rest of the long list.

1 Thank you

2 Thank you

3 Thank you

4 Thank you

5 Thank you. Interesting idea about the Prefill. I will explore. We need to use Google sheets to be able to manipulate the data. We are working on a setup that goes Jotform >> My SQL >>[via Integromat]>>Google Sheets

6 Thank you

7 It is OK, now.

8 Wow, we have well over 100 forms and have been using jotform for a very long time, and we never knew about

9 Thank you

10 Thank you

Hi,

(A

Why is the Conditional greyed out?

When a condition is disabled, they are greyed out. You can hover your mouse on top of the condition, and click on the gear icon to select to enable the condition.

(B)

You can see that if the Get Page URL contains edit, then it should show Date of Edit.
It does. The Date of Edit field is set to time = Current Date; Default Time = Current; and Read-Only.
The problem is that the Edit Date field is not updated when I edit the form.

I tested this on a cloned form, and for the Date to update when the form is edited, the Date field needs to be hidden, and the "Clear Hidden Fields" setting will need to be turned on to "Clear on Submit".
Then we need a condition to show the Date when the form is edited.

This will ensure that the hidden Date field does not have value until it is shown. When the field is shown when opening the form in the edit mode, the field gets assigned the current date as default.

Previously I said that you will need to set the Hidden Hidden Fields" to "Don't Clear" in order to show the field in the Google Sheets. However, it seems that we need to do this in a bit different way.
Keep the "Clear Hidden Fields" setting as "Clear on Submit", otherwise the edit date will not update.
If you use the condition mentioned above to show the Date field upon edit, as the field is not hidden anymore, the values should be passed to Google Sheets correctly.

(C)

I see the "Current " choice for Default time. How do I set the current date to be the default date?

You can set the default date from the Options tab, and the default time from the Time tab.

(D)

If we a Condition so that if the URL contains "Edit," then Date Picker will display.
That sets the current time and (after you guide me on (C), the current date.
But when someone comes back to edit the form again, will that date and time be updated?

This method will update the Edit Date only once.
If the form needs to be updated multiple times, the only workaround would be to create for example "Edit 2 Date" and "Edit 3 Date" fields, and similarly show them with conditions.

Hi,

Please also take a look at the Prefill options, and let us know if you have any questions.

Regarding the Clear Hidden Fields option, I have forwarded your feedback to our developers as a feature request in a new ticket, that you can find below:
https://www.jotform.com/answers/3547748