FileMaker’s internal SQL and System Formats

I’ve been using the ability to execute SQL statements via a FileMaker Pro plug-in more and more lately (see An Approach to FileMaker Server-Side Script Debugging for an example of this) and recently encountered one issue that might arise when working with files created with different system formats to those on your computer. It’s important to note that FileMaker Pro uses your computer’s system formats to determine how dates, times, and numbers display and sort when you first created a new file.

I was working with a file that I didn’t even realise was created with different system formats as the developer of the file had set the File Options to “Always use current system settings” which tells FileMaker to use the current system settings for data entry of numbers, dates, and times instead of those saved with the file when it was first created or cloned:

The startup/OnOpen script for the file was also using the Set Use System Formats script step and setting this to [On] which also instructs FileMaker Pro to use the current system formats. It wasn’t until I started using the BaseElements plug-in to enter the current timestamp along with some additional text into a text field that I noticed something was amiss. Instead of inserting a timestamp for my system settings (Australian) which would appear as:

9/11/2011 3:49:07 PM

I was getting this instead:

09.11.2011 15:49:20 Uhr

I did some tests and whenever I evaluated the Get ( CurrentTimeStamp ) function I would get a correctly formatted timestamp for my region (e.g 21/11/2011 3:51:20 PM ) but as soon as I used this in an SQL statement or converted it to a text data type I would lose the system settings and get the original settings that were saved with the file when it was first created (German in this case). This appears to happen anytime you need to convert dates, times etc into a text data type via the calculation engine (not specifically related to using a plug-in), which is what I’m doing with the internal SQL feature and the BaseElements plug-in. For example my plug-in calculation looked liked this:

BE_FileMakerSQL ("
INSERT INTO ServerLog(Description)
VALUES('Logging Commenced at " &  Get ( CurrentTimeStamp ) & "'" & ")")

The only solution that I’m aware of is to create a clone of the file and then open it on a computer with the required system formats. It will then use the local system formats and you will get the same results when converting date and time values to a string.

fmSMS wins Clickatell Personalized Priority Messaging Award

We’re very happy to report that fmSMS, our solution that lets you send and receive SMS messages with FileMaker, has just been announced the winner of the 2011 Clickatell Personalized Priority Messaging Award in the Application Developers Category.



You can read the full announcement here:

http://www.marketwire.com/press-release/Clickatell-Announces-Personalized-Priority-Messaging-Award-Winners-1577896.htm

The full list of winners is at:

http://www.clickatell.com/ppmawards/winners_2011.php

We’ll post a photo of the award when we receive it.

An Approach to FileMaker Server-Side Script Debugging

The ability to schedule the execution of FileMaker Scripts – that is scripts created in ScriptMaker/Manage Scripts using FileMaker Pro/FileMaker Pro Advanced client application – under FileMaker Server was one of the sleeper features when it was first introduced in FileMaker Server v9 in July, 2007. It didn’t even rate a mention in the original press release but it’s become a very important tool in my developer bag ever since. Some examples of how I’m using FileMaker Server Side scripts include:

– one client requires a .xml file to be downloaded each day from a URL and imported into a file that then conditionally updates and creates new records in related tables and logs the execution

– we use server side scripts with our fmSMS solution to enable our clients to have the sending of the SMS messages performed by FileMaker Server instead of the client application. For bulk operations (e.g. sending several hundred or more messages at the one time) or for scheduling messages to be sent in the future this enables a user to offload the heavy lifting to the server which can typically perform the scripts faster and without tying up the user’s computer waiting for the script to run

– another client send and receives emails from a FileMaker database. Sending bulk emails previously would take almost 8 hours for their mailing list and require a dedicated computer to handle this that couldn’t be used for the duration of the script.

– we update account balances overnight for another client so that all searches can be performed on stored – and therefore indexed – number fields instead of unstored calculation fields, which can result in a find operation taking seconds vs minutes for large data sets.

FileMaker Server Side Scripts – Before You Begin

There are several important caveats that you need to know before you dive head first into setting up your FileMaker Server Side scripts:

– not all Script steps are supported. Only “Server” compatible script steps are supported. You can view the list of server compatible script steps by changing the Show Comptability popup menu in the bottom left hand corner the script window when editing a script:

All script steps that are NOT server compatible will then be greyed out, leaving you with the list of compatible script steps:

If you’re hoping to offload the generation of PDF invoices which are then emailed to a contact then you’re out of luck as far as native FileMaker script steps are concerned. You can use the Send Email via STMP Server step but the Save Records As PDF step is not available – hopefully this will be supported in a future version of FileMaker Server.

– some Script steps are only compatible when certain options are selected. For  example the Send Mail step is only compatible when used with the send via Server option (not client):

Any supported script step that has the option of presenting a dialog box to the user is only compatible when the dialog is not displayed – you’ll need to select the “Perform without dialog” option in this case. Examples of this include Sort Records, Commit Records and Delete Record.

– the Allow User Abort script step determines what happens when a server side script encounters an unsupported script step. From the FileMaker Server v11 Help:

  • If the Allow User Abort script step option is enabled (On), unsupported script steps will stop the script from continuing.
  • If the Allow User Abort script step option is disabled (Off), unsupported script steps are skipped over and the script continues to execute.
  • If this script step is not included, scripts are executed as if the feature is enabled, so unsupported script steps will stop scripts.

– A server-side FileMaker script that is running on one FileMaker server cannot open a database that is hosted on a different FileMaker server.

– Server-side FileMaker scripts run in separate sessions on the Database Server. Each session has its own copy of global fields and variables.

– FileMaker Server can also reference FileMaker plug-ins (see the FileMaker Server v11 Help for details on how to install and enable plug-ins under FileMaker Server)

– there is no script debugger!

Server Side Script Logging

The lack of a script debugger is the focus of the rest of this post. FileMaker Pro Advanced provides a number of tools to assist developers in debugging scripts, including the Script Debugger and the Data Viewer. Both of these tools make life easy for the FileMaker developer to see what is happening at each step of the script and the current values in fields and variables that the script is referencing. However when it comes to debugging scripts under FileMaker Server you’re essentially on your own and need to roll your own solution – remember that there is no interface on FileMaker Server to show you the progress of each script and set breakpoints or access the Data Viewer. The FileMaker Server Log Viewer shows some information AFTER the script has executed which can help but it is very limited. The list of Schedules in the Admin Console also shows when the schedule was last completed and when it’s due to run next.

Some of my server-side scripts have hundreds of steps – when things go wrong or you don’t get the result you expected (or nothing appears to happen) you’re not sure where to start. I needed a solution that was as dynamic as possible and would involve the minimal amount of additional schema changes and let me insert the equivalent of breakpoints at any point in the script to capture information about the current state. My solution to this was to create a built-in log table that I can populate as I go without having to change contexts (i.e. no switching to a new layout based on the log table occurrence to create records). This can be done quite easily using the Magic Key/Pivot technique but I wanted a solution that didn’t require any new relationships and required the least amount of additional script steps so I turned to FileMaker’s internal SQL feature that is currently only exposed through the use of an external plug-in.

This approach does require a plug-in – there are several free and paid ones to choose from – but for me it’s the neatest way to adding logging features to a server side script. Once you’ve installed and enabled the plug-in under FileMaker Server you can easily add some logging features to your database to help you debug a server-side script process. Here’s my approach to server-side script debugging/logging:

– in the database that contains the script I copy/paste in a new table: ServerLog. This table has a handful of fields and FileMaker Pro will then create a single table occurrence and layout for you automatically (you can make the layout visible only in Layout mode). Do NOT rename or delete the table occurrence!

– install and enable the plug-in that can perform the internal SQL operations for you: see the list of plug-ins at the end of this article that I’ve used recently. I currently use the free BaseElements Plugin(thanks Goya!) and my example file uses the BaseElements plug-in to demonstrate my approach. Instructions for installing and enabling plug-ins under FileMaker Server can be found in the FileMaker Server v11 Help under “Managing plug-ins”.

– if your database has an OnFirstWindowOpen/startup script or a OnLastWindowClose/close script (as set under the File Options for the file) you might need to modify these depending on your requirements. These scripts will be performed by the server-side script and depending on their compatibility with server side steps and the use of the Allow User Abort script step that may create unexpected results. I typically bypass the startup and close scripts when performed under FileMaker Server by adding the following steps to the top of the script:

If [ PatternCount ( Get (ApplicationVersion); "server") ]
    Exit Script [  ]
Else
... continue with script as normal

– I set a local variable at the top of the actual server-side script that I wish to debug to allow me to enable or disable the logging on the fly:

Set Variable [ $EnableLoggingSQL; Value:1 ]

– as I’m using a plug-in I need to test that the plug-in is installed (and possibly registered and also the correct minimum version if required)

– I first delete all previous entries in my ServerLog table as I’m only interested in the log files for the current script execution (you can bypass this if you wish to keep a history). Here’s where the power of FileMaker’s internal SQL engine kicks in – I don’t need to change layouts, show all records, then delete them and return to the orignal layout. I can simply make one function call as the calculation value for a Set Variable script step (N.B. all examples in this post are using the BaseElements plug-in):

Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("DELETE FROM ServerLog") ) ]

The function call and syntax will depend on the plug-in you use but the SQL statement will generally be similar. Here the use of  “DELETE FROM ServerLog” deletes all records in the ServerLog table. Note that I’m wrapping the plug-in call within the Case function which checks whether logging has been enabled (the $serverLogSQL variable is set to either 1 or 0 at the top of the script) – if logging is enabled the plug-in function will be evaluated, otherwise it will be ignored. Be careful when using the SQL Delete command as, unlike FileMaker Pro, you WON’T get a dialog box confirming that you wish to delete the records.

– Adding a single Set Variable script step is usually the maximum that you need to add if you wish to capture and log information about the current state (e.g. by calling one of FileMaker’s functions) or by checking the result of the last script step or retrieving the current value of a variable or field. For example the following Set Variable steps will return information using FileMaker Get Functions:

Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("INSERT INTO ServerLog(Description) VALUES('Logging Commenced at " &  Get ( CurrentTimeStamp ) & "'" & ")"))]
Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("INSERT INTO ServerLog(Description) VALUES('Get (AccountName) result " &  Get ( AccountName ) & "'" & ")") )]

– you don’t always necessarily need to add a Set Variable step to create a log entry: anywhere you can access FileMaker’s calculation dialog you can create a log entry. For example you can add a log entry when the Exit Script step is performed as long as you specify a calculation result. I usually decide where in my script I’m going to start debugging and add one or more Set Variable steps at the appropriate places then perform the script manually using the FileMaker Server Admin Console:

 

– when setting up the new Schedule for the server side script in the FileMaker Sever Admin Console you’ll need to specify the Account Name and Password to use when performing the script. Make sure the Account Name and Password has sufficient privileges to perform the required steps. I generally create a new Account for server side scripts with the appropriate privileges. It’s also worth nothing that Get(AccountName) returns the account name that the script was run under and Get(UserName) returns the schedule name.

When it comes to setting up the frequency for the Schedule that you create in the FileMaker Server Admin Console you might be surprised that there’s no option that lets you create a script that runs every x minutes all the time. The limitation is that the start time and end time of a schedule must fall within the same day – there’s no option to tell it to run every day every 5 minutes for example. You can work around this by creating a schedule that starts say at 12:01AM and finishes at 11:59pm if you require a script that runs constantly every 5 minutes.

Whilst debugging I strongly recommend enabling the email notification to get as much information as possible about the execution of the server side script. You can also use the Log Viewer in the FileMaker Server Admin Console to view relevant log entries – these are often helpful as they pinpoint the script step that caused an error. It’s also worth noting that not all errors are actual errors. For example if you’re script involves a Find operation that results in no records found (error #401) this will be captured but might be completely expected. Also if your FileMaker script involves a Loop that uses the  Go to Record/Request/Page [ Next ] step you’ll get an error when it gets to the last record (error #101 – once again this would be expected). You can use the following step immediately above the Go to Record/Request/Page [ Next ] step to bypass this:

Exit Loop If [ Get (FoundCount ) = Get (RecordNumber ) ]

The FileMaker Server Admin Console also allows you to specify some Script Options. You can select Abort schedule if time limit reached or server stopped in the Schedule assistant to abort the FileMaker script schedule if the script takes longer to run than the specified Time limit, or if the Database Server stops:

I’ve created an example file that you can download and pull apart (you’ll need to also download the BaseElements plugin (link below) and install and enable this on FileMaker Server):  ServerLogging.fp7

Here’s a list of the plug-ins I’ve used with my server-side debugging approach:

BaseElements Plugin

SQL Runner

MMQuery

 

Additional Resources:

FileMaker’s Internal SQL Engine, part 1 

Server Side Imports and Exports

FileMaker Server 11 Help

FileMaker Server Event Log Messages

Creating .zip archives from FileMaker using AppleScript

All FileMaker developers have their own habits about how they develop, what tools they prefer to use and how they setup their working folders and backups. I develop on locally stored files as well as files hosted by FileMaker Server (I do a lot of FileMaker Custom Web publishing work using the FileMaker PHP API which requires the files to be hosted by FileMaker Server). I’ve traditionally developed using locally stored files except for my CWP/PHP work and have used a companion database for each new project that I start that I use to create compressed timestamped backup archives of my master database file/s and any associated php files.

I use a mix of backup plans to keep known good copies of my files locally as well as off-site (this post doesn’t go into the details of what constitutes a comprehensive backup plan). Whilst I’m developing I like to close my files periodically (usually every 20-30 minutes or after a major chunk of work has been done) and make a quick .zip of the files before proceeding. I’ve been working this way for so long now (over 10 years from memory) that it’s become a habit for me. I’ve also learnt the hard way too – there’s nothing worse than having to re-do a major chunk of work because FileMaker crashes, you accidentally click cancel instead of OK, there’s a blackout and you haven’t got a UPS etc etc. I go to great effort to ensure that files I’m working on for my clients have never crashed before they are deployed into production – if FileMaker Pro Advanced did crash whilst I had any client files open I simply trash them and revert to the previous backup I had made. I also like to confirm that the files that I’m about to copy into a .zip archive are in fact closed first.

I use a simple FileMaker database that I duplicate for every new project that I start – the link to download this is below. It’s Mac only as it uses AppleScript – I do 99% of my development on the Mac platform so this makes sense for me. It has 4 fields that I use to store the locations to:

  1. the source folder containing the master database file/s that I’m working on (always stored on my local hard disk)
  2. the destination folder where I wish to save a backup .zip archive of these into (usually on an external hard disk)
  3. the source folder containing the master PHP files that are associated with the current project (if applicable)
  4. the destination folder where I wish to save a backup .zip archive of these PHP files (usually on an external hard disk)

Getting the Path to the Source and Destination Folders

Before I can create the .zip archive I first need to populate these fields with the path to my selected source and destination folders. To do this I perform a very simple AppleScript:
set theFolder to choose folder
copy POSIX path of theFolder as text to cell "zResult_g" of current record
which prompts the user to select a folder using the familiar Mac OS X dialog box:
If I haven’t already created the folder in the appropriate location I can click the New Folder button to create it as I go, all from within the same dialog. Assuming I click the Choose button and not the Cancel button the path to the selected folder will be returned to the FileMaker via the 2nd line of the AppleScript which copies this to the zResult_g field (N.B. all the fields in this example are global fields and there is only one table so I don’t need to specify which table or record for AppleScript to reference). Note that I’m instructing AppleScript to return the POSIX  path to the selected folder – this is important as the POSIX path is required by the shell script to create the .zip archive in the 2nd AppleScript. The POSIX path should look something like this:
/Users/andrew/Documents/FileMaker/Projects/Example Project/
If I didn’t specify the POSIX path it would return the AppleScript path which looks something like this:
Macintosh HD:Users:andrew:Documents:FileMaker:Projects:Example Project:
The syntax for returning the AppleScript path would be:
copy theFolder as text to cell "zResult_g" of current record
Once I’ve selected the path to the source and destination folders I’m using some additional FileMaker calculation fields to retrieve the path to the parent folder of the selected folder and also the name of the selected folder. I need to refer to these folders when running the shell script that makes the .zip archive as I want it to run from the location of the parent folder and tell it the name of the folder to archive (this involves performing the change directory command later on to switch to the location of the parent folder). This is not strictly necessary but results in a much neater .zip archive when expanded that doesn’t include all of the folder paths to the source folder – this is because by default AppleScript is running the shell script from the root directory. I could also generate these paths within AppleScript without too much effort but I’m a novice when it comes to AppleScript and can do this quite easily in FileMaker Pro as well.
The field ProjectDatabaseFolderParent_c does a simple calculation to get the path to the parent folder of the selected folder (still using the POSIX format):
/Users/andrew/Documents/FileMaker/Projects
and the field ProjectDatabaseFolder_c calculates the name of the selected folder:
Example Project

Creating the .zip archive/backup

There are 2 buttons to create the .zip backup for both the FileMaker databases and the PHP files. Both buttons perform the same script but pass in a different parameter to the script. The FileMaker script that is attached to these buttons use native FileMaker script steps to check for any mandatory requirements (e.g. that I’ve selected a source and destination folder, that there are no other FileMaker files open etc) and then perform an AppleScript that creates the .zip archive. The AppleScript executes a few processes in order as follows:
set tableName to "Backup Template"
set the item_path to get data cell "ProjectDatabaseFolder_c" of table tableName
set the backupFolder to get data cell "BackupProjectDatabaseFolderPath" of table tableName
set the parentFolder to get data cell "ProjectDatabaseFolderParent_c" of table tableName
set the startFolder to the quoted form of (parentFolder as text)

This section retrieves the values from the fields in the Backup Template.fp7 file that the AppleScript needs to reference when running the shell script. Note that the references to the FileMaker fields are hardcoded in strings – if you rename any of these fields you will need to update the AppleScript or it will break (as far as I know you can’t reference FileMaker $ variables from within an AppleScript but I would love to be proved wrong here). The next step is to generate the timestamped filename that will be used when creating the .zip archive:

set aDate to (current date)
set fDate to formatDate(aDate) & ".zip"
on formatDate(aDate)
set {yr, mo, dy, hr, mn, sc} to ¬
{year, month, day, hours, minutes, seconds} of aDate
return ("" & addLeadingZero(dy) & ¬
addLeadingZero(mo as integer) & yr & " " & ¬
addLeadingZero(hr) & ¬
addLeadingZero(mn) & addLeadingZero(sc))
end formatDate
on addLeadingZero(n)
return text -2 thru -1 of ("00" & n)
end addLeadingZero

Essentially all I’m doing here is getting the current timestamp then extracting the various date and time components in the format which I require. This will result in a filename like “10102011 150304.zip” (I’m using the Australian preferred DDMMYYYY HHMMSS format to generate the text timestamp string but you can re-order this any way you like). AppleScript, like FileMaker, has it’s own rules about data types and how date and times are formatted so I have to add any leading zeros where required etc.

Now I’m ready to create the .zip archive by calling a shell script:

tell application "Finder"
set the sourceFolder to the quoted form of (item_path as text)
set zipFile to fDate as text
set the zipFolder to the quoted form of ((backupFolder & zipFile) as text)
set shellscript to ("cd " & startFolder & "; /usr/bin/zip -r " & zipFolder & " " & sourceFolder)
do shell script shellscript
end tell

Note that I’m first changing directories in the shell script to the parent folder before I generate the .zip archive using the “cd” command. As I’m calling more than 1 command in my shell script I’m using the semi-colon character to separate these.

Finally I’m passing the result back to a global utility field in the FileMaker table:

copy result as text to cell "zResult_g" of current record

It returns the the result of the AppleScript which shows the file/s that were compressed and how much compression was achieved for each:

adding: Databases Test/ (stored 0%)
adding: Databases Test/FileMaker Project A.fp7 (deflated 90%)
adding: Databases Test/FileMaker Project B.fp7 (deflated 64%)

I’ve included the zResult_g field on the main layout as I can use that as a quick visual reference as to how the AppleScript performed. From my quick testing there are some Objects in the FileMaker AppleScript dictionary that do require the field to be on the layout – I haven’t found a definitive list of these yet and haven’t completed the results of my testing of these. If you were to remove the zResult_g field it will break the last line of the AppleScript.

The actual AppleScripts were cobbled together from various examples that I found on the Internet as well as making use of the FileMaker Pro Apple Events reference database. I’m very much a beginner when it comes to AppleScript so many of you can probably optimise the actual AppleScripts – for example I’m not bothering to do any error trapping in the AppleScript.

You can download a copy of my backup tool here: BackupTemplate.fp7

If you have any comments and suggestions about how this tool can be improved please leave a comment below and I’ll post an updated version of with any changes. I’m planning on updating this in the future to do some error handling in the actual AppleScript as well as some other tests, such as ensuring the destination folder isn’t the same as the source folder etc. This database was created and tested under Mac OS X 10.6.8 – your milage may vary on other versions of Mac OS X.

Here are some references that you might also find useful if you wish to modify this tool yourself or learn more about AppleScript and FileMaker:

Introduction to AppleScript Language Guide

AppleScript Error Codes

Keeping good backups – a simple snapshot tool (Goya)

A Simple Backup Script (filemakerhacks)

Create Contacts in the OS X Address Book (Mark Banks)

AppleScript Essentials – Introduction to Scripting FileMaker Pro (mactech)

Apple Events Reference database (FileMaker Inc)

Applescript bold & fearless PauseOnError video by Bruce Robertson

AppleScript and POSIX paths

Clickatell 2011 Personalized Priority Messaging Awards

We’ve entered fmSMS, our SMS/text messaging solution that lets you send and receive SMS messages using FileMaker Pro,  in the Clickatell 2011 Personalized Priority Messaging Awards. We would love to get your support for fmSMS – you can vote for us by clicking on the image below and voting for fmSMS. Just find “Databuzz” in the list of entries (should be #29 – have to scroll down to find it) and click the Vote Now link:

Thanks for your support!

 

 

 

 

 

 

 

Clickatell launches new US small business SMS messaging package

Clickatell, a global leader in mobile communications specialising in SMS messaging, has launched a new monthly service for US small businesses, starting at just $24.95 per month, which includes a dedicated number and up to 1,000 free outbound text messages per month. Additional outbound text messages are as little as 2¢ per message, and all incoming customer messages are free of charge.

Clickatell was the very first SMS Gateway supported by fmSMS and they have a very comprehensive developer API that allows us to integrate sending and receiving of SMS/TEXT messages from FileMaker Pro (receiving will require hosting by FileMaker Server v9 or higher with Custom Web Publishing/PHP API). This new offer, combined with fmSMS, provides a turnkey solution for US small businesses to be sending and receiving SMS messages from FileMaker Pro in minutes.

According to Clickatell SMS messages have been proven to generate 10x or higher direct response rates than other media, averaging between 15-45 percent versus a typical 1-5 percent for popular forms of direct marketing. Text messaging also has a 5x higher open rate than email (98 percent vs. 20 percent). Text messaging is capable of reaching 91 percent of people and is used by 64 percent of the U.S. population, versus only 26 percent using Twitter and 42 percent using Facebook. In addition, mobile messaging enables much more targeted, personal interaction and receipt notification than popular social media engines.

Click on the graphic below for full details or visit the Clickatell US Small Business website at http://www.clickatell.com/solutions/business/us_small_business.php


International Bulk SMS Gateway: Easy to integate

Databuzz releases fmSMS – Send and Receive SMS/TXT Messages in FileMaker Pro

Just a quick note to say that we’ve finally got fmSMS out the door. It represents everything we’ve learned in over 10 years of FileMaker/SMS integration. We’d love to hear your feedback – you can download a trial copy now from the fmSMS website at http://www.fmsms.com. We’re already working on some new features and improvements for the next version.

Here’s the official press release:

Sydney, Australia – February 10, 2011 – Databuzz today announced fmSMS, a new solution that lets you send and receive SMS/TXT messages from within FileMaker Pro.

fmSMS allows you to send an SMS from FileMaker Pro to almost any mobile phone in the world via one of the supported SMS Gateways, reaching over 800 networks in more than 200 countries. SMS is great means of direct communication with customers, staff, suppliers, and students.

SMS traffic is growing at an exponential rate – an estimated 2.3 trillion text messages were sent in 2010. One billion text messages were sent in one day alone in Beijing this month on the eve of the Spring Festival, China’s biggest festival. fmSMS allows FileMaker users to be part of this growing and important means of communication.

SMS is perfect for appointment reminders, phone messages, promotions, segmented marketing, school absence alerts, and password confirmations. With SMS you can reduce your costs and play less “telephone tag”.

fmSMS works with multiple SMS Gateway providers internationally and supports the following features*:

– Send single and bulk SMS messages (messages are typically delivered in under 15 seconds)
– Send long messages (greater than 160 characters)
– Works with over 20 SMS Gateway providers internationally
– Delayed Delivery – send a message now for delivery at a future time
– Alphanumeric Sender ID
– Message Logging – track the history of all sent messages
– Message templates – create an unlimited number of pro forma templates
– 2 Way SMS – allow recipients to reply to messages and have them appear in fmSMS

“We’ve been helping customers integrate SMS with their FileMaker solutions for over 10 years,” said Andrew Duncan, Director of Databuzz. “fmSMS can be used as a standalone turnkey solution or can be integrated directly into a customer’s existing FileMaker solution.”

Praise from independent FileMaker developers

“As a vertical solution provider, there is no point in re-inventing the wheel. fmSMS provided us an accelerated learning curve and bolt on schema saving us loads of time in development. A great investment.” Andrew Markham Xercise Pro

Availability, Pricing, and Compatibility

fmSMS is available now from the fmSMS website at http://www.fmsms.com. A 14 day trial version is available for both Macintosh and Windows. Workgroup Licenses start at AUD $495.00. fmSMS requires FileMaker Pro v10 or later.

* Not all SMS Gateways support all features of fmSMS. Some features might incur additional charges by your selected SMS Gateway. Some features require hosting by FileMaker Server v9 or later with Custom Web Publishing using the PHP API and a static IP address. See our website at http://www.fmsms.com for more details.

Media/Customer Contact:

Andrew Duncan
Phone: +61 418 468 103
sales@databuzz.com.au
http://www.fmsms.com
http://www.databuzz.com.au

About Databuzz: Databuzz is a long standing member is a member of the FileMaker Business Alliance and has been developing and deploying FileMaker solutions for clients in Australia and internationally since 1999. Our clients are individuals, small-medium businesses, government agencies and multi-national corporations. Databuzz was founded by Andrew Duncan, a Certified FileMaker 11, 10,9 and 8 Developer. In 2010 Andrew presented a session at the annual FileMaker Developer Conference on integrating SMS/TXT Message Integration with FileMaker. For more information please visit our website at http://www.databuzz.com.au.

###

FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners.

Troubleshooting Errors with FileMaker Server Auto Update plug-ins feature

I’ve been spending a lot of time recently deploying solutions that require plug-ins and having FileMaker Server distribute them automatically to clients via it’s nifty Auto Update feature. Inevitably you will encounter issues for some clients when attempting to download the plug-in: permissions issues with the plug-ins on the server, not having the plug-in in the correct location etc. They are usually easy to resolve once you know what the error is.

When you write a script to download a plug-in you end up calling the FMSAUC_UpdatePlugIn function. This is a function of the AutoUpdate plugin (yes you need to use an existing FileMaker plug-in to download a plug-in), which thankfully by default is always installed by FileMaker Pro/Pro Advanced (there are only 3 functions for this plug-in: one checks the version (used to see if the plug-in is installed), another to find the plug-in you’re after on the FileMaker Server computer, and the third to download the plug-in). Assuming you have the plug-in installed in the correct location on your FileMaker Server with the appropriate permissions set (see the FileMaker Server 11 Guide to Updating Plug-ins for the full details) you’ll end up calling the FMSAUC_UpdatePlugIn function to initiate the download of the plug-in. If successful this function will return 0 like a normal successful script step, and if unsuccessful it will return an error number in the range of -6 to 101.

I like to capture the error number and if necessary display that to the user along with a more human readable error description. There are some custom functions that you can use to return the description for the error code. For example you capture the error code into a variable using the Get (LastError) function and then pass that to the custom function which will return the error description. For example using the custom function linked above ErrorToEnglish ( 101 ) returns “Record is missing” and saves you the trouble of looking up the error code in FileMaker’s help. However when using the FMSAUC_UpdatePlugIn function you won’t able to use this as it has it’s own list of error codes which overlap with existing list of FileMaker error codes. For example error 101 means “The function call from the client computer to the computer running FileMaker Server failed. The server computer might be running a previous version of FileMaker Server.” when returned by the FMSAUC_UpdatePlugIn function but could return “Record is missing” from the Go to Record/Request/Page script step.

I ended up creating another custom function that you can call when performing the FMSAUC_UpdatePlugIn function that will return the appropriate description for any errors encountered when attempting to download a FileMaker plug-in from FileMaker Server. Note you could also extend the existing ErrorToEnglish custom function to include a 2nd parameter to indicate whether it is a script step or the FMSAUC_UpdatePlugIn you are capturing the error code for, rather than have separate custom functions.

Using FileMaker Plug-ins with Instant Web Publishing

I normally don’t work with FileMaker’s Instant Web Publishing (IWP), preferring to work with Custom Web Publishing and the PHP API which gives me more flexibility without the limitations of IWP. I was recently working with a client that had deployed an IWP solution hosted by FileMaker Pro (not FileMaker Server Advanced) and wanted to integrate our fmSMS solution, which allows you to send/receive SMS/txt messages in FileMaker. I quickly found a problem though – the plug-in that we use to send the SMS messages was not being recognised by the IWP clients/browsers, despite it working fine in the FileMaker Pro client. The problem took a while to locate but the solution was simple and was not something I could find documented anywhere else so I thought I would write it up here in case this helps someone else out there having the same problem.

As I started to test whether we could deploy our SMS solution via IWP I wasn’t too confident to start with as I wasn’t sure whether plug-ins were supported under IWP in the first place, and had already worked out a backup plan if that was the case. I first checked the FileMaker 11 Instant Web Publishing Guide to see if it mentioned the compatability of plug-ins with IWP, and was encouraged to see the following: “Typically, third party plug-ins can be used for web published databases if they do not attempt to display information to an end-user’s screen, if they do not require direct end-user interaction, if they do not interact with the FileMaker Pro user interface, or otherwise require interaction from end users”. I was bolstered by this as our plug-in doesn’t involve any user interface interaction – it simply communicates with an online SMS Gateway and returns the result into a field via the Set Field script step. As fmSMS is a multi-file solution that was never designed with IWP in mind I decided to create a quick test file with only the basic tables, fields, layouts and scripts necessary to test it under the IWP environment. I made sure all the scripts were IWP compatible, tested it in FileMaker Pro Advanced as the host successfully then was ready to test in IWP.

My first test in IWP returned a “?”, which I know usually means the plug-in was not installed or enabled. However I could see the plug-in listed under the FileMaker preferences and knew it was loaded as FileMaker Pro Advanced was able to run my scripts successfully and return the correct result. I wasn’t getting puzzled at this point and spent a few more hours modifying scripts, creating new plug-in tests to ensure it was loaded but I always ended up with a “?” everytime I called a plug-in function. I gave up for the night and decided to sleep on it.

The next day I started again and continued to get the “?” result no matter what I tried. I was almost ready to give up and pronounce that this plug-in was not compatible with IWP but decided to try one last test. By default I always install (or FileMaker Server installs for me via it’s Auto Update feature) all my plug-ins in the user’s Application Data folder, which is located at the following for each of the supported platforms:

  • Windows XP: C:\Document Settings\User Name\Local Settings\ApplicationData\FileMaker\Extensions
  • Windows Vista/Windows 7: C:\Users\User Name\AppData\Local\FileMaker\Extensions
  • Mac OS X: Macintosh HD/Users/User Name/Library/Application Support

I moved the plug-in from here to the traditional location inside the FileMaker Application folder, which is usually one of these for each platform:

  • C:\Program Files\FileMaker\FileMaker Pro 11 Advanced\Extensions
  • Macintosh HD/Applications/FileMaker Pro 11 Advanced/Extensions

After relaunching FileMaker Pro Advanced and quickly tested everything worked fine I started what was going to be my last test via IWP and it worked! I’m not 100% sure why this is the case as I can’t find any documentation from FileMaker Inc about this but I suspect it relates to the FM Web Publishing application. The FM Web Publishing app is located at either:

  • C:\Program Files\FileMaker\FileMaker Pro 11 Advanced\ExtensionsWeb Support\FM Web Publishing.exe
  • Macintosh HD/Applications/FileMaker Pro 11 Advanced/Extensions/Web Support/FM Web Publishing.app

and it appears that the FM Web Publishing app can only see plug-ins that are also in the FileMaker Pro Application Extensions folder – at least in the case of the plug-in I’m working with, I haven’t tested this with other plug-ins under IWP. If you’re hosting your IWP solution under FileMaker Server Advanced you won’t encounter this as it only applies to hosting IWP solutions with FileMaker Pro/Pro Advanced – you install plug-ins in different locations for FileMaker Server Advanced hosting.

So in summary if you’re working with plug-ins for solutions hosted by FileMaker Pro/Pro Advanced and deployed via IWP and can’t get the plug-in functions to work correctly, make sure you have installed the plug-in in the FileMaker Pro/Pro Advanced Application Extensions folder not in the user’s Application Data folder. Note that if you have the same plug-in installed in both locations the user’s Application Data folder will take preference over the Application Extensions folder so make sure to remove it from the user’s Application Data folder.

Simple Pagination with the FileMaker PHP API

During one of my sessions at the recent 2010 FileMaker Developer Conference in San Diego I was asked about the pagination/navigation on a search results page that is generated by the FileMaker PHP API. They were referring to the links you might see at the top/bottom of the search results page like this:

First | Previous | Record 21 – 40 of 52 Next | Last

which are generated dynamically and use the equivalent of conditional formatting in FileMaker to display only the necessary elements. For example on the first page of the search results there is no Previous page nor can you go to the First page as you are already on it; likewise with the last page there is no Next page and no Last page, so these should not appear depending on the context of the current page.

I finally found some time to put together a simple example of how you can generate these using the FileMaker PHP API.

As with FileMaker when you perform a search via PHP/Custom Web Publishing you will generate a found set of records (or no matching records). With a large found set of records you might want to present these to the user in chunks, for example you could present 5/10/20 records per page and allow the user to navigate to the next page. This is similar to how most search engines on the web work these days. Google for example defaults to showing 10 results per page. With the FileMaker PHP API you use the setRange() method to request only part of the found set of records by passing in 2 paremeters: the first parameter is the number of records to skip past and the second parameter is the maximum number of records to return.

Say you do a search and you find 52 matching records. Using a $max value of 20 you will get records 1-20 on the first page, records 21 to 40 on the second page, and records 41 to 52 on the third (and last) page. On the first page we are skipping 0 records, on the second page we are skipping 20 records and on the third (and last) page we are skipping 40 records.

Here’s an example of how to use the setRange method:


// Set a Max value. Paging 20 records at a time
$max = 20;
$skip = $_GET['skip'];
if(!isset($skip)) { $skip = 0; }
$request->setRange($skip, $max);

Here I’ve hardcoded the $max value to 20 which means I’ll only ever get back 20 records at the most per page. For the skip value I’m using the $skip variable and if this hasn’t already been set I’m setting it to 0, which is typically done on the first page of search results. Otherwise this will be passed as a parameter in the URL and the php script will check to see if this GET variable exists. Now we need to perform the find and check for a found set of records:

// Perform the Find
$result = $request->execute();
if (FileMaker::isError($result)) {
if ($result->code = 401) {
$errorMessage = "There are no Contacts that match that request: " . ' (' . $result->code . ')';
} else {
$errorMessage = "Contacts Find Error: " . $result->getMessage() . ' (' . $result->code . ')';
}
} else {

If there are matching records we then generate some variables that will be used in the First, Previous, Next and Last links, as well as calculating the number of records found and how many were “fetched” on the current page:

If you use the previous example of 52 matching records here are the values of these variables for each of the 3 pages of search results:

  • Page 1 (records 1-20): $skip = 0, $max = 20, $found = 52, $ fetchcount = 20, $prev = -20, $next = 20, $lastskip =42, $firstrecord = 1
  • Page 2 (records 1-20): $skip = 20, $max = 20, $found = 52, $ fetchcount = 20, $prev = 0, $next = 40, $lastskip =42, $firstrecord = 21
  • Page 3 (records 1-20): $skip = 40, $max = 20, $found = 52, $ fetchcount = 12, $prev = 20, $next = 60, $lastskip =42, $firstrecord = 41

I’m also using a pipe character as a separator between the links as well which is stored in the $sepbar variable. Now we’re ready to generate the links within the HTML part of the php page: Here’s the code for the “First” link:


<?php
if ($skip != 0) {echo '<a href="?skip=0">First</a>'.$sepbar;
}
?>

As we only want to show the First link on every page except for the first page of search results we are checking the value of the $skip variable. If $skip is not equal to 0 then we are not on the first page of search results so we can show this link, along with the pipe character to give it some space between the links.

For the “Previous” link we use:

<?php
if ($prev >= 0) {
echo '<a href="?skip='.$prev.'">Previous</a>'.$sepbar;
}
?>

Here we only want to show the Previous link if there is a previous page of search results, so we can check the contents of the $prev variable which will only equal 0 on the first page of the search results.

For the “Next” link we use:


<?php
if (($skip + $max) < $found) {
echo '<a href="?skip='.$next.'">Next</a>'.$sepbar;
}
?>

Here we need to check that there actually are subsequent pages of search results, which we do by checking that the total of the $skip and $max variables is less than the total found set of records.

Finally for the “Last” link we use:


<?php
if (($skip + $fetchcount) < $found) {
echo '<a href="?skip='.$lastskip.'">Last</a>';
}
?>

Here we need to test that we are not on the last page of the search results which we do by checking that the total of the $skip and $fetchcount variables is less than the total found set of records.

If you want to display the total number of found records along with the records that you are currently viewing (e.g. “Record 21 – 40 of 52”) you can use:


Record <?php echo $firstrecord; ?> - <?php echo $lastrecord; ?> of <?php echo $found; ?>

I’d like to credit Sonja Froyen’s article Custom Web Publishing: Paginate Your Results which I used as a starting point for some of these links (no need to reinvent the wheel here). If you Google “PHP Pagination” there are thousands of other articles on different ways to generate the pagination links, including variations which show how to include the search results page numbers like Google (e.g. “Previous 1 2 3 4 5 6 7 8 9 10 11 Next”. Most of these reference MySQL as the data source but it’s relatively easy to swap the references to the FileMaker PHP API.

As some of this code won’t make sense on it’s own here’s the full php page with the HTML (remember this is a simple page that finds all of the Contacts in the DevCon sample file):

<?php
require_once 'FileMaker.php';
require_once 'connections/INT002.php';

}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>
FileMaker DevCon INT002 Contacts
</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/glass_grey.css">
<style type="text/css">
form#auth label.error { display: none;
margin-top: 5px;
color: red;}
.appropriateError{
color: red;
}
.emphasise{
color: red;
}
</style>
</head>
<body>
<div id="container">
<!-- HEADER -->
<div id="header">
<h1>
FileMaker DevCon INT002 Contacts
</h1>
</div>
</div>
<table>
<thead>
<tr>
<th>
Customer ID                          </th>
<th>
First Name                            </th>
<th>
Last Name                            </th>
<th>
Country                            </th>
<th>
Phone Mobile                          </th>
</tr>
</thead>
<tbody>
<?php
$recnum = 1;
foreach($result->getRecords() as $contacts_search_row){
$rowclass = ($recnum % 2 == 0) ? "table_row" : "alt_row";
$recid = $contacts_search_row->getRecordId();
$pos = strpos($recid, "RID_!");
if ($pos !== false) {
$recid = substr($recid,0,5) . urlencode(substr($recid,strlen("RID_!")));
}
?>
<tr>
<td><a href='<?php echo "contactdetails.php?recid=$recid";?>'><?php echo nl2br( $contacts_search_row->getField('_kp_ContactID'))?></a></td>
<td>
<?php echo nl2br( $contacts_search_row->getField('NameFirst'))?>                            </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('NameLast'))?>                            </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('Country'))?>                            </td>
<td>
<?php echo nl2br( $contacts_search_row->getField('PhoneMobile'))?>                            </td>
</tr>
<?php $recnum++; } /* foreach record */?>
</tbody>
</table>
</div>
</div>
</body>
</html>

<!– Navigation Menu –>
<?php include_once ‘navigation.php’ ?><!– PAGE BODY –>
<div>
<?php
if ($errorMessage != ”) {
echo $errorMessage;
die;
}
?>
</div>
<div id=”content”>
<h1>
Contacts List                </h1>

Click on the Customer ID to view the full Contact details
<!–  Display record list page navigation controls –>
<div>

<?php
if ($skip != 0) {
echo ‘<a href=”?skip=0″>First</a>’.$sepbar;
}
?>

<?php
if ($prev >= 0) {
echo ‘<a href=”?skip=’.$prev.'”>Previous</a>’.$sepbar;
}
?>

Record <?php echo $firstrecord; ?> – <?php echo $lastrecord; ?> of <?php echo $found; ?>

<?php
if (($skip + $max) < $found) {
echo ‘<a href=”?skip=’.$next.'”>Next</a>’.$sepbar;
}
?>

<?php
if (($skip + $fetchcount) < $found) {
echo ‘<a href=”?skip=’.$lastskip.'”>Last</a>’;
}
?>

$layouts = $fm->listLayouts();
if(FileMaker::isError($layouts)) {
// FileMaker PHP API Error — Alert User.
$errorMessage = “FileMaker PHP Error: ” . $layouts->getMessage();
} else {

// Find all Contact records
$request = $fm->newFindAllCommand(‘WebContacts’);

// Set a Max value. Paging 20 records at a time
$max = 20;

$skip = $_GET[‘skip’];
if(!isset($skip)) { $skip = 0; }
$request->setRange($skip, $max);

// Perform the Find
$result = $request->execute();

if (FileMaker::isError($result)) {
if ($result->code = 401) {
$errorMessage = “There are no Contacts that match that request: ”  . ‘ (‘ . $result->code . ‘)’;
} else {
$errorMessage = “Contacts Find Error: ” . $result->getMessage() . ‘ (‘ . $result->code . ‘)’;
}

} else {

// Get the found records and setup page navigation links
$records = $result->getRecords();
$found = $result->getFoundSetCount();
$fetchcount = $result->getFetchCount();

// $totalpages = ceil($found / $max);

$prev = $skip – $max;
$next = $skip + $max;
if(($skip + $max) > $found) {$next = $skip; }

$lastskip = $found – $max;

$firstrecord = $skip + 1;

if ($fetchcount == $max) {
$lastrecord = (($firstrecord + $fetchcount) – 1);
} else {
$lastrecord = ($skip + $fetchcount);
}

$sepbar = ” | “;

// Get the found records and setup page navigation links
$records = $result->getRecords();
$found = $result->getFoundSetCount();
$fetchcount = $result->getFetchCount();

$totalpagesceil = ceil($found / $max);
$totalpagesfloor = floor($found / $max);

$prev = $skip – $max;
$next = $skip + $max;
if(($skip + $max) > $found) {$next = $skip; }

if($totalpagesceil == $totalpagesfloor) {
$lastskip = ($totalpagesceil – 1) * $max;
} else {
$lastskip = $totalpagesfloor * $max;
}

$firstrecord = $skip + 1;

if ($fetchcount == $max) {
$lastrecord = (($firstrecord + $fetchcount) – 1);
} else {
$lastrecord = ($skip + $fetchcount);
}

$sepbar = ” | “;

}
}