The Philosophy of FileMaker

"No Holding Back FileMaker Blogging"
Navigation: About John Mark Blog Listing Blog Roulette Contact John Mark Subscribe to Newsletter FileMaker Friends
Support this site by clicking on a sponsor below or becoming a patron! The Philosophy of FileMaker recommends PCI! Become a patron of this FREE web site!
Recent Blogs:
Window Locking
Everything Changes
Subsummary Unique Count
Scripted Change Log
Abstracted Log

Meta-Consulting Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
The Philosophy of FileMaker recommends PCI!
Quick Tip:
Coffee Cup Do you see the coffee cup cursor icon a lot? This icon shows up in place of the arrow pointer when the host is busy and cannot process a request from a guest. This is different than the squiggly line cursor, which indicates the guest is sending information to the host. Using FileMaker Server as a host will probably eliminate the coffee cup icon, since it is multithreaded and can handle multiple jobs at once. This is different than multitasking. FileMaker Server actually does a little of each job that is queued. For instance, if a guest sorts a million records to preview a report, this may take a while to process. If another guest follows this long process with a simple find, they may get the coffee cup with regular FileMaker Pro as the host, since it can only process one guest at a time. What FileMaker Pro Server does is work on each task for a short time. Since the find takes a lot less time to process, FileMaker Pro Server will take care of it in one cycle and the user will never know another guest is processing a much more complex request.

$10.00 Beginner Video Training
Fun Stuff:
FileMaker, Inc. FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.

Fireside FileMaker Podcast
RSS Feed


`Google Calendar`		Level: Advanced Version: FileMaker 17 Category: Plug-Ins Tuesday, March 19, 2019

As integrators and developers, we have had many requests to integrate a client’s FileMaker solution with Google Calendar. The partnership of a customized database with the flexibility and easy accessibility of an online calendar system is ideal; events can be composed in the CRM of one’s choice, and pushed to the online calendar, where users out and about can access it from their phones, tablets or browsers. But it can be a challenge to connect Database A to Calendar B, so let’s delve a little into how to tackle that task.

Why Use a Plug-In?
It can be quite a challenge to communicate with a robust online service such as Google using native FileMaker functionality, and we’ve found that using a plug-in drastically improves how integrations flow. We’ve spoken previously about our experience developing the gManipulator plug-in and you can check it out here in an article titled Connect FileMaker and Google: A Developer’s Tale!

FYI: PCI makes quite a few plug-ins including integration with QuickBooks, Mac Address Book, iCal, Credit Cards and much more!

A plug-in allows for complex and difficult workflows to be encapsulated within a function call, and the flexibility of using a compiled programming language such as Objective C, Swift, C#, or even C++ helps to expand the range of power that FileMaker can access. It certainly doesn’t hurt that plug-ins, by the nature of how they are made, can make use of compiled libraries for different third-party services to perform actions that FileMaker would either be incapable of or hard-pressed to do natively.

So let’s launch into our most-requested example of communicating with Google from FileMaker: pushing an event to Google Calendar. The general workflow of posting events to Google Calendar (or for that matter, of performing any sort of data exchange from a source to a Google service) consists of four major parts:

Authenticate with Google
Gather data to push up to Google
Submit the data as an upload packet
Process results

Authorization to Google
Let’s start off with authentication. For a quick review, here’s a handy simplified graphic demonstrating the authorization process:

Image Source: Using OAuth 2.0 to Access Google APIs.

This graphic displays the essential workflow, starting from the app requesting access for which the user is consenting, through to receiving an authorization code, and then to exchanging the authorization code and retrieving the access token that is used for every successive call to the Google APIs.

From the user’s perspective, all they will see is a consent screen; everything else should be handled behind the scenes by the client application. There are also some additional workflows to control refreshing an outstanding access token, as well as a process to revoke an access token, but we won’t cover that here.

Let’s take a look at how the gManipulator would handle this. First and foremost, we will need to authenticate to Google using the Google Auth API. The authentication script would issue a call to the function PCGM_Authenticate, which will prompt the user to log into their Google account; ideally, this would be the account owning the desired calendar, but it could also be an account that has been granted shared access or other similar permissions to write to the calendar. Below is the authentication script taken from the gManipulator demo file showing how simple the authentication path can be.

Allow User Abort [Off]
Set Error Capture [On]

# Verify the plug-in is ready to function
Perform Script [“Plug-in Checker”]

# Attempt to authenticate – this should be done
at least once per FileMaker session
Set Field [Main::gAuthentication Result;
PCGM_Authenticate]

# Check for errors
If [ Main::gAuthentication Result = “!!ERROR!!” ]
   Show Custom Dialog [“Authentication Failed”;
    PCGM_GetLastError( “Text” )]
Else
   # Next steps
   Set Variable [$$Authenticated; Value: 1]

   # Gather all folder lists
   Perform Script [Specified:From list; “CNTCT__Get
    Contact Folders”; Parameters:]
   Perform Script [Specified:From list; “EVENT__Get
    Calendar Folders”; Parameters:]
   Perform Script [Specified:From list; “MAIL__Get
    Mail Folders”; Parameters:]
   Perform Script [Specified:From list; “TASK__Get
    Task Folders”; Parameters:]

   Go to Layout [original layout; Animation:None]
   Go to Object [Object Name: “setup”]
   Show Custom Dialog [“Authentication Successful”;
    “Authentication successful.”]
End If

The call to PCGM_Authenticate will bring up a web page (on Windows) or a web app (on Mac) that will guide the user through the authentication process with Google, using Google’s OAuth 2.0 authorization flow. They will have the option to choose which account to authenticate with, as well as whether to accept or decline the authorization attempt. While all of this is going on, the script sits and waits, expecting a response back as to whether the user consented and accepted the connection (success), canceled the request for access (failure), or the process timed out after 2 minutes (also a failure).

Upon success, the plug-in follows along with the overview graphic above, presenting the authorization token and some additional internal information to Google and exchanging it for an access token, storing this information internally, which will allow the plug-in to sign every request that needs to reach Google’s servers. This access token is crucial, as without the access token attached to any request submitted to Google, the request will be denied and any dreams of talking to Google’s APIs will fall flat and fail.

The latter part of the demo file’s authentication script calls out to Google to pull in related folders after a successful authorization, populating them within the FileMaker solution. Folder requests for each of the Google APIs are signed with the authorization token and the results are parsed from their native JSON details by convenient plug-in GetFolderProperty calls. The folder names and IDs are necessary as the plug-in will need to know what calendar, mail folder, contact group, and task list it will need to add, edit, or delete records from.

Gather and Post Data
For our calendar process, let’s look at the script for posting an event to Google from the gManipulator Demo file. We’ll go ahead and simplify any error capture and user notifications for the sake of brevity, as well as assume that we are in the correct context and have at least one new event record we wish to submit to Google.

Allow User Abort [Off]
Set Error Capture [On]

# Verify the plug-in is ready to function
Perform Script [“Plug-in Checker”]

# Verify that there is data to push…

# Verify the user wishes to push found set or
current record…

# Open the Calendar folder that will receive the
record(s)
Set Variable [$result; Value: PCGM_OpenFolder(
Main::gFolderEvents ; “Calendar” )]

Loop
   # Determine whether to open the record or create
a new one in Google
   If [not IsEmpty( Events::Google_ID )]
      Set Variable [$result; Value:
       PCGM_OpenRecord( Events::Google_ID ;
       “Event” )]
   Else
      Set Variable [$result; Value: PCGM_NewRecord(
    “Event” )]
   End If

   # Set the editable fields
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Attendees” ; Events::Attendees )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Description” ; Events::Description )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Location” ; Events::Location )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Start” ; Events::Start )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Summary” ; Events::Summary )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “End” ; Events::End )]
   Set Variable [$result; Value: PCGM_SetFieldValue(
    “Visibility” ; Events::Visibility )]

   # Save the record
   Set Variable [$result; Value: PCGM_SaveRecord]

   If [$result = “!!ERROR!!”]
      # Error handling…
   Else
      Set Field [Events::Google_ID; $result]

      # Pull the Read-Only fields
      Set Variable [$result; Value: PCGM_OpenRecord(
       Events::Google_ID ; “Event” )]

      Set Field [Events::Google_ID; $result]
      Set Field [Events::Created;
       PCGM_GetFieldValue( “Created” )]
      Set Field [Events::Creator;
       PCGM_GetFieldValue( “Creator” )]
      Set Field [Events::Kind;
       PCGM_GetFieldValue(
       “Kind” )]
      Set Field [Events::Updated;
       PCGM_GetFieldValue( “Updated” )]
      Commit Records/Requests [With dialog:Off]
   End If

   Go to Record/Request/Page [Next; Exit after
    last:On]
End Loop

# Success Dialog
Show Custom Dialog [“Success”; “Record has
successfully been added/updated in Google.”]

Using the plug-in’s SetFieldValue functions, we either create or edit an event and designate what time it starts and ends, who will be attending the event, what it is called, where it will be, and how visible it is on the main calendar. We then submit the record’s changes as an authorized packet of data, letting Google’s Calendar system record the details and update the event in the opened calendar. If a user has the calendar accessible on another device, the event will appear or update as directed when the calendar next syncs with Google’s servers.

Under the Hood
But what exactly does that event record the plug-in creates look like? Below is a simplified example of the HTTP header and body for an event record request using the same fields as the script above:

HTTP Header:
POST /calendars/<<Folder ID>>/events HTTP/1.1
Host: www.googleapis.com
Content-Type: application/json
Authorization: Bearer <>

HTTP Body:
{
"end": {
"dateTime": "2019-01-31T13:00:00Z"
},
"start": {
"dateTime": "2019-01-31T12:00:00Z"
},
"attendees": [
{
"email": "john.smith@email.com",
"responseStatus": "needsAction",
"optional": true
},
{
"email": "jane.doe@email.com",
"responseStatus": "needsAction"
}
],
"description": "This is a sample event",
"visibility": "public",
"summary": "Sample Event",
"location": "Main Office"
}

To achieve the same result, you must submit a POST request to the create events endpoint, providing along the authorization header with the authorized access token, and submit the JSON document of the event as the body. While this is relatively simple for one-off submissions, or sending data immediately after authentication, there are other constraints that must be checked when submitting such info, such as:

Are the fields formatted correctly for their data types?
Are there any other settings or parameters that need to be adjusted?
Is the access token stale and in need of refreshing when attempting to submit the request?
How compatible will this process be for a server-side deployment?

It is imperative that the developer knows how best to handle the above situations and designs the system accordingly.

Using the gManipulator plug-in makes the above considerations simpler: the plug-in handles all necessary JSON data conversions; settings are added as optional parameters to the “SaveRecord” function; if the access token goes stale before the request is executed, the plug-in will automatically refresh the token internally before attempting the request again; and the plug-in is compatible with FileMaker Server environments, whether through the script engine or the web publishing engine.

Server or No Server
Speaking of server-side compatibility, how would authentication be possible when the user is required to log in? Server-side functionality, as a rule, means that there can be no user interface allowed; no error dialogs, no user confirmation messages, nothing that would pop up anything that could stop the server from doing its job. Thankfully, the gManipulator comes equipped with an additional function that allows a user to authenticate on the server without needing to be there, so long as they have authenticated previously on a client machine and still maintain an active connection to Google: PCGM_LoadSession.

The PCGM_LoadSession function accepts a session data string, which is a snippet of text that is generated by the PCGM_SaveSession function and contains everything the gManipulator plug-in needs to recreate an active access token in memory. It will untangle the session string, parse out the contents, test the access with Google to ensure it’s a valid connection (refreshing as necessary), and store it all in memory for easy access. Let’s see what the server-side version of our calendar event push script above would look like.

Allow User Abort [Off]
Set Error Capture [On]

# Verify the plug-in is registered…

# Verify that the plug-in is authenticated
Perform Script [“PSOS – Authenticate”; Parameter:]

# Verify that there is data to push…

# Open the Calendar folder that will receive the
record(s)
Set Variable [$result; Value: PCGM_OpenFolder(
Main::gFolderEvents ; “Calendar” )]

# Determine whether to open the record or create a new one in Google
If [not IsEmpty( Events::Google_ID )]
   Set Variable [$result; Value: PCGM_OpenRecord(
    Events::Google_ID ; “Event” )]
Else
   Set Variable [$result; Value: PCGM_NewRecord(
    “Event” )]
End If

# Set the editable fields
Set Variable [$result; Value: PCGM_SetFieldValue(
“Attendees” ; Events::Attendees )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“Description” ; Events::Description )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“Location” ; Events::Location )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“Start” ; Events::Start )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“Summary” ; Events::Summary )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“End” ; Events::End )]
Set Variable [$result; Value: PCGM_SetFieldValue(
“Visibility” ; Events::Visibility )]

# Save the record
Set Variable [$result; Value: PCGM_SaveRecord]

If [$result = “!!ERROR!!”]
   # Error handling…
Else
   Set Field [Events::Google_ID; $result]

   # Pull the Read-Only fields
   Set Variable [$result; Value: PCGM_OpenRecord(
    Events::Google_ID ; “Event” )]

   Set Field [Events::Google_ID; $result]
   Set Field [Events::Created; PCGM_GetFieldValue(
    “Created” )]
   Set Field [Events::Creator; PCGM_GetFieldValue(
    “Creator” )]
   Set Field [Events::Kind; PCGM_GetFieldValue(
    “Kind” )]
   Set Field [Events::Updated; PCGM_GetFieldValue(
    “Updated” )]
   Commit Records/Requests [With dialog:Off]
End If

# Return results
Exit Script [Text Result:”0”]

Unsurprisingly, the scripting is largely the same. We still need to open the Calendar folder, we still need to either open or create the event record, and we still set the fields, save the record, and process the results. The key difference here is the call to authentication. Here’s a breakdown of the server-side demo file’s “PSOS – Authenticate” script:

Set Error Capture [On]

Go to Layout [“Main” (Main); Animation:None]

# Check the parameters
If [Main::SessionInfo = “”]
   Exit Script [Text Result:”No session information
    is saved. Please connect to Google using the
    Client plug-in and save the session.”]
End If

# Attempt to Authenticate
Set Field [Main::gAuthentication Result;
PCGM_LoadSession( Main::SessionInfo )]

# Capture for errors
If [Main::gAuthentication Result = “!!ERROR!!”]
   Exit Script [Text Result:PCGM_GetLastError(
    “Text” )]
Else
   Exit Script [Text Result:0]
End If

# Return results
Exit Script [Text Result:”0”]

Upon calling this script, a server-side execution can be assured that the plug-in will be authenticated to Google, provided that the user on their client machine has previously authenticated, and that the system has stored its session information within the “Main::SessionInfo” field. An easy setup, leading to an easy auth, which feeds into an easy push of information to Google.

Wrapping It All Up
It is important to have a firm grasp of what is required by the service, however, as a little bit of understanding can go a long way in resolving headaches and breaking through development walls. Communicating with Google can be daunting, but armed with the right tools, any developer can cut through and make it simple. Using a tool like the gManipulator plug-in can save precious hours of a client’s development budget through compact and handy functions, but ultimately, the decision of how to accomplish a goal is up to the developer.

For developers who are interested in integrating FileMaker and Google using the gManipulator plug-in, Productive Computing offers a full, online training course on how to integrate FileMaker and Google. This course is available at Productive Computing University.

Author:
Chris Turner
sales@productivecomputing.com
www.productivecomputing.com

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

John Mark Osborne

03/19/2019

I recently had the opportunity to use gManipulator in a client solution. Previously, I had tried performing the integration manually without success. gManipulator made it so easy. I had the basics done in thirty minutes with help from the demo file and documentation.



Add Comment:

First Name:	*
Last Name:
Email:	*
Web Site:
Comment:	* Email Addresses will not be shared on the web site!

Navigation:

Quick Tip:

Fun Stuff: