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.