How can I prefill real-time "timestamp"?

Saigon_Realty_5 · ‎Jul 16, 2021

A brief summary: I would like to create a form with time counting (count how long a person opens a form - start as they opened it, and then end as they submitted it)

Thus, I tried create a table with a form [which contained 1field save the created milestone (which will be updated as people submitted the form - Created Time) + 1 Date field which will be recorded as people open the form]. For my form, I prefilled the time with NOW(), but it did not work well (NOW() merely refresh after about 15 mins).

I wonder if my explanation is clear enough (if not, just state it, I’ll try my best to present it in another better way).

In short, My purpose is to count the open time of my form, you guys can suggest any other solutions if available, or you can help me solve the prefill with NOW () problems.

Thanks for your support!! :blush: :winking_face:

Justin_Barrett · ‎Jul 16, 2021

Welcome to the community, @Saigon_Realty_52! :grinning_face_with_big_eyes:

The only way that I can think of to insert the true current time into a field would be to use a script. Inside Airtable, you could either have a Scripting app that’s run manually (directly via the app or using a button field), or an automation with a Run script action step. Either way, the script could determine the current time, then use a fetch command to call the form URL with the time prefilled. Something similar could be done by running a script on an external server depending on how you envision this form being triggered.

With that said, here are the issues that immediately come to mind:

Using either a Scripting app or an automation would mean that the people filling out the form would need access to your base. Sharing a view with a button that triggers a script won’t work because script-triggering buttons are disabled in shared views, and shared views are read-only so nothing could be changed that would trigger an automation.
Because a script is the only way to prefill the form with the current time, you couldn’t just send the form URL to someone via email or post it on a website. However, you could send/post a link that goes to a different server that then runs a script to open the form with the time prefilled. (Note: I haven’t done this myself, so I can’t talk specifics about how this is set up, but I believe that a proxy service like AutoCode or Nginx would work if I’m correctly interpreting a recent comment by @Bill.French in another thread)
If you use an Airtable form, the field where you prefill the start time can’t be hidden (based on how Airtable forms currently operate), meaning that the user would be free to edit that inserted start time if they chose. Other form systems like JotForm allow you to prefill hidden fields, so it might be worth considering that direction.

Bill_French · ‎Jul 16, 2021

I think @Justin_Barrett nailed the challenges with Airtable forms. I’ll go another step forward and say this is a simple analytics requirement not unlike real-time analytics used to measure attentiveness, extrapolated eye-gaze, and time on pages or on specific entities within pages. To gather these types of analytics requires access to the DOM of the form itself and that’s where Airtable is unhelpful. You need to use a different form solution that will allow javascript to sense the form has been rendered and start the timer and update the start field (hidden, of course). On submit it’s a simple task of updating the end time and posting the data to the database.

Indeed, and while unstated, I suspect this is a requirement.

Even JotForm doesn’t allow this and for many reasons. Maybe there’s a forms service that would enable you to customize the form itself and easily pipe the results into Airtable, but I’m not aware of one.

Yes, but calling the form and populating the start time field is still an issue because something - like a proxy - would have to serve as the gateway to the form itself. That is a complexity which may not fit into the business requirements of the process.

Where does that leave you? Building and hosting your own custom web form and using the Airtable API to submit the data to Airtable.

Cameron_Kaiser · ‎Jul 16, 2021

Create one form that asks simply for the user’s Name as input. Have the post-form message be a link to a read only view where the just-created record will be displayed with their name, along with a button link to the actual form you want them to fill out. The URL used to get to the actual form will come with a prefill query that links back to the original record (just prefill a linker field with the recordId(). From there do a time difference calculation based on the creation time of the first record vs the second. It’s clunky, but it works within Airtable’s limitations.

You can filter the buffer view to only show records that haven’t been linked back to, so it will only display records whose users still need to fill out their form.

Justin_Barrett · ‎Jul 16, 2021

While it’s certainly a clever solution to the problem, I have two concerns with that approach:

It exposes data about others filling out the form (on the filtered view of users who haven’t completed the primary form)
There’s no way to prevent someone from clicking the wrong button in that shared view.

Depending on how you might try to address #1, it might make the issue mentioned in #2 even more of a potential problem; e.g. masking names by using initials could lead to users clicking the wrong button. Or maybe someone just wants to intentionally mess with the system and fill out another user’s form. In either case the validity of the data is compromised.

One possible way to solve both problems would be to have the first form ask for both name and email. The new record created by that form triggers an automation to email the user a link to the primary form, which has the link to the original prefilled as described previously. The downside is that this introduces greater delays between the submission of Form 1 and the start of filling out Form 2. In other words, it’s still a clunky workaround. If the goal is to get accurate timing, none of these are going to be as effective as what @Bill.French suggested: