Importing Data to FileMaker 16 via APIs

I’m new to APIs. Have quite a bit of FileMaker experience. I’ve decided to see if I can build a system to track parent-teacher contact. This is a system that once existed for the school in an ASP system that I mocked up and outsourced. It was replaced with Edsby, an LMS system that wasn’t truly designed for this purpose. We were smashing a square peg into a round hole.

My proof of concept for this system involves seeing if I can build something that does three things I’ve never done in FileMaker before.

1. OAuth login using Google authentication
2. Sending nightly emails of any records that have changed for those affected
3. Import data from Managebac’s API

The first step was surprisingly easy. Thanks to a blog post that had the entire process outlined on a simple PDF. Thoroughly detailed post from William Porter of Rucksack Texnology.

Step two is actually step three, but ending the blog post with “I haven’t done this yet” would not be very exciting.

For step three, I got off to a bad start. I did some research and didn’t realize that JSON support was added to FileMaker 16 and was looking at a third-party plug in. That plug in was super-confusing, so I stepped back. At that point I decided to upgrade from FileMaker 15 to 16, as I knew I needed that for the OAuth login.

Now that I had FileMaker 16, I decided to rewatch a Lynda.com video that I had watched months ago, and see if there was anything I missed there. There was, there was a whole section dedicated to JSON. Ready to conquer this task with the built-in functionality of FileMaker 16, I began.

I decided to start with the smallest table, teachers. Obviously there are fewer teachers than students, and fewer students than parents.

Using Terminal.app I was able to get what I needed using this command.

curl --request GET --url https://api.managebac.com/v2/teachers --header 'auth-token: <<AUTHTOKENVALUE>>'

Obviously, I removed the actual authtokenvalue, because I’m not a dum dum.

Open FileMaker, open my working database, create a field in the TeacherContacts table for the temp data dump.

I chose “Insert From URL”
Verify SSL Certificate was selected
Select Entire Contents was selected
URL was https://api.managebac.com/v2/teachers
cURL options was “–request GET –H \’auth-token: <<AUTHTOKENVALUE>>\'”

Everything matched my terminal command, the only problem was I was getting an error saying that “Authorization Failed.” Eventually I realized that my problem was using single quotes around auth-token. I replaced those with a double-quote and it worked perfectly.

cURL options is now “–request GET –H \”auth-token: <<AUTHTOKENVALUE>>\””

Once that was working, I was then able to get rid of the data other than the “teachers” table. Using Insert Calculated Results into the field $jsonTeachers, I used the calculation JSONGetElement ( $json ; "teachers" )

The data needed to be cleaned up a bit, using a new Insert Calculated Results this time it was enter into $jsonTeachersFormatted.JSONFormatElements ( $jsonTeachers )

From there, I needed to create records, and to do that, I needed to know when to stop.

This time I Inserted Calculated Results into the $jsonCount variable. The value was the number of records that was exported from Managebac. ValueCount ( JSONListKeys ( $jsonTeachersFormatted ; "" ) )

The JSON array starts count at 0, so $jsonCount is one more than I need. I began a loop and immediately Set Variable [ $jsonCount ; Value: $jsonCount -1 ]. This would trigger every time the loop began.

I then created a New Record/Request and Set Field [StaffContacts:id ; JSONGetElement ( $jsonTeachersFormatted ; "[" & $jsonCount & "]id" ) . I duplicated that for every field I wanted to import and modified id to the new field name. After all that I had an Exit Loop If [ $jsonCount = 0 ] and closed the loop.

Now I had a script that would pull all the teachers from ManageBac, create a new record for each and bring in the data to FileMaker. Now I just need to get it to update a record if it already exists, rather than creating a new record. We’re getting there.

I spent a long time trying to figure this out and it’s a lot easier than I thought. In the loop, after the decrease of the $jsonCount, I went to find mode, Enter Find Mode [ Pause: Off ] and Set Field [ StaffContacts::id ; JSONGetElement ( $jsonTeachersFormatted ; "[" & $jsonCount & "]id" ) . Needed to Set Error Capture [ On ] and ran the find Perform Find [ ] .

At this, I need to enter the data, either on a new record, or into the existing found record. So:

If [ Get (FoundCount) = 0 ]
     New Record/Request
End If

And I’m done. I win! I just have to do this with all the other tables of data, relate them all and build the actual parent contact part.