Server Side Email using FileMaker Server Advanced
- Introduction
- Who should read this document
- FileMaker developers who would like to send email from FileMaker Server Advanced.
- FileMaker developers who would like to know how to create an XSLT template file.
Having a working knowledge of FileMaker Custom Web Publishing is not required but is a good prerequisite for working with FileMaker Server Advanced and XSLT. If you have never worked with FileMaker Custom Web Publishing before you may find parts of this paper difficult to understand. If you have not read the FileMaker® Server 8 Advanced Custom Web Publishing Guide, I suggest you do so before reading on. If you don't want to read it cover to cover, at least keep it handy so you can refer to it.
- Purpose of this document
- This document was created to give an overview of the process of setting up an XSLT template for the purpose of sending email from FileMaker Server Advanced.
- Sample Files
- XSL is extremely useful, but can be very confusing at first. If you are a new to XSL, you can download a sample file from (http://www.grahamsprague.com/server_side_email/server_side_email.zip) and modify it to suit your needs.
- Who should read this document
- The Problem
Many companies are using FileMaker Server Advanced to publish their FileMaker solutions to the Web. Often, it is desireable to access email functionality within these Web-enabled solutions. In most cases, it is best to manage email functionallity from a central location, such as the server. In fact, it is often impractical to accomplish in other ways.
For example, imagine that a user logs into your solution via IWP and marks an Invoice record as "Ready for Review". Your boss would like this action to send a notification email to the person assigned to review the Invoice. To accommodate this request, you might be tempted to utilize FileMaker's Send Mail script step. Unfortunately, that script step is not Web compatible and therefore not an option in this case. You could set up an elaborate work-around involving a drone FileMaker machine that periodically checks for records that need to be emailed out with the Send Mail script step, but this solution is fragile, inflexible, and difficult to administer.
- The Solution - Server Side Email
Server side email is the most robust, flexible, and extensible solution to this problem. Here are a few of the top reasons:
- Server side email can be configured to be sent from any address.
- Server side email is not dependent on an installed and configured email client.
- Server side email does not tie up the resources of a user's local machine.
- Once server side email is set up, it can be easily triggered by automated processes. Common automated triggering methods would include cron, or iCal on Mac OS X, or Scheduled Tasks on Windows.
For information on cron visit (http://www.unixgeeks.org/security/newbie/unix/cron-1.html)
For information on iCal visit (http://www.apple.com/macosx/features/ical/)
For information on Windows Sheduled Tasks visit (http://www.microsoft.com/technet/prodtechnol/windows2000serv/evaluate/featfunc/taskschd.mspx).
- Why use XSLT?
- FileMaker Database
- FileMaker Server Advanced
- XSLT Template
- Web Server
- FileMaker Database
- FileMaker Server Advanced
- FX.php
- PHP installed and configured
- PHP scripts
- Web Server
There are many ways to send server side email, however, solutions with the fewest "moving parts" tend to have the least problems. An XSLT template has almost no "moving parts". Here's what is required:
Compare that with the components required to implement my second favorite option using FX.php:
The XSLT configuration requires fewer components of the next best option, resulting in not just better reliability, but less time to install and configure. Troubleshooting and rebooting the XSLT configuration is also simpler and faster.
- Setup of FileMaker Database, Server and XSLT template file.
- Email Creation and Storage
Before any email can be sent there needs to be a place to store it. Create an email table in FileMaker that looks like Fig.1 below. Notice, in addition to the usual email fields there are fields named id, send, sent, error and errorcode. The id is an auto enter serial number, send will indicate that the email is ready and sent will be marked with a “1” once the email is sent. The error field will be marked with a "1" if the email fails to be sent and the errorcode field will contain the error code given for the failure. I’ll elaborate on this in a bit. The file used in the screen shots is included in the sample files (http://www.grahamsprague.com/server_side_email/server_side_email.zip).
Fig. 1
Now that you have a place to store email, you need a script that creates email messages. Create a script called "NewEmail ( to; from; subject; message; [cc]; [bcc] )". Here’s the code:
# Unpack the parameters. Set Variable [ $to; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 1 ) ) ] Set Variable [ $from; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 2 ) ) ] Set Variable [ $subject; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 3 ) ) ] Set Variable [ $message; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 4 ) ) ] Set Variable [ $cc; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 5 ) ) ] Set Variable [ $bcc; Value:Evaluate ( GetValue ( Get ( ScriptParameter ) ; 6 ) ) ] # Check for required parameters. If [ IsEmpty ( $to ) or IsEmpty ( $from ) or IsEmpty ( $subject ) or IsEmpty ( $message ) ] Show Custom Dialog [ Title: "Missing Parameter"; Message: "This script requires to, from, subject and message parameters. Please look at the Create Example Email script for an example of how to use it."; Buttons: “OK” ] Exit Script [ ] End If Go to Layout [ “Utility_Email” (Email) ] # Create a new email record and populate it's fields with the parameters. New Record/Request Set Field [ Email::to; $to ] Set Field [ Email::from; $from ] Set Field [ Email::subject; $subject ] Set Field [ Email::message; $message ] Set Field [ Email::cc; $cc ] Set Field [ Email::bcc; $bcc ] Set Field [ Email::send; 1 ] Commit Records/Requests [ No dialog ]
You can use this script anywhere in your solution either by invoking it with a button or by calling it from within another script. Here’s an example of this second option in a script called “Create Example Email.”
# Populate variables for parameters. Set Variable [ $to; Value:"<enter a to address>" ] Set Variable [ $from; Value:"<enter a from address>" ] Set Variable [ $subject; Value:"Subject Line" ] Set Variable [ $message; Value:"I am an email message!¶ I want to be sent using a server side XSLT template via FileMaker's Web Publishing Engine!" ] Set Variable [ $cc; Value:"" ] Set Variable [ $bcc; Value:"" ] # Call NewEmail script and pass in the parameters. Perform Script [ “NewEmail ( to; from; subject; message; [cc]; [bcc] )”; Parameter: Quote ( $to )& "¶" & Quote ( $from ) & "¶" & Quote ( $subject ) & "¶" & Quote ( $message ) & "¶" & Quote ( $cc ) & "¶" & Quote ( $bcc ) ]Now after running the “Create Example Email” script the table looks like Fig. 2. Notice you now have an email and it's ready to be sent.
Fig. 2
- Configuring the FileMaker Database for XSLT Publishing
You'll need to be able to use both XSLT Web Publishing and CWP Web Publishing, so be sure to create a privilege set that has the extended privileges for XML Web Publishing and XSLT Web Publishing enabled. Note that these extended privileges only work with FileMaker Server Advanced. They can't be used if the database is only being hosted by FileMaker Pro.
Fig. 3
- Configuring FileMaker Server Advanced for XSLT and SMTP
- Log in to your FileMaker Server Advanced Web Publishing Administration Console.
- Then click on the Publishing Engine tab.
- Click the XSLT Publishing link.
- Check to make sure that XSLT Publishing is set to On.
- Enter SMTP host, port, authentication type, user and password. Contact your network administrator or ISP if you are unsure of what settings to use.
Fig. 4
- Creating an XSLT Style Sheet to Send Email.
Now that FileMaker Server Advanced is configured to send email, you can create an XSLT style sheet to utilize it. Here’s the code for the style sheet. It's been broken up with commentary that describes each section of the code. If you want to see the style sheet in it's entirety, it's included on the last page of this document and in the sample files (http://www.grahamsprague.com/server_side_email/server_side_email.zip). As you read, keep this in mind: XSL can be confusing, but it is good for you (and it is not going away). You can do it. You are smart enough, you are good enough, and gosh darn it, people like you. Just keep at it and you will be rewarded.
The first few first lines are pretty standard and you shouldn't need to modify them.
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmrs="http://www.filemaker.com/xml/fmresultset" xmlns:fml="http://www.filemaker.com/fmpxmllayout" xmlns:fmq="http://www.filemaker.com/xml/query" xmlns:fmxslt="xalan://com.fmi.xslt.ExtensionFunctions">
This line stores the username, password and address of the current request. This will be needed to authenticate the user with the FileMaker database.
<xsl:param name="authenticated-xml-base-uri"/>
This line stores the query used to call this style sheet.
<xsl:param name="request-query"/>
This line specifies encoding and output method. The output could be in a format such as XML, but this specifies that the data will be output as html so that we can see the results in a web browser. Using UTF-8 encoding will give you the widest character support in most modern web browsers. For additional information on encoding visit (http://www.w3schools.com/xml/xml_encoding.asp).
<xsl:output encoding="UTF-8" indent="yes" method="html"/>
This line is the true beginning of your template. It allows you to specify a starting point in the xml data that is returned from FileMaker. In this case, you are starting at the root level, indicated by the “/”.
<xsl:template match="/">
In order to send multiple emails per query, the template must execute once for each record. The <xsl:for-each> and </xsl:for-each> make this possible. The code contained within these tags will be executed once for each record found in the XML returned from FileMaker. You can think of it as the FileMaker equivalent of the Loop and End Loop script steps, respectively. Each time through, the current record is advanced forward by one record. The fmrs:record refers to the current record. This tag will be closed at the end of the repeating portion using the </xsl:for-each> tag.
<xsl:for-each select="/fmrs:fmresultset/fmrs:resultset/fmrs:record">
In order to access each record, the template places the contents of the FileMaker record into a variable named record. As the style sheet loops, current node is changed to the next fmrs:record in the resultset. The current fmrs:record node can then be accessed using the relative path "." .
<xsl:variable name="record" select="."/>
Using the $record variable, the template accesses the record-id and places it in a variable called recid. This refers to FileMaker’s internal record id, not a user-defined auto enter id field. The record-id will be needed later to uniquely identify the database record in order to mark it as sent.
<xsl:variable name="recid" select="$record/@record-id"/>
The next section shows how the data from the fields in the database record are accessed. Notice that the 'select' parameter uses the $record variable and the fmrs:field node by name to get the data from the FileMaker database.
<xsl:variable name="id" select="$record/fmrs:field[@name = 'id']/fmrs:data[1]"/> <xsl:variable name="to" select="$record/fmrs:field[@name = 'to']/fmrs:data[1]"/> <xsl:variable name="from" select="$record/fmrs:field[@name = 'from']/fmrs:data[1]"/> <xsl:variable name="subject" select="$record/fmrs:field[@name = 'subject']/fmrs:data[1]"/> <xsl:variable name="message" select="$record/fmrs:field[@name = 'message']/fmrs:data[1]"/> <xsl:variable name="cc" select="$record/fmrs:field[@name = 'cc']/fmrs:data[1]"/> <xsl:variable name="bcc" select="$record/fmrs:field[@name = 'bcc']/fmrs:data[1]"/>
Finally, the collected data is used to send an email and place the result in a variable called emailreturn. The concat() function combines the send_email() function's parameters with the data gathered from the FileMaker record to create a complete email message.
<xsl:variable name="emailreturn" select="fmxslt:send_email( concat($to,'?from=',$from,'&subject=',$subject,'&cc=',$cc,'&bcc=',$bcc),$message)"/>
This line is very important. It captures the current error state for FileMakers XSLT engine and places it in a variable called emailerror. The fmxslt:check_error_status() function is similar to the FileMaker Get ( LastError ) function. It returns the error code of the last function called. In this case we are getting the error code returned from the send_email() function.
<xsl:variable name="emailerror" select="fmxslt:check_error_status()"/>
Using the $emailreturn variable, check to see if the result was true to determine if the email was sent or if an error was reported. This is where we update the FileMaker Record to sent or report any error that are reported while attempting to do so.
Editing FileMaker records and error checking are not the focus of this article, so I'm not going to extensively comment this portion. I did place some comments in the code below and they are included in the sample file as well. For more details on error checking consult FileMaker® Server 8 Advanced Custom Web Publishing Guide.
<xsl:choose> <!-- Check to see if send_email was successfull --> <xsl:when test="$emailreturn = true()"> <!-- Output to browser --> <div> Email ID <xsl:value-of select="$id"/> to <xsl:value-of select="$to"/> sent successfully. </div> <!-- Edit the FileMaker record to show that it was sent. --> <xsl:variable name="editreturn" select="document(concat( $authenticated-xml-base-uri,'/fmi/xml/fmresultset.xml?-db=Email&-lay=Utility_Email&-recid=', $recid,'&send=&sent=1&-edit'))"/> <xsl:choose> <!-- Check to make sure the edit did not return an error --> <xsl:when test="$editreturn/fmrs:fmresultset/fmrs:error/@code != 0"> <!-- Edit failed Output to the browser --> <div> <strong> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record fialed due to an error of type <xsl:value-of select="$editreturn/fmrs:fmresultset/fmrs:error/@code"/>. </strong> </div> <br /> </xsl:when> <xsl:otherwise> <!-- Edit succeeded Output to the browser --> <div> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record successful.</div> <br /> </xsl:otherwise> </xsl:choose> </xsl:when> <xsl:otherwise> <!-- Send Email failed Output to the browser --> <div> <strong>Email ID <xsl:value-of select="$id"/> to <xsl:value-of select="$to"/> failed to be sent due to an error of type <xsl:value-of select="$emailerror"/>. </strong> </div> <!-- Edit the FileMaker record to show that it was not sent and include the error number --> <xsl:variable name="editreturn2" select="document(concat( $authenticated-xml-base-uri,'/fmi/xml/fmresultset.xml?-db=Email&-lay=Utility_Email&-recid=', $recid,'&send=&sent=&error=1&errorcode=',$emailerror,'&-edit'))"/> <xsl:choose> <!-- Check to make sure the edit did not return an error --> <xsl:when test="$editreturn2/fmrs:fmresultset/fmrs:error/@code != 0"> <!-- Edit failed Output to the browser --> <div> <strong> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record failed due to an error of type <xsl:value-of select="$editreturn2/fmrs:fmresultset/fmrs:error/@code"/>. </strong> </div> <br /> </xsl:when> <xsl:otherwise> <!-- Edit succeeded Output to the browser --> <div> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record successful.</div> <br /> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose>
This line closes up the record loop. This represents the end of the repeating portion of the template.
</xsl:for-each>
The last lines close up the remaining template and stylesheet tags.
</xsl:template> </xsl:stylesheet>
- Installing an XSLT template file into FileMaker Server Advanced.
Now you need to give the style sheet a filename. A good choice might be “send_email.xsl”. In order for the Server to be able to use the template, the template needs to be placed in the xslt-template-files folder, which is located inside the Web Publishing folder in your FileMaker Server. See Chapter 5 of the FileMaker® Server 8 Advanced Custom Web Publishing Guide for additional information.
- Invoking the XSLT Style Sheet using a web browser.
Now that the template is installed, you can use any web browser to access the style sheet and cause email to be sent. Normally, the template would be triggered by a FileMaker script or a scheduled action, but a browser is a great way to test the template and see what is going on.
To access the XSLT Template use the following. Notice that the url includes the name of the style sheet followed by a query. The query contains the database name, layout name, find criteria for the sent and send fields, as well as a -max parameter and the –find command.
http://<user>:<pass>@<fmsaserveraddress>/fmi/xsl/send_email.xsl ?-grammar=fmresultset&-db=Email&-lay=Utility_Email&sent=&send=1&-max=50&-find
If all goes well the output returned to the browser window will look something like Fig. 5.
e
Fig. 5
Checking the record in the FileMaker database shows that it was marked sent. See Fig. 6.
Fig. 6
- Email Creation and Storage
- Conclusion
Server side email is desirable because it's centrally configured and administered. It doesn't tie up resources of a user's local machine and can be triggered by automated processes. It's also flexible, extensible, and reusable. Using XSL to implement server side email is a great choice because it has fewer moving parts and it's based on industry standard technology. The learning curve is a little steep at first, but it is worth it. So head over to The Moyer Group website, download the sample files (http://www.grahamsprague.com/server_side_email/server_side_email.zip), put on your XSL boots and get started.
- Complete Code of the send_email.xsl style sheet for reference.
- About the Author
Graham Sprague has been a FileMaker Developer for 9 years and is FileMaker 8 Certified.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fmrs="http://www.filemaker.com/xml/fmresultset"
xmlns:fml="http://www.filemaker.com/fmpxmllayout"
xmlns:fmq="http://www.filemaker.com/xml/query"
xmlns:fmxslt="xalan://com.fmi.xslt.ExtensionFunctions">
<!--Get paramerters from the URL used to access the template-->
<xsl:param name="authenticated-xml-base-uri"/>
<xsl:param name="request-query"/>
<xsl:output encoding="UTF-8" indent="yes" method="html"/>
<!-- Begin Template-->
<xsl:template match="/">
<!-- Start of repeating portion, executes once for each record in succession-->
<xsl:for-each select="/fmrs:fmresultset/fmrs:resultset/fmrs:record">
<!-- Get the current FileMaker record and place it in a variable named record -->
<xsl:variable name="record" select="."/>
<!-- Get FileMakers internal record id for the current record -->
<xsl:variable name="recid" select="$record/@record-id"/>
<!-- Get data from the Filemaker fields for the current record -->
<xsl:variable name="id" select="$record/fmrs:field[@name = 'id']/fmrs:data[1]"/>
<xsl:variable name="to" select="$record/fmrs:field[@name = 'to']/fmrs:data[1]"/>
<xsl:variable name="from" select="$record/fmrs:field[@name = 'from']/fmrs:data[1]"/>
<xsl:variable name="subject" select="$record/fmrs:field[@name = 'subject']/fmrs:data[1]"/>
<xsl:variable name="message" select="$record/fmrs:field[@name = 'message']/fmrs:data[1]"/>
<xsl:variable name="cc" select="$record/fmrs:field[@name = 'cc']/fmrs:data[1]"/>
<xsl:variable name="bcc" select="$record/fmrs:field[@name = 'bcc']/fmrs:data[1]"/>
<!-- Send Email using data from the current record. -->
<xsl:variable name="emailreturn" select="fmxslt:send_email(concat(
$to,'?from=',$from,'&subject=',$subject,'&cc=',$cc,'&bcc=',$bcc),$message)"/>
<xsl:variable name="emailerror" select="fmxslt:check_error_status()"/>
<xsl:choose>
<!-- Check to see if send_email was successfull -->
<xsl:when test="$emailreturn = true()">
<!-- Output to browser -->
<div>
Email ID <xsl:value-of select="$id"/> to
<xsl:value-of select="$to"/> sent successfully.
</div>
<!-- Edit the FileMaker record to show that it was sent. -->
<xsl:variable name="editreturn" select="document(concat(
$authenticated-xml-base-uri,'/fmi/xml/fmresultset.xml?-db=Email&-lay=Utility_Email&-recid=',
$recid,'&send=&sent=1&-edit'))"/>
<xsl:choose>
<!-- Check to make sure the edit did not return an error -->
<xsl:when test="$editreturn/fmrs:fmresultset/fmrs:error/@code != 0">
<!-- Edit failed Output to the browser -->
<div>
<strong>
Email ID <xsl:value-of select="$id"/>
Edit of FileMaker record fialed due to an error of type
<xsl:value-of select="$editreturn/fmrs:fmresultset/fmrs:error/@code"/>.
</strong>
</div>
<br />
</xsl:when>
<xsl:otherwise>
<!-- Edit succeeded Output to the browser -->
<div> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record successful.</div>
<br />
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>
<!-- Send Email failed Output to the browser -->
<div>
<strong>Email ID
<xsl:value-of select="$id"/> to
<xsl:value-of select="$to"/> failed to be sent due to an error of type
<xsl:value-of select="$emailerror"/>.
</strong>
</div>
<!-- Edit the FileMaker record to show that it was not sent and include the error number -->
<xsl:variable name="editreturn2" select="document(concat(
$authenticated-xml-base-uri,'/fmi/xml/fmresultset.xml?-db=Email&-lay=Utility_Email&-recid=',
$recid,'&send=&sent=&error=1&errorcode=',$emailerror,'&-edit'))"/>
<xsl:choose>
<!-- Check to make sure the edit did not return an error -->
<xsl:when test="$editreturn2/fmrs:fmresultset/fmrs:error/@code != 0">
<!-- Edit failed Output to the browser -->
<div>
<strong>
Email ID <xsl:value-of select="$id"/>
Edit of FileMaker record failed due to an error of type
<xsl:value-of select="$editreturn2/fmrs:fmresultset/fmrs:error/@code"/>.
</strong>
</div>
<br />
</xsl:when>
<xsl:otherwise>
<!-- Edit succeeded Output to the browser -->
<div> Email ID <xsl:value-of select="$id"/> Edit of FileMaker record successful.</div>
<br />
</xsl:otherwise>
</xsl:choose>
</xsl:otherwise>
</xsl:choose>
<!-- End of repeating portion-->
</xsl:for-each>
<!-- End of template-->
</xsl:template>
<!-- End of stylesheet-->
</xsl:stylesheet>