Cleaning up Smart Quotes in FileMaker Pro v10

/in , , , , /by

One of my pet hates is the use of “smart quotes” in FileMaker Pro. Smart quotes are the curly quotation marks and curly apostrophes and are usually entered in 2 ways:

1. the setting for smart quotes is enabled by default for the creation of new .fp7 database files on Mac OS X but not for Windows XP, so anytime you enter an apostrophe or a quote mark you will be using smart quotes.

2. pasting in text from Microsoft Word, which also has a preference setting for smart quotes and which is also enabled by default (you can get instructions for how to turn this off on Windows here)

Here are some examples of how they appear:

Single Left Smart Quote: ‘

Single Right Smart Quote: ’

Double Left Smart Quote: “

Double Right Smart Quote: ”

The smart quotes setting in FileMaker Pro can be found by opening a database file and going to the File menu > File Options, and click the Text tab (note that you must log in using the Full Access privilege set to edit items in the Text tab). Here you can toggle smart quotes on and off- if you enable smart quotes when it was previously off this will only affect new typing as any existing data will not be edited automatically.

For many users smart quotes are not problematic, but when it comes to exchanging data with other database platforms (e.g. SQL) and systems (particularly web based systems) their presence will often cause data exchange processes to break or for text not to be displayed correctly, which means you then have to deal with any smart quotes in your data. I was recently working on submitting FileMaker data to on online system and was required to URL encode my data. Everything was working well until one day data was being truncated for no obvious reason. After many tests I realised that some of the apostrophe and quotation characters were not being URL encoded correctly (I was using this handy custom function to URL encode the data before submitting it via HTTP POST). Once I zoomed in on the text to 400% and changed the font to a sans serif font all was clear – the text was using smart quotes which the customer has enabled as this was the default setting for a new .fp7 file on Mac OS X. I quickly turned that off which meant any new data entered directly into the fields would not be using smart quotes, but all the existing data needed to be cleaned up. I created a new custom function RemoveSmartQuotes (text) which uses one of the new functions in FileMaker Pro v10 to reference a character without having to actually type/enter the character into a calculation.

The following calculation uses the Char function to identify any instances of the single and double smart quotes/apostrophes and replace them with plain (straight) quotes and apostrophes:

Substitute (

text ;

[ Char ( 8216 ) ; “‘” ] ; // left smart quote
[ Char ( 8217 ) ; “‘” ] ;  // right smart quote

[ Char ( 8220) ; “\”” ] ; // left double smart quote
[ Char ( 8221) ; “\”” ]   // right double smart quote

 

)

I’ve dcreate a custom function that uses this calculation that you can copy/paste into your file which should appear shortly on Brian Dunning’s custom function site. This custom function can then be used in a number of ways to clean up text that cannot have any smart quotes in them, for example as an auto enter calculation on a field or in a script that transforms data. If you’re using a version of FileMaker Pro before v10 you simply need to change the use of the Char function with the actual smart quotes themselves and remember to escape the double quotes.

Databuzz eNews – December 2008 Released

/0 Comments/in , , , , , , , , /by

We’ve just released our eNews newsletter for December 2008. You can view it online at:

http://www.databuzz.com.au/enews/enews_122008_generic.html

If you want to be added to the newsletter mailing list just leave a comment below and we’ll add you to the list until our new automated subscription service is operational.

fmSMS . . . Coming Soon

/6 Comments/in , , /by

fmsms-logo-s

We’ve been hard at work finalising a new product of ours – fmSMS – which is the combination of over 7 years of experience integrating FileMaker Pro with SMS (Short Message Service/TXT) gateways to allow you to send single/bulk SMS messages at the click of a button from a FileMaker Pro database. I can still remember the excitement when I sent my first SMS message from a FileMaker Pro v5 database to my mobile phone and seeing it arrive within seconds. That same “wow factor” is also seen by all of our clients when they first implement SMS with their FileMaker solutions and see how easy it is to sent short messages to their customers.

We’ve decided to start from scratch and combine all the great features of FileMaker Pro 9 with the power of SMS communication. With fmSMS you will be able to:

  • send an SMS message to a single Contact which is typically received in under 10 seconds
  • send a bulk SMS to a group of Contacts using templates and merge fields so each message is personalised
  • check on the delivery status of each message that you have sent
  • schedule a message to be sent up to 7 days in advance (for example you could send an SMS today and have it delivered on the weekend or holidays. Make it look like you’re in the office on XMas!)
  • check your available SMS credits
  • purchase additional SMS credits

There’s a stack of other great features that we’re finalising now. You will be able to access over 700 mobile networks in over 200 countries with SMS prices around AUD 6-8 cents each.

We’d love to hear about any must have features that you’d like to see in fmSMS in case we’ve missed anything – just leave us a comment below. If you’d like to be added to the notification list when we release fmSMS just leave a comment with your name and email address and we’ll take care of the rest.

UPDATE: fmSMS has now been released and you can get all the details at www.fmsms.com. A trial version is available to download.

We’re FileMaker 9 Certified!

/0 Comments/in , , , , /by

Andrew Duncan finally found time to sit for the FileMaker 9 Certification exam today and we’re pleased to announce that he passed with flying colours. We aim to maintain our certification with each new version of FileMaker that is released. FileMaker Certification lets our customers know that we’re experts in the FileMaker industry and is the only credential sponsored by FileMaker Inc.

 

FileMaker 9 Certified!

Unable to see hosted files on FileMaker Server

/0 Comments/in , , /by

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:

 

Not sure if a FileMaker Server 9 update for Windows is necessary at this stage.

FileMaker API for PHP

/0 Comments/in , , , , /by

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 DevCon 2008 Post Mortem

/1 Comment/in , , , , , /by

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

/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.