Intro
In an increasingly-paperless world, platforms such as Jotform and Google Forms have quickly rose to stardome. Especially in this post-pandemic world we live in, many doctors offices and event organizers are transitioning from physical paper forms to digital. Not only does this reduce unneeded paper waste, but it also eliminates the need for manual data entry and processing. This in-turn reduces the margin of error for a second party inputting written information on a paper form into a digital system or database.
For developers like you and me, this presents new opportunities for integrations and data processing. This article seeks to explore exactly that.
Getting Started
This article assumes an intermediate knowledge of both FileMaker and cURL. You should also be familiar with Jotform’s API Docs and JSON. You’ll be referencing these a good bit, trust me.
Creating a Form
To begin, head over to your Jotform account and create a new form. Then, do the following:
- Give your form a name
- Add a name, email, phone, and address widget
Connecting to a Form
To connect to a form, enter the Form Builder for the form you wish to connect to.
From there, go to the Publish tab and take note of the Form’s ID, these appear in the link: https://form.jotform.com/{formID}.
Next, you will need an API Key so that you can connect to your Jotform. To do so, click your avatar in the top-right corner and click "Settings". Once in Settings, navigate to the API tab and click the "Create New Key" button. Name the key whatever you like. Personally, I prefer something generic like "FileMaker". Also, change the Permissions from "Read Access" to "Full Access". Take note of this API Key. And as always with API Keys– keep it secret, keep it safe.
Connecting via the Jotform API
According to the Jotform API Docs, to get any submissions on this form, we need to reach the /form/{id}/submissions endpoint. On a very basic level, the cURL request we want to generate looks like this:
curl -X GET "https://api.jotform.com/form/{formID}/submissions?apiKey={apiKey}"The Jotform API also provides several optional fields that we can use for more advanced form queries. These include offset, limit, filter, and orderby. In most production applications, you’ll likely find a combination of limit and filter to be of use. For example, a more advanced form query that gets all submissions since a given timestamp looks like this:
curl -X GET "https://api.jotform.com/form/{formID}/submissions?apiKey={apiKey}&limit=1000&filter={"created_at:gt":"2021-11-08 14:02:00"}"Example Response Body
A typical GET request to either endpoint mentioned above will result in a response similar to this:
{
"responseCode": 200,
"message": "success",
"content": [
{
"id": "5122007793685231478",
"form_id": "{{FORM_ID}}",
"ip": "{{IP_ADDRESS}}",
"created_at": "2021-11-08 12:13:00",
"status": "ACTIVE",
"new": "1",
"flag": "0",
"notes": "",
"updated_at": null,
"answers": {
"1": {
"name": "whoAre",
"order": "1",
"text": "Who are you?",
"type": "control_head"
},
"2": {
"name": "submit2",
"order": "6",
"text": "Submit",
"type": "control_button"
},
"3": {
"name": "name",
"order": "2",
"sublabels": "{\"prefix\":\"Prefix\",\"first\":\"First Name\",\"middle\":\"Middle Name\",\"last\":\"Last Name\",\"suffix\":\"Suffix\"}",
"text": "Name",
"type": "control_fullname",
"answer": {
"first": "John",
"last": "Smith"
},
"prettyFormat": "John Smith"
},
"4": {
"name": "email",
"order": "3",
"text": "Email",
"type": "control_email",
"answer": "[email protected]"
},
"5": {
"name": "phoneNumber",
"order": "4",
"sublabels": "{\"country\":\"Country Code\",\"area\":\"Area Code\",\"phone\":\"Phone Number\",\"full\":\"Phone Number\",\"masked\":\"Please enter a valid phone number.\"}",
"text": "Phone Number",
"type": "control_phone",
"answer": {
"full": "(111) 111-1111"
},
"prettyFormat": "(111) 111-1111"
},
"6": {
"name": "address",
"order": "5",
"sublabels": "{\"cc_firstName\":\"First Name\",\"cc_lastName\":\"Last Name\",\"cc_number\":\"Credit Card Number\",\"cc_ccv\":\"Security Code\",\"cc_exp_month\":\"Expiration Month\",\"cc_exp_year\":\"Expiration Year\",\"addr_line1\":\"Street Address\",\"addr_line2\":\"Street Address Line 2\",\"city\":\"City\",\"state\":\"State \\\/ Province\",\"postal\":\"Postal \\\/ Zip Code\",\"country\":\"Country\"}",
"text": "Address",
"type": "control_address",
"answer": {
"addr_line1": "111 Pine Street",
"addr_line2": "Suite 1815",
"city": "San Francisco",
"state": "CA",
"postal": "94111"
},
"prettyFormat": "Street Address: 111 Pine Street<br>Street Address Line 2: Suite 1815<br>City: San Francisco<br>State \/ Province: CA<br>Postal \/ Zip Code: 94111<br>"
}
}
},
...
],
"duration": "20.14ms",
"resultSet": {
"offset": 0,
"limit": 20,
"count": 2
},
"limit-left": 989
}Response Breakdown
Although the Jotform API response is rich with data, we are really only concerned about the content object. The content object (as the name implies) contains the contents of the various form submissions. Some values that may prove useful are the id (the primary key of the submission record) and the form_id (the primary key of the form being requested). In most production applications, this is useful for testing against existing records in your FileMaker database to ensure you don’t import the same Submission record more than once.
From there, you’ll notice the answers object nested inside the content object. The answers object contains all the metadata of each object on the form. Some developers might find this naming misleading, as answers also contains metadata about form objects such as submit buttons, titles, logos, etc. As a typcial rule-of-thumb, I tend to only treat an individual answer as a data point if it contains both a valid name and answer in its metadata. The name is a unique identifier generated by Jotform that can be overwritten by the Form developer. In our integrations, we prefer to name each form element something human-readable such as name, address, payment, etc. Additionally, the answer key contains the answer (as it was entered) by the user filling out the form.
To illustrate my point, see the code below:
// Import this
{
"name": "email", // valid name
"order": "3",
"text": "Email",
"type": "control_email",
"answer": "[email protected]" // valid answer
}
// Not this
{
"name": "submit2", // valid name
"order": "6",
"text": "Submit",
"type": "control_button"
}Scripting the Integration
Now that you (hopefully) have a better understanding of the inner-workings of a Form on Jotform, let’s dive into getting this thing scripted. A generic setup that will GET all submissions for a given Form might look something like this:
# Get Form Submissions
# Created 11.08.21 by Nick Cheatwood
Set Error Capture [ On ]
# Specify the Form ID and API Key
Set Variable [ $Form_ID ; Value: "{{MY_Form_ID}}" ]
Set Variable [ $API_KEY ; Value: "{{MY_API_KEY}}" ]
# Specify the URL to GET
Set Variable [ $URL ; Value: "https://api.jotform.com/form/"& $Form_ID & "/submissions?apiKey=" & $API_KEY ]
# Specify cURL Options
Set Variable [ $curl_options ; Value: "-X GET --dump-headers $headers --show-error" ]
# Get the Result
Insert from URL [ Select ; With dialog: Off ; Target: $Response ; $URL ; cURL options: $curl_options ]
# Parse out the API Response Code
Set Variable [ $code ; Value: JSONGetElement ( $Response ; "responseCode" ) ]
# If we have a valid response, parse out the form submissions
If [ $code = 200 ]
# Parse submissions
Perform Script [ Specified: From list ; “Parse Form Submissions [ json ]” ; Parameter: JSONGetElement ( $Response ; "content" ) ]
Else
# Error in response
Show Custom Dialog [ "API Error" ; "API Response failed with error code: " & $code ]
Exit Script [ Text Result: False ]
End IfFrom here, you’ll likely want to parse out each submission in the Response body. Remember, the data we care about is located in the content object. One appraoch (as outlined below), is to simply iterate through each content index and grab a few things– id and each answer’s name and answer. Depending on the complexity and the configuration of the Jotform you need to integrate with, this could be more or less complex that the script listed below.
One more thing– don’t be afraid to fail. The odds you get this perfectly on your first attempt are slim-to-none. Like any complex task, keep hacking away at it little by little. If it helps, break the big task of parsing out an entire Form’s submission into smaller chunks, such as parsing out only the address block, or maybe only the user’s first and last name.
# Parse Form Submissions [ json ]
# Created 11.08.21 by Nick Cheatwood
# Get the Submissions JSON
Set Variable [ $ParseErrors ; Value: Let([ $json = JSONFormatElements ( Get ( ScriptParameter ) ) ]; Case ( Left ( $json ; 1 ) = "?" or $json = "[]" ; True ; False ) ) ]
If [ $ParseErrors ]
Exit Script [ Text Result: False ]
End If
# For each array index. list the answer ids
Set Variable [ $i ; Value: 0 ]
Set Variable [ $length_array ; Value: ValueCount ( JSONListKeys ( $json ; "." ) ) ]
Set Variable [ $Submissions ; Value: "[]" ]
Loop
Set Variable [ $answer_ids ; Value: JSONListKeys ( $json ; "[" & $i & "].answers" ) ]
# Parse out the submission id
Set Variable [ $submission_id ; Value: JSONGetElement ( $json ; "[" & $i & "].id" ) ]
# For each answer in the array, append the name and value to a new JSON Object
Set Variable [ $record ; Value: "{}" ]
Set Variable [ $n ; Value: 1 ]
Loop
Set Variable [ $answer_id ; Value: GetValue ( $answer_ids ; $n ) ]
# Parse out the key and value
Set Variable [ $key ; Value: JSONGetElement ( $json ; "[" & $i & "].answers." & $answer_id & ".name" ) ]
Set Variable [ $value ; Value: JSONGetElement ( $json ; "[" & $i & "].answers." & $answer_id & ".answer" ) ]
If [ not IsEmpty ( $key ) and not IsEmpty ( $value ) ]
Set Variable [ $record ; Value: JSONSetElement ( $record ; [ $submission_id & "." & $key ; $value ; JSONRaw ] ) ]
End If
Set Variable [ $n ; Value: $n + 1 ]
Exit Loop If [ $n > ValueCount ( $answer_ids ) ]
End Loop
# Append the record to the array of Submissions
Set Variable [ $Submissions ; Value: JSONSetElement ( $Submissions ; $i ; $record ; JSONObject ) ]
Set Variable [ $i ; Value: $i + 1 ]
Exit Loop If [ $i ≥ $length_array ]
End Loop
# Import the submissions into the system
Perform Script [ Specified: From list ; “Import Form Submissions [ json ]” ; Parameter: $Submissions ]Lastly, you’ll likely want to import new submissions into your database. To avoid duplicates, this is where the Submission’s ID comes in handy.
# Import Form Submissions [ json ]
# Created 11.08.21 by Nick Cheatwood
Set Error Capture [ On ]
# Get the Array of Submissions
Set Variable [ $ParseErrors ; Value: Let([ $json = JSONFormatElements ( Get ( ScriptParameter ) ) ]; Case ( Left ( $json ; 1 ) = "?" or $json = "[]" ; True ; False ) ) ]
If [ $ParseErrors ]
Exit Script [ Text Result: False ]
End If
# Check how many submissions exist
Set Variable [ $count_submissions ; Value: ValueCount ( JSONListKeys ( $json ; "." ) ) ]
Set Variable [ $New ; Value: 0 ]
# For each submission, see if it exists. If not, add it.
Set Variable [ $n ; Value: 0 ]
Loop
Set Variable [ $submission ; Value: JSONFormatElements ( JSONGetElement ( $json ; $n ) ) ]
Set Variable [ $submission_id ; Value: JSONListKeys ( $submission ; "." ) ]
Go to Layout [ “Contacts” (Contacts) ; Animation: None ]
Enter Find Mode [ Pause: Off ]
Set Field [ Contacts::API_Submission_ID ; GetAsText ( $submission_id ) ]
Perform Find []
If [ Get ( FoundCount ) = 0 ]
# Create
New Record/Request
Set Field [ Contacts::API_Submission_ID ; GetAsText ( $submission_id ) ]
# Name field
Set Field [ Contacts::First Name ; JSONGetElement ( $submission ; $submission_id & ".name.first" ) ]
Set Field [ Contacts::Last Name ; JSONGetElement ( $submission ; $submission_id & ".name.last" ) ]
# Contact info
Set Field [ Contacts::Email ; JSONGetElement ( $submission ; $submission_id & ".email" ) ]
Set Field [ Contacts::Phone ; JSONGetElement ( $submission ; $submission_id & ".phoneNumber.full" ) ]
# Address
Set Field [ Contacts::Street ; List ( JSONGetElement ( $submission ; $submission_id & ".address.addr_line1" ) ; JSONGetElement ( $submission ; $submission_id & ".address.addr_line2" ) ) ]
Set Field [ Contacts::City ; JSONGetElement ( $submission ; $submission_id & ".address.city" ) ]
Set Field [ Contacts::State ; JSONGetElement ( $submission ; $submission_id & ".address.state" ) ]
Set Field [ Contacts::Postal Code ; JSONGetElement ( $submission ; $submission_id & ".address.postal" ) ]
Commit Records/Requests [ With dialog: Off ]
Set Variable [ $New ; Value: $New + 1 ]
End If
Set Variable [ $n ; Value: $n + 1 ]
Exit Loop If [ $n ≥ $count_submissions ]
End Loop
Show Custom Dialog [ "Fetched New Submissions" ; "Finished fetching Submissions. Created " & $New & " records." ]Conclusion
And that’s it! You’ve successfully connected to and imported data from Jotform. But why stop there? Not only can Jotform pull in Form Submissions via the API, but you can actually create Submissions and entire new Forms from the API. Hopefully, this article has opened your eyes to the bountiful possibilities of integrting Jotform with FileMaker and beyond. We can’t wait to see what you do.
As always, farewell, and happy Filemaking!