Importing complex Feedback-Forms into FileMaker

From Provider Wiki

Jump to: navigation, search

Contents

Introduction

The University has a simple system in place to create simple web forms that can either email the results of a html form or write it to a log file. Normally this log file is a simple one-line one-entry sort of affair, but that does not work when you want the end-user to enter free text that can include new-lines, or lots of data. The Feedback-Form software can deal with this well, creating a html-ish output that can deal with this sort of multi-line data. However this will not be importable into FileMaker Pro without a little creativity.

This article will go into a method of bringing this data into FileMaker with the newlines intact. To do so we will use a four step process:

  1. Create the Feedback-Form configuration file in a format we can use
  2. Create a small perl cgi script (other languages would work fine) that makes a couple of minor modifications to this file
  3. Create a XSLT file that FileMaker will use to make the input look the way it wants it to for import
  4. Create a script in the FileMaker database that will do the import

Note: I am not dealing with any sort of cross-site or other security situations in this article. There is nothing in this article that is venerable to those sorts of issues, but you should always be conscious of them when taking input from the web.

The Feedback-Form configuration file

Note: the official page for this file is at: http://www.upenn.edu/computing/web/webdev/form/config-options.html

The main aim we should have in creating this file is that the output will look like XML, almost. There will be a few problems with it, but those we will solve in the script from the next step.

In this how-to I am assuming that you already have the html side of creating the form done, so that it is "POST"ing the data to the Feedback-Form that is using this configuration file. If that does not make sense to you, then you need to go back to the Feedback-Form documentation first. The important settings in this file are:

  • A "Log:" line. This is where the information that users fill in is going to be written. I am going to write all the responses to a single file, and this is by-far the easiest way to go. This file will need to be readable by the perl script from the next section. That can either be by direct file access, or by http access. Note that you might need to limit the access for privacy reasons, but that we will be going through a cgi, so the same considerations apply there. We also need to use the ".html" file ending, so that the "<HTML>" will get appended to the beginning of the file.
  • The "message" lines that will be filled in.

Sample configuration file

This is a fairly simple sample configuration file that you could use (note this has not been checked for typos). It assumes that there is a html form POSTing it data that includes fields named "Name" and "FavoriteColor", and that it can write to the directory output: 

Mandatory:
Name|Your name
FavoriteColor|Your favorite color
END.
Log: output/sampleOutput.html
<entry>
    <name>$FName</name>
    <favoriteColor>$FFavoriteColor</favoriteColor>
</entry>
END.

Notes:

  1. My choice of names is completely arbitrary as is my capitalization. You have to be consistant though. The parts preceded by $F have to match the output from the html form, and the tag-names ("entry", "name", and "favoriteColor") have to match the XSLT exactly
  2. I have chosen to use non-html tags here. You could also make it line up in a table, and look nice in a browser. It really does not matter, so long as you make it line up. My thinking is that this is easier to look at (the raw text output).

This should produce an output file that looks like this: 

<HTML>
<TITLE>Feedback log</TITLE>
	<entry>
		<name>The first name entry</name>
		<favoriteColor>The first favorite color entry</favoriteColor>
	</entry>
	<entry>
		<name>The second name entry</name>
		<favoriteColor>The second favorite color entry</favoriteColor>
	</entry>

Note that there is no "</HTML>" tag. That is important.

The Perl script

Up to this point we have started logging the responses from the users into a file, and that file is mostly in the format that we want, however we are going to be feeing the file into a XML workflow, and those are incredibly picky. So we have to do a couple of things to cater to this pickiness, and we will do that in a Perl script (other languages are very useable, but our example will be in Perl).

Since we want to be able to import data at any time, rather than waiting until people have stopped adding new responses, we can't just write </HTML> at the end of each entry, so we will slide it into the stream going to FileMaker without writing it to the log file.

This script fixes the following problems:

  1. It adds the </HTML> at the end of the file
  2. It converts the poorly formated '&amp' character codes that InternetExplorer sends to the official '&' versions
  3. It converts "smart quotes" into straight quotes (this is a character encoding issue)
  4. It converts the ñ character to a simple n

You could run into other issues (primarily characters that are not correctly encoded), but these were the ones that we ran into. If you are working with users that are likely to use non-US characters a lot I would encourage going through the web form and making sure that you specify your character encoding to be Unicode.

Here is the sample perl script. Please note that there are three different sections for bringing in the file, and it depends on how you want to read it in. Please use the appropriate one for your instance: 

#!/usr/bin/perl

# use this if the CGI lives on the same server as the log file
#open(DATAFILE, "/path/to/logfile.html");
#$allData = join("", <DATAFILE>);
#close (DATAFILE);

# use this if you are on a *unix box
$allData = `/usr/bin/curl 'http://www.server.upenn.edu/path/to/logfile.html'`;

# use this if you are using Windows
#use Win32::Internet;
#$INET = new Win32::Internet();
#$allData = $INET->FetchURL("http://www.server.upenn.edu/path/to/logfile.html");

# these change the bad thml character encoding the IE sends
$allData =~ s/&amp /&/g;
$allData =~ s/&gt />/g;
$allData =~ s/&lt /</g;

# these convert the smart quotes to straight ones
$allData =~ s/\x92/\'/g;
$allData =~ s/\x93/\"/g;
$allData =~ s/\x94/\"/g;

$allData =~ s/\xf1/n/g;

print "Content-Type: text/html\n\n";
print $allData . "\n</HTML>";

You will need to make sure that this is executable as a CGI, and set the permissions (and probably a .htaccess file) so that the appropriate people can get access. The way things work this has to be running on a web server to provide FileMaker with a proper feed for the next step.

The XSLT file

At this point we have a file that is called a "html" file, but conforms to most of the rules of XML. We are now going to take advantage of that and have FileMaker use an XSLT file to convert the output to the form of XML that it likes to import. There are some things that FileMaker is very particular about, and it is a little bit of an annoying format, but if you start with this template and modify it to your case, you should be fine. 

<?xml version='1.0' encoding='utf-8'?>
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>

<xsl:output method='xml' version='1.0' encoding='utf-8' indent='yes'/>

<xsl:template match="/">
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
	<ERRORCODE>0</ERRORCODE> 
	<PRODUCT BUILD="5/23/2002" NAME="FileMaker Pro" VERSION="8.0"/>
	<DATABASE DATEFORMAT="MM/dd/yy" LAYOUT="whatever" NAME="whatever.fp7" RECORDS="0" TIMEFORMAT="hh:mm:ss"/>

	<METADATA>
		<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="name" TYPE="TEXT"/>
		<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="favoriteColor" TYPE="TEXT"/>
	</METADATA>

	<RESULTSET>
	<xsl:attribute name="FOUND"><xsl:number value="count(HTML/entry)" format="1"/></xsl:attribute>
	<xsl:for-each select="HTML/entry">
		<ROW>
		<xsl:attribute name="RECORDID">
			<xsl:number value="position()" format="1"/>
		</xsl:attribute>
		<xsl:attribute name="MODID">0</xsl:attribute>
			<COL><DATA><xsl:value-of select="name"/></DATA></COL>
			<COL><DATA><xsl:value-of select="favoriteColor"/></DATA></COL>
		</ROW>
	</xsl:for-each>
	</RESULTSET>
</FMPXMLRESULT>
</xsl:template>
</xsl:stylesheet>

This is a little bit to digest, so here are a few notes:

  1. The "<ERRORCODE>" line should always have 0 in it.
  2. In the "<DATABASE>" line the name of the database is unimportant, but it has to be there.
  3. In the "<METADATA>" section you should have one line for each column of data you are producing. The names used here will be the ones that appear in the import dialog in FileMaker, so it would be a good idea to try and line these up against the names you use for the fields in FileMaker.
  4. In the '<xsl:attribute name="FOUND">' line we are giving a count of the lines we are importing. I don't think that FileMaker actually looks for this, but it is easy to provide. Note the "count(HTML/entry)" where the names line up with the XML that we are producing.
  5. Inside the '<xsl:for-each select="HTML/entry">' is where we are producing our lines of output.
  6. The '<COL><DATA><xsl:value-of select="name"/></DATA></COL>' and the corresponding line for "favoriteColor". This is where the data is actually written.

At then end of this you will have to decide where the XSLT file is going to live, and give it a name. I would advise using the standard ending on the name, ".xslt" as FileMaker is a bit happier with that, but it is not required. As to where the file will live, you have two choices: you can make the file live on your computer, and reference it that way (either relative to the database, or an absolute path), or you can make it live on the web server, possibly next to the log file. I would advise the latter, as then you are not limited to only using this import routine on one computer.

Importing in to FileMaker

With all of this done, we are now ready to actually do (and script) the importing of the data into the FileMaker Pro database that you already have ready. I would advise doing this via a script, as it is easier to then make changes during development, and you then don't have to write down a complicated setup routine when you go to do the import.

The script can be a trivial, one-line script that just calls the "Import Records" script step, but I usually like to make sure that the table I am importing to is the correct one, and that all of the records are showing. But the actual import is a bit of a anti-climax after all this XML handling. The process is:

  1. Add your "Import Records" script step.
  2. Click on "Specify data source" and choose "XML Data..."
  3. This should pop up a window asking you to specify the source of the data. You will choose "HTTP request" and enter in the http path to the CGI script we created. This must start with "http://"
  4. Click on the Checkbox to "Use Style Sheet"
  5. The style sheet (xslt file) is the one that we created before. This one you can point either to the file you are using locally (if you are the only one using it, and only from this computer), or via http (generally better, as then it can be used from any computer).
  6. Click "OK" to dismiss the "Specify XML and XSL Options" dialog
  7. Check the "Specify import order" box, and in the resulting dialog line up your import fields as you would normally.

And after all that, we are done!

Troubleshooting Problems

I have seen a few problems with this system, so you might want to be aware of them:

  • There seems to be a file-size limit to how long the log file can get. Once it reaches that limit you need to split it into multiple files. I don't have an exact number, but it was quite long.
  • If you run into FileMaker complaining about an unexpected end of a tag, you might be running into a character encoding problem. You should look for any "high-ASCI" characters (anything not normally in English). You can usually take care of them in the CGI file (and I have you started out with what I ran into already), but finding the character that is causing the problem can be a real bear of a problem. What I did was to make a copy of the script/database and the log-file, and then use a halving solution (remove half, test it, if the problem goes away it was in the other half, take half of the problematic section, test it, repeat). Annoying, but it worked.
  • Make sure that the CGI is working. If permissions are not set right, or you can't access it because of access restrictions, then it will give bad error messages.
Retrieved from "http://prowiki.isc.upenn.edu/wiki/Importing_complex_Feedback-Forms_into_FileMaker"
Personal tools