FileMaker Data Sources

Lasso Server allows access to FileMaker Server 7, 8, 9, 10, or 11 Advanced and FileMaker Server 9, 10, or 11 through the Lasso Connector for FileMaker SA. Lasso provides several tags and options which are unique to FileMaker Server connections including -LayoutResponse and -NoValueLists.

LassoScript is a predominantly data source-independent language. It does include many FileMaker specific tags which are documented in this chapter. However, all of the common procedures outlined in the Data Source Fundamentals, Searching and Displaying Data, and Adding and Updating Records sections can be used with FileMaker data sources. 

Note: The tags and procedures defined in this chapter can only be used with FileMaker data sources. Any solution which relies on the tags in this chapter cannot be easily retargeted to work with a different back-end database.

Terminology

Since Lasso works with many different data sources this documentation uses data source agnostic terms to refer to databases, tables, and fields. The following terms which are used in the FileMaker documentation are equivalent to their Lasso counterparts.

• Database – Database is used to refer to a single FileMaker database file. FileMaker databases differ from other databases in Lasso in that they contain layouts rather than individual data tables. Even in FileMaker 7-11, Lasso sees individual layouts rather than data tables. From a data storage point of view, a FileMaker database is equivalent to a single MySQL table.

• Layout – Within Lasso a FileMaker layout is treated as equivalent to a Table. The two terms can be used interchangeably. This equivalence simplifies Lasso security and makes transitioning between back-end data sources easier. All FileMaker layouts can be thought of as views of a single data table. Lasso can only access fields which are contained in the layout named within the current database action.

• Record – FileMaker records are referenced using a single -KeyValue rather than a -KeyField and -KeyValue pair. The -KeyField in FileMaker is always the record ID which is set internally.

• Fields – The value for any field in the current layout in FileMaker can be returned including the values for related fields, repeating fields, and fields in portals. 

Important: Every database which is referenced by a related field or a portal must have the same permissions defined. If a related database does not have the proper permissions then FileMaker will not just leave the related fields blank, but will deny the entire database request.

Performance Tips

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

• Dedicated FileMaker Machine – For best performance, place the FileMaker Pro or FileMaker Server on a different machine from Lasso Service 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, 8, 9, 10, or 11 Advanced, FileMaker Server 9, 10, or 11, or through a single FileMaker Pro 4, 5, or 6 client which is connected as a guest to FileMaker Server.

• Index Fields – Any fields which will be searched through Lasso should have indexing turned on. Avoid searching on unstored 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 which needs to be sent from FileMaker to Lasso.

• Value Lists – For FileMaker Server data sources use the -NoValueLists tag 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 keyword to specify what layout should be used to return results from FileMaker. A different layout can be used to specify the request and for the result of the request.

• 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 can speed up performance. Use -MaxRecords and -SkipRecords or the [Link_…] methods to navigate a visitor through the found set.

• Calculation Fields – Calculation fields should be avoided if possible. Searching or sorting on unindexed, unstored calculation fields can have a negative effect on FileMaker performance.

• FileMaker Scripts – The use of FileMaker scripts should be avoided if possible. When FileMaker executes a script, no other database actions can be performed at the same time. FileMaker scripts can usually be rewritten as LassoScripts to achieve the same effect, often with greater performance. 

In addition to these tips, MySQL can be used to shift some of the burden off of FileMaker. MySQL can usually perform database searches much faster than FileMaker. Lasso also includes sessions and compound data types that can be used to 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 easy to transfer data from a FileMaker database to another data source, such as a MySQL 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 which can be performed with scripts can be performed using the database actions available within Lasso.
• Record ID – Create a calculation field named ID and assign it to the following calculation. Always use the -KeyField='ID' within [Inline] database actions, HTML forms, and URLs. This ensures that when moving to a database that relies on storing the key field value explicitly, a unique key field value is available.

Status(CurrentRecordID)

FileMaker Queries

The queries generated by inlines for FileMaker data sources differ from the queries generated for other data sources in several significant ways. This section includes a description of how search operators, logical operators, and other keywords are used to construct queries for each of the FileMaker data sources.

Search Operators

By default FileMaker performs a “begins with” search for each field in a query. 

• FileMaker Server – In FileMaker Server each field can only be specified one time within each search query. See the information about FileMaker search symbols below for strategies to perform complex queries in FileMaker Server.

Lasso also provides the following operators which allow different queries to be performed. 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 other 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. For example, to find records where the field FirstName is not Joe the following search terms must be used.

-Not,
-Op='Eq', 'FirstName'='Joe',

The range symbol .. is most useful for performing searches within a date range. For example a date in 2006 can be found by searching for -RX, 'DateField'='1/1/2006 .. 12/31/2006'.

Logical Operators

FileMaker data sources default to performing an “and” search. The records which are returned from the data source must match all of the criteria that are specified. It is also possible to specify -OpLogical to switch to an “or” search where the records which are returned from the data source may match any of the criteria that are specified.
For example, the following criteria returns records where the FirstName is John and the LastName is Doe.

-EQ, 'FirstName'='John', -EQ, 'LastName'='Doe'

The following criteria instead returns records where the FirstName is John or the LastName is Doe. This would return records for John Doe as well as Jane Doe and John Walker.

-OpLogical='or', -EQ, 'FirstName'='John', -EQ, 'LastName'='Doe'

FileMaker 9 Complex Queries

A FileMaker Server 9 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 Pro 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. 

FileMaker Server 9 search requests do not support 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 FirstName begins with the letter J and the field LastName 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, 'FirstName'='J',
-BW, 'LastName='D'

We start an additional query using an -Or operator. 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 FirstName begins with the letter J and the field LastName begins with either the letter D or the letter S. Each records in the result set will match either the first query or the second query.

-BW, 'FirstName'='J',
-BW, 'LastName='D'
-Or,
-BW, 'FirstName'='J',
-BW, 'LastName='S'

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

The result set can be narrowed by adding an omit query using a -Not operator. 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 which 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 FirstName begins with the letter J and the field LastName begins withthe letter D except for the record for John Doe. Each records in the result set will match the first query and will not match the second query.

-BW, 'FirstName'='J',
-BW, 'LastName='D'
-Not,
-BW, 'FirstName'='John',
-BW, 'LastName='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

FileMaker Server 9 supports a number of additional unique commands which are summarized in the following table. Most of these commands passed through to FileMaker 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.

Primary Key Field and Record ID

FileMaker databases include a built-in primary key value called the Record ID. This value is guaranteed to be unique for any record in a FileMaker database. It is predominantly sequential, but should not be relied upon to be sequential. The values of the record IDs within a database may change after an import or after a  database is compressed using Save a Copy As…. Record IDs can be used within a solution to refer to a record on multiple pages, but should not be stored as permanent references to FileMaker records.

Note: The [RecordID_Value] method can also be used to retrieve the record ID from FileMaker records. However, for best results, it is recommended that the [KeyField_Value] method be used.

To return the current record ID:
The record ID for the current record can be returned using [KeyField_Value]. The following example shows [Inline] tags that perform a -FindAll action and return the record ID for each returned record using the [KeyFIeld_Value] method.

inline( -database='Contacts', -table='People', -findall ) => {^
	 records => {^
		 keyfield_value; ': '; field( 'First_Name' ); ' '; field( 'Last_Name' ); '<br />';
	 ^} // Close records
 ^} // Close inline

126: John Doe

127: Jane Doe

4096: Jane Person

To reference a record by record ID:

For -Update and -Delete keywords the record ID for the record which should be 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 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( -database='Contacts', -table='People', -keyValue=126,
		 'First_Name'='John',
		 'Last_Name'='Surname',
		 -update ) => {^
	keyfield_value; ': '; field( 'First_Name' ); ' '; field( 'Last_Name' ); '<br />';
^} // Close inline

126: John Surname

The following example shows a record in Contacts being deleted with -KeyValue=127. The -KeyField keyword is included, but its value is set to the empty string.

inline( -database='Contacts', -table='People', -keyfield='', -keyvalue=126, -delete ) => {	 
} // Close inline

To access the record ID within FileMaker:
The record ID for the current record in FileMaker can be accessed using the calculation value Status(CurrentRecordID) within FileMaker.

Sorting Records

In addition to the Ascending and Descending values for the -SortOrder keyword, FileMaker data sources can also accept a custom value. In FileMaker Server, the value for -SortOrder should name a value list. The order of that value list will be used as the custom sorting order for records in the result set. Note also that FileMaker Server only supports the specification of nine sort fields in a single database search.

To return custom sorted results:
Specify -SortField and -SortOrder keyword within the search parameters. 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( -database='Contacts', -table='People', -keyfield='ID',
		-sortField='Title', -sortOrder='Title',
		-sortField='Last_Name', -sortOrder='Ascending',
		-sortField-'First_Name', -sortOrder='Ascending',
		-findall ) => {^
	records => {^
		'<br />'; field( 'Title' ); ' '; field( 'First_Name' ); ' '; field( 'Last_Name' );
	^} // Close records
^} // Close inline

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

Displaying Data

FileMaker includes a number of methods tags that allow the different types of FileMaker fields to be displayed. These tags are summarized in FileMaker Data Display Methods and then examples are included in the sections that follow. See also the sections on Value Lists and Images for more information about returning values from FileMaker fields.

Note: All fields which are referenced by Lasso must be contained in the current layout in FileMaker. For portals and repeating fields only the number of repetitions shown in the current layout will be available to Lasso.

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 which are 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.
Important: Every database which is referenced by a related field or a portal must have the same permissions defined. If a related database does not have the proper permissions then FileMaker will not just leave the related fields blank, but will deny the entire database request. 

To return data from a related field:
Specify the name of the related field within a [Field] method. The related field must be contained in the current layout either individually or within a portal. In a one-to-one relationship, the value from the single related record will be returned. In a one-to-many relationship, the value from the first related record as defined by the relationship options will be returned. See the section on Portals below for more control over one-to many relationships.

The following example shows a -FindAll action being performed in a database Contacts. The related field Last_Call_Time from the Calls databases is returned for each record through a relationship named Calls.

inline( -database='Contacts', -table='People', -findall )=> {^
	 records => {^
		 '<br />'; keyfield_value; ': '; field( 'First_Name' ); ' '; field( 'Last_Name' );
		 '(Last call at: '; field( 'Calls::Last_Call_Time' ); ').';		 
	 ^} // Close records
^} // Close inline

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

To set the value for a related field:
Specify the name of the related field, along with the related field's record ID, within the action which adds or updates a record within the database. The related field must be contained in the current layout either individually or within a portal.

In one-to-one or one-to-many relationships, the fully qualified field name must be used along with the record ID of the related field in the format: table::field.id, where id is the related field's record ID. See the section on Portals below for more information.

The following example shows an -Update action being performed in a database Contacts. The related field Last_Call_Time, with a record ID of 9, from the Calls database is updated for Jane Person. The new value is returned.

inline( -database='Contacts', -table='People', -keyfield='', -keyvalue='7', 
			'Calls::Last_Call_Time.9'='12:14:56', -update ) => {^
	field( 'Calls::Last_Call_Time' );
^} // Close inline

12:14:56

Portals

Portals allow one-to-many relationships to be displayed within FileMaker databases. Portals allow data from many related records to be retrieved and displayed 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.
Important: Every database which is referenced by a related field or a portal must have the same permissions defined. If a related database does not have the proper permissions then FileMaker will not just leave the related fields blank, but will deny the entire database request.

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.
Note: Everything that is possible to do with portals can also be performed using nested [Inline] capture blocks to perform actions in the related database. Portals are unique to FileMaker databases.

To return values from a portal:
Use the [Portal] method with the name of the portal referenced. [Field] methods within the [Portal] capture block should reference the fields from the current portal row using related field syntax.

The following example shows a portal Calls that is contained in the People layout of the Contacts database. The Time, Duration, and Number of each call is displayed.

inline( -database='Contact', -table='People', -findall )=> {^
	records => {^
		'<p>Calls for '; field( 'First_Name' ); ' '; field( 'Last_Name' ); ':';
		portal( 'Calls' ) => {^
			'<br />'; field( 'Calls::Number' ); ' at '; field( 'Calls::Time' );
			'for '; field( 'Calls::Duration' ); ' minutes.';
		^}// Close portal
	^} // Close records	 
^} // Close inline

<p>Calls for John Doe:
<br>555-1212 at 12:00 pm for 15 minutes.
<p>Calls for Jane Doe:
<br>555-1212 at 9:25 am for 60 minutes.
<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.

To add a record to a portal:
A record can be added to a portal by adding the record directly to the related database. In the following example the Calls database is related to the Contacts database by virtue of a field Contact_ID that stores the ID for the contact which the calls were made to. New records added to Calls with the appropriate Contact_ID will be shown through the portal to the next site visitor.

In the following example a new call is added to the Calls database for John Doe. John Doe has an ID of 123 in the Contacts database. This is the value used for the Contact_ID field in Calls.

Value Lists

Value lists in FileMaker allow a set of possible values to be defined 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 methods defined in FileMaker Value List Methods. See the documentation for FileMaker for more information about how to create and use value lists within FileMaker.

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 pop-up menu, select list, check boxes, or radio buttons. Lasso cannot reference a value list directly. Lasso can only reference a value list through a formatted field in the current layout.

Note: See the Searching and Displaying Data chapter for information about the -Show command tag which is used throughout this section.

To display all values from a value list:
The following example shows how to display all values from a value list using a -Show action within [Inline] capture. The field Title in the Contacts database contains five values Mr., Mrs., Ms., and Dr. The -Show action allows the values for value lists to be retrieved without performing a database action.

inline( -database='database', -table='table', -show )=> {^
	value_list( 'Field Name' )=> {^
		value_listItem;
	^}
^}

Mr.
Mrs.
Ms.
Dr.

To display an HTML pop-up menu in an -Add form with all values from a value list:
The following example shows how to format an HTML <select> … </select> pop-up menu to show all the values from a value list. A select list can be created with the same code by including size and/or multiple parameters within the <select> tag. This code is usually used within an HTML form that performs an -Add action so the visitor can select a value from the value list for the record they create.

The example shows a single <select> … </select> within an [Inline] capture with a -Show command. If many value lists from the same database are being formatted, they can all be contained within a single [Inline] capture.

<form action="response.lasso" method="POST">
	<input type="hidden" name="-Add" value="">
	<input type="hidden" name="-Database" value="Contacts.fp5">
	<input type="hidden" name="-Table" value="People">
	<input type="hidden" name="-KeyField" value="ID">
	[inline( -database='database', -table='table', -show )]
		<select name="Title">
			[value_list( 'title' )]
			 	<option value="[Value_ListItem]">[Value_ListItem]</option>
			[/value_list]
		</select>
	[/inline]
	<p><input type="submit" value="Add Record">
</form>

<form action="response.lasso" method="POST">
	<input type="hidden" name="-Add" value="">
	<input type="hidden" name="-Database" value="Contacts.fp5">
	<input type="hidden" name="-Table" value="People">
	<input type="hidden" name="-KeyField" value="ID">
	[inline( -database='database', -table='table', -show )]
	 	[value_list( 'Title' )]
	 	 	<input type="radio" name="Title" value="[Value_ListItem]">[Value_ListItem]
	 	[/value_list]
	[/inline]
	 <p><input type="submit" value="Add Record">
</form>