Stored Calculations and FileMaker Plug-ins

FileMaker Server does some funny things when you create a stored calculation that uses a plug-in. In fact, odds are you received everyone’s favorite invalid character, the `?`. This article explains why this occurs and gives some solutions for this rather pesky problem.

Umm… I Think Something’s Broken

Let’s say you just bought our new barcode plug-in (shameless plug I know… but I needed an example) and you are just chomping at the bit to go home and make a bunch of barcodes to help organize your vintage Transformer collection. So you fire up your computer, log on to your FileMaker Server, open up the Transformer database and define a new field, aptly named `Barcode`. You set the field up as a stored calculation that evaluates the plug-in function `sfrbc_barcode( Transformer::ID )`, then sit back and relax while your computer chugs along and processes your 294,131 records. When it’s all finished you throw your new `Barcode` field onto the layout and expecting to see your lovely new barcodes but instead you see the dreaded `?`. The first thing you do now is probably call us up and yell at us for providing you with a piece of crap plug-in that doesn’t work. As much as we wish we could make this work auto-magically… alas, we cannot. And here’s why.

The Explanation

Typically, when you create a record with a stored calculation the calculation result is evaluated on your, the client, machine. This however is not the case when you are defining fields. During field definition, FileMaker Server actually handles all the heavy lifting and creates those barcodes for the 294,131 original works of art you have hiding in show boxes. Unfortunately, since you probably don’t have the plug-in installed on FileMaker Server this is a problem.

Using a Replace

The quick and dirty solution to this problem is to use a replace. I know… replaces don’t work on calculations, but we aren’t going to use a replace in the calculation field itself. We are going to replace the field it *depends* on. In our example from above, we defined the `Barcode` field as `sfrbc_barcode( Transformer::ID )`. In this calculation, the *dependent field* is `Transformer::ID`, so we are going to replace that field with itself. To accomplish this just follow these easy steps

  1. Select Records -> Show All Records to ensure that you update all your records
  2. Click in the `Transformer::ID` field.
  3. Open the Records -> Replace Field Contents dialog box
  4. Click on `Replace with calculated result:` like so:

    5. Replace Dialog

  5. In the Specify Calculation box that pops up, select the name of the field you would like to use in the replace, in our case Transformer::ID
  6. Click `OK` then Click `Replace`

You should now be ripping through your records and replacing `Transformer::ID` with itself.

>Note: Since your are replacing a field with itself, you aren’t blowing away any of your data or messing up any of your relationships. You are simply re-triggering the calculation in the `Barcode` field. You *will* change any Modification times, dates, or timestamps in your record, though.

Once this process is finished, you will notice that the barcodes that you paid your hard earned money for are now present. The whole premise behind this solution is that when you use the replace command, stored calculations all evaluate on the client, your machine where the plug-in is installed, rather then on FileMaker Server. This allows you to get away without having to actually install the plug-in on your FileMaker Server, but quite frankly it isn’t quite ideal.

Install/Enable Plug-in on FileMaker Server 9

This is definitely the preferred solution. You’ll never have to worry about doing any hokey replaces and you’ll be able to leverage the greater (unless your sever is pokey) processing power of your FileMaker Server machine. All we have to do is install the plug-in on the server, then enable FileMaker Server 9 to use the plug-in.

>Note: This approach works equally well on FileMaker Server 7+. This particular article is written with FileMaker Server 9 in mind, because — quite frankly — the interface is now the same on a mac and pc so it’s easier to write up. You can achieve the same results with FileMaker Server 7+ we just don’t have the exact steps handy.

Installing a plug-in on FileMaker Server 9

Installing the plug-in is actually quite easy. All you have to do is take a copy of your plug-in and place it in the appropriate folder on your server.

For a Mac:

/Library/FileMaker Server/Database Server/Extensions

For a PC

C:/Program Files/FileMaker/FileMaker Server/Database Server/Extensions

You’ve now successfully installed your first plug-in on FileMaker Server. Now, all we need to do is make sure that FileMaker Server knows that it can use the plug-in, and for that we need the trusty old FileMaker Server 9 web interface.

>Note: The Mac folder path is pretty much always the same. The PC directory can change depending on where you actually installed FileMaker Server but the one I provided is the default install. If you did your own custom install on a PC, just find the `/FileMaker Server/Database Server/Extension` folder and drag a copy of your plug-in into it.

Enabling a plug-in on FileMaker Server 9

Now that the plug-in is installed we just have to tell FileMaker Server 9 that it is OK to use the plug-in. First things first, we have to get FileMaker Server to recognize the plug-in and to do that you have to shut down the server and start it back up.

Start Stop Server Graphic

1. Fire up your admin interface and you should see these two buttons above in the upper left hand corner of your screen

1. Hit the Red button to stop server

1. Type a message to send to all connected clients and hit OK

1. Once all the files have closed, hit the Green button to start the server back up

With the server rebooted, we will be able to enable the plug-in. Select Configuration -> Database Server in left side of your admin interface like so:

Database Utilities

A new set of tabs will appear that look something like this:

Database Server Config

Just click the `Server Plug-Ins` tab and you’ll see a list of the plug-ins installed on your Filemaker Server. Check the `Enable FileMaker Server to use plug-ins` box and check the plug-in(s) which you would like FileMaker Server to use and you’re finished. FileMaker Server 9 will now be able to create as many stored calculations using your plug-in as you want. Now get back to barcoding your Decepticons.

Leave a Comment

13 thoughts on “Stored Calculations and FileMaker Plug-ins”

  1. So you have to have the plug-in installed and enabled on both server and client in order for it work in a served environment? You have to have it installed on the server to perform the calculation and installed on the client machine for the display? Is that correct? Thanks!

    (and thanks for the blog by the way!)

  2. @Dwayne – Good question…
    You only really need to have the plug-in on the server if you want to define a stored calculations on a served file that already has records in it. The server will use that plug-in to initialize all those stored calcs to the correct value. Once you finish defining your calc you actually won’t ever use that server plug-in again (unless you define another calc of course).

    Every other time you execute a plug-in call it actually occurs on the Client (so you need to have installed on the client)

    If you want to get around all the hassle of installing the plug-in on the server, just take the file off the server and define the field on a local computer where the plug-in is installed. Then you can just throw your file back on the server and you’re all set.

    Hopefully, that cleared things up a little bit.

  3. what if you use a plugin that requires a registration text file, like say Events, is it enough that the client has the plugin registered (and installed)?
    (just found your Blog BTW, its fantastic!)

  4. @Jim – Welcome! Glad you like the blog. I’m not really sure I get your question though. If you’re plug-in is registered through the use of a text file or a serial number this problem with stored calculations will affect you. You would have to use the techniques described above to create your stored calc. I hope that helps. If not, just reply again and I will see if I can make any clarifications

  5. @Jim: Thanks for the kind words 🙂 You’ll have to check with your particular plug-in vendor to find out about server compatibility. In the case of Events (from Worqsmart/Waves in Motion), I don’t think this problem applies because it isn’t used to calculate legitimate stored text values. You might use a stored calculation with Events in some cases, but you don’t actually care about storing the result in the database, so it is fine to add a new field for this purpose. Hope that makes sense.

  7. Hi, I was wondering what do you mean by plugin compatible with FM Server. Am i right that if a plugin works with FM9 pro advanced, it may not work in FM9 Server??
    I have a plugin which has fmx file as plugin file and a dll as support file for it. I want to put them in server and copy it to client, as the client access those database. What will be the quick idea on how to for me in this situation. The plugin is to interact with Microsoft office documents so need to operate in each of client environment.
    Many Thanks, Deepak.

  8. @deepak: Most any plug-in will work with a database hosted on FileMaker server. But the plug-in needs to be installed on each client computer that uses it. Some plug-ins, though, can be installed directly on the server, and will operate in meaningful ways for all connected clients. In some cases (as this article explains) calculations happen on the server, where a server-installed plug-in is needed. In other cases, they happen on the client where a client-installed plug-in is needed.

    In most cases though, plug-ins are used with scripting, and scripts *always* run on the client side.

    Hope that clears it up.

  9. Love you website (both content and design). I am curious how UNstored calculations that use plug-ins work in a hosted environment. We have a database of all of our customers (~3000 records). A great deal of our business is repeat customers, but we often end up with duplicates resulting from spelling errors (i.e. ‘Crump’ and ‘Krump’ or ‘Fleming’ and ‘Flemming’). I created a simple plug-in that utilizes Levenshtein Distance (http://en.wikipedia.org/wiki/Levenshtein_distance) to find names with similar spellings. Each customer record has a Match_Field: an unstored calculation that compares the name against a Global_Search_Field using the Levenshtein plug-in. When a customer calls to make a purchase, the end-user enters the customer name in Global_Search_Field and then triggers a script that finds/sorts based on Match_Field. If I enable this plug-in on our server, will the server make the calculations for this find/sort, or will it occur on the client?

  10. @Jason: Unstored calculations are *always* evaluated on the client side, so far as I know. So you will need to put the plug-in on the client machines. You can see this article for help getting plug-ins onto every client.

  11. Hi, do you know if there is any way to force FM to evaluate a plugin function on the server rather than the client during a script? Cheers

  12. Simon:

    I’ve never tried this, but it should work:

    • Create a new table as a helper.
    • Add fields to this table to represent the “parameters” to your calculation.
    • Add a stored calculation to this table with your calculation formula
    • In your script, make a new record in this table, set the parameter fields, fetch the calculated field value to a variable, then delete the record.

    In this way, you should be able to force the actual calculation to happen on the server side. I can’t guarantee that is what will happen all the time, so you should definitely test various scenarios.

    Also, one caveat. If the calculation result is a container value (like a picture, file, or sound) then you’ll run in to trouble if you delete the helper record before you use the variable. You’ll want to copy the data from the helper record, store it in some field somewhere (or use it, export it, or whatever you need) and then delete the helper record. This is because when you put container data into a variable, FileMaker holds only a reference to the field value in the variable. If the original field goes away, the variable won’t work.

    Let me know how it works out.

    Geoff