URI /RESTfm/{database}/layout/{layout} Read - Find Request with SQL

RESTfm (4.0.0 and above) supports an SQL subset to perform complex find requests on layouts by passing an RFMfind=<url_encoded_SQL> query string parameter. The same find criteria and sorting functionality as provided within FileMaker is available to use.

Syntax

[SELECT field_name [, field_name ...]] [WHERE where_condition] [ORDER BY field_name [ASC | DESC] [, field_name [ ASC | DESC ] ...]] [LIMIT row_count] [OFFSET offset]  where_condition:   expr [OR expr ...] [OMIT expr]  expr:   field_name operator test_value [ AND field_name operator test_value ...]  operator:   = | >= | > | <= | < | LIKE

Any or all of SELECT, WHERE, ORDER BY, LIMIT and OFFSET clauses may be used in a single query, but must be in the order specified above.

The SELECT, WHERE, ORDER BY, ASC, DESC, LIMIT and OFFSET keywords are case insensitive, but are often capitalised for readability.

Example URI

Given the following SQL query:

SELECT Locality, Pcode WHERE (Pcode > 3000 AND Locality LIKE '*melb*') OR (Pcode < 3000 AND Locality LIKE '*syd*') OMIT Comments LIKE '*box*' ORDER BY Pcode DESC LIMIT 10 OFFSET 5 

With the above SQL query URL encoded, the final URI would be:

http://demo.restfm.com/RESTfm/postcodes/layout/brief%20postcodes.html?RFMfind=SELECT%20Locality%2C%20Pcode%20WHERE%20%28Pcode%20%3E%203000%20AND%20Locality%20LIKE%20%27*melb*%27%29%20OR%20%28Pcode%20%3C%203000%20AND%20Locality%20LIKE%20%27*syd*%27%29%20OMIT%20Comments%20LIKE%20%27*box*%27%20ORDER%20BY%20Pcode%20DESC%20LIMIT%2010%20OFFSET%205

SQL clauses explained

SELECT is used to specify which fields to return and in which order.

SELECT Locality, Pcode, "Delivery Office"

WHERE indicates the criteria that rows must satisfy to be selected. Each find request row in FileMaker is equivalent to an OR expression, and each field within a request row is equivalent to an AND expression.

An example of a find in FileMaker where you have two "Include" find requests, followed by an "Omit" find request. The equivalent WHERE clause would be:

WHERE (Pcode > 3000 AND Locality LIKE '*melb*') OR (Pcode < 3000 AND Locality LIKE '*syd*') OMIT Comments LIKE '*box*' 

ORDER BY sets ordering by the specified field name(s) in the FileMaker default ordering. The order may be explicitly set by using the optional DESC (descending) or ASC (ascending) keywords.

ORDER BY Pcode DESC, BSPnumber ASC

LIMIT constrains the number of rows returned.

OFFSET specifies the offset of the first row to return.

WHERE operators explained

The most basic WHERE clause takes the form: WHERE field_name operator test_value

The operator may be a comparison operator: = | > | >= | < | <=

Or the operator may be the pattern operator: LIKE

The LIKE pattern operator may be used with any FileMaker find accepted test_value: text, numbers and dates, ranges, empty, duplicates. A selection of these is demonstrated in these examples:

WHERE foo LIKE "1000..2000"  WHERE foo LIKE "11:30..17:30"  WHERE foo LIKE "{1..3}/{10..16}/2010"  WHERE foo LIKE '"Marten and Jones Interiors"'  WHERE foo LIKE "#3"  WHERE foo LIKE "*phan*"  WHERE foo LIKE 'Gr@y'  WHERE foo LIKE '\"Joey\"'  WHERE foo LIKE '==John'  WHERE foo LIKE "=Chris =Smith"

The following comparison and LIKE operator examples are actually equivalent:

Comparison operator	Equivalent LIKE pattern operator
WHERE foo = 2	WHERE foo LIKE '=2'
WHERE foo <= 5	WHERE foo LIKE '<=5'
WHERE foo = '=John'	WHERE foo LIKE '==John'

Limitations and illegal SQL queries

The FileMaker "find" engine has some peculiarities in it's query mechanism that may not appear to map obviously to SQL. Some reading of the FileMaker documentation linked in the "WHERE operators explained" subsection may be required.

FileMaker cannot have the same field name used twice in a single request row (ANDed expression), so the following is illegal (the syntax is correct, but the result is undefined):

ILLEGAL: WHERE Pcode > 3000 AND Pcode < 4000 

The above example may be rewritten into the following legal expression:

WHERE Pcode LIKE '3001..3999'

FileMaker has no mechanism to express the following, so it is illegal (the RESTfm SQL parser will return a syntax error if OR or OMIT is used within parenthesis):

ILLEGAL: WHERE (Pcode < 3000 OR Pcode >= 4000) AND Locality LIKE '*melb*'

The above example may be rewritten into the following legal expression:

WHERE (Pcode < 3000 AND Locality LIKE '*melb*') OR (Pcode >= 4000 AND Locality LIKE '*melb*')

Performance considerations

• Find criteria can be slow, ensure FileMaker guidelines are followed regarding indexes and stored calculations.
• SELECT allows less fields to be returned than in the layout, but FileMaker Server always returns all fields to RESTfm. Consider a dedicated layout with only the necessary fields.
• RESTfm does not support related records in layout portals, but FileMaker Server always returns all related records to RESTfm. Consider a dedicated layout without portals.