untitled copy 300x83

April – Meetup: FileMaker Web Service Integration With The FileMaker Admin API

Please join Skeleton Key’s FileMaker STL meetup, Tuesday, April 3rd, 6pm (CT). Colleague, Greg Lane, will present: FileMaker web service integration with the FileMaker Admin API. The FileMaker Admin API allows web services to perform administrative tasks on FileMaker Cloud. During this meeting, Greg L. will show how to use FileMaker Pro to connect to the FileMaker Admin API to manage users and perform other tasks. We’ll look at some general techniques for using the Insert from URL script step with cURL options to integrate with any REST-based web service, so even if you aren’t using FileMaker Cloud, you’re sure to find something useful. Our Meetups are always intended to create dialog among attendees and generate a renewed excitement for the love of what we all do as part of the FileMaker community. Be sure to RSVP now! FileMaker STL About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

sk alexa ask filemaker06

January-FileMaker STL Meetup: “Alexa, ask FileMaker…”

Please join Skeleton Key’s FileMaker STL meetup, January 23, 2018, 6pm (CT). Colleague, Jeremy Upton, will present: “Alexa, ask FileMaker…”.

"Alexa, ask FileMaker..."

SK is Skeleton Key. Skeleton Key is host to FileMaker STL

Hey Siri. Hey Google. Hey Alexa…the holiday season was explosive with its ads for voice command devices. Wouldn’t it be cool if you could say something like “Hey Alexa, ask FileMaker how many contacts live in the state of Texas?”, and have the results of your FileMaker database said back to you? We think so too! And that’s why we are excited to have Skeleton Key’s very own Jeremy Upton lead the meetup topic this month. Jeremy will introduce you to the free, event-driven developer resource provided by Amazon called AWS Lambda. His live demos will use JavaScript, running as a Lambda function, that utilizes an Alexa Skill kit to allow voice controlled queries of a hosted FileMaker file. No JavaScript knowledge is required! Jeremy Upton has been a FileMaker Developer for 20 years starting with version 4. He has been a Skeleton Key crew member since October 2016. Our Meetups are always intended to create dialog among attendees and generate a renewed excitement for the love of what we all do as part of the FileMaker community. Be sure to RSVP now!      

About Skeleton Key

Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business.

In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

 

Skeleton Key is Headed to FileMaker DevCon 2017

Mark Richman, President, Greg Lane, VP of Application Development, and Application Developers: Chad Adams, Jay Sayers, Todd Stark, and Jeremy Upton, are headed to Phoenix, AZ from July 24-27 to participate in FileMaker, Inc.’s annual Developer Conference aka DevCon. They’ll be staying at The JW Marriott Phoenix Desert Ridge, host hotel of more than 50 DevCon sessions. They will join over 1,500 FileMaker developers to network, catch up with colleagues. Primarily, they will take advantage of the variety of session tracks, to further their knowledge with tips, best practices, and techniques to continue building powerful business solutions for our clients. Mark R., Greg L. and Chad A. will have the honor to host the following sessions:

Other Highlights:

Other Important Links – #FileMakerDevCon

About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

Web Service Int

A Simple Browser-Like Back Button in FileMaker

I recently tackled a request from a client to implement a back button that functioned similar in nature to the back button in a web browser. This is certainly not an uncommon request and a simple explanation as to why this isn’t practical normally results in the client withdrawing their request. However, the customer was adamant that they wanted this functionality. So, I set out to design a solution for them and came up with a solution using snapshot links.

Snapshot History Demo

Using snapshot links (.fmpsl), a user’s layout, found set, sort order and current record can be restored without any fuss. The trick is making this whole process as seamless as possible for the user. And a few other, minor, gotchas. The general idea behind this is simple and requires only a few aspects to be in place. I have split these up into the following categories: script triggers, snapshot history and restoring snapshots.

Script Triggers

The first step is to create a script trigger that saves a snapshot link every time the layout changes. This snapshot link will store the user’s current layout, found set, sort order and current record in the snapshot link. The snapshot link is generated with a timestamp in the filename to keep it unique and the snapshot links are stored in the temporary path. The second step is to create a script trigger that updates the snapshot history variable (detailed below) when a new window is opened. This script will remove the most recent snapshot link path from the history variable. One thing of note: On Layout Exit script triggers will not be triggered if you navigate away from the layout while in find mode. Keep this in mind when creating navigation scripts.

History

At the time the snapshot link is created the full path for the snapshot link is stored in a global history variable ($$history). Every snapshot link gets added to the beginning of the history variable with a return character at the end. The result is that the history variable contains a return-delimited list of paths to snapshot links.

Restoring Snapshots

Here is where things get to be a little trickier and where making things seamless comes in to play. Here is a brief timeline of what occurs when a snapshot link is opened: 1. The solution is opened in a new window (at the default location and size) and the default layout is displayed. 2. The OnWindowOpen script trigger is fired. 3. The snapshot loads the layout, found set, sort order and current record. The first problem you will run into is that you haven’t actually navigated back to the desired layout and found set. You now have two open windows, one of which has a window name of the filename for the snapshot link. This new window is not ideal. I was able to resolve this with the following: Store the current window name, location and dimensions in global variables before opening the snapshot link and rename the current window. Restore the window to the location and dimensions we stored once the snapshot link is opened. This can be accomplished by the OnWindowOpen script trigger. NOTE: You cannot do this step in the same script that opens the snapshot link. You will need a separate script that is triggered and ran by the window opened with the snapshot link. Rename the new window then close the old window. But don’t create a snapshot for this window! See other notes below. This results in the user seeing a brief flash on the screen followed by their previous layout and found set now being restored. Nearly seamless!

Other Notes

The solution I built this feature for had some dialog windows at various points. I did not want to include these dialog windows in the history so I incorporated a bypass global variable. Any script that opened a dialog window would set the bypass variable to 1. I then incorporated an if statement at the beginning of my scripts that set bypass to “” and exited the script when bypass was set to 1. This effectively routes around the creation of the snapshot link, where desired. This was also useful when I realized that closing the old window caused a new snapshot link to be created. This technique is not the most robust method out of the box but it could certainly be expanded upon. One idea I had was to encode additional parameters into the snapshot link name. These parameters could identify active layout objects, local variables, window parameters, etc. This was not necessary for this particular implementation but could be added into future implementations.

Demo File

I have put together a simple demonstration file detailing the methods I described above. Click here to download the demo file.

Calvin Cooper is a FileMaker Certified Developer at Skeleton Key.

About Skeleton Key

Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

FileMaker CWP performance tip

If you have a website that connects to a FileMaker database, this simple tip might help improve performance. One particular site we built connects to a legacy FileMaker database that was built years ago by another developer. The solution has one of the more complex relationships graphs I’ve seen, and it takes over ten seconds for a FileMaker Pro client to open the hosted file. Ten seconds is not a big deal when you’re opening a database once per day. However, it turns out that this ten-second delay was also affecting our website. As we investigated the reports of intermittent performance issues, we found that the first page a user accessed would sometimes take more than ten seconds to load. When the FileMaker Server Web Publishing Engine (WPE) receives an XML or FileMaker API for PHP request, it opens a connection to the database and fulfills the request. After the request is processed, the WPE will wait three minutes before closing the connection. If additional requests are received while the connection is open, that connection will be reused and the three minute timer starts over. When we finally connected the dots and realized what was happening, the solution was obvious…we just needed to keep the database connection open constantly so that users never experience the delay. In our case, we simply created a FileMaker script with an “Insert from URL” script step that calls a PHP page that loads a small amount of data from the FileMaker database. We then created a schedule in FileMaker Server to run the script every minute. The same thing could be accomplished in a number of ways, such as the Laravel task scheduler or even a cron job. This simple change made a significant difference for the website users who no longer encounter that ten-second delay.

Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business.

In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

Skeleton Key is headed to FileMaker DevCon 2016

top, right: Mark Richman; bottom, left to right: Calvin Cooper, Chad Adams, Greg Lane top, right: Mark Richman; bottom, left to right: Calvin Cooper, Chad Adams, Greg Lane Mark Richman, President, Greg Lane, VP of Application Development, Chad Adams, Senior Developer and Trainer, and Calvin Cooper, Developer, are headed to Las Vegas from July 18-21 to participate in FileMaker, Inc.’s annual Developer Conference aka DevCon. They’ll be staying at The Cosmopolitan, host hotel of more than 60 DevCon sessions. They will join over 1,500 FileMaker developers to network, catch up with colleagues and take advantage of the variety of session tracks including core, design, innovation, mobility, business & web, to further their knowledge with tips, best practices, and techniques to continue building powerful business solutions for our clients. Mark and Chad will have the honor to host three of these sessions. SESSIONS:

  • Mark will contribute to the business track in his session, Developing a Sales Process, on Tuesday, 7/19 at 10:45am. You’ll get the most from this session, if you believe that influencing others is part of your job, and that repeatable success depends to some degree on repeatable behavior.
  • Mark will also contribute to core track in his session, Designing for WAN Performance, on Wednesday, 7/20, 9:00am. To get the most out of this session, attendees should be familiar with most aspects of the FileMaker Platform (i.e. schema, security, structure, and business logic) and should have some experience deploying and administrating FileMaker Server.
  • Chad will contribute to core track in his session, Relationships Graph Techniques: Which is Best – and When? on Wednesday, 7/20, 3:45pm. To get the most out of this session, Attendees should have some experience building FileMaker custom apps and be familiar with the concept of FileMaker relationships and the Relationships Graph. Scripting experience will be helpful.

COMPETITION:

During the conference, small teams will demonstrate their creativity and expertise, competing in an informal hackathon to design simple-but-useful custom apps for three Las Vegas area nonprofits. Learn more here. Skeleton Key’s Chad Adams & Calvin Cooper will participate on team Four Empty Coffee Cups in competition with 5 other teams, creating a custom solution for the Nevada non profit, Foster Kinship. DC5-Full w-no-names OTHER HIGHLIGHTS:

  • Ask The Experts: Tech Support Central – drop in Tuesday – Thursday
  • Ask The Experts: Visionary Bar – sign up for a time slot Tuesday – Thursday
  • Ask the Experts: Office Hours – sign up for a time slot Tuesday – Thursday

OTHER IMPORTANT LINKS – #FileMakerDevCon

About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

Sizing layouts for Android devices for FileMaker 15 WebDirect

With the release of FileMaker 15 Server the Chrome browser on the Android operating system is now supported for use with FileMaker 15 WebDirect when using devices with a five inch or larger screen. I have spent some time testing this out and I’ve found that things seem to work as well as it does on the other platforms. However, during my testing I did come across something I found to be a bit odd and not quite as intuitive due to the variety of Android screen sizes and resolutions. iOS has historically used whole number multipliers to scale to the native screen resolution. Using either a 2x or 3x multiplier they can scale the output resolution to the native screen resolution and keep all elements of the items large and legible on the device screens. Android uses multipliers in the same manner as iOS, however the difference is that Android strays from simple 2x and 3x multipliers. Depending on the device resolution the multiplier may be anywhere from 1.3x to 3.5x! With the increasing density of new devices this problem is only going to get worse. Fortunately, there is a very nice reference available at https://design.google.com/devices/ that provides the dp (density-independent pixels) of various popular devices. Using the dp value you can determine your starting point when designing the size of our FileMaker layout. But the fun does not stop there! To complicate things the actual usable space that is available on the device is further limited by things such as the notification bar, status toolbar and button bar. And…one last thing: There is no defined cut off between pixel density for tablets. I’m not going to go into too much detail since the Android developer documents do a great job of explaining why this is complicated. I will just say that a general rule would be that if the width is 600dp or higher the device is most likley a tablet and will behave slightly different from a phone. https://developer.android.com/guide/practices/screens_support.html#DeclaringTabletLayouts

How To Calculate Usable Screen Size

First, some important numbers: 1 dp = ~1pt

iOS:

iOS Screen Elements Notification Bar and Safari Search Bar Height: 64 dp Safari Tab Bar Height: 32 dp Status Toolbar Height: 44 dp

Android Phone:

Android Screen Elements Notification Bar and Chrome Search Bar Height: 80 dp Status Toolbar Height: 44 dp Button Bar Height (Portrait): 48 dp Button Bar Width (Landscape): 48 dp

Android Tablet:

Notification Bar and Chrome Search Bar Height: 120 dp Status Toolbar Height: 44 dp Button Bar Height (Portrait and Landscape): 48 dp

iOS calculations:

For iOS devices it is simple to calculate how much space the notification toolbar and status toolbar take up since the layout elements are consistent across phones and tablets. For iOS devices use the following formula to determine the usable vertical dp: Without Status Toolbar and no tabs = vertical dp – 64 dp With Status Toolbar and no tabs = vertical dp – 108 dp Without Status Toolbar and tabs = vertical dp – 96 dp With Status Toolbar and tabs = vertical dp – 140 dp

Android calculations:

For Android devices it is slightly more complicated since chrome is displayed differently on tablets than on phones. You also have to take into account the button bar for devices that have on-screen buttons. Tablets always move the button bar on the bottom of the screen when switching between portrait and landscape orientation. Phones keep the button bar on the bottom or right, depending on orientation. This, of course, only applies if your Android device has on-screen buttons.

For Android phones without on-screen buttons:

Portrait and Landscape: Without Status Toolbar = Vertical dp – 80 dp With Status Toolbar = Vertical dp – 124 dp

For Android phones with on-screen buttons:

Portrait Orientation: Without Status Toolbar = Vertical dp – 128 dp With Status Toolbar = Vertical dp – 172 dp Landscape Orientation: Without Status Toolbar = Vertical dp – 80 dp With Status Toolbar = Vertical dp – 124 dp Available Width = Horizontal dp – 48 dp

For Android tablets without on-screen buttons:

Portrait and landscape: Without Status Toolbar = Vertical dp – 120 dp With Status Toolbar = Vertical dp – 164 dp

For Android tablets with on-screen buttons:

Portrait and landscape: Without Status Toolbar = Vertical dp – 168 dp With Status Toolbar = Vertical dp – 212 dp

Calculating WebDirect Screen Size The Other Way

As an alternative to the math: FileMaker 15 has some built in functions that will calculate the usable space available to you. You can use the Get(WindowContentHeight) and Get(WindowContentWidth) functions to return the usable space in pts that is available at the time you call the function. Showing or hiding the toolbar will allow you to determine the usable space for either scenario. Of course, you need to be calling the function on the device while connected with Web Direct to get a result on the device. This makes it a bit more time consuming to set up if you don’t already have FileMaker WebDirect configured and running.

Calculating WebDirect Screen Size the Easy Way

To make calculating the screen size even simpler I have created a simple utility that will calculate the usable space available for your device. This Javascript based utility uses the formulas above to determine the usable space for your device. To use this tool simply navigate to the link below from your target device and view the results! https://skeletonkey.com/webd_screen_calculator.html Calvin Cooper is a FileMaker 15 Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

fm webdirect 17 app icons

FileMaker Go 15 background scripts

When FileMaker Go is running a script and the user locks the device or switches to another app, the script is paused and FileMaker Go enters a suspended state. Check out this video for a simple technique to allow a script to continue to run while FileMaker Go 15 is in the background. This can be a great way to help ensure a long-running script isn’t interrupted by an impatient user.  

Demo File

Here’s a demo file.

Greg Lane is VP of Application Development at Skeleton Key and a FileMaker Certified Developer.

About Skeleton Key

Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business.

In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

Integrating Autotask with FileMaker

Autotask to FileMaker

Group Session #1

Hi. My name is Andy and I’m an API Addict. In my defense, I have to say it’s all Brian Dunning’s fault. See, when I was first starting out with FileMaker, and discovered all that sample data on Mr. Dunning’s website, I found I couldn’t get enough. I was sorting, and parsing, and testing, and munging, and wrangling, and…and…and I just kept needing bigger and bigger datasets to “work” with. When I’d gone through the biggest one there, I realized I still needed more. Then I discovered APIs—unlimited, constantly changing data from all kinds of different sources, on all kinds of topics—and things started to get out of hand. Plus, for consuming these APIs, there were FileMaker’s Web Viewers, and there was PHP…and then later there was the glorious “Insert from URL” which allowed me to do things server-side—impossible things…crazy things…insane things—I think I might need help. Starting tomorrow, I promise I’ll get it under control. Today, though, I thought I might share some of the cool things I’ve discovered that you can do with the API supplied by Autotask. Autotask is an online Professional Services Automation tool used by many development and IT firms. As is the case with a lot of these systems, the data gets much more useful when you can get it into your own FileMaker solution and muck around with it as you see fit.

The Raw Materials

  1. Be sure you have your FileMaker Server configured for Custom Web Publishing with PHP. I won’t get into that here; there’s lots of info about that online, particularly here and here.
  2. Get the OpenDNS PHP SOAP wrapper for the Autotask API here from Github. Although the Autotask API is not too complex on its own, it is a little unusual in its query structure, so this wrapper is very helpful for making things go more smoothly.
  3. You’ll find it useful, when figuring out what things are called and where they’re located, to have the official API Documentation (PDF) handy.
  4. The Autotask API uses regular user accounts for authentication, rather than separate “API-only” accounts, so you’ll need to have those credentials handy, too.
  5. Of course, you’ll also want to have a FileMaker file hosted on your FileMaker Server, and for that file, you’ll want the credentials for at least one account from a Privilege Set which has the fmphp Extended Privilege enabled. That account may also need other privileges, but we’ll look at that later.

The Staging

We’re going to build a small web application for this. Even though there won’t be many (or any) human users going in and interacting with it, your FileMaker solution is going to make regular use of it, feeding it input and receiving output. So it’s still technically a web application. Call me old-fashioned, but I like to have a semi-standard directory structure when putting together a web app. I like to put images in their own folder (usually called “images” or “img”), stylesheets in their own folder (“stylesheets” or “css”), and “functional” elements, like JavaScript files and PHP “includes” or ajax processors, in their own folder (usually called “lib” for “library,” and this is sometimes further broken down into separate folders for JavaScript files and PHP, depending on how complex the app is going to be). For FileMaker web apps, I also like to include the standalone API files, even if the app is hosted on the FileMaker Server, basically for portability. If I ever have reason to move it to a standard web host (one without FileMaker), the app already has everything it needs, so then I just have to change where it looks for FileMaker Server. Here’s the starting setup:

  1. Create a folder in your web root (the web root is most likely your FileMaker Server’s “htdocs” folder). For our purposes, let’s call this “ATMunger.” That’s also what I’m going to assume your FileMaker file is called. Substitute as necessary.
  2. In that folder, create a blank “index.php” file.
  3. Also in that folder, create a subfolder called “lib.”
  4. Unzip the PHP SOAP wrapper file you got from Github above. Inside the resulting folder (which, at the time of this writing, should have named itself “autotask-php-master”), there is a subfolder called “src”. Copy the contents of “src” and put them into your “lib” folder, so now in “lib,” there are 3 PHP files, plus one subfolder called “AutotaskObjects.”
  5. Get the “FM_API_for_PHP_Standalone.zip” file from your FileMaker Server installation’s “Web Publishing” folder, and unzip it into “lib” (which should add a 4th PHP file called “FileMaker.php” and a second subfolder called “FileMaker”).
  6. So now, your directory should look something like this:
    • ATMunger/
      • index.php
      • lib/
        • ATWSException.php
        • autoload.php
        • AutotaskObjects/
          • {about 10 dozen PHP files which come with the OpenDNS PHP SOAP wrapper}
        • Client.php
        • FileMaker.php
        • FileMaker/
          • {files & subfolders which come with the FileMaker API standalone set of files}

Now, in the “lib” folder, create a plain text file and call it “db.inc.” There will be debates until the end of time about where this file should be located and what it should be called for security’s sake, but for our purposes, let’s put it here. Just understand that if someone wants to unearth all your saucy little secrets, and they’re a reasonably imaginitive web developer, they could take a guess and enter “http://your.IP.address/ATMunger/lib/db.inc” and your browser would happily display it in all its unprotected security-breaching glory. You could change the extension to “php” and that wouldn’t happen quite so easily. You could also put this file outside your web root so that nasty hacker couldn’t use their browser to navigate to it. I’ll leave it up to you to explore the security options that work best for you, but for this admittedly insecure example, it’s “db.inc” and it’s in “lib.” You’ve been warned. Inside that file, add some basics your web app will need whenever it interacts with FileMaker or Autotask:

<?php define('FM_HOST' , '127.0.0.1'); define('FM_FILE' , 'ATMunger'); define('FM_USER' , 'myFMPHPUser'); define('FM_PASS' , 'crazyStupidFineSecretPassword'); define('AT_USER' , 'aRealLiveAutotaskUser@example.com'); define('AT_PASS' , 'anotherCrazyStupidFineSecretPassword');

You’ve probably seen the first 4 pieces (prefixed with “FM”) before – they specify the host address, file name, username, and password, respectively, for our app’s FileMaker file. The next two pieces (prefixed with “AT”) are the username and password we’ll use to get into Autotask. Once again, there are more secure ways to do this – you could, for example, use a PHP session model and have the user log in each time. Explore at your leisure; for this, we’re logging in this way. Now let’s create a helper file to gather some necessary PHP scripts and define some more basics to make things easier later. In “lib,” create a PHP file called “functions.php,” and add the following to it:

<?php require_once('lib/FileMaker.php'); require_once('lib/autoload.php'); require_once('lib/db.inc');  function atLogin($username,$password,$wsdl) {     $opts = array('login'=>$username,'password'=>$password,'trace' => 1);     $client = new ATWSClient($wsdl, $opts);     return $client; }

The first 3 lines here bring in the FileMaker API, the SOAP wrapper for Autotask, and the credentials file we set up earlier. We use “require_once” here so PHP knows that if for some reason they’re called again in another script, it should just ignore that and use the instance loaded here. The next block is a function based on the syntax used by the SOAP wrapper. It takes the Autotask username, password, and wsdl (more on that in a minute) as parameters, and creates an object representing the logged in “client” of the Autotask API. If you’ve done much PHP work with FileMaker before, this is pulling together the equivalent of the “new FileMaker($host,$file,$user,$pass)” kind of thing. About that $wsdl thingy. Autotask divides the world into 12 zones, each representing a country, region, or special area. Each Autotask client is based in a specific zone, and each zone has its own domain. You have to point your app at the right domain for your zone (the zone associated with the username you’re using) in order to work with the API. Luckily there’s an easy way to find out what that is. First, let’s set up a testing/utility file for things like this. While in development, I often do this to try out code options before deciding on the best way to go for a particular function. In “ATMunger,” create a file called “test.php.” To get your zone info, add the following code to it, then load it up in your browser (BTW, this is taken almost verbatim from the very helpful README.md file included with the SOAP wrapper):

<?php require_once('lib/autoload.php'); $username = 'aRealLiveAutotaskUser@example.com'; $wsdl = 'https://webservices.autotask.net/atservices/1.5/atws.wsdl'; $opts = array('trace'=>1); $client = new ATWSClient($wsdl, $opts);  $zoneInfo = $client->getZoneInfo($username); echo '<pre>'; print_r($zoneInfo); echo '</pre>';

If everything goes right, you should get something that looks like this:

stdClass Object (     [getZoneInfoResult] => ATWSAutotaskObjectsATWSZoneInfo Object         (             [CI] => 123456             [DataBaseType] =>              [ErrorCode] => 0             [URL] => https://webservices25.autotask.net/ATServices/1.5/atws.asmx             [WebUrl] => https://ww25.autotask.net/         )  )

The info you’re looking for is labeled “URL” – in this example, that’s “https://webservices25.autotask.net/ATServices/1.5/atws.asmx” (that’s not a real address – be sure to get the right one for your account). The URL you get is what you’ll pass as the $wsdl parameter to the atLogin() function we defined earlier. In fact, so you don’t have to keep typing that, go back into “lib/db.inc” and add this to the end (using the URL you just got, of course):

define('AT_WSDL' , 'https://webservices25.autotask.net/ATServices/1.5/atws.asmx');

Getting Autotask Data

Here’s where the fun stuff begins. The Autotask API exposes almost every type of data available, so the general rule is, if it’s in there, you can get it out. The full list of tables (or “Entities,” as they’re called in the documentation, or “Categories” as they’re called in the LiveReports Designer) is available in the PDF. But for now, let’s assume you want to get info about your “Accounts.” As you may know, in Autotask, “Accounts” are basically your Customers or Clients. First, create a file in “ATMunger,” and call it “accounts.php.” Head it up with some basics:

<?php  require_once('lib/functions.php');

Notice there that we’re including our “functions” file, which in turn will load up the FileMaker API, the SOAP wrapper, and our atLogin() function for us, so they’re ready when we need them. As it turns out, we need some of them in the very next line:

$client = atLogin(AT_USER,AT_PASS,AT_WSDL);

This creates a “logged in user” object for the SOAP wrapper and the Autotask API, and sticks that object into the variable called $client. That’s a fancy way of saying we can now use this variable to get in and do some real investigation of our Autotask data. Next, since we’re going to look at Accounts, we have to set up a “Query object” and point it at the “Account” entity.

$accountQuery = new ATWSAutotaskObjectsQuery('Account');

You can see the important bits there: we’re defining a variable called “$accountQuery,” and in it, we’re storing a Query object, which queries the entity called “Account.” This might be thought of, in FileMaker terms, as going to a particular layout in order to set the context for a Find. Next we need to add query parameters. For each field we want to specify find criteria for, we need something like this:

$accountActive = new ATWSAutotaskObjectsQueryField('Active'); $accountActive->addExpression('Equals', '1'); $accountQuery->addField($accountActive);

Note there are three lines there.

  1. The first line creates a new variable (“$accountActive“), and stores a “QueryField” object in it, and points that to the field called “Active.”
  2. The second line takes that variable object, and extends it to include an operator (“Equals“) and a value to test against (“1“).
  3. The third line takes what was set up in the first two lines and adds it to the Query we started building on the Accounts table.

Whew! Long way to go to add one field to a find! But…it’s pretty simple, really. Verbose, but simple. Specify the table, then for each condition you’re searching for, add a field with an operator and a value to test against. The list of operators it accepts is impressive (and can be found on page 200 of the PDF, so I won’t get into it here). You’ll repeat that for each field you want to query. For this example, let’s just say we want to get all Active accounts (in FileMaker-speak, “Accounts::Active=1”), so the query we just added is all we need. The next step is to execute the query (notice that we’re logging in with the $client variable we set up before)…

$accountResult = $client->query($accountQuery);

…and then put the results of that query into an array…

$accounts = $accountResult->queryResult->EntityResults->Entity;

Once we’ve got that, we can loop through it and get stuff ready to send over to FileMaker. A few additional notes:

  • Every table has a field called “id” – it’s a good idea, for several reasons, to capture that. More on that later.
  • The API limits you to getting 500 records out at a time. If you believe you have more than that, you can use the id field to figure out where you left off and loop through until you’ve got them all. The id field is just an auto-incrementing number field, so for each pass, you’d just add a query condition for an id that is greater than the last one you got in the previous pass.
  • There’s no way to specify in a query that you want to get back only certain fields – you always get them all. You don’t necessarily have to use them all, though…that’s why we’re going to loop thru and just pull out what we need.
  • You can query UDFs (User-Defined Fields), but only one at a time. In other words, if we had, say, 5 fields we were adding conditions to for our query, only one of them could be a UDF. Otherwise they work almost the same way as regular fields, except you have to put “true” in as a second parameter on the QueryField object, e.g. “new ATWSAutotaskObjectsQueryField(‘MyUDF’,true);.”
  • You can only query one table at a time – there’s no equivalent of a SQL “join” or a FileMaker relationship that you can add into your queries.

So, with all that in mind, let’s go through the new $accounts array and gather up some data. First, since we’re going to use a FileMaker script to bring the data in (much, much, MUCH faster than any other method, and only requires one call to the server), let’s start by initializing a variable to collect the Autotask data into a script parameter, and also a message variable so we have somewhere to put a message about the success (or failure) of what we’ve done here:

$params = ''; $messageOut = '';

Let’s assume, for now, that you really just want to get the Autotask IDs out of here and into FileMaker, so you can then use those to query all the other fun stuff like Contacts, Contracts, Projects, Opportunities, Invoices, and others by searching on their AccountID fields (yes, most tables have that…see how cool this could get?). Let’s also assume that each of your accounts has a unique identifier, such as an account number or client code, stored in both Autotask and in FileMaker, which we’ll use to match them up later. So let’s get the Autotask id, account name, and account number/code for each account, and add that info to the $params variable in a “pseudo-XML” format (more on that later):

foreach($accounts as $account) {  	$id = $account->id; 	$code = $account->AccountNumber; 	$name = $account->AccountName; 	 	$params .= "<account><id>{$id}</id><code>{$code}</code><name>{$name}</name></account>"; 	 }

Sharp-eyed PHP junkies may be thinking “Why not just use PHP’s built-in SimpleXML functions to populate $params?” Well, there may be cases where you want to grab UDFs (User-Defined-Fields), and they’re stored in an array within the record array. That can get messy unless you’re defining things individually, as we’re doing here. Also, as mentioned before, the API doesn’t let you specify what fields to return—it’s all or nothing—so if we wanted to reduce the amount of data we have to pass back to FileMaker (always a good idea), we’d still have to loop through the results to pull out the fields we wanted. There, now, aren’t you glad you asked?

Now let’s fire up the FileMaker PHP API and get all this juicy new data in:

   $fm = new FileMaker(FM_FILE, FM_HOST, FM_USER, FM_PASS);   $request = $fm->newPerformScriptCommand('MyLayout','MyScript',$params);   $result = $request->execute();   if(FileMaker::isError($result)) {    $msg = $result->getMessage();    $code = $result->code;    $messageOut = "FileMaker PHP Error: '{$msg}'";    $messageOut .= ($code != '') ? " (#{$code})" : "";   } else {     $messageOut = "Success!";   }

Fairly standard stuff there – we’re using the constants we set up in our “db.inc” file to create a new connection to FileMaker, and we’re running a script called “MyScript” on a layout called “MyLayout” and passing it all the Autotask data in the $params variable.

We’re also trapping for any error that might be thrown back by FileMaker, and sticking whatever error message we get into the $messageOut variable. If there’s no error, $messageOut will just say “Success!”

The last thing to do in the PHP script is surround the last few pieces in an “if” block, to give us just a little more error trapping, and some useful feedback (which we’ll use in a minute). So the whole thing looks like this:

   <?php    require_once('lib/functions.php');   $client = atLogin(AT_USER,AT_PASS,AT_WSDL);   $accountQuery = new ATWSAutotaskObjectsQuery('Account');   $accountActive = new ATWSAutotaskObjectsQueryField('Active');   $accountActive->addExpression('Equals', '1');   $accountQuery->addField($accountActive);   $accountResult = $client->query($accountQuery);   $accounts = $accountResult->queryResult->EntityResults->Entity;       $params = '';    $messageOut = '';   if($accounts) {    foreach($accounts as $account) {        $id = $account->id;    $code = $account->AccountNumber;    $name = $account->AccountName;        $params .= "<account><id>{$id}</id><code>{$code}</code><name>{$name}</name></account>";        }            $fm = new FileMaker(FM_FILE, FM_HOST, FM_USER, FM_PASS);    $request = $fm->newPerformScriptCommand('MyLayout','MyScript',$params);    $result = $request->execute();    if(FileMaker::isError($result)) {    $msg = $result->getMessage();    $code = $result->code;    $messageOut = "FileMaker PHP Error: '{$msg}'";    $messageOut .= ($code != '') ? " (#{$code})" : "";    } else {     $messageOut = "Success!";    }           } else {     $messageOut .= 'Error: No accounts found.';   }   echo $messageOut;

So, basically, what this will output to the browser is a message indicating what happened. It’ll either say “Success!” or it will have some sort of description of an error. We’re not outputting any actual data to the browser a) because it’s not really necessary in this context, and b) it’s more secure this way – no prying eyes will see anything about your accounts (or whatever else you may look up using this method). The message we output here, though, becomes important when we use this in FileMaker…see below:

Using Autotask Data in FileMaker

For this example, we’re just going to create new records in a “join” table which will tie our existing Account or Customer records in FileMaker with any future data we want to use from Autotask (say, for example, we created a FileMaker table for Project data from Autotask – this will connect that to your existing Customers). Naturally there are about a kajillion different ways to use this kind of data…but we’ll keep it simple for now. To get ready, create a table in FileMaker (let’s call it ATAccounts), and give it at least these fields:

  • AutotaskID (a number field)
  • AccountName (a text field)
  • AccountCode (another text field)

Naturally, you follow best practices and will also include a record ID field of some sort (serial increment or UUID) to uniquely identify these records, and creation/modification account and timestamp information. But you’ve done that so often you don’t even need to think about it, it’s just automatic. Right? RIGHT??

We’ll need a layout for this new table. You can use either the layout created automatically, or create a new one just for use by this process (that would be my choice). The PHP above calls for a layout named “MyLayout”…they’ll have to match, so you’ll need to either use that for your layout name or change the name in the PHP.

Since we’re passing the data in a “pseudo-XML” format, it will be helpful to get some custom functions to parse it. There are a few that I’ve been using for years for this and really like. ExtractData is the one we’ll be using to get individual Accounts, and we’ll use xmlGet to get the info from within those accounts.

Next, we’ll need a script for PHP to call. Like the layout, we’ve already specified a name in the PHP (‘MyScript’), so you know what to do: either use that for your script name, or change the PHP to match your script. The other thing to consider about this script is whether to grant it “Full Access” privileges. The only thing I can say about that is, it depends. If the script has Full Access, you won’t have to grant as much to the user account which PHP is using (but that user account, at the very least, must have permission to run the script). Then again, if you have concerns about who may have (physical) access to the script, you may not want to give it that much permission. I’ll leave that up to you and your individual situation.

On to the script itself. For this kind of thing, I like to start by making sure we’ve got something to work with. In this case, that means we need a script parameter. If we don’t, we just exit the script (and if you’re doing this server side, perhaps have some sort of error logging; if you’re on the client, perhaps a custom dialog). If we do have a parameter, it’s most likely XML passed from PHP, so we’ll stick it into a variable called, poetically “$x,” then go to our layout (I know we already specified the layout in the PHP; call me paranoid, but hard experience has taught me to hedge my bets regarding context), like so:

   If [ IsEmpty(Get(ScriptParameter)) ]    Exit Script [ ]   Else    Set Variable [ $x ; Value: Get(ScriptParameter) ]    Go to Layout [ "MyLayout" (ATAccounts) ]   End If

Next, we’ll set up a few variables for control of the loop. The first will be the count of incoming accounts we’re going to be dealing with (we’ll call it “$incoming”), so we know when we’re “done.” We’ll get that by counting the occurrences of “<account>” (note that we include the brackets – if we just counted the occurrences of the word, we’d get both the opening and closing tags). We’ll also need an iterator (“$i”) to keep track of where we are.

   Set Variable [ $incoming ; Value: PatternCount($x;"<account>") ]   Set Variable [ $i ; Value: 1 ]

Finally, the loop itself. In a nutshell, we open and close the loop with our control stuff – start off by exiting the loop if our iterator is greater than the count of incoming accounts, and end by incrementing the iterator by 1. Then we use ExtractData to extract “this” occurrence of the account element (that’s why I like ExtractData for this part – you can use the $i variable to tell it which account you want). Then we create a new record, and set our fields using xmlGet to extract the individual pieces of data from within “this” account.

   Loop    Exit Loop If [ $i > $incoming ]    Set Variable [ $this ; Value: ExtractData($x;"account";$i) ]    New Record/Request   Set Field [ ATAccounts::AutotaskID ; xmlGet($this;"id") ]    Set Field [ ATAccounts::AccountName ; xmlGet($this;"name") ]    Set Field [ ATAccounts::AccountCode ; xmlGet($this;"code") ]    Set Variable [ $i ; Value: $i+1 ]   End Loop

And that’s it! Well, almost. We still have to get something to actually run the PHP script, which in turn runs the FileMaker script. The way I handle that is with another FileMaker script, scheduled server side, which runs on a “Globals” table (a table with some global fields for temporary stuff like this), and looks something like this:

   Go to Layout [ "Globals" (Globals) ]   # Start a timer   Set Variable [ $startTS ; Value: Get(CurrentHostTimestamp) ]   # Run the PHP script using Insert from URL   Insert from URL [ Select ; No dialog ; Global::MyGlobalField ; "http://"&Get(HostIPAddress)&"/ATMunger/accounts.php" ]   # Give it some time to finish   # Move on when the global field contains the PHP's output message or after we've run for 30 seconds. Loop    Pause/Resume Script [ Duration (seconds): 2 ]    Exit Loop If [ (PatternCount(Global::MyGlobalField;"Success!")) or    (PatternCount(Global::MyGlobalField;"Error")) or    (Get(CurrentHostTimestamp)-$startTS > 30) ]   End Loop

Naturally we’re only scratching the surface here, but the possibilities are endless. Now you can loop thru each of these new records and grab Projects, Invoices, etc., etc. using the Autotask ID for each of your Accounts. One way to do this would be to set up a PHP file for each entity type (or one big one with a switch statement), send it the ID in the query string (like “projects.php?id=1234”), and have it run a FileMaker script to bring in the new info, just like above.

Have fun with it, and let us know in the comments if you come up with a new or novel use for this!

Andy Frazier is a FileMaker 15 Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.

FileMaker naming conventions

In a recent discussion at the FileMaker Community a question was asked about why a FileMaker developer would choose NOT to use SQL-friendly field names. It’s an important question that I believe many FileMaker developers often answer incorrectly. Here’s a bit about how my view of naming conventions has changed over time. For reference, a SQL-friendly field name in FileMaker would be a name that:

  • starts with a letter
  • contains only letters, numbers, and underscore characters
  • is not a reserved word (an evolving list of over 200 words including DATE, FIRST, FROM, VALUE, ZONE, etc.)

Developers also often adopt naming conventions that use PascalCase, camelCase, or a variety of prefixes and suffixes to indicate properties of the field or alter the sort order of fields. I was once in the camp that thought it was a best practice to always use these kinds of naming conventions to optimize developer productivity. I come from a computer science background with experience in dozens of different languages, each with its own arcane naming rules. Some of those languages were created when every byte was precious…many computers had just a few kilobytes of memory, data transfer rates were measured in bits per second, and programs and data were stored on punched cards or magnetic tape. I started using some amalgamation of the various rules I knew when I began using FileMaker in 1986. Over the years, my naming conventions evolved modestly as the FileMaker platform changed and I was exposed to other developers’ code. When I joined Skeleton Key about eight years ago I was challenged with a different philosophy: optimize for the end-user’s experience by using natural names for anything that might be exposed to users. In FileMaker, this includes things like table occurrences, fields, and layouts. This approach allows the developer to empower the user by exposing more of FileMaker’s native interface. While I had historically hidden the Status Toolbar and most menu commands, it suddenly made sense to encourage users to use table view, the layout menu, the import/export dialogs, sort dialogs, and saved finds. Field labels matched the actual field names and we no longer needed to hide a confusing and “unnatural” schema from users. We no longer needed to script every sort and export because the dialogs could now make sense to users. Honestly, I didn’t take to the idea immediately. It was an uncomfortable change and I was pretty sure I would eventually hit a productivity wall. But as time passed and my thinking shifted, it was a truly liberating change. From a developer perspective, being able to drag all of a table’s fields from the field picker without having to edit the field labels is a huge time saver. Users have a better experience overall and they often get the benefit of new features that are added to the FileMaker platform without having to make any development changes. There are some downsides to using natural names. However, every single one of them are minor inconveniences that only impact the developer. The FileMaker experience is greatly enhanced when we let FileMaker be FileMaker. When we bring baggage from past experience and ancient languages into FileMaker, we quickly paint ourselves into corners and our users’ experiences are diminished. New developers at Skeleton Key sometimes ask, when should I NOT use natural names? My most frequent answer is that we often work on FileMaker solutions created by other developers (or our past selves) and that if the solution has a consistently-applied naming convention, we should stick to it. We value consistency within a solution more than our own standards. There are also other special situations for vertical solutions, enterprise systems, or integration requirements that sometimes require us to de-prioritize the user experience and adopt a different naming convention for specific systems. Are your FileMaker development standards and naming conventions focused on your experience as a developer or do they truly enhance the end-user experience? GL_BW_2011-06 Greg Lane is VP of Application Development at Skeleton Key and a FileMaker Certified Developer. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.