Capturing data with Google Forms
One of FileMaker’s best advantages is its ability to quickly provide an interface to common data tasks. Sorting, filtering and analysis, via automated routines, make a database system better suited to routine tasks. Primarily, because you can control the user interface.
Typically, spreadsheet users don’t think in terms of disjointed data. Boiling data down into a relational model is something which takes a bit of patience and know-how. Data collection, however, is easily done within a spreadsheet because it’s super simple. Rows and Columns, rows and columns.
When distributed data collection becomes a high priority and handling scale is important, then using FileMaker Server’s Web Direct technology isn’t always the ideal solution.
If collecting data from thousands of people within a short period of time is needed, then there are only a few options when it comes to FileMaker. You can provide a distributed database file which runs on mobile and import all the individual files into one or you can use a free service like Google Forms.
The route of using Google Forms is both immediate and easy. The software, a browser, is already on the device and the problem of displaying and allowing user interaction is already solved. All you need to do is create the form and then create the system around it.
In this video, you’ll learn the ins-and-outs of using Google Forms in conjunction with a FileMaker system. If quick and easy is your goal for data collection, then it doesn’t get much easier than this!
Comments
Google sheet shareable link
Matt,
I use a google sheet that gets updated by Mailchimp form on a web site. I wrote a database that accesses the google sheet and imports it into a filemaker database using an insert from url call. This way a filemaker db user doesn't have to interact with google at all.
I import the file into a single field - and process one line at a time , replacing the Char(44) with Return. Just use getValue to move the info into the correct fields, then go to next line.
In my case I just delete the database of all names and import all the new data, then process for possible duplicates. You could also import to a secondary table then compare with working table of addresses or Just compare field by field in a record as you import. Any case this works well and keeps the user out of google sheets. In google sheets use Get shareable link. Put this in the the Insert from Url script - Looks soomething like this - "https://docs.google.com/spreadsheets/d/1lmjTMssUVojtfbQp64-h2WaVhPXhfEll...
Thanks for all the great videos
Cj Watson
Nice idea!
I didn't think about using the secret link to the sheet to try downloading into a field. This is yet another way to make things happen. My personal approach still uses the user-friendly method of going through Google APIs, but this takes A LOT more effort to implement.
Thanks for the feedback!
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Thank you for the video!
This technique in combination with the insert from url script step to make the process automatic, opens a world of posibilites.
Thank you Matt for all your videos, glad to be a subscriber!
Insert from URL
Here is a little prototype I made: temporaryData is a global field.
New Record/Request
Go to Layout [ original layout ]
Insert from URL [ Select; With dialog: Off; Gform test::temporaryData; "https://docs.google.com/spreadsheets/d/xxxxxxx/export?format=tsv" ]
Export Field Contents [ Gform test::temporaryData; “file:tmp.tsv” ]
Import Records [With dialog: Off; “tmp.tsv”; Add; Mac Roman]
Running on server
I am trying to implement a similar function for the drag and drop on a server. How do I change the $exportPath to the local computer, instead of the server?
Thanks,
---- Nash