Custom Function, FileMaker

Generating URLs with FileMaker

Recently we inherited a new project where FileMaker was being used to manage the content for a website, with an ODBC connection updating the MySQL database behind the site.

The previous developers were using numeric IDs to link from page to page and category to category to sub-category to company etc. While this was certainly efficient it resulted in URLs like

The client wasn’t very happy with this solution, if for no other reason than it made looking at analytics for the site really difficult because they couldn’t tell at a glance what category 16 was, or sub category 220, or who company 1472 were!

Both the client and their SEO consultant wanted those numbers replaced with a URL which described the content – a ‘slug’ as it’s often referred to.

Clearly URLs can only safely contain certain characters, if a ? or an & turned up in the URL it could change the meaning of the URL to the web server, and non-standard characters also pose other issues (things like ä or ę or any of the other high ASCII character).

We came up with a FileMaker custom function we call CreateWebSafeSlug which takes a single parameter text.

Substitute (
  Filter ( 
    Lower ( 
      Substitute ( 
          TrimAll ( text ; 0 ; 0 )
        ) ; [ "&" ; "and" ] ; [ " " ; "-" ] ; [ "." ; "-" ] ) 
      ) ;
  ) ; ["----" ; "-"] ; ["---" ; "-"] ; ["--" ; "-" ]

As you can see we’re doing a number of things to get the output we wanted (from the ‘inside’ out):

  • trimming all the whitespace from either end of the text
  • replacing all ‘special’ characters with their nearest ‘regular’ character using another custom function ReplaceAccents which is below
  • replacing ‘&’ with ‘and’, and both a space and a full-stop (period) with a hyphen
  • converting everything to lowercase
  • filtering out anything that might still be in there which we don’t want
  • doing some tidying up to remove multiple consecutive hyphens which can come about from dirty data, existing hyphens (e.g. {space}-{space} having been in the text) etc

All this means that if we had the text

François & Chloé visit the Musée du Louvre

We’d end up with


Which turns ‘risky’ text into something which all browsers, search engines, and users find more useful than ‘1472’!

The ReplaceAccents custom function also takes a single text parameter and looks like this.

[" ";" "];
[" ";" "];
[" ";" "];

As you can see it’s not only ‘accents’ which are being converted into low ASCII characters, but lots of other things which over time have turned up – usually pasted from word – in the content.

This function particularly feels like a ‘hack’, so we’d love to hear from you if you’ve solved this problem in another way!

Leave A Comment