fmAddRecord

fmCloseDatabase

fmCountFields

fmCountMatching

fmCountRecords

fmDoScript

fmGetDatabases

fmGetFieldInfo

fmGetFields

fmGetLayouts

fmGetRecord

fmGetRecordID

fmLookup

fmOpenDatabase

fmRemoveRecord

fmRetrieve

fmSetRecord

Parameter	Description
database	name of database
[field-values]	list of field/value pairs [optional]

Adds a new record to database and returns the new record's ID number as an integer. The optional field-values parameter may be used to set the field contents of the new record, rather than using an additional function call.

You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, fields which are defined in the database, but are not in the layout specified will not be available.

Both of the examples below perform the same action, one demonstrating the use of the field-values parameter rather than having to use fmSetRecord().

Examples:

1. new_id = fmAddRecord("Customers");
   fmSetRecord("Customers", new_id, [["Customer ID" 1]
                                     ["First Name" "Nathan"]
                                     ["Last Name" "Nunn"]]);

2. fmAddRecord("Customers", [["Customer ID" 1]
   	                  ["First Name" "Nathan"]
   	                  ["Last Name" "Nunn"]]);

See Also:

• fmSetRecord

Parameter	Description
database	name of database

Closes the database whose name is database. To be used again, the database must be opened using the fmOpenDatabase() function.

Example:

fmCloseDatabase("Customers");

See Also:

• fmOpenDatabase

Parameter	Description
database	name of database

Counts the number of defined fields in database and returns the count as an integer.

You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, fields which are defined in the database, but are not in the layout specified will not be available.

Example:

num_fields = fmCountFields("Customers");   // num_fields will be equal to 3

Parameter	Description
database	name of database and layout
[query-list]	query list (see below) [optional]

Returns the total number of records that match the query specified using query-list. This function is helpful when used in conjunction with the start-with and max-hits parameters in fmLookup() or fmRetreive() allowing you to retrieve matching records in smaller chunks.

The database name to search is specified by the database parameter. You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, fields which are defined in the database, but are not in the layout specified will not be available.

The query-list parameter is a structured list specifying the type of query to perform. The query-list may contain either a single, simple query or a more complex, multiple field query. See the examples below as a demonstration of creating both simple and complex queries using this parameter.

The query-list parameter should use the structure:

Valid values for compare-op are:

Each of the examples below demonstrate various levels of query complexity and return number of matching records.

Examples:

1. // Return number of records in the "Customers" database
   num_hits = fmCountMatching("Customers");

2. // Return number of records where the field "First Name" 
   // contains "Bob"
   query_list = [["First Name" "contains" "Bob"]];
   num_hits = fmCountMatching("Customers", query_list);

3. // Return number of records where the field "First Name"
   // contains "Rob" or is equal to "Robert"
   query_list = [["First Name" "contains" "Rob"] 
                 "or" ["First Name" "=" "Robert"]];
   num_hits = fmCountMatching("Customers", query_list);

4. // ...where the field "First Name" is "Patrick" and 
   // "Last Name" is "Jordan"
   query_list = [["First Name" "=" "Patrick"]
                 "and" ["Last Name" "=" "Jordan"]];
   num_hits = fmCountMatching("Customers", query_list);

5. // ...where the field "First Name" is "Carl" or "Fred"
   // and the "E-Mail" field ends with "apple.com"
   query_list = [["E-Mail" "ends with" "apple.com"] 
                 "and" [["First Name" "=" "Carl"] 
                        "or" ["First Name" "=" "Fred"]]];
   num_hits = fmCountMatching("Customers", query_list);

See Also:

• fmLookup
• fmRetrieve

Parameter	Description
database	name of database

Returns the total number of records in database.

Example:

num_customers = fmCountRecords("Customers");
print "We have served " & num_customers & " customers!";

Parameter	Description
database	name of database
script-name	name of script

Runs the specified FileMaker script whose name is script-name. The named script must be a valid script already defined in database.

Returns a list of all databases currently open and available for use.

Example:

db_list = fmGetDatabases();
num_databases = sizeOf(db_list);
print num_databases & " databases available: " & db_list;

>> 3 databases available: Catalog Customers Orders

See Also:

• fmGetLayouts

Parameter	Description
database	name of database
field	field name or index number

Returns a structured list containing the definition of the field specifed by field in database. The field parameter may be the field's name or its sequential index number in the database.

The database name is specified by the database parameter. You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, a field which is defined in the database, but is not in the layout specified will not be available.

To get field definitions for all defined fields in a database, refer to fmGetFields().

The returned list has the following structure:

See Also:

• fmGetFields

Parameter	Description
database	name of database

Returns a structured list describing all the defined fields in database.

You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. Specifying a layout will retrieve field definitions from FileMaker that are available in the named layout. However, any fields which are defined in the database, but are not in the layout specified will not be returned.

Each item in the returned list is a sub-list with the following structure:

See Also:

• fmGetFieldInfo

Parameter	Description
database	name of database

Returns a list of the defined layouts in database.

Example:

layout_list = fmGetDatabases("Customers");
num_layouts = sizeOf(layout_list);
print num_layouts & " layouts available: " & layout_list;

>> 3 layouts available: Download Info, Full Record, Mailing Labels

See Also:

• fmGetDatabases

Parameter	Description
database	name of database and layout
record-id	record ID
[return-fields]	list of fields to return [optional]

Given a record ID in record-id, this function will return the contents of the record specified in the database specified. If you do not want all the fields in a record returned, use the optional return-fields parameter to specify which fields to return, and in what order. Including the special field "----" in return-fields will return the record's ID number in the results.

You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. Specifying a layout will retrieve field values from FileMaker as they are formatted in the named layout. However, fields which are defined in the database, but are not in the layout specified will not be available.

Example:

1. record_id = fmGetRecordID("Customers", 1);
   printList(fmGetRecord("Customers", record_id));
   
   >> [1 "Nathan" "Nunn"]

2. record_id = fmGetRecordID("Customers", 1);
   printList(fmGetRecord("Customers", record_id, ["Last Name" "First Name"]));
   
   >> ["Nunn" "Nathan"]

See Also:

• fmGetRecordID
• fmSetRecord

Parameter	Description
database	name of database
record-number	sequential record number

Given a record's sequential number in record-number, the record's internal ID is returned. The name of the database is specified by the database parameter.

Example:

// get the 50th record in "Customers" database
record_id = fmGetRecordID("Customers", 50);
the_customer = fmGetRecord("Customers", record_id);

Parameter	Description
database	name of database and layout
[query-list]	query list (see below) [optional]
[sort-fields]	sort by field(s) [optional]
[start-with]	start with hit number [optional]
[max-hits]	maximum hits to return [optional]

This function is used to search for records in a database that match a given query. A list containing the record ID numbers of all records matching the query is returned. To receive actual field values for any returned record, you must use fmGetRecord.

The database name to search is specified by the database parameter. You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, fields which are defined in the database, but are not in the layout specified will not be available.

The query-list parameter is a structured list specifying the type of query to perform. The query-list may contain either a single, simple query or a more complex, multiple field query. See the examples below as a demonstration of creating both simple and complex queries using this parameter.

The query-list parameter should use the structure:

If you want the returned results sorted, the optional sort-fields parameter may be used to designate a field or fields to sort by. If you wish to sort by multiple key fields, the sort-fields parameter should be a list of sub-lists. If the sort order isn't defined, the default is "ascending".

Each sub-list in the sort-fields parameter should use the structure:

The start-with and max-hits parameters are used to return a subset of the total matching records. For example, if there are 125 matching records (this can be determined using the fmCountMatching() function) and you only want blocks of 25 at a time, set max-hits to 25 and start-with to 25 * the number of the results block to be shown (0 for the first block of 25, 1 for the second block of 25, and so forth). This functionality is very useful when returning the results of large queries in an organized and quick fashion. See the last example below for further reference.

Valid values for compare-op are:

Each of the examples below demonstrate various levels of query complexity and return a list of record ID numbers. To retrieve actual record data, you must use the fmGetRecord() function. To query and receive results using a single function, use fmRetrieve().

Examples:

1. // Return all record IDs in the "Customers" database
   hits = fmLookup("Customers");

2. // Return only records IDs where the field "First Name" 
   // contains "Kathrine"
   query_list = [["First Name" "contains" "Kathrine"]];
   hits = fmLookup("Customers", query_list);

3. // Return all record IDs where the field "First Name" 
   // contains "Kat" or is equal to "Kathy"
   query_list = [["First Name" "contains" "Kat"] 
                 "or" ["First Name" "=" "Kathy"]]
   hits = fmLookup("Customers", query_list);

4. // ...where the field "First Name" is "Ben" and 
   // "Last Name" is "Franklin"
   query_list = [["First Name" "=" "Ben"] 
                 "and" ["Last Name" "=" "Franklin"]];
   hits = fmLookup("Customers", query_list);

5. // ...where the field "City" is "Austin" or "Cupertino" 
   // and the "E-Mail" field ends with "apple.com". The results 
   // are sorted by key field "Last Name" in descending order and 
   // key field "First Name" in ascending order.
   query_list = [["E-Mail" "ends with" "apple.com"] 
                 "and" [["City" "=" "Austin"] 
                        "or" ["City" "=" "Cupertino"]]];
   sort_list = [["Last Name" "descending"] ["First Name"]];
   hits = fmLookup("Customers", query_list, sort_list);

6. // This last example demonstrates how to lookup matches in 
   // batches using start-with and max-hits. The query specifies 
   // all records in the "Customers" database whose last name 
   // starts with "A", as in a directory listing, sorted by the 
   // "Last Name" field.
   page_num = 1;
   max_hits = 25;
   start_with = (page_num - 1) * max_hits;
   query_list = [["Last Name" "starts with" "A"]];
   hits = fmRetrieve("Customers", query_list, "Last Name", 
                     start_with, max_hits);
   
   // In this example, changing the value of page_num to 2 will 
   // return the second page of matches, 3 the third page, and 
   // so forth.

See Also:

• fmCountMatching
• fmGetRecord
• fmRetrieve

Parameter	Description
database-path	full path to a database
[password]	secure password [optional]

Given the full path to a database file in database-path, this function will open the database therefore making it available for use. If the database has been protected by a password within FileMaker, it may be specified using the optional password parameter.

This function does not work correctly when directing a remote copy of FileMaker using the FileMakerLib's auto-login functionality, only when WebSiphon and FileMaker are present on the same machine.

Examples:

1. fmOpenDatabase("Hard Drive:FileMaker Databases:Customers");

2. fmOpenDatabase("Hard Drive:FileMaker Databases:Orders", "kabuki");

See Also:

• fmCloseDatabase

Parameter	Description
database	name of database
record-id	ID of record to remove

Permanently removes the record whose ID is record-id from the database. The name of the database is specified by the database parameter.

Warning: This operation is not reversible!

Example:

// Remove all customers in the database whose last name 
// starts with "a"
the_a_list = fmLookup("Customers", [["lnam" "starts with" "A"]]);
repeat with delete_id in the_a_list
  fmRemoveRecord("Customers", delete_id);
end repeat;

See Also:

• fmAddRecord
• fmGetRecordID

Parameter	Description
database	name of database and layout
[query-list]	query list (see below) [optional]
[return-fields]	list of fields to return [optional]
[sort-fields]	sort by field(s) [optional]
[start-with]	start with hit number [optional]
[max-hits]	maximum hits to return [optional]

This versatile function performs a query much like fmLookup(), but rather than returning record ID numbers, it returns the actual record data. This removes the need to repeatedly get each record to access data returned based on a query.

The database name to search is specified by the database parameter. You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. Specifying a layout will retrieve field values from FileMaker as they are formatted in the named layout. However, fields which are defined in the database, but are not in the layout specified will not be available.

The query-list parameter is a structured list specifying the type of query to perform. The query-list may contain either a single, simple query or a more complex, multiple field query. If query-list is not provided, all record data will be returned. See the examples below as a demonstration of creating both simple and complex queries using this parameter.

Each sub-list in the query-list parameter should use the structure:

Using the optional return-fields parameter specifies which fields will be retrieved from all records matching the query. This is particularly useful for large databases where you are only interested in a few of the fields. The returned list will contain each field requested, in the order specified. Additionally, including the special field "----" in return-fields will return the record's ID number in the results.

If you want the returned results sorted, the optional sort-fields parameter may be used to designate a field or fields to sort by. If you wish to sort by multiple key fields, the sort-fields parameter should be a list of sub-lists. If the sort order isn't defined, the default is "ascending".

Each sub-list in the sort-fields parameter should use the structure:

The start-with and max-hits parameters are used to return a subset of the total matching records. For example, if there are 125 matching records (this can be determined using the fmCountMatching() function) and you only want blocks of 25 at a time, set max-hits to 25 and start-with to 25 * the number of the results block to be shown (0 for the first block of 25, 1 for the second block of 25, and so forth). This functionality is very useful when returning the results of large queries in an organized and quick fashion. See the last example below for further reference.

Valid values for compare-op are:

Each of the examples below demonstrate various levels of query complexity and return actual record data.

Examples:

1. // Return all records in the "Customers" database
   results = fmRetrieve("Customers");

2. // Return only records where the field "First Name" 
   // contains "Abraham"
   query_list = [["First Name" "contains" "Abraham"]];
   results = fmRetrieve("Customers", query_list);

3. // Return all records where the field "First Name" 
   // contains "Abe" or is equal to "Abraham"
   query_list = [["First Name" "contains" "Bob"] 
                 "or" [First Name" "=" "Fred"]];
   results = fmRetrieve("Customers", query_list);

4. // Return all records where the field "First Name" 
   // is "Robert" and "Last Name" is "Smith"
   query_list = [["First Name" "=" "Bob"]
                 "and" ["Last Name" "=" "Smith"]];
   results = fmRetrieve("Customers", query_list);

5. // Return all records where the field "City" 
   // is "Austin" or "Cupertino" and the "E-Mail" 
   // field ends with "apple.com". The results 
   // contain "First Name", "Last Name" and 
   // "E-Mail" sorted.
   query_list = [["E-Mail" "ends with" "apple.com"]
                 "and" [["City" "=" "Austin"]
                        "or"
                        ["City" "=" "Cupertino"]]];
   return_fields = ["First Name" "Last Name" "E-Mail"];
   sort_list = [["Last Name" "descending"] ["First Name"]];
   results = fmRetrieve("Customers", query_list, return_fields, 
                        sort_list);

6. // This last example demonstrates how to display results 
   // in batches using start-with and max-hits. The query specifies 
   // all records in the "Customers" database whose last name starts 
   // with "M", as in a directory listing. The results contain the 
   // values of fields used to form a mailing address sorted by the 
   // "Last Name" field.
   page_num = 1;
   max_hits = 25;
   start_with = (page_num - 1) * max_hits;
   return_fields = ["First" "Last" "City" "State" "Postal Code" 
                    "Country"];
   results = fmRetrieve("Customers", [["Last" "starts with" "M"]], 
                                     return_fields, "Last Name", 
                                     start_with, max_hits);
   
   // In this example, changing the value of page_num to 2 will 
   // return the second page of matches, 3 the third page, and 
   // so forth. 

See Also:

• fmCountMatching

Parameter	Description
database	name of database
record-id	record ID
field-values	list of field/value pairs

Modifies the value of one or more fields in the record whose ID is record-id. The field-values parameter is a list of field name and value pairs which are to be modified in the record. The name of the database is specified by the database parameter.

You may optionally specify a layout, with the database parameter as a list in the format [ database-name layout-name ]. However, fields which are defined in the database, but are not in the layout specified will not be available.

Example:

fmSetRecord("Users", rec_id, [["Password" new_password]]);

See Also:

• fmAddRecord