The FileMaker to JSON Converter

All the commands available in FileMaker's Custom Web Publishing with XML are available. The only difference is that data is returned as JSON instead of XML.

Installation

To use this file, you must have a FileMaker Web Publishing engine installed and configured. If you do not, refer to the documentation that came with your FileMaker Server Advanced and get that set up first.
Installation of this file is a breeze: Just copy the file called "json.xsl" to the "xslt-template-files" folder on your Web Publishing Engine computer. Again, instructions for finding this folder are in your FMSA documentation.

Usage

Since this is an XSLT file, you must prefix your URLs like this:

http://myserver/fmi/xsl/json.xsl

Then add a query string to tell FileMaker what you want. For example:

http://myserver/fmi/xsl/json.xsl?-db=My+Database&-lay=My+Layout&-findany

The above command will instruct FileMaker to return a random record from the table associated with My Layout in My Database. Since this is now just FileMaker's XSLT Web Publishing, you can use all the commands FileMaker makes available:

- -findall
- -findany
- -find
- -edit
- -add
- -delete
- -script

and so forth. Of course access depends on your FileMaker privileges. You must have the `fmxslt` extended privilege enabled for the account you log in with. All of this is explained in the FMSA documentation.

Understanding the Response

When you send in a request using the `json.xsl` template, the response comes back as a single object. It contains a series of arrays and hashes containing all the data from the typical FileMaker XML response. Here is a sample JSON string that might come from a `-find` request:

{
'errorCode':'0',
'datasource':
{
'database':'My Database',
'dateFormat':'MM/dd/yyyy',
'layout':'My Layout',
'table':'People',
'timeFormat':'HH:mm:ss',
'timestampFormat':'MM/dd/yyyy HH:mm:ss',
'totalCount':'3'
},
'fieldDefinitions':
[
{
'name':'First Name',
'autoEnter':false,
'global':false,
'maxRepeat':'1',
'notEmpty':false,
'result':'text',
'type':'normal'
},{
'name':'Last Name',
'autoEnter':false,
'global':false,
'maxRepeat':'1',
'notEmpty':false,
'result':'text',
'type':'normal'
},{
'name':'Email Address',
'autoEnter':false,
'global':false,
'maxRepeat':'1',
'notEmpty':false,
'result':'text',
'type':'normal'
}],
'resultset':
{
'count':'3',
'fetchSize':'3',
'records':
[
{
'modId':'3',
'recordId':'1',
'fields':
{
'First Name':['Mamie'],
'Last Name':['Coffey'],
'Email Address':['mamie@sixfriedrice.com']
}
},{
'modId':'1',
'recordId':'2',
'fields':
{
'First Name':['Isabel'],
'Last Name':['Coffey'],
'Email Address':['isabel@sixfriedrice.com']
}
},{
'modId':'11',
'recordId':'3',
'fields':
{
'First Name':['Sophia'],
'Last Name':['Coffey'],
'Email Address':['sohpia@sixfriedrice.com']
}
}
]
}
}

Each component of the response is documented below. Note that every piece of information and almost every property name comes straight from the `fmresultset` XML grammar (with hyphenated-words converted to interCaps), so FileMaker's documentation applies here as well.
All examples are in Javascript but of course you can use any language that supports JSON.

The Response Object

The response object itself has the following properties:

`errorCode`: the FileMaker error code if an error occurred, otherwise 0 (zero)
- `datasource`: information about the database the response is coming from
- `fieldDefinitions`: an array of `field` objects describing the field metadata
- `resultset`: the actual data returned

Example:

var response = // the JSON response
var error_code = response.errorCode;
var field_count = response.fieldDefinitions.length;

The Datasource Object

The datasource object (`response.dataSource`) tells you where the data is coming from and how it is formatted. It has the following properties:

- `database`: the name of the database
- `dateFormat`: tells you how dates are represented, ie: `MM/dd/yyyy`
- `layout`: the name of the layout the data comes from
- `table`: the name of the table _occurrence_ the data comes from
- `timeFormat`: tells you how times are represented, ie: `HH:mm:ss`
- `timestampFormat`: tells you how timestamps are represented, ie: ``
- `totalCount`: the total number of records that resulted from the query; note that this number may be larger than the number of records in this response, if you has a -max in your request

Note: for clarification on any of these values, see the FileMaker Custom Web Publishing documentation.

Example:

var response = // the JSON response
var database_name = response.datasource.database;
var layout_name = response.datasource.layout

The FieldDefinitions Array

The fieldDefinitions array (`response.fieldDefinitions`) is an array of FieldDefinition objects. This is not where you'll find the _data_. Rather, this include information _about_ the fields, like their names, types, and whether or not they are globals.

Example:

var response = // the JSON response
var field_count = response.fieldDefinitions.length;

The FieldDefintion Object

A fieldDefinition object represents one field from the response. It includes the following properties:
- `name`: the name of the field
- `autoEnter`: true if the field has an Auto Enter option enabled
- `global`: true if the field uses global storage
- `maxRepear`: the number of repetitions the field supports
- `notEmpty`: true if the field's "Not empty" validation is enabled
- `result`: the data type of the field; ie: text, number, date, time, and so forth (but see `type`
below)
- `type`: either "normal", "calculation", or "summary"; note that a number calculation field will
have a `type` of "calculation" and a `result` of "number"

Example:

var response = // the JSON response
var first_field = response.fieldDefinitions[0];
var first_field_type = first_field.type;

The Resultset Object

The resultSet object contains information about the records returned, and the actual record data. Specifically, it includes these properties:

- `count`: the number of records in the resultset
- `fetchSize`: I'm not honestly sure what this one means; it isn't covered in the FileMaker docs
and I've never used it
- `records`: an array of `record` objects, one for each record returned

Example:

var response = // the JSON response
var record_count = response.resultset.count
var record_count_another_way = response.resultset.records.length;
var first_record = response.resultset.records[0];
for (var i = 0; i < record_count; i++)
{
var current_record = response.resultset.records[i];
// do something with the record here
}

The Record Object

A record object represents one record from the response. It includes the following properties:

- `modId`: the modification id of the record
- `recordId`: FileMaker's internal record id (used for edit and delete)
- `fields`: an hash of field values from the record

Example:

var response = // the JSON response
var first_record = response.resultset.records[0];
var recid = first_record.recordId;
var field_count = first_record.fields.length;

The Fields Hash

The `fields` property of the `record` object maps field names to values. As such, its exact contents will vary depending on the specifics of your database. The keys in the hash are the actual field names. Each and every value is _always_ an array. It usually has just one element, but if you're dealing with a repeating field, it will have a value for each repetition. __I repeat: the value is _always_ an array__.
In Javascript, a field can be accessed two ways. If a field name is a legal Javascript identifier (no spaces or whacky punctuation, etc...) then you can do this:

var first_name = a_record.fields.first_name[0];

But if the field name doesn't conform, you can do this instead:

var first_name = a_record.fields['First Name'][0];

You may be wondering why each example treats the field value as an array. But you shouldn't be. I'll say it one more time: Every field value is an array. It only has one element unless it is a repeating field.

Example:

var response = // the JSON response
for (int i = 0; i < response.resultset.records.length; i++)
{
var record = response.resultset.records[i];
var full_name = record.fields['First Name'][0] + " " + record.fields['Last Name'][0];
alert("The person's full name is " + full_name);
}

Support

json.xsl is provided free of charge and without support. If you need help with your FileMaker web development, Six Fried Rice would be thrilled to help. Contact us for an estimate.