Chapter Fifteen

Using Basic Database Actions

Search, Insert, Update, and Delete Actions

Tango includes several fundamental database actions that allow you to search (Search action), add (Insert action), modify (Update action), and delete (Delete action) database records.

The topics covered in this chapter include setting up and executing Search, Insert, Update, and Delete actions.

[back to top]

Searching a Database

Search actions retrieve database records matching a given criteria.

You use the Search action editing window to define what columns are selected, the order of the data retrieved, and the criteria that determine which rows are found.

Tip: The SQL Query window gives you a convenient way to look at your database values. Choose SQL Query from the Windows menu or from the contextual menu that appears when you control+click the Search action editing window. For more information, see "The SQL Query Window".

You use the action's Results HTML to format the results of the search.

Setting Up a Search Action

When you drag the Search action icon from the Actions palette into an application file, the Search action editing window appears. The window consists of four sections. The Select, Criteria, and Results sections are covered in this chapter. The Joins section is covered in Working With Joins.

Select Tab

You use the Select section to select the type of search to perform, the columns to retrieve, and the ordering of the records returned.

You can perform three types of searches with a Search action: Normal, Summaries of Groups, and Summary of All Rows.

Select which type of search you want to perform from the Select Type pop-up menu .

• Normal returns rows matching specified criteria.
• Summaries of Groups returns summaries of rows whose values in given columns (the grouping columns) are the same.
• Summary of All Rows returns a single row summarizing all rows matching your criteria. This kind of search lets you get information such as the maximum or average value of a particular column in a database table.

Note: FileMaker Pro data sources support only the Normal search type, so the Search Type pop-up menu does not appear when you are using this data source type.

Normal Search

The Normal type of search returns rows matching specified criteria. This is the most common type of search.

When you select Normal from the Select Type pop-up menu, the Select section appears.

Specify values for the parameters in the Select section:

• Select Columns. Drag into this list from the Data Sources Workspace the columns whose data is to be retrieved from the database.

For more information, see "Joining Database Tables"..

You can include columns from multiple tables; if you do, you must define joins to describe how the tables are related.

---

Note: For best results when using a FileMaker Pro data source, it is recommended that you use "Layout 0" whenever possible.

---

• Order By Columns. Drag into this list the columns that are used to sort the results returned to the user. Ordering by columns is optional.

  The order of the columns in the list determines the ordering hierarchy. For example, if the first order column is "state or province" and the second "customer name", the results are first ordered by state or province; customers in the same state or province are then ordered by name.

  The triangle to the left of the column name determines whether the ordering is ascending or descending. To change the order direction for a column, click the triangle.

Summaries of Groups

The Summaries of Groups search type returns summaries for groups of rows with the same values in specified columns. For example, it allows you to find out the total sales for each sales region in an invoices table by selecting the sum of invoice amounts and grouping by sales region.

When you select Summaries of Groups, the Select section appears.

• Select Columns. Drag into this list the columns you want to select. Select columns for this select type have an associated function. This function is performed on the column for all the rows in a particular group, as determined by the Group By Columns list. For example, if you selected the MAX function for a "price" column and group by the "classification" column, you would receive one row for each unique classification. Each row would contain the maximum value of the "price" column for the classification being summarized.

  The following table lists the available functions:

  Function	Description
  MAX	The maximum value of column in the group.
  MIN	The minimum value of column in the group.
  AVG	The average value of column in the group. Valid only for numeric columns.
  SUM	The sum of all column values in the group. Valid only for numeric columns
  COUNT	The number of non-null values in the column for the group.
  None	Perform no function; return the value of the column for each group. Columns with this option must appear in the Group By Columns list, because only group columns are sure to have the same value within a group.

  To choose the function for a column, click the Function column and select the function from the pop-up menu.

• Order By Columns. As with the normal select type, you specify in this list the ordering of results. You can drag columns from the Data Sources Workspace or from the Select Columns list. You can order only by columns appearing in the Select Columns list.

• Group By Columns. The columns in this list determine how rows are grouped before being summarized. Groups consist of all the rows that have the same values in the columns specified. For example, if you group by the "cust_state" and "cust_rep" columns in a customer table, you get one summary row for each group of rows with the same values in the "cust_state" and "cust_rep" columns.

• Show Group Criteria. Normally, all the summary rows are returned for records matching the user's criteria. You can eliminate summary rows by specifying group criteria. The group criteria have a different function from the regular criteria in that the regular criteria specify which rows are eligible for grouping, while the group criteria specify which summary rows are returned.

  ---

  Note: The group criteria section is equivalent to the HAVING clause in a SQL SELECT statement.

  ---

  For example, if you are grouping by classification and selecting the maximum order amount, you can use group criteria to limit the returned rows to those customers whose maximum order amount is greater than $5,000.

  To specify group criteria, click Show Group Criteria.

  The Select section expands to show the area for entering group criteria.

  Drag columns from either the Data Sources Workspace or the Select Columns list.

  ---

  Note: You can specify only columns that appear in the Select Columns list.

  ---

For more information, see "Criteria Tab".

Except for the function option, you specify group criteria just like normal criteria.

Summary of All Rows

To get a summary of all rows matching a specified criteria, use the Summary of All Rows search type. Only one summary row is returned. For example, you could use this search type to find the average amount of all orders in an orders table.

As with the Summaries of Groups search type, each select column has an associated function that determines how that column is summarized. All the column values in the rows matching the criteria are aggregated using the specified function.

Criteria Tab

The Search action criteria determine which rows from the database are returned by the action. If no criteria are specified, all rows are returned; otherwise, each row in the database is compared to your criteria and only those meeting them are returned .

To specify the criteria, drag columns from the Data Sources Workspace to the Criteria list. For each column, you must specify:

• Logical Operator
• Column
• Operator
• Value
• Include Empty
• Quote Value.

Note: To use the standard criteria specification with a FileMaker Pro data source, make sure the Shown Below radio button is active.

Column

In the Column field, specify the column whose value you want to compare. Drag the column from the Data Sources Workspace.

Logical Operator

The first field in the criteria list is the logical operator, which is either and or or. To specify the operator, do one of the following:

• Click the row, then click the field to display a pop-up menu and choose an operator.
• Control+click the field, and choose Edit from the contextual menu. Then choose an operator from the pop-up menu.

Note: You must specify at least two columns before the logical operators are available.

The logical operator determines whether the current and previous criteria must be true for a record to be included in the result or whether a match on either the current or previous criterion causes a record to be included in the result. For example, if your criteria are:

     cust_num   =             5100
and  cust_name  Begins with   A

only records matching both criteria are returned. If the logical operator is changed to or, records matching either one of the criteria are returned.

There is an implied order of operation for logical operators. Criteria joined with the and logical operator are evaluated before those joined with the or logical operator.

For example, in the following criteria:

     cust_num   =             5100
and  cust_name  Begins with   A
and  cust_state =             NY

a match is made if both the second and third criteria are true or the first criterion is true.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can also use the Insert Meta Tag command to enter in the Criteria window entry fields many of the commonly used meta tags.

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or control+click the field and choose Insert Meta Tag from the contextual menu that appears.

For more information, see "Criteria Separators".

You can use criteria separators to control the order of criteria evaluation, regardless of this default logical operator hierarchy.

Operator

In the operator field (Oper.), specify the operator to use when comparing the field by doing one of the following:

• Click the row, then click the field to display a pop-up menu to choose an operator from.
• Control+click the field, and choose Edit from the contextual menu. Then choose an operator from the pop-up menu.

Possible operators include:

Note: The Is Null and Is Not Null operators are not available for FileMaker Pro data sources.

Text columns permit the use of any operator; for other columns, the Contains, Begins with, and Ends with operators are disabled.

You can either specify a static operator or insert a meta tag to get the value at execution time. Using a variable operator allows you, for example, to put a pop-up menu on your Web page to let users choose the comparison operator.

When using a variable to specify the criterion operator, Tango requires you to use special values to represent each of the operators. The following table lists these special values:

Note: The Is Null and Is Not Null operators are not available for FileMaker Pro data sources.

For example, to create an operator pop-up menu in an HTML form whose value you want to use as the operator in a search criterion, you could use HTML similar to the following:

<SELECT NAME="cust_name_op" SIZE=1>
<OPTION VALUE = "iseq" SELECTED>=
<OPTION VALUE = "isnt">!=
<OPTION VALUE = "gthn">&gt;
<OPTION VALUE = "lthn">&lt;
<OPTION VALUE = "gteq">&gt;=
<OPTION VALUE = "lteq">&lt;=
<OPTION VALUE = "swth">Begins With
<OPTION VALUE = "ewth">Ends With
<OPTION VALUE = "cont">Contains
</SELECT>

and set the operator in the Search action to

<@ARG cust_name_op>

The Is In operator needs some additional explanation. It matches records where a column value is in a list of values.

For example, the following criteria:

cust_num Is in 200, 300, 400

matches records in which the cust_num field has a value of 200, 300, or 400. The Is in operator can be thought of as a shortcut for a series of OR equals criteria:

cust_num = 200
or cust_num = 300
or cust_num = 400

The value specified can be a single-column or single-row array (as would be returned by the <@ARG> tag with a type attribute of ARRAY, for example) or a comma-separated list of values.

Value

In the Value field, enter the value to use in the comparison.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

The value can also contain any value-returning Tango meta tags, which are substituted when the application file is executed. Use the Insert Meta Tag command to enter many of the commonly used meta tags.

Include Empty

In the Incl. Empty field, specify whether the criterion is included, even if the comparison value is empty, by doing one of the following:

• Click the row, then click the field to display a pop-up menu to choose a value from.
• Control+click the field, and choose Edit from the contextual menu. Then choose an operator from the pop-up menu.

The values appear as false and true.

false omits the criterion if the value (after meta tag substitution) is empty; true includes the criterion regardless of the value's contents.

This option is used mainly for columns whose search value is taken from a search form on a Web page. For example, you may have a search form that allows you to enter search values for several columns, but you want the search done only on the columns you enter values for. To do this, set the Incl. Empty option for each of the corresponding Search action criteria to false.

There are cases where you do want a criterion included, even if the value is empty. For example, suppose you have a Web page that asks for a user name and password, and a corresponding Search action that finds the user in a users' database. In the Search action, you probably want to set the Incl. Empty option for each of the values to true. If you do not, and the user leaves both fields empty, the Search action omits both criteria and returns all user records.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can control+click the Incl. Empty field, and choose Insert Meta Tag from the contextual menu that appears to enter many of the commonly used meta tags.

Quote Value

In the Quote Value field, specify whether Tango puts quotation mark characters around the value in the SQL it generates for this criterion by doing one of the following:

• Click the row, then click the field to display a pop-up menu to choose a value from.
• Control+click the field, and choose Edit from the contextual menu. Then choose an operator from the pop-up menu.

The values appear as false and true.

For text, date, time, and timestamp columns, you should set this option to true. For date, time, and timestamp columns, this option has special meaning. true converts the specified value from the default Tango format to the format required by the database server; false passes the value specified as is to the database server.

If you want to use an expression that the database server evaluates (instead of a literal Tango-supplied value), set the Quote Value option to false and enter the expression in the Value field.

For numeric and Boolean types, you should set the Quote Value option to false.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can control+click the Quote Value field, and choose Insert Meta Tag from the contextual menu that appears to enter many of the commonly used meta tags.

Note: FileMaker Pro data sources support neither the Quote Value option nor conversion of date and time values.

Criteria Separators

To group criteria, select the position between the criteria you want to group and do one of the following:

• From the Edit menu, choose Insert Criteria Separator.
• Xontrol+click and choose Insert Criteria Separator from the contextual menu that appears.

Only the logical operator cell can be edited for separator items .

Upon execution, the criteria before the separator are combined with the criteria after the separator using the logical operator specified in the separator line in the criteria list.

Executing a FileMaker Pro Script in a Search Action

If you are using a FileMaker Pro data source with your Search action, you can execute a FileMaker Pro script in the Criteria section. (The Criteria section is different for FileMaker Pro data sources; it contains a field where you can enter the name of a FileMaker Pro script.)

To use a FileMaker Pro script

You can also execute a FileMaker Pro script directly in a Direct DBMS action; see "Executing a FileMaker Pro Script (Direct DBMS Action)".

For more information on FileMaker Pro scripts, consult your Claris FileMaker Pro documentation.

1. Select the records to be returned.
2. Click the Found set after script execution radio button and choose a FileMaker Pro script from the pop-up menu.

The scripts in the pop-up menu are the scripts that are defined in the FileMaker Pro data source you are connected to.

The script to be executed can also be specified by entering the name manually in the field provided.

The script you specify executes when the Search action is executed and the found set at the end of the script's execution determines which records are returned.

When using a FileMaker Pro script to print, your script must first bring FileMaker Pro to the front. FileMaker Pro does not print if it is in the background (even if the "Perform without dialog option" is set for the command). Use a Perform AppleScript command in the ScriptMaker and specify activate application "FileMaker Pro 3.0" (replace the value in quotes with the exact name of your FileMaker Pro application) as the script text. When printing is complete, restore your Web server to the foreground by using the same command, but with the name of the Web server application.

Results Tab

In the Results section, you specify the maximum number of records to retrieve from the data source, at which result record number retrieval begins, and whether Tango gets the count of matching records .

Number of rows to retrieve

To return all matching records, select No Maximum.

To limit how many records you want the search to return, select Limit To and enter the maximum number of records to retrieve.

Start retrieval at row number

Select this option if you want to skip some of the matching records. Specify the row number you want the Search action to start retrieval at. The default is "1". When the value is other than "1", the Search action returns records starting at that number, skipping any records before it.

This option is most useful when you use a variable (such as <@SEARCHARG start>) for the starting record number.

For more information, see "Show Multiple Pages If Limit Exceeded"..

For an example of how to use this option to provide results paging for large result sets, look at the Search action in a Search Builder-generated file created with the Show Multiple Pages If Limit Exceeded option selected.

Retrieve distinct rows only

If you select this option, Tango Server adds the DISTINCT keyword after the SELECT keyword in the generated SQL. The DISTINCT keyword specifies whether duplicate rows are to be eliminated from the result set. For example,

SELECT c1.cust_state, c1.cust_zip
FROM customer c1;

becomes

SELECT DISTINCT c1.cust_state, c1.cust_zip
FROM customer c1;

Get total number of matching rows

You use this option to retrieve the number of records matching the search criteria, irrespective of how many records are actually retrieved.

Using this option, you can access the value in the Search action's Results HTML by using the <@TOTALROWS> meta tag.

Note: Selecting this option involves an extra database operation, so unless you require the information it provides, do not select it.

Executing a Search Action

When Tango Server executes a Search action, the search is performed against the associated data source. The result rowset is automatically stored as an array in the local variable resultSet. The Results HTML for the Search action is then processed.

For more information, see "<@ROWS> </@ROWS>", "<@COL>", and "<@COLUMN>" in the Meta Tags and Configuration Variables manual.

The HTML in the <@ROWS><@/ROWS> block, if any, is processed once for each record in the results. Use <@COLUMN> or <@COL> meta tags to include field values.

If the Search action generates no results, and you have specified No Results HTML for the action, that HTML is processed instead of the Results HTML.

[back to top]

Adding Records to a Database

The Insert action adds a record to a table in a database.

Setting Up an Insert Action

When you drag the Insert action icon from the Actions palette into an application file, the Insert action editing window appears.

To set up an Insert action

1. From the Data Sources Workspace, drag into the Column list the columns whose values you want to set in the new record.

   ---

   Note: You can select columns from only one table.

   ---

   If you do not add all of the table's columns to the Insert action, the omitted columns are given the default values defined when the database was created.

2. In the Value field for each column, enter the value for that column in the new record. The value can contain any of the value-returning Tango meta tags, which are substituted upon execution of the application file.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or control+click the field and choose Insert Meta Tag from the contextual menu that appears.

For more information, see "Quote Value".

The Quote Value option operates the same as it does in search criteria.

Executing an Insert Action

When Tango Server executes an Insert action, a record is added to the database with the column values specified. The Insert action returns no results.

[back to top]

Modifying a Database Record

The Update action modifies database records matching specified criteria.

Setting Up an Update Action

When you drag the Update action icon from the Actions palette into an application file, the Update action editing window appears.

To set up an Update action

For more information, see "Criteria Tab".

1. In the criteria list at the top of the action's editing window, specify which records you want to update.

   You edit the criteria list the same way you edit the Search action's criteria list.

   ---

   Caution: For an Update action, be extremely careful when setting the Incl. Empty option to false. You may end up affecting more rows than you intend, possibly even updating all the records from your database table. Just like leaving Incl. Empty set to false in a Search action returns all the records, leaving it set to false in an Update action updates all records.

   ---

2. From the Data Sources Workspace, drag the columns whose values you want to update into the update columns list at the bottom of the action's editing window.

   ---

   Note: You can specify columns from only one table. If you want to update multiple tables, use an Update action for each table. In this case, consider using a Transaction action to make sure all or none of the updates are processed.

   

   Update actions using FileMaker Pro data sources allow fields from any number of layouts.

   ---

   Only the values in the columns you specify are modified when the action is executed.

3. Under Value for each column, enter the new value for that column.

   The value can contain any of the value-returning Tango meta tags, which are substituted upon execution of the application file.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or control+click the field and choose Insert Meta Tag from the contextual menu that appears.

If you always want to update a column with a fixed value, simply enter that value.

For more information, see "Quote Value".

The Quote Value option operates in the same way it does in search criteria.

Note: Quote Value does not apply to FileMaker Pro data sources.

Executing an Update Action

When Tango Server executes an Update action, Tango searches for records matching the specified criteria and updates them with the specified column values.

The Update action returns no results.

[back to top]

Removing a Database Record

For more information, see "Criteria Tab"..

The Delete action removes database records that match the specified criteria. You edit the criteria list the same way you edit the Search action's criteria list.

Setting Up a Delete Action

To set up a Delete action

1. Drag the Delete action icon from the Actions palette into an application file.

   The Delete action editing window appears.

2. In the criteria list of the Delete action's editing window, specify which records you want to delete.

Note: You can specify columns from only one table. If you want to delete multiple tables, use a Delete action for each table. In this case, consider using a Transaction action to make sure all or none of the deletes are processed.

Delete actions using FileMaker Pro data sources allow fields from any number of layouts.

You must specify at least one criterion for the Delete action to be valid.

Caution: For a Delete action, be extremely careful when setting the Incl. Empty option to false. You may end up affecting more rows than you intend, possibly even deleting all the records from your database table. Just like leaving Incl. Empty set to false in a Search action returns all the records, leaving it set to false in a Delete action deletes all records.

Executing a Delete Action

When Tango Server executes a Delete action, records matching the specified criteria are deleted.

The Delete action returns no results.

[back to top]

Adding Custom Columns to Database Actions

A custom column entry lets you enter any text as the column reference. You can use custom columns wherever Tango accepts columns dragged from the Data Sources Workspace.

Make sure the text entered makes sense in the database action. For example, in a Search action, you could enter the following calculation as a Select column:

orders.order_amt + 20

To add a custom column to a database action

1. Do one of the following:
   • From the Edit menu, choose Insert Custom Column.
   • Control+click any database action editing window where you can add columns, and choose Insert Custom Column from the contextual menu that appears.

   The Custom Column Entry dialog box appears.

2. Enter the text to use as the column reference.

   You can insert meta tags here.

3. Click OK.

   Custom columns can be edited later by double-clicking the column reference in the list.