FileMaker Pro and SMS/TXT Messages

/2 Comments/in , , , /by

Update: we’ve released a new product – fmSMS – that allows you to send/receive SMS/TXT messages from FileMaker Pro. See the website at www.fmsms.com for full details.

We’ve been integrating FileMaker solutions into SMS/TXT message gateways for many years. We sent our first SMS from a FileMaker database in 1999 (back then it involved lots of integration with third party software and modems etc). In 2002 we integrated a CRM solution into one of Australia’s largest SMS Gateway providers using one plugin – simply type your message in your FileMaker database, click a button and within seconds the SMS was sent and received on the recipient’s handset.

SMS messages are an effective communication tool for many users – you can use them to send appointment reminders, alerts (e.g new pricing specials), internal office messages and promotional material to your customers. At around 8c each they are also much cheaper than phone calls and letters (around 40c each) and when used appropriately can save you and your business money on wasted phone calls and mailouts. You are generally limited to 160 characters so you need to keep your messages short and to the point. There’s also none of the hassle with mass email mailings and dealing with anti spam software/filtering and limits on how many messages you can send at once. You know within seconds whether your message was sent successfully.

Databuzz has now partnered with the world’s leading SMS Gateway provider – Clickatell – to bring integrated SMS/TXT message sending into any FileMaker Pro solution. Using the Clickatell SMS Gateway we can send messages to over 700 different mobile networks in over 200 different countries, usually within seconds. We can also offer delayed SMS sending (send a message for future delivery for up to 7 days in advance) and message verification to ensure your message was received by the recipient’s mobile handset.

Clickatell also offer great pricing – messages cost around AUD $0.06-0.08 cents and there are discounts if you purchase in bulk. You can get more information about Clickatell and their coverage/pricing by clicking on the link below:

If you need to integrate SMS/TXT message functionality into your FileMaker Pro 7, 8, 8.5 or 9.0 solution contact us for an obligation free quote. We can setup SMS sending for one/multiple recipients, allow you to use merge fields and templates and tracking the delivey of sent messages. If you’re using FileMaker Server 9 we can also leverage it’s script scheduling feature to have it send the messages after hours to free up workstations.

FileMaker and Web Services the easy way

/7 Comments/in , , /by

I’ve worked on many FileMaker implementations that require integration with external systems, whether it is importing, exporting, posting or syncing data, usually with XML involved in there somewhere. I’ve had many discussions with FileMaker engineering staff at FileMaker DevCons over the years as well about where I would like to see FileMaker Pro expand in these areas.

FileMaker can do a good job and exporting and importing XML data, and with the importing of XML data you can also specify an HTTP address and with the use of variables you can by quite dynamic with setting up your XML imports. Unfortunately things are much more limited on the XML export side of the equation – you can only export XML data to a file location (although you can reference an XSLT stylesheet during the export to transform your XML which can be stored on a web server). To get that XML onto the web you need to look at other technologies available for FileMaker Pro, Windows or the Macintosh and hook into these.

I’ve previously used the Troi URL plugin to assist with doing an HTTP POST of xml data generated via an XML export with a stylesheet then read back into a FileMaker field, but a recent project required the use of Web Services to submit data via XML, which is something that hasn’t been easy to do until now. The new Web Services plugin from FM Nexus makes working with Web Services incredibly simple. As long as you can locate a WSDL file for the web service you do everything else within a FileMaker calculation. This blew me away the first time I got this working – working with web services has never been this easy before.

The plugin does require some additional overhead and maintenance, as you need to create a wsdl folder in the FileMaker extensions folder (where you would normally store the plugin) and put your wsdl’s inside there. There’s no automated way to easily distribute these that I know off, though I ended up creating a simple installer to install the plugin and the wsdl folder. All the XML is taken care of by the plugin and you typically just need to replace any placeholders with the appropriate field from your FileMaker table which couldn’t be any easier. I encountered a number of issues with empty/NULL values that required some additional logic in the FileMaker calculation and each wsdl/web service will probably have it’s own unique requirements, but generally you can work around these with existing FileMaker Pro calculation functions.

You can get all the info and download a demo of the plugin at the FM Nexus website. FileMaker Advisor magazine also did a review of the plugin (note the pricing of the plugin appears to have changed from the review – it’s gone up unfortunately).

Plugins, AutoUpate and FileMaker Pro Runtimes

/0 Comments/in , , , /by

I’ve written previously about the changes to where plugins are downloaded to with FileMaker Pro 9, which now has 2 possible locations for storing plugins:

1 – the FileMaker Pro Extensions folder and;

2 – the user’s Application Data folder

Having the same plugins (but different versions) in both locations can create some plugin management problems for you as the plugins in the user’s Application Data folder take precedence over plugins with the same name in the FileMaker Pro Extensions folder. Today I encounterd a new scenario where this creates problems for the user – a runtime application that also uses plugins created with FileMaker Pro Advanced v9.

I created a runtime using FMPA 9.0v3 (Windows XP) and installed it on a client’s computer, which also had some of the same plugins in the /Application Support/FileMaker/Extensions folder but several of them were older versions used for other FileMaker Pro databases.

This led to several errors with my runtime as it couldn’t register the older versions of the plugin and my runtime was checking for specific versions of the plugin as it uses functions not available in the older versions. Whilst I am a fan of the AutoUpdate feature and the new download location in the user’s Application Data folder it certainly does create problems with interference with runtime applications on the same computer that use the same but different versions of a plugin, which is something we haven’t had to worry about with previous versions of FMP. I’ve reported this to FileMaker Inc as both a product problem and a feature request for this to be addressed in future releases.

If you’re a heavy user of plugins like myself with FileMaker Pro 9 you will be spending more time on plugin management issues and locating the user’s Application Data folder unfortunately. Runtime applications should only be able to reference plugins installed in the runtime application’s Extensions folder like runtime’s created with previous versions of FileMaker Pro.

FileMaker Pro 9, ESS and Those Strange Box Characters

/in , , /by

Anyone who has had to deal with exchanging data between a FileMaker Pro database and data stored in other database systems (MySQL, SQL Server, Oracle etc) will be familar with handling characters that FileMaker doesn’t appear to handle all that well. This is especially true when retrieving data via the web, e.g. an HTTP POST response back into a FileMaker field. Many of you for example are familar with the strange square box character that appears. If you’re working with ESS tables you more than likely going to have to come up with a way of dealing with these characters, which are generally ASCII characters that you normally don’t see in FileMaker, such as Line Feed, Vertical Tab and Carriage Return.

Here’s an example that we came across. If you fill out our Contact form on our website you might enter something like this:

web form

Note the returns in the Comments field. This form data is being stored in a MySQL database which we access using the great ESS feature of FileMaker Pro 9 (no more laborious copy/paste of data from an email or trying to parse the email form into the different FileMaker Pro fields!). When you view the ESS table and this field it now (at least on Windows with FileMaker Pro Advanced 9.0v3) looks like this:

ess view

Notice the square box characters that appear – it’s obviously not ideal to display these to your FileMaker users. There are a number of ways to deal with this but the way we’ve approached it in this case is to create a supplemental field in our ESS shadow table (remember you’re limited to calculation and summary fields when adding supplemental fields to an ESS table) and take advantage of a great new function that’s part of the Troi File v4.0 plugin: TrFile_AsciiValueToText function. As you’ll see shortly this makes easy work of dealing with special or invisible ASCII characters. We simply created a new calculation field with a text result using this formula:

Substitute ( comments; TrFile_AsciiValueToText( “-Unused” ; 10 ) ; “” )

Now if you look at the original MySQL field on the left and the new FileMaker supplemental calculation field on the right you’ll see the difference:

ess supplemental

ess supplementa

In the past we have tried to use the native Substitute function in FileMaker to handle these characters with mixed results. We tried copying the square box character to the clipboard and pasting into the calculation dialog:

paste into calculation formula

So far so good. Now click OK to save the formula:

click ok

 You can’t see the box anymore but it looks like something’s still there – let’s go back in to the calculation and check it:

back into formula

Hmmm that’s interesting the square box character has gone. Once you get to this point and do some replacements using the Substitute function you don’t get very far. We also noticed some other strange visual behavour. If you have a field with this same character in it and view it at 100% zoom level in Browse mode it looks like this:

browse mode 100%

Change the zoom level to less than 100% or more than 100% and you’ll see something like this:

Browse Mode 200%

Our suggestion is to find a tool that can help you identify which ASCII character you are dealing with. If you’re using the Troi File plugin make sure you upgrade to v4.0 or later so you can use the TrFile_AsciiValueToText function and simply call that within a Substitute function referencing the ASCII character you wish to replace. It’s much easier and cleaner than trying to copy/paste characters which don’t survive a copy/paste, at least on Windows.

There’s more details in this article Entering Clean Text (or: avoiding unwanted characters) and if you’re a FileMaker TechNet member search the TechTalk archives for other approaches and comments. You can get more background info on ASCII characters at this WIKI entry.

Latest FileMaker Australia Promotion

/0 Comments/in , /by

FileMaker Australia have a new promotion available – purchase 10 seats of FileMaker Pro 9 at Volume License Prices (VLA) and get a free copy of FileMaker Server 9 (RRP AUD $1645.00). Education customers get an even bigger discount. This also includes 12 months of maintenance which means you will automatically receive any upgrades for FileMaker Pro 9 and FileMaker Server 9 that you would normally have to pay for. You can get further details here – this promotion runs until 26 September, 2008 which is longer than the usual quarterly promotion FileMaker Australia usually run. Similar promotions are being run in other regions so check with your local FileMaker office.

Sample Data for FileMaker Developers

/2 Comments/in , , /by

If you’re ever looking for some sample data to populate a typical FileMaker Pro contacts database here are some sources for free sample data:

  • Fake Name Generator – creates single and bulk names, addresses, phone numbers etc. Has different format options for different countries
  • Briandunning.com – has free downloadable .csv files for 500, 5000, 50000 and 350000 records with a US centric focus

When I get a chance I’ll put together a Australian specific one and post it online here but for now you can download either of these and modify to suit your requirements.

FileMaker Developer Conference 2008

/0 Comments/in , , , /by

FileMaker Inc recently announced the details for this year’s FileMaker Developer Conference – DevCon 2008. This year DevCon moves back to Phoenix, Arizona after a few years in Orlando, Florida. For Australian attendees this is good news – connecting to Phoenix is much quicker than another long flight to Orlando from the US West Coast after 14 hours in a plane just getting to the US (even worse if you had to fly economy). It’s a shame the new V Australia flights aren’t taking off until December as finally we see some real competition on the route which Qantas and United have been sharing for years. I like the new timetable for the V Australia flights as well – Qantas seem to enjoy arriving in Los Angeles at 6am, having left Australia at 6am and if you’re like me you haven’t slept much on the way.

The conference is on  from July 13 to 17 at the JW Marriott Desert Ridge Resort & Spa in Phoenix, Arizona and if you register by May 16 you will save US $200.00 (registration is US $1,195.00 and increases to US $1,395 after May 16). There are 3 tracks this year: Fundamentals, Advanced and Integration. You can get all the details here. FMPug also have a Scheduler formatted for handheld/mobile devices at http://www.fmpug.com/devcon.

Databuzz will once again be attending DevCon 2008 – this is our seventh straight year attending the FileMaker DevCon from memory – and we look forward to learning some new tricks and techniques which we can bring back and implement in our customer’s solutions.

 Andrew Duncan

Beta of FTPit Pro 2.0 released

/0 Comments/in , /by

FTPit Pro 2.0.0b1 has recently been released by the fine folks at CNS Plug-ins. I’ve been eagerly waiting for the “Pro” version of FTPit for quite a while now. I am a big fan of the SMTPit Pro and Pop3it Pro plugins for sending and receiving email from a FileMaker database respectively. You can download the beta of the FTPit Pro, which if you haven’t used it before allows you to upload/download files from/to FTP servers from your FileMaker databsae, from:

http://jake.cnsplug-ins.com/index.htm?id=wR3t3EMb

FileMaker Pro 9 Bible Book Released

/0 Comments/in , , /by

A new FileMaker related book has recently been released – FileMaker Pro 9 Bible, co-authored by Australia’s very own Ray Cologon and Dennis R. Cohen. Weighing in at a massize 768 pages this will be an invaluable addition for most FileMaker developers, both beginner and experienced. I’ve ordered my copy from Amazon already and are looking forward to it’s imminent arival. Members of the FileMaker TechNet and FileMaker Business Alliance will be familiar with the quality of Ray’s contributions to the TechTalk/BizTalk mailing lists. A PDF of the table of contents is available at:

http://www.nightwing.com.au/FileMaker/FMPBible/FMP9Bible_TOC.pdf

(which itself is 21 pages!) which shows the depth of content that this new book offers. I suspect this book will become one of the “must haves” for FileMaker developers worldwide (at least English speaking ones at this stage). Several example extracts are available on the publisher’s website at:

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470177438,descCd-description.html

FileMaker Server and Auto Updating Plugins

/2 Comments/in , , , /by

As a long time user of as many plugins as I can get my hands on one of my favourite features of FileMaker Server (starting with v5.5 if my memory serves me correctly) is the ability to push plugins to FileMaker Pro and FileMaker Pro Advanced clients automatically from the server using some simple FileMaker Pro scripts. Plugin maintenance was a painful process before this, having to manually install and update new plugin versions on each client workstation.The Auto Update feature is great for:

  1. ensuring all users of your database solution have the plugin installed
  2. distributing plugin updates by making some small changes to the server and having these downloaded automatically
  3. updating plugins without having to restart FileMaker Pro/Advanced!

The documentation for setting up the Auto Update feature is covered in depth in the FileMaker Server 9 Guide to Updating Plug-ins available from the FileMaker website and the FileMaker Server CD. Essentially you need to setup some scripts that you call (typically as part of the on open or startup script) which will download the plugin if it is not installed or update the plugin if an older version of the plugin is installed (if you were to manually update an older version of the plugin you would have to quit FileMaker Pro/Advanced and re-open it if it was already open).

The Auto Update feature has worked in much the same way from Server 5.5 through Server 7, 8 and 9. However there is one small change in FileMaker Pro 9 that has caused some extra plugin maintenance work. FileMaker Pro 9 now has the ability to store plugins in 2 locations. In addition to storing plugins in the Extensions directory within the FileMaker Pro folder, for example on Windows for FileMaker Pro Advanced v9 this would be:

C:\Program Files\FileMaker\FileMaker Pro 9 Advanced\Extensions

it can also store plugins in the user’s application data directory, which is typically a hidden folder that is not subject to the same access privileges issues as the application directory might be which can cause plugins not be downloaded (the user might not have read/write access to the application directory, for example). The application data directory has different locations for all supported operating systems for FileMaker Pro 9:

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

Now that you can install plugins in more than one location you will need to learn the new rules about managing plugins in these different locations. For example what happens if you have the same plugin installed in both locations? Which one do you update?

As I encountered these problems within days of FileMaker Pro 9 being released I’ve developed a good understanding of how to manage plugins in both locations and have developed these rules:

  • Any plugin distributed by FileMaker Server (not just Server 9) to a FileMaker Pro 9 client will be downloaded to the user’s application folder. I regularly use FileMaker Pro 9 with FileMaker Server 8 and it is a function of FileMaker Pro 9 not Server 9 that causes the plugins to be downloaded to the user’s application data folder.
  • If you have the same plugin installed in both locations the one installed in the user’s application data folder takes precedence, even if it is an older version. This can be hard to troubleshoot as the user’s application data folder is typically hidden in my experience so you need to know the location and how to turn on hidden folders so you can find it in the first place.
  • If you are attempting to update a plugin that is installed in both locations it will be downloaded to the user’s application data folder and any older versions will be moved to the Saved folder like normal. However if an older version of the plugin is currently installed in the FileMaker extensions folder you will have to quit/relaunch FileMaker Pro for the newer downloaded version of the plugin to be loaded by FileMaker Pro 9. This is one of the downsides to this new feature, mostly for developers that work with plugins and are used to manually putting them into the FileMaker Pro extensions folder. For most workstations all plugins will be downloaded and updated without incident in the user’s application data folder.

The information about the change to downloading to the user’s application data folder is tucked away in the FileMaker Server 9 readme – in my opinion it would be more appropriate to include it in the FileMaker Pro 9 readme. There’s some useful information about Auto Update and FileMaker Server on the Troi website and the 24u website as well.

P.S: I needed to locate the user’s application data folder quickly on different workstations so I wrote this script (which uses the Troi File plugin naturally!) to get the location to the user’s application data folder (currently only for Windows XP and Vista, haven’t done a Mac version yet sorry). Haven’t worked out the best way to format scripts in a WordPress blog post so sorry for the formatting – if you know how to do this please let me know!

 

#This script gets the path to the local application data extensions folder that FileMaker Pro 9 and FileMaker Server 9 use for downloading

plugins automatically

 

Enter Browse Mode

 

#First check to see if single user or multi user as paths will be different

 

If [ Get( MultiUserState) <> 2 ]

 

#They are single user so just open the local application Extensions folder for the currently running version of FileMaker Pro

 

Set Variable [ $VarTroiPath; Value:TrFile_Launch( “” ; TrFile_GetPathTo( “-CurrentAppFolder” ) & “Extensions\\” ; ) ]

 

If [ $VarTroiPath 0 ]

 

Show Custom Dialog [ Title: “Error – Opening Folder”; Message: “There was an error opening the plugins folder!”; Buttons: OK ]

 

Exit Script [ ]

 

End If

Exit Script [ ]

 

End If

 

#First need to determine if current user is on Windows XP or Vista as the paths are different

 

If [ Abs ( GetAsNumber ( Get ( SystemVersion ) = 5.1) ) ]

 

#It’s Windows XP SP 2

 

Set Variable [ $VarPath1; Value:Right ( ( Get(DesktopPath) ) ; (Length ( Get(DesktopPath) ) – 27)) ]

 

Set Variable [ $VarPath2; Value:Left ($VarPath1 ; (Length ($VarPath1 ) -9 )) ]

 

#First check to see if the folder actually exists as it might not yet exist if no plugins have been downloaded to this location yet

Set Variable [ $VarFolderExists; Value:TrFile_Exists( “-Unused” ; “C:\Documents and Settings\\” & $VarPath2 & “\Local

Settings\Application Data\FileMaker\Extensions\\” ) ]

 

#Will return 0 if it doesn’t exist otherwise will return 1

 

If [ $VarFolderExists = 0 ]

 

#Doesn’t exist so just open local FileMaker Pro extensions folder instead

 

Set Variable [ $VarTroiPath; Value:TrFile_Launch( “” ; TrFile_GetPathTo( “-CurrentAppFolder” ) & “Extensions\\” ; ) ]

 

Else

 

#Does exist so open that one

Set Variable [ $VarTroiPath; Value:TrFile_Launch( “” ; “C:\Documents and Settings\\” & $VarPath2 & “\Local Settings\Application

Data\FileMaker\Extensions\\” ; ) ]

 

End If

If [ $VarTroiPath 0 ]

 

Show Custom Dialog [ Title: “Error – Opening Folder”; Message: “There was an error opening the plugins folder!”; Buttons: OK ]

 

Exit Script [ ]

 

End If

Else If [ Abs ( GetAsNumber ( Get ( SystemVersion ) = 6) ) ]

 

#It’s Windows Vista

 

Set Variable [ $VarPath1; Value:Right ( ( Get(DesktopPath) ) ; (Length ( Get(DesktopPath) ) – 10)) ]

 

Set Variable [ $VarPath2; Value:Left ($VarPath1 ; (Length ($VarPath1 ) -9 )) ]

 

#First check to see if the folder actually exists as it might not yet exist if no plugins have been downloaded to this location yet

Set Variable [ $VarFolderExists; Value:TrFile_Exists( “-Unused” ; “C:\Users\\” & $VarPath2 &

“\AppData\Local\FileMaker\Extensions\\” ) ]

 

#Will return 0 if it doesn’t exist otherwise will return 1

 

If [ $VarFolderExists = 0 ]

 

#Doesn’t exist so just open local FileMaker Pro extensions folder instead

 

Set Variable [ $VarTroiPath; Value:TrFile_Launch( “” ; TrFile_GetPathTo( “-CurrentAppFolder” ) & “Extensions\\” ; ) ]

 

Else

 

#Does exist so open that one

 

Set Variable [ $VarTroiPath; Value:TrFile_Launch( “” ; “C:\Users\\” & $VarPath2 & “\AppData\Local\FileMaker\Extensions\\” ; ) ]

 

End If

If [ $VarTroiPath 0 ]

 

Show Custom Dialog [ Title: “Error – Opening Folder”; Message: “There was an error opening the plugins folder!”; Buttons: OK ]

 

Exit Script [ ]

 

End If

End If

Commit Records/Requests