FileMaker Data Sources¶

Performance Tips¶

This section contains a number of tips that will help get the best performance from a FileMaker database. Since queries must be performed sequentially within FileMaker Server, even small optimizations can yield significant increases in the speed of web serving under heavy load.

• Dedicated FileMaker Server Machine – For best performance, place the FileMaker Server on a different machine from Lasso Server and the web server.
• FileMaker Server – If a FileMaker database must be accessed by a mix of FileMaker clients and web visitors through Lasso, it should be hosted on FileMaker Server. Lasso can access the database directly through FileMaker Server 7–12 Advanced and FileMaker Server 9–12.
• Index Fields – Any fields that will be searched through Lasso should have indexing turned on. Avoid searching on non-stored calculation fields, related fields, and summary fields.
• Custom Layouts – Layouts should be created with the minimal number of fields required for Lasso. All the data for the fields in the layout will be sent to Lasso with the query results. Limiting the number of fields can dramatically cut down the amount of data that needs to be sent from FileMaker Server to Lasso.
• Value Lists – For FileMaker Server data sources use the -noValueLists parameter to suppress the automatic sending of value lists from FileMaker when those value lists are not going to be used on the response page.
• Layout Response – For FileMaker Server data sources use the -layoutResponse parameter to specify which layout should be used to return results from FileMaker. A different layout from what was specified in the request can be used for the result. This is a replacement for the -returnField parameter, which is not supported for FileMaker data sources.
• Sorting – Sorting can have a serious impact on performance if large numbers of records must be sorted. Avoid sorting large record sets and avoid sorting on calculation fields, related fields, unindexed fields, or summary fields.
• Contains Searching – FileMaker is optimized for the default “Begins With” searches (and for numerical searches). Use of the contains operator (-cn) can dramatically slow down performance since FileMaker will not be able to use its indices to optimize searches.
• Max Records – Using -maxRecords to limit the number of records returned in the result set from FileMaker Server can speed up performance. Use -maxRecords and -skipRecords methods to navigate a visitor through the found set.
• Calculation Fields – Calculation fields should be avoided if possible. Searching or sorting on unindexed, uncached calculation fields can have a negative effect on FileMaker Server performance.
• FileMaker Scripts – The use of FileMaker scripts should be avoided if possible. While FileMaker executes a script, no other database actions can be performed. FileMaker scripts can usually be rewritten as Lasso code to achieve the same effect, often with greater performance.

In addition to these tips, MySQL or PostgreSQL can shift some of the burden off of FileMaker Server. MySQL and PostgreSQL can usually perform database searches much faster than FileMaker. Lasso also includes sessions and collection types that can perform some of the tasks of a database, but with higher performance for small amounts of data.

Compatibility Tips¶

Following these tips will help to ensure that it is easy to transfer data from a FileMaker database to another data source, such as a PostgreSQL database, at a future date.

• Database Names – Database, layout, and field names should contain only a mix of letters, numbers, and the underscore character.
• Calculation Fields – Avoid the use of calculation fields. Instead, perform calculations within Lasso and store the results back into regular fields if they will be needed later.
• Summary Fields – Avoid the use of summary fields. Instead, summarize data using inline searches within Lasso.
• Scripts – Avoid the use of FileMaker scripts. Most actions performed with scripts can be performed using the database actions available within Lasso.
• Record ID – Create a calculation field with the calculation Status(CurrentRecordID) and name it “id”. Always use the -keyField='id' within inline database actions. This ensures that when moving to a database that relies on storing the key field value explicitly, a unique key field value is available.

Search Operators¶

By default FileMaker performs a “begins with” search for each field in a query. In FileMaker Server each field can only be specified one time within each search query. See the information below on FileMaker search symbols for strategies to perform complex queries in FileMaker Server.

Lasso also provides the following operators that allow performing different queries. Each operator should be specified immediately before the field and its search value are specified. Note that this list of operators is somewhat different from those supported by other data source connectors including older FileMaker data source connectors.

Note that there is no -neq operator or other negated operators. It is necessary to use a -not query to omit records from the found set instead, as explained further below. For example, to find records where the field “first_name” is not “Joe” the following search terms must be used: -not, -op='eq', 'first_name'='Joe'

The -rx operator can pass a raw FileMaker search expression as a query. This allows the use of any of the FileMaker search symbols. See the FileMaker documentation for a full explanation of how these symbols work.

The range symbol (..) is most useful for performing searches within a date range, e.g. a date in 2006 can be found by searching for -rx, 'date_field'='1/1/2006..12/31/2006'.

Logical Operators¶

FileMaker data sources default to performing an AND search. The records that are returned from the data source must match all of the specified criteria. It is also possible to specify -opLogical to switch to an OR search where the records that are returned from the data source may match any of the specified criteria.

For example, the following criteria returns records where the “first_name” is “John” and the “last_name” is “Doe”: -eq, 'first_name'='John', -eq, 'last_name'='Doe'

The following criteria instead returns records where the “first_name” is “John” or the “last_name” is “Doe”. This would return records for “John Doe” as well as “Jane Doe” and “John Walker”: -opLogical='Or', -eq, 'first_name'='John', -eq, 'last_name'='Doe'

Complex Queries with FileMaker Server 9 and Later¶

Starting with FileMaker Server 9, a search request is made up of one or more queries. By default a single query is generated and all of the search terms within it are combined using an AND operator. Additional queries can be added to either extend the found set using an OR operator or to omit records from the found set using a NOT operator. These queries correspond precisely to find requests within the FileMaker Server user interface.

Each field can only be listed once per query. The standard Lasso operators can be used for most common search parameters like equals, begins with, ends with, contains, less than, greater than, etc. FileMaker’s standard find symbols can be used for more complex criteria. It may also be necessary to use multiple queries for more complex search criteria.

Search requests in FileMaker Server 9 and later do not support the “Not Equals” operator or any of the NOT-variant operators. Instead, these should be created by combining an omit query with the appropriate affirmative operator. The -opLogical, -opBegin, and -opEnd operators are not supported. The -or and -not operators must be used instead.

A search with a single query uses an AND operator to combine each of the search terms. Records where the field “first_name” begins with the letter “J” and the field “last_name” begins with the letter “D” can be found using the following search terms in Lasso. Each record in the result set will match every search term in the query: -bw, 'first_name'='J', -bw, 'last_name='D'

We start an additional query using an -or parameter. FileMaker runs the first and second queries independently and then combines the search results. The result of the following search terms will be to find every record where the field “first_name” begins with the letter “J” and the field “last_name” begins with either the letter “D” or the letter “S”. Each record in the result set will match either the first query or the second query.

-bw, 'first_name'='J',
-bw, 'last_name'='D',
-or,
-bw, 'first_name'='J',
-bw, 'last_name'='S'

Note that each field name can only appear once per query, but the same field name can be used in multiple queries. The “first_name” search term is repeated in both queries so that all returned records will have a “first_name” starting with “J”. If the “first_name” search term was left out of the second query then the result set would contain every record where the field “first_name” begins with the “J” and the field “last_name” begins with the letter “D” and every record where the field “last_name” begins with the letter “S”.

The result set can be narrowed by adding an omit query using a -not parameter. FileMaker will run the first query and any -or queries first, generating a complete result set. Then, the -not queries will be run and any records that match those queries will be omitted from the found set. The result of the following search terms will be to find every record where the field “first_name” begins with the letter “J” and the field “last_name” begins with the letter “D” except for the record for “John Doe”. Each record in the result set will match the first query and will not match the second query.

-bw, 'first_name'='J',
-bw, 'last_name'='D',
-not,
-bw, 'first_name'='John',
-bw, 'last_name'='Doe'

It is possible to construct most searches positively using only a single query or a few -or queries, but sometimes it is more logical to construct a large result set and then use one or more -not queries to omit records from it.

Additional Commands for FileMaker Server 9 and Later¶

FileMaker Server 9 supports a number of additional unique commands that are summarized in the following table. Most of these commands are passed through to FileMaker Server without modification by Lasso. The FileMaker Server 9 Custom Web Publishing with XML and XSLT documentation should be consulted for full details about these commands.

Return the Current Record ID¶

The Record ID for the current record can be returned using keyField_value. The following example shows an inline method that will perform a -findAll action and return the Record ID for each returned record using the keyField_value method:

inline(
   -findAll,
   -database='contacts',
   -table='people'
) => {^
   records => {^
      '<br />' + keyField_value + ': ' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />126: John Doe
// <br />127: Jane Doe
// <br />4096: Jane Person

Reference a Record by Record ID¶

For -update and -delete action parameters the Record ID for the record being operated upon can be referenced using -keyValue. The -keyField does not need to be defined or should be set to an empty string if it currently is (-keyField=''). The following example shows a record in “contacts” being updated with -keyValue=126. The name of the person referenced by the record is changed to “John Surname”.

inline(
   -update,
   -database='contacts',
   -table='people',
   -keyValue=126,
   'first_name'='John',
   'last_name'='Surname'
) => {^
   keyField_value + ': ' + field('first_name') + ' ' + field('last_name')
^}

// => 126: John Surname

The following example shows a record in “contacts” being deleted with -keyValue=127. The -keyField keyword parameter is included, but its value is set to the empty string.

inline(
   -delete,
   -database='contacts',
   -table='people',
   -keyField='',
   -keyValue=127
) => {}

Return Results in Custom Sort Order¶

Specify -sortField and -sortOrder keyword parameters within the search inline. The records are first sorted by “title” in custom order, then by “last_name” and “first_name” in ascending order. The “title” field will be sorted in the order of the elements within the value list associated with the field in the database. In this case, it will be sorted as “Mr., Mrs., Ms.”.

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id',
   -sortField='title',      -sortOrder='title',
   -sortField='last_name',  -sortOrder='ascending',
   -sortField='first_name', -sortOrder='ascending'
) => {^
   records => {^
      '<br />' + field('title') + ' ' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

The following results could be returned when this page is loaded. Each of the records with a title of “Mr.” appear before each of the records with a title of “Mrs.”. Within each title, the names are sorted in ascending alphabetical order.

// =>
// <br />Mr. John Doe
// <br />Mr. John Person
// <br />Mrs. Jane Doe
// <br />Mrs. Jane Person

Related Fields¶

Related fields are named using the relationship name followed by two colons and the field name. For example, a related field “call_duration” from a “calls” database might be referenced as calls::call_duration. Any related fields included in the layout specified for the current Lasso action can be referenced using this syntax. Data can be retrieved from related fields or it can be set in related fields when records are added or updated.

inline(
   -findAll,
   -database='contacts',
   -table='people'
) => {^
   records => {^
      '<br />' + keyField_value + ': ' + field('first_name') + ' ' + field('last_name') +
      '(Last call at: ' + field('calls::last_call_time') + ').\n'
   ^}
^}

// =>
// <br />126: John Doe (Last call at 12:00 pm).
// <br />127: Jane Doe (Last call at 9:25 am).
// <br />496: Jane Person (Last call at 4:46 pm).

inline(
   -update,
   -database='contacts',
   -table='people',
   -keyField='',
   -keyValue='7',
   'Calls::last_call_time.9'='12:14:56'
) => {^
   field('calls::last_call_time')
^}

// => 12:14:56

Portals¶

A portal allows one-to-many relationships to be displayed within FileMaker databases. Portals allow retrieving data from many related records and displaying it in a single Lasso page. A portal must be present in the current FileMaker layout in order for its values to be retrieved using Lasso.

Only the number of repetitions formatted to display within FileMaker will be displayed using Lasso. A portal must contain a scroll bar in order for all records from the portal to be displayed using Lasso.

Fields in portals are named using the same convention as related fields. The relationship name is followed by two colons and the field name. For example, a related field “call_duration” from a “calls” database might be referenced as calls::call_duration.

inline(
   -findAll,
   -database='contact',
   -table='people'
) => {^
   records => {^
      '<p>Calls for ' + field('first_name') + ' ' + field('last_name') + ':\n'
      portal('calls') => {^
         '<br />' + field('calls::number') + ' at ' + field('calls::time') +
         'for ' + field('calls::duration') + ' minutes.\n'
      ^}
      '</p>\n'
   ^}
^}

// =>
// <p>Calls for John Doe:
// <br />555-1212 at 12:00 pm for 15 minutes.
// </p>
// <p>Calls for Jane Doe:
// <br />555-1212 at 09:25 am for 60 minutes.
// </p>
// <p>Calls for Jane Person:
// <br />555-1212 at 2:23 pm for 55 minutes.
// <br />555-1212 at 4:46 pm for 5 minutes.
// </p>

inline(
   -add,
   -database='calls',
   -table='people',
   'contact_id'=123,
   'number'='555-1212',
   'time'='12:00 am',
   'duration'=55
) => {}

Value Lists¶

Value lists in FileMaker allow defining a set of possible values for a field. The items in the value list associated with a field on the current layout for a Lasso action can be retrieved using the value_list methods as shown in the examples below. See the FileMaker documentation for more information on how to create and use value lists.

In order to display values from a value list, the layout referenced in the current database action must contain a field formatted to show the desired value list as a drop-down menu, select list, checkboxes, or radio buttons. Lasso cannot reference a value list directly, but can reference a value list through a formatted field in the current layout.

value_list(colName::string)

Executes a capture block for each value in the named value list. Requires a single parameter specifying the name of a field from the current layout that has a value list assigned to it.

value_listItem()

While in a value_list capture block, it returns the value for the current item.

selected()

Displays the word “selected” if the current value list item is selected in the field associated with the value list.

checked()

Displays the word “checked” if the current value list item is selected in the field associated with the value list.

inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   value_list('title') => {^
      value_listItem + ', '
   ^}
^}

// => Mr., Mrs., Ms., Dr.,

'<form action="response.lasso" method="post">\n'
inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   '<select name="title">\n'
      value_list('title') => {^
         '   <option value="' + value_listItem + '">' + value_listItem + '</option>\n'
      ^}
   '</select>\n'
^}
'<p><input type="submit" name="submit" value="Add Record">\n</form>\n'

// =>
// <form action="response.lasso" method="post">
// <select name="title">
//    <option value="Mr." selected>Mr.</option>
//    <option value="Mrs." >Mrs.</option>
//    <option value="Ms." >Ms.</option>
//    <option value="Dr." >Dr.</option>
// </select>
// <p><input type="submit" name="submit" value="Add Record"></p>
// </form>

'<form action="response.lasso" method="post">\n'
inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   value_list('title') => {^
      '   <input type="radio" name="title" value="' + value_listItem + '" /> ' + value_listItem + '\n'
   ^}
^}
'<p><input type="submit" name="submit" value="Add Record">\n</form>\n'

// =>
// <form action="response.lasso" method="post">
//    <input type="radio" name="title" value="Mr." /> Mr.
//    <input type="radio" name="title" value="Mrs." /> Mrs.
//    <input type="radio" name="title" value="Ms." /> Ms.
//    <input type="radio" name="title" value="Dr." /> Dr.
// <p><input type="submit" name="submit" value="Add Record"></p>
// </form>