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.

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.

Google Charts API and FileMaker

/2 Comments/in , , , /by

I’ve been spending most of my time over the past week or so working on integrating the Google Charts API into my upcoming FileMaker Pro CRM solution. For those not familiar with it the Google Charts API was released on December 6, 2007 and lets you dynamically generate charts via an HTTP request. So what’s in it for FileMaker developers? Well with the introduction of the Web Viewer in FileMaker Pro 8.5 you can now compose a chart request in your database and have it displayed on a Web Viewer on a layout. As the data changes the chart updates live. The chart is returned as a .PNG file. Here’s an example:

http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250×100&chl=Hello|World

You obviously require an active Internet connection to use this feature. Learning the API is pretty straightforward and most of the time will be spent getting familiar with the different charts available and the syntax required in the HTTP request, along with which data encoding method you will decide to use.

As I did more testing I was disapointed that, as the chart is a PNG file, I wasn’t able to use the new layout object resizing to resize the chart to expand/contract as the web viewer was resized, which is how I normally setup my web viewer layouts. So I decided to use the Troi URL plugin which is able to GET images from a website. As the URL appears in this format:

http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250×100&chl=Hello|World

you are not able to use that as is to retrieve the PNG file into a FileMaker container field. However there is a new switch in v2.0 and later of the plugin -“-ExtraImageCheck” – which when used with the TURL_Get function checks the returned data to verify if it is an image, even though there is no .png extension on the URL. Now I can set the container field to resize with the layout and the chart grows bigger and smaller – you don’t have control over the resolution of the PNG so it’s appearance will start to degrade as you increase it’s size (you do have control over the image size in pixels).

I’ve been quite impressed with the features of the Google Charts API. It was updated on March 20, 2008 with some new chart types (the maps one is pretty cool) and lifted the previous limit of 50,000 queries per user per day. FMWebschool have a sample (locked) file that you can download here that shows some of the options for FileMaker integration, along with 2 movies here and here. If you know what data you are charting and what chart types you prefer you should be able to integrate Google Charts pretty quickly into your FileMaker Pro solution. Whilst not offering as many features as some of the charting plugins available you can’t complain about the price.

A great future enhancement of the Web Viewer in FileMaker Pro would be the addition of another option for “Google Charts” to make setting up a web viewer to display Google Charts even easier.

Andrew Duncan

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

 

FileMaker Server 9 – server-side ScriptMaker scripts

/0 Comments/in , , /by

One of the great new features of FileMaker Server 9 is the ability to schedule regular ScriptMaker scripts (i.e those that you create with FileMaker Pro/Pro Advanced). FileMaker Server has had the ability to perform system-level scripts since at least v5.5 as far as I can recall (e.g. Windows batch scripts for Windows and AppleScripts for Mac OS) but v9 allows you to schedule ScriptMaker scripts as well. This allows administrators to schedule batch processes to take place on the server or after business hours, for example for batch processing records for end of day processing.

The only limitation is that, like with Instant Web Publishing, the scripts must be “web compatible”:

The screenshot above shows the standard ScriptMaker script dialog box. In the bottom left hand corner there is a checkbox to indicate web compatability. With this ticked all web compatible scripts are left in black and non web compatible scripts are greyed out. So remember when creating scripts that you intend to schedule on Server 9 you must include only the web compatible script steps as indicated. I haven’t done any tests to determine what happens with non web compatible script steps to see if they are simply ignored or the script stops executing at that point.

Whilst working on the finishing touches to our new CRM product Data Manager I came across the idea of utilising the ability to schedule ScriptMaker steps in FileMaker Server 9 which will be the standard base version for multi user clients. We have routines that perform batch processes and use the Troi Dialog plugin to display a progress bar whilst the script is running to give feedback to the user. Unfortunately this means the user cannot use FileMaker whilst the script is running, for example when sending a bulk email to 500 customers. If it isn’t urgent that the email is sent immediately we will have the ability to set the batch operation to be scheduled so that the sending is actually performed by FileMaker Server, which will not interupt the user and can be performed out of business hours (less disruption to Internet connection) and makes better use of the Server’s resources. You can even use the new email notification feature to get feedback via email about the scripts operation, however you will need to build in some interface to allow clients to review batch operations to check for sending errors (e.g. Internet was down). Most plugins use the Set Field script step which is web compatble so it’s easy to use plugins that don’t rely on keyboard/mouse input and dialog boxes.

You can get more information on Server 9 script scheduling at:

FileMaker Web Viewer and Data URL’s

/3 Comments/in , , /by

Whilst working on our new CRM (Customer Relationship Management) solution – Data Manager – this week we made an interesting discovery with the way the Web Viewer works. We wanted to have a way to preview an html email that was being composed in Data Manager so the user could see how it will look to the recipient, just as it does in Outlook etc when composing a new email. We also didn’t want to have to keep exporting files and then opening the exported file as that creates unnecessary clutter for the developer and the customer (though with FM Pro 9 you can use the new Get(TemporaryPath) function to dump files into the user’s temp folder. We investigated using a Data URL with a Web Viewer as this would provide a “live” view of the content. This works well for plain text type emails with no inline images as you can simply reference the fields using something like:

“data:text/html,” &

Substitute ( DM_Email::email_text ; “¶”; “<br>”)

This replaces any FileMaker carriage returns with the HTML equivalent.

However if you’re using email stationery like you can in Microsoft Outlook you might be referencing one or more inline images (gif, jpeg, etc) which are stored in a folder on the hard drive, server etc. This makes things a bit more complicated. Fortunately we have a table in Data Manager that stores all the inline images for each user which stores the path, file name and a copy of the image in a container field as well. Whilst scratching our heads trying to work out how we can do a substitute calculation to change any image references which might be:

<img src=”banner.gif” mce_src=”banner.gif”

<img border=”0″ src=”clickhere_btn.jpg” mce_src=”clickhere_btn.jpg”

we made an accidental discovery. If you export the contents of the container fields to the user’s temp folder using the Get(TemporaryPath) function the Web Viewer will somehow “discover” the images and show them when displaying the preview of the html email. This made things a lot easier for us as we don’t have to deal with complex substitutions in the html source (we’ve only tested this on Windows XP and have since heard this doesn’t work for Mac users but we haven’t had a chance to test on a Mac yet). We’re also big fans on the SMTPit Pro plugin which has a nifty function for converting rich text into HTML on the fly which makes this so much easier.

You can read more about the Data URL scheme at:

http://en.wikipedia.org/wiki/Data:_URI_scheme

http://www.ietf.org/rfc/rfc2397.txt

There’s also some good info on FileMaker and Data URL’s in the Web Viewer with some example files at:

http://sixfriedrice.com/wp/filemaker-9-tip9-web-viewers-without-the-web

http://www.nightwing.com.au/FileMaker/demos9/demo908.html

http://www.nightwing.com.au/FileMaker/demos9/demo901.html

Posting XML Data

/in , , /by

I’ve been working with some web services lately that require some XML data to be submitted as a POST. FileMaker Pro can import XML data from a .xml file or from an HTTP request (and use a XSLT stylesheet from a file or an HTTP request). However XML can only be exported to a .xml file which limits it’s usefullness when working with web services. I managed to workaround this using the Troi File Plugin and the Troi URL Plugin from Troi Automaterising (we’re big fans of the Troi plugins).

For one customer we exported the XML data using a stylesheet then read that .xml file back into the FileMaker solution using the Troi fil plugin. We then used the Troi URL plugin to POST that xml data directly to a web service. We then had to parse the XML that was returned and isolate a URL which we could then view using the Web Viewer. This all happens within a few seconds.

For the other customer the XML wasn’t particularly complicated so we crafted the XML using FileMaker calculations and posted that.

zippScript Plugin

/1 Comment/in , , /by

I’m a big fan of FileMaker Plugins and use them extensively in my solutions as they allow me to implement features that are not native to FileMaker Pro. I’ve spent some time lately with the zippScript plugin developed by John Kornhaus [Updated October 10 to remove the link to the zippTools website as John Kornhaus has advised he is no longer able to distribute the plugin). This is an event plugin that lets you trigger scripts based on certain contions, such as when exiting a field or committing a record. You can also schedule scripts to be run at certain times.

Another freeware plugin that I’ve started to look at is the MooPlug which has a number of handy functions and you can’t complain about the price.

FileMaker Pro 9 and ESS

/1 Comment/in , , /by

I’ve spent some time over the past few weeks using the new External SQL Data Sources feature that is part of FileMaker Pro 9 and I have to say I’m pretty impressed. I’ve started using it internally to do something I’ve been dreaming of for many years – live integration (no import/export) between and internal FileMaker CRM system and an external web based SQL database. With work on my new website almost complete I’ve been building some MySQL databases for the usual tasks of feedback, product suggestions and other web based forms. In the past I would receive emails when these forms had been submitted and copy/paste into a FileMaker database. Now I can view these MySQL tables from within FileMaker Pro 9 at the office. I’m impressed with the speed and responsiveness so far and also how you can set this up on FileMaker Server 9 so you don’t have to worry about installing ODBC drivers on all the workstations.

If you’re getting started with ESS and FileMaker Pro 9 I would recommend the public ESS Tech Brief and if you’re a TechNet member there’s a more detailed technical version available in the TechNet members only area. There’s also an article in the March 2008 issue of FileMaker Advisor by the same author as the tech briefs available from FileMaker.

Finally make sure you use the new Refresh Window menu command (under the Records menu) or have a button/script that uses the Refresh Window [flush cached SQL data] script step to update your ESS views.

I also tested ESS with WordPress which uses MySQL as the database backend and I can view the WordPress tables in FileMaker Pro 9 too!