FileMakerの集計は、便利に使えるときもありますが、ちょっと柔軟性が足りないと思うときもありなかなか使いこなすのが難しい機能でもあります。ここで説明するのは、集計結果をテキストファイルで出力したり、JSONで得るなどの処理をするための基本的な方法です。FileMakerのスクリプトだけで実現でき、十分に速く処理できる方法なので、記事として公開してみました。FileMakerの基本や集計の一般的なことをご存知という前提で、この記事は進めます。
まず、背景ですが、実際のデータベースで説明します。次のような、タイムスタンプと「金額」のフィールドがあるようなデータベースがあるとします。話を簡単にするために、ごくシンプルなデータベースで説明します。「年月」フィールドは計算フィールドで、「日時」フィールドより求めています。この先やりたいことは、『月ごとの金額の合計を求める』ということです。ということで、金額フィールドの合計を求める集計フィールド「金額の合計」も定義しました。
ここから、新しいレイアウトを作ります。詳細は省略しますが、年月フィールドでソートした場合に表示される小計パートをボディパートの上に設置し、そこには「年月」および「金額の合計」フィールドを配置します。以下の図では、小計パートは薄いグレーの背景です。白背景がボディパートで、「年月」「金額」フィールドが配置されています。一応、全レコードを表示した上でソートして、以下のように集計が出ています。
ここで、「各月の集計結果だけが欲しい」という要求が発生しました。もちろん、上記レイアウトのボディパートを消せばいいですね（消さないで見えなくすることができればいいのにと思いながら）。消したら、もちろん、小計パートだけが見えます。
これでOKと思ったら、「この画面の結果をテキストファイルで欲しい」という要求がさらに出ました。最も、最初から集計結果のテキストだけ欲しいというのなら、集計フィールドを使わないで、FileMaker以外の手段を考えるかもしれませんが、集計レイアウトを作る上で、さらにテキストも欲しいということになったとしてください。
では、このままエクスポートしても、３行のテキストファイルは作られません。エクスポートの中身はボディパートに展開された集計元のレコードです。もちろん、「金額の合計」フィールドには金額の合計はあるのですが、1ヶ月分でも何百のレコードがありそうです。手で消すのもちょっと無理があります。
もしかしたら、以下のようなスクリプトでなんとかなるでしょうと思う人もいるかもしれません。つまり、レイアウトで最初のレコードから最後のレコードまでグルグル回してフィールド値を変数に取り込むのです。
このスクリプトを実行した結果、$result変数は、こんな感じ。多分、レコード数の1000だけあって、最初のいくつかだけが見えています。細かいことはともかく、レイアウトに見える結果と大きく違います。
この方法でのスクリプトは、ボディパートを順番に舐めて行くのであって、小計パートは関係ありません。残念ながら小計パートを選択するということがFileMakerではできないのであって、この方法ではどうすることもできません。もっとも、「年月」フィールドの変わり目を…とやったらできるのでしょうけど、この方法で、数万くらいのレコードになると、そこそこ時間がかかります。データが多いと現実的ではありません。
しかしながら、ちょっとした発想の転換で、同じような方法ながら、小計に見えている数値だけを取り出すスクリプトが書けました。次のようなスクリプトで、結果として見えるダイアログボックスも紹介します。ダイアログボックスは、集計レイアウトの小計パートに見える「金額の合計」フィールドの数値が見えています。
ここでは改行付きのテキスト（リスト形式）で取り出しましたが、変数をそのままテキストファイルに書き出すこともできますし、JSON形式で取り出すということもできます。とにかく、小計パートでのフィールドあるいは集計フィールドだけを取り出せるということがポイントなので、細かくはソリューションに合わせて対処してください。
このスクリプトのポイントは、「対象レコードの絞り込み」スクリプトステップを利用するということです。なお、レコードが0になるまで省略を続けるので、「エラー処理」スクリプトステップで、「レコードがない」というエラーを出ないようにしておきます。なお、前述のスクリプトは、最初からレコードがない場合にはいい感じでは動いてくれないと思いますので、データがあること前提です。デバッガでステップ動作していただければ大体わかると思いますが、このスクリプトは次のような流れを想定しています。
- 先頭のレコードを選択する。これは「年月」フィールドの最初のグループ（202503）に属するレコードなので、そのレコードの「金額の合計」フィールドは集計されて、202503の合計金額（49734287797）が得られる
- 取り出したレコードの所属するグループのレコードを全部省略する。つまり、ここでは年月が202503のレコードを省略する。そのために「対象レコードの絞り込み」スクリプトステップを利用する
- 最初のレコードに移動すると、レイアウトでは2つ目に見えていた「年月」フィールドが202504のグループのレコードになる。「金額の合計」フィールドから、202504の金額の合計値が得られる
- レコードの絞り込み後レコードがなくなるまで2,3を続ける
レコードを移動するのは諦めて、先頭のレコードから取り出せるものをうまく利用します。データを取り出したら、そのグループを集計対象外、つまり省略してしまうのです。いろんなやり方はあるでしょうけど、「対象レコードの絞り込み」スクリプトステップが便利です。変数$ymに現在の「年月」フィールドの値を入れておいて、検索条件を組み立てます。このように必ず6桁の数値になるようなフィールドの検索では==をつけるのはちょっとやりすぎかもしれませんが、念の為ということで。ポイントは、処理として「レコードを対象外に」を選択することです。つまり、条件に合ったレコードを対象外にして、集計対象から排除するのです。
このような処理は非常に遅いのでは無いかと危惧するかもしれませんが、結構早いです。集計時に一旦レコードを全部ロードしていて、対象外にする処理では、メモリの移動や増加、ネットワーク処理などは入らないからでしょう。すぐに終わります。10万件で試してみても、数秒もかからずに終わりました。
小計パートが2つある場合でも、同様にできました。ただし、レコードを対象外にする場合には、2つの小計パートのソート対象フィールドに対して、ANDで検索をかけます。ということで、ちょっとずつレコードを省略して最初のレコードの集計値を取り出せば良いので、小計パートがいくつになっても大丈夫です。
ちなみに、レコードの省略では、対象レコードが増えないので、ソート結果はそのまま保持されるという動作もこの方法での集計結果の取得を実現する仕様の1つです。
集計結果だけのテキストを得るにはいろんな方法がありますが、この方法は、FileMakerの中だけで完結しているというところが大きなポイントでしょう。外部にCSVを出すなどして処理するのは別システムの管理がかえって面倒になりますし、後々のメンテナンスを考えれば、システム基盤はシンプルな方が良いということになります。ちなみに、この仕組みがどんなところで必要になったかと言うと、複数の異なるテーブルの集計結果を1つの表にまとめたかったからです。ただ、その纏める部分は色々考えて、集計結果をJSONで得て、WebビューでJavaScript処理で表を作るということで解決しました。
テストしたデータベースも公開します。レコードは10万くらいにしてあります。あまり、使い勝手は良くないですが、ご了承ください。
