ExecuteSQLで入力逐次検索 |

ExecuteSQL関数で利用できるSelect文のメリットは、検索対象のテーブルやそのリレーション、フィールド、検索条件などを動的に組み立ててその場ですぐに実行できるところにあります。
今回はExecuteSQL関数を使って入力逐次検索の機能を簡単に実装する方法を説明した、Soliant ConsultingのMike Duncanさんの記事をご紹介します。

Search as You Type Using ExecuteSQL

(元記事はこちら)

Mike Duncan
2019/1/7

ExecuteSQL関数は、FileMakerの他の関数とは異なるとてもユニークな機能です。FileMaker 12で初めて導入され、FileMakerの従来の「検索」機能とは異なり、FileMakerデータに対してSQLのSELECTクエリを実行することができます。

SQL (Structured Query Language)は、FileMaker以外の一般的なデータベースサーバで使われている標準です。SQLを使用することで、索引付きのデータに対してSELECTを高速に効率よく実行することができます。アプリケーションで定義されていないリレーションをSQLクエリ内で組み立てることもできます。

この関数の導入以来、役に立つテクニックがいくつも紹介されてきました。この記事では、ExecuteSQL関数を使ったテクニックの一つである「入力逐次検索」について詳しく説明します。

入力逐次検索のテクニック

この例では、グローバルフィールドにキー入力ごとに起動するスクリプトトリガを設定します。スクリプトがExecuteSQLを実行して、検索対象のテーブル内の関連するレコードのIDリストを作成します。取得したIDリストをグローバルフィールドに入れると、それがマルチキーのリレーションとして機能して、関連レコードをポータルに表示します。簡単だと思いませんか?

ポイントは、検索に使うSQLを動的に組み立てる点にあります。ただし、これはすべてスクリプトで処理されるので、このソリューションを使用するのにSQLを理解する必要はありません。検索するフィールドのリストを動的に生成することによって、入力された検索語で必要な数のフィールドに対して検索ができます。

最後に、カンマで区切って複数の検索語を入力できるようにすることで、すべてのフィールドに対して複数のクエリで検索して入力した結果を絞り込むクエリを作成できます。サンプル画面を以下に示します。

複数の検索語をカンマで区切って入力

ステップ1: フィールドリストを作成する

はじめに、検索するフィールドのリストが必要です。幸い、FileMakerの内部テーブルを使ってテーブルスキーマを参照することができます。たとえば、次のSQLはリレーションシップグラフに表示されるすべてのテーブルオカレンスを返します。

SELECT * FROM FileMaker_Fields

検索するフィールドのタイプは、LIKE演算子が使えるという点で、テキスト型が適しています。対応するSQLのフィールド型は“varchar”です。データベース内のテキストフィールドのみのリストを返すには、SQL句に”WHERE FieldType = ?”を追加して、パラメータとして”varchar”を指定します。さらに”FieldClass”が”Normal”と一致するフィールドのみを検索するよう制限をかけます。これにより、グローバルフィールドや集計フィールドを除外したテキストフィールドのリストを得ることができます。

サンプルファイル内のこのテクニックでは、繰り返しフィールドには対応していません。対応させることもできますが、一般的には繰り返しフィールドの使用は避けるべきです。

サンプルコードでは、移植性を考慮してテーブル名を抽象化しています。自身のソリューションに合わせて変更を加える場合は、変数$get.tablenameを更新して、検索対象のテーブルを設定します。

ステップ2: 検索リクエストを作成する

検索対象フィールドのリストができたので、次にExecuteSQL関数に必要な対応するパラメータのリストを作成します。ExecuteSQL関数に使用する式を組み立てることによって、変数を使用してSQL文を構築してからEvaluate関数を使用して実行するのが少し簡単になります。

次に、検索対象の各フィールドに対応する検索パラメータを定義します。その結果として$this.fieldsと$this.paramsの2つの変数に値を設定します。

またSQLクエリでは大文字と小文字が区別されるため、LOWER関数を使用してすべての検索リクエストをSQLで両方とも小文字にします。FileMakerのLower関数も使用します。SQLでLOWERを使用することで、FileMakerが検索対象のすべてのフィールドに自動的に索引を作成するのを防ぎます。

結果として必要なフィールドはIDフィールドのみで、これはテーブルの主キーです。主キーのリストを取得したら、それらを一時的にグローバルのテキストフィールドに格納し、それと検索対象テーブルに多対多のリレーションを設定して、標準的なポータルに結果を表示します。

ExecuteSQL用の式が組み立てられたら、最後にEvaluate関数を使って実行します。

ステップ3: 複数の検索パラメータ

SQL式を動的に生成するので、複数の検索パラメータの入力にも対応させることができます。具体的には、入力した検索パラメータごとに”AND”クエリを実行します。

コンマは、検索文字の区切りとして定義されています。たとえば、いずれかのフィールドに”Chicago”を含み、かついずれかのフィールドに”IL”を含むレコードを検索する場合に、”Chicago, IL”と入力します。クエリは、すべてのフィールドに対して”LIKE %chicago%”と”LIKE %il%”で検索を実行し、一致したレコードを返します。