Latest Databuzz Newsletter Now Online
We’ve just released our latest eNews for October 2008. You can read it online at:
Unable to see hosted files on FileMaker Server
An issue has arisen with installations of FileMaker Pro and FileMaker Server on or after Sept. 22, 2008 using SSL that causes hosted database files to be invisible to workstation computers. If you have installed FileMaker Pro on or after Sept. 22, 2008 and have FileMaker Server set to use SSL you will experience this. FileMaker now have an article in their KnowledgeBase about this with a suggested workaround. There’s also been some discussion about this on fmforums with some other suggestions that you might find more practical, including simply replacing the server.pem file with a known good copy from another workstation in your office that was installed prior to Sept. 22, 2008 or turning off SSL.
Update (October 19, 2003): FileMaker have released a number of “SSL Updaters” that will install a valid SSL certificate file (server.pem). Updaters are available at the FileMaker downloads site. At the time of writing there are the following updates:
- FileMaker Pro 9 & FileMaker Pro 9 Advanced Mac OS X Update
- FileMaker Pro 9 Windows Update
- FileMaker Pro 9 Advanced Windows Update
- FileMaker Server 9 and FileMaker Server 9 Advanced Mac OS X Update
Not sure if a FileMaker Server 9 update for Windows is necessary at this stage.
FileMaker API for PHP
It’s been over 2 years now since the FileMaker API for PHP public beta was first released, followed by it’s inclusion in FileMaker Server 9 (not Advanced which was a great decision to really get the technology widely adopted). I’ve done a lot of projects this year involving the FileMaker API for PHP to create web interfaces to backend FileMaker 9 databases, as well as upgrading some old .fp5/CDML sites to PHP API. Upgrading an old CDML site is great as you can accomplish so much more with PHP compared to the old proprietary CDML, and more often than not there is a great code snippet that you can reuse on the php.net site or other third party sites. I’ve been able to convert a 20+ page CDML site down to less than 10 pages with PHP, mainly through the use of include pages and variables.
FileMaker Server 9 ships with the PHP Site Assistant, which for anyone who worked with CDML and used the FileMaker Connection Assistant that was part of Claris Home Page, will see as it’s logical successor for the PHP API. It does a good job in creating a site in a matter of minutes (you can choose what type of site you require) and creates the necessary php pages and uses some nice looking CSS. However the more you use php pages generated by the Site Assistant the more problems you are likely to encounter in my experience. The php pages created by the Site Assistant are multi-purposed and have code for a number of requirements that you might not require (e.g. a record details page can be used to view, edit, delete or find). It also makes use of an include file – fmview.php – which is where things get really confusing. This php page has lots of php code to handle lots of different tasks and as you start to extend and customise the pages generated by the Site Assistant you are likely to butt heads with the fmview.php page as you try to understand what it’s trying to do.
Like me you’ll probably find extending pages generated by the Site Assistant problematic if you don’t want to rely on the fmview.php file, which is required for other functions. You quickly get to the point where you need to rewrite the pages from scratch so they don’t rely on the fmview.php page and you can have total control over each page and how it interacts with your FileMaker database. There’s also a few bugs with the php generated by the Site Assistant that you’ll have to deal with (e.g. in the recordlist.php page it creates navigation links for moving from one page to another in your found set of records, but the links overlap. For example it will have Record 1-20 on page 1, then Record 20-40 on page 2, and so on but it should be Record 1-20, Record 21-40 etc.)
Another alternative to generating PHP API pages quickly is FM Studio from fmwebschool. Like the Site Assistant it will generate the necessary PHP code for the PHP API and create your browse, find, edit, delete etc code for you. Like the Site Assistant you will also probably spend some time understanding how and why FM Studio does certain things and getting your head around the include files that are required.
Once your become familiar with the PHP code required you can hopefully get to the point where you can simply write the code yourself into your php editor (I use Dreamweaver). You’ll generally end up with cleaner and more concise code that you understand and can make sense of (don’t forget those comments!) without wondering why someone else did it that way.
Getting started with PHP does takes some time, like learning anything new. Here’s a list of some of the resources that I found useful in getting started with PHP in general and the new PHP API:
six.fried.rice blog article “Up to Speed with the FileMaker PHP API”
FileMaker Inc PHP Web Publishing Overview
Article in PHP Architect Magazine by Jonathan Stark – Part 1
Article in PHP Architect Magazine by Jonathan Stark – Part 2
FileMaker Custom Web Publishing with PHP PDF
VTC Training CD – FileMaker 9 PHP Foundations
jonathanstark.com – Jonathan Stark is the author of the Web Publishing with PHP and FileMaker 9 book
As well as Jonathan Stark’s book there is also another book I found useful that extends the content of Jonathan’s book – FileMaker Web Publishing: A Complete Guide to Using the API for PHP
By Allyson Olm, Stephen Knight, Michael Petrov. Both books come with sample code that you can open in your editor of choice and get hands on as you work your way through the chapters.
Note: as well as FileMaker’s official API for PHP there are some other alternative PHP API’s. Firstly there is FX.PHP which was around for many years before FileMaker’s PHP API was released. There’s also FM and PHP which I haven’t looked at yet.
FileMaker Mobile for the iPhone
I didn’t mention this in my DevCon 2008 recap but one of the other new tools that was previewed in the foyer and hallways of DevCon was the new FMTouch. Think of FMTouch as FileMaker Mobile for the Apple iPhone or iTouch, but much better. The relational capabilities of FMTouch is something long time FileMaker Mobile (now discontinued) users have been wanting for years. FMTouch, whilst limited to the Apple iPhone and iTouch, does a great job in transferring data between your iPhone and your copy of FileMaker on your desktop computer. Like FileMaker Mobile the data is not live but synced on demand – the iPhone just needs to be on your local network using the built in WiFi or you can setup port forwarding to sync remotely.
It works via a plugin and does require the use of FileMaker Pro Advanced to create the Database Design Report that gets downloaded to the iPhone to create the database structure on the iPhone. Databuzz can help you if you don’t have a copy of FileMaker Pro Advanced but need to create the DDR to get FMTouch up and running.
Databuzz is working on some web based solutions for handheld users that need live access to their data, using the FileMaker API for PHP and FileMaker Server 9. We can create smaller versions of the main layouts with navigation between them and on a 3G connection the performance is generally more than adequate. This will work on all handheld web browsers and provides live access to your FileMaker database, which is becoming more and more of a necessity for many of our customers when on the road with only their mobile/smartphones.
FileMaker DevCon 2008 Post Mortem
It’s been a few weeks now since I returned from the FileMaker 2008 Developer Conference in Phoenix, Arizona. It was a whirlwind trip this time – there and back in 6 days with lots of jetlag and not much sleep, no thanks to Qantas for cancelling my return flight and delaying my departure. I’ve finally settled back in and can report on some of the highlights from the conference and the product showcase. I spent most of my time attending any PHP related sessions and learnt a lot about the power of PHP in conjunction with the FileMaker PHP API. I’m currently working on a project that is an upgrade from a CDML/FileMaker Unlimited 5 database driven site to a FileMaker Server 9/PHP driven site. There’s lots of new stuff to learn and I’m impressed with the capabilities of PHP compared to CDML. I particularly enjoyed Lance Hallberg’s sessions on the PHP API fundamentals and the advanced features.
The two sessions on using the Web Viewer to interact with your FileMaker tables and as a dynamic interface were both well attended. Both highlighted the power of the MBS plugin and the Fusion Reactor plugin – the amazing Fusion Reactor also won the FileMaker Excellence award for Solution of the Year. Congratulations to Craig and the gang at Digital Fusion for their amazing plugin which is one of the most truly innovative plugins to be released in the last few years.
In the product showcase I ended up buying some new tools and upgrades:
BaseElements 2 – Goya have released their update to v2 of their DDR Analysis tool BaseElements. This is almost a must have for the professional FileMaker Developer. I’m still using both Inspector and BaseElements as each have their strengths but spend most of my time with BaseElements. SixFriedRice were demoing an early released of a web hosted service – FM Lumen – which they promise will provide deeper and faster analysis of the FileMaker DDR. I can’t wait to give that one a try.
ClipManager – My FM Butler have released a beta of v3.0 of their handy tool Clip Manager which allows you to store and manipulate the contents of the FileMaker clipboard. It’s also at present one of the only ways to copy custom functions and you can use it to build up a library of fields, scripts, layouts and other elements that you can quickly add to your solutions. v3.0 is also the first release that runs on Windows as well as the Macintosh.
Web Services Plugin – I already have a developer licence for the amazing Web Services plugin from FM Nexus. This was featured in the opening keynote and FM Nexus released a beta of v2 which makes it easier to load functions on the fly by storing the WSDL file inside a field in your database.
I also bought the VTC PHP Training CD so I can totally immerse myself in the FileMaker API for PHP. I look forward to attending the next DevCon in 2009 in San Francisco – finally a new city and new new hotel!
FileMaker Pro and SMS/TXT Messages
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
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
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
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:
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:
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:
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:
So far so good. Now click OK to save the formula:
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:
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:
Change the zoom level to less than 100% or more than 100% and you’ll see something like this:
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
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.