Introduction

FileMaker is a database solution that while not as popular as systems like Access, MySQL, SQL Server, Oracle, etc., still has a very large number of users building solutions on it every single day.

In this article I’d like to discuss a little bit about what exactly FileMaker is, the different techniques you can use for moving data in and out of it, and then I’ll cover the use of some custom functions that I’ve developed specifically for FileMaker that make it extremely simple to work directly with PayPal’s web services while avoiding common bottlenecks when working with FileMaker.

What is FileMaker?

FileMaker is a relational database management system (RDMS) that combines a back-end database with easy to use tools for building front-end user interfaces.  By combining graphical interface design and database design, as well as a basic internal scripting engine, it makes it very easy for novice developers to build sophisticated applications that work on both Windows and Mac platforms.

FileMaker Pro is the primary software package you’ll use to build and use FileMaker solutions.  It can be used on its own (and shared with other users) or you can also host your FileMaker databases on a FileMaker server platform which will provide higher quality service from the database.

Users will typically build a FileMaker database by creating “layouts”, or views within the solution.  For example, you might create a view that displays customer information including their name and contact information.  These fields can be created in the database system as you create them on the design layout, making it very easy for people to build database systems without actually knowing a whole lot about databases.

As you can imagine, this has its advantages and disadvantages, for the sake of this article we’ll assume that we understand relational database design, specifically normalization, and we would build our design views in FileMaker to include related data accordingly (FileMaker users “Portals” to include related data on a design layout.)

I could write an entire article on FileMaker itself, but that’s not what I’m trying to do here.  As such, I’ll simply recommend that if you’re not already familiar with FileMaker (and are still interested in the information provided within this article) that you check out the video demonstrations provided by FileMaker themselves.   You may just decide you love this platform and its ease of use.

FileMaker Data Connection Options

If you are familiar with FileMaker, you probably know it’s not the greatest solution in the world for the building web applications.  While FileMaker itself is simply an RDMS, it was not originally designed as a SQL based platform, and while it provides a number of methods for connecting to it, they’re not all recommended.

ODBC

While you can communicate with FileMaker via ODBC connections like you would any other RDMS, the ODBC drivers are provided by a third party and are very slow.  They also cause a tremendous strain on your FileMaker server’s CPU each time you query the database over ODBC, no matter how simple the query.

Custom Web Publishing (FileMaker Server Required)

FileMaker then introduced a new feature, Custom Web Publishing, which provides direct access to the FileMaker data via XML web services.  While this opened up new doors for working with FileMaker to provide and store data, the same problems exist with performance and I would not recommend building high-traffic applications in this fashion.

PHP API (FileMaker Server Required)

FileMaker now provides a direct PHP API which greatly reduces the development time involved with connecting to FileMaker from PHP web applications.  However, the underlying system still uses the original Custom Web Publishing engine built in FileMaker Server, and as such, the same problems with performance still remain.

External SQL Sources (ESS)

To get around these performance issues all together, FileMaker, in my opinion, sort of gave up.  They introduced a new featured called External SQL Sources that provides the ability to bind third-party data sources (MySQL, SQL Server, etc.) to FileMaker in order to build design layouts and internal FileMaker scripts using the data provided by these sources.

This can be very useful, however, it generally results in nearly doubling your development time because you’re forced to replicate everything you’re doing in FileMaker as well as in a MySQL database and sync the two.  This drives up the cost for your clients and can put a halt to some really cool solution ideas, which is unfortunate for everybody involved.

How Do We Get Around This Problem?

One of the powerful things about FileMaker is the vast amount of built in functions available for developers to use within field calculations and scripts.

Much like any programming language, there are functions available for formatting text, numbers, dates, times, performing calculations, returning system data, and a whole lot more.  As a FileMaker developer, though, you’re simply selecting a function and then populating the parameters with data from within your FileMaker solution.  It’s mostly point and click, and you don’t need to know any actual programming.

When building solutions in this fashion, we do not run in to any of the performances issues that we do when connecting to FileMaker from outside sources.  The internet is still involved for the web service calls, of course, but FileMaker’s internal database engine is all that is used for data access and does not strain the server the way other methods do.

The only problem is that FileMaker does not ship with built in functions specifically for PayPal API web services.  I have recently developed a set of custom functions for FileMaker, though, that provide exactly that.  So, let’s take a look at them and how we can use them to integrate PayPal directly into a FileMaker solution.

PayPal Custom Functions for FileMaker

Making the PayPal custom functions for FileMaker available within your solution is very simple.  Just purchase our PayPal for FileMaker solution and open it up within FileMaker to load all of the PayPal functions into your system.  That’s it.  Now you’ll have access to the majority of PayPal’s API’s via simple FileMaker functions, and I’ll be adding more in the near future.

The functions work exactly the same as any other function you use within a FileMaker calculation.  You simply pass in request parameters and the function handles the rest.

All PayPal response data is stored in the solution file in a single table as global fields so the data is immediately accessible via your FileMaker script to populate your own tables/layouts, make calculations, or anything else you’d like to do with the response information.

All of the functions provided were built using the same request parameter names (as closely as possible) that PayPal uses in their web services.  As such, if you need information about what exactly a request parameter value should be you can find those details in PayPal’s documentation.  I’ll link you directly to the documentation for each call below.

The same goes for response data.  Each link below will provide all of the details you need about what exactly the data coming back to you means.

The following functions are available within the FileMaker solution file at the time of this writing:

• AE_PayPal_AddressVerify – The AddressVerify API operation confirms whether a postal address and postal code match those of the specified PayPal account holder.
  AE_PayPal_BillOutstandingAmount – The BillOutstandingAmount API operation bills the buyer for the outstanding balance associated with a recurring payments profile.
  AE_PayPal_CreateRecurringPaymentsProfile – The CreateRecurringPaymentsProfile API operation creates a recurring payments profile.
  AE_PayPal_DoAuthorization – Authorize a payment.
  AE_PayPal_DoCapture – Captures an authorized payment.
  AE_PayPal_DoDirectPayment – The DoDirectPayment API Operation enables you to process a credit card payment.
  AE_PayPal_DoNonReferencedCredit – The DoNonReferencedCredit API issues a credit to a card not referenced by the original transaction.
  AE_PayPal_DoReauthorization – Reauthorize an existing authorization to extend its valid period.
  AE_PayPal_DoReferenceTransaction – The DoReferenceTransaction API operation processes a payment from a buyer’s account, which is identified by a previous transaction.
  AE_PayPal_DoVoid – Void an order or an authorization.
  AE_PayPal_GetBalance – The GetBalance API Operation obtains the available balance for a PayPal account.
  AE_PayPal_GetBillingAgreementCustomerDetails – The GetBillingAgreementCustomerDetails API operation obtains information about a billing agreement’s PayPal account holder.
  AE_PayPal_GetRecurringPaymentsProfileDetails – The GetRecurringPaymentsProfileDetails API operation obtains information about a recurring payments profile.
  AE_PayPal_GetTransactionDetails – The GetTransactionDetails API operation obtains information about a specific transaction.
  AE_PayPal_ManageRecurringPaymentsProfileStatus – The ManageRecurringPaymentsProfileStatus API operation cancels, suspends, or reactivates a recurring payments profile.
  AE_PayPal_ManagePendingTransactionStatus – The ManagePendingTransactionStatus API operation accepts or denys a pending transaction held by Fraud Management Filters.
  AE_PayPal_MassPay – The MassPay API operation makes a payment to one or more PayPal account holders.
  AE_PayPal_RefundTransaction – The RefundTransaction API operation issues a refund to the PayPal account holder associated with a transaction.
  AE_PayPal_TransactionSearch – The TransactionSearch API searches transaction history for transactions that meet the specified criteria.
  AE_PayPal_UpdateRecurringPaymentsProfile – The UpdateRecurringPaymentsProfile API operation updates a recurring payments profile.

Usage Sample

Now let’s take a closer look at exactly how we’ll use these functions within a FileMaker solution.

For those of you that like video demonstrations, here’s a quick introduction to the use of these functions within a FileMaker solution.

As shown in the video, the solution file comes with a simple layout that allows the user to input their PayPal API credentials and toggle test mode (Sandbox) on or off:

Upon loading the solution file, custom functions are made available to you within the External Functions section of any calculation window.

As you can see, there are simply parameters in the function that need to be filled in so that it can fetch the data from PayPal accordingly.

These parameters can be filled in using data from the FileMaker database, with static values, FileMaker variable values, etc.  Here is one sample of the same function used within a FileMaker script that has now been populated with data.

You’ll notice I’ve populated the API credential values using the FileMaker config data that we looked at previously.  I filled in a static value of USD for the currency code, and left api_subject and related_record_id blank in this case.  Note that any time we leave a value blank, we still need include its parameter, but we simply use double quotes (“”) to make it an empty value.

Once this script runs with our calculation (custom function) being performed, the global fields layout within the solution file will contain all of the response information returned from PayPal for that particular call.

The table/layout includes fields for every possible response field PayPal might return, so in many cases they will not all be used.  They are simply available at any time within any FileMaker script or calculation to pull the current value from.

Here’s an example of the response layout after calling using the AE_PayPal_GetTransactionDetails function within a FileMaker script or calculation.

With the data available within these global response fields in the FileMaker system, you are free to copy it into any of your own tables, layouts, calculations, scripts, etc. within your own FileMaker database solution.

For more samples you might be interested in checking out my other video demonstrations that are more specific to other API calls.

Conclusion

If you’re already familiar with FileMaker I hope you’ve seen the power of these custom functions and how they can help you integrate PayPal into your solutions quickly and easily.

If you’re not familiar with FileMaker I’d encourage you to at least take a look at their solution and what they provide.  You might be surprised and find yourself enjoying a new platform for database solutions, desktop applications, and even mobile application development.

Integrating PayPal is now just as easy as working with any other FileMaker function, and you don’t have to be a programmer to understand it!