Blog by msyk

FileMakerの集計は、便利に使えるときもありますが、ちょっと柔軟性が足りないと思うときもありなかなか使いこなすのが難しい機能でもあります。ここで説明するのは、集計結果をテキストファイルで出力したり、JSONで得るなどの処理をするための基本的な方法です。FileMakerのスクリプトだけで実現でき、十分に速く処理できる方法なので、記事として公開してみました。FileMakerの基本や集計の一般的なことをご存知という前提で、この記事は進めます。

まず、背景ですが、実際のデータベースで説明します。次のような、タイムスタンプと「金額」のフィールドがあるようなデータベースがあるとします。話を簡単にするために、ごくシンプルなデータベースで説明します。「年月」フィールドは計算フィールドで、「日時」フィールドより求めています。この先やりたいことは、『月ごとの金額の合計を求める』ということです。ということで、金額フィールドの合計を求める集計フィールド「金額の合計」も定義しました。

ここから、新しいレイアウトを作ります。詳細は省略しますが、年月フィールドでソートした場合に表示される小計パートをボディパートの上に設置し、そこには「年月」および「金額の合計」フィールドを配置します。以下の図では、小計パートは薄いグレーの背景です。白背景がボディパートで、「年月」「金額」フィールドが配置されています。一応、全レコードを表示した上でソートして、以下のように集計が出ています。

ここで、「各月の集計結果だけが欲しい」という要求が発生しました。もちろん、上記レイアウトのボディパートを消せばいいですね（消さないで見えなくすることができればいいのにと思いながら）。消したら、もちろん、小計パートだけが見えます。

これでOKと思ったら、「この画面の結果をテキストファイルで欲しい」という要求がさらに出ました。最も、最初から集計結果のテキストだけ欲しいというのなら、集計フィールドを使わないで、FileMaker以外の手段を考えるかもしれませんが、集計レイアウトを作る上で、さらにテキストも欲しいということになったとしてください。

では、このままエクスポートしても、３行のテキストファイルは作られません。エクスポートの中身はボディパートに展開された集計元のレコードです。もちろん、「金額の合計」フィールドには金額の合計はあるのですが、1ヶ月分でも何百のレコードがありそうです。手で消すのもちょっと無理があります。

もしかしたら、以下のようなスクリプトでなんとかなるでしょうと思う人もいるかもしれません。つまり、レイアウトで最初のレコードから最後のレコードまでグルグル回してフィールド値を変数に取り込むのです。

このスクリプトを実行した結果、$result変数は、こんな感じ。多分、レコード数の1000だけあって、最初のいくつかだけが見えています。細かいことはともかく、レイアウトに見える結果と大きく違います。

この方法でのスクリプトは、ボディパートを順番に舐めて行くのであって、小計パートは関係ありません。残念ながら小計パートを選択するということがFileMakerではできないのであって、この方法ではどうすることもできません。もっとも、「年月」フィールドの変わり目を…とやったらできるのでしょうけど、この方法で、数万くらいのレコードになると、そこそこ時間がかかります。データが多いと現実的ではありません。

しかしながら、ちょっとした発想の転換で、同じような方法ながら、小計に見えている数値だけを取り出すスクリプトが書けました。次のようなスクリプトで、結果として見えるダイアログボックスも紹介します。ダイアログボックスは、集計レイアウトの小計パートに見える「金額の合計」フィールドの数値が見えています。

ここでは改行付きのテキスト（リスト形式）で取り出しましたが、変数をそのままテキストファイルに書き出すこともできますし、JSON形式で取り出すということもできます。とにかく、小計パートでのフィールドあるいは集計フィールドだけを取り出せるということがポイントなので、細かくはソリューションに合わせて対処してください。

このスクリプトのポイントは、「対象レコードの絞り込み」スクリプトステップを利用するということです。なお、レコードが0になるまで省略を続けるので、「エラー処理」スクリプトステップで、「レコードがない」というエラーを出ないようにしておきます。なお、前述のスクリプトは、最初からレコードがない場合にはいい感じでは動いてくれないと思いますので、データがあること前提です。デバッガでステップ動作していただければ大体わかると思いますが、このスクリプトは次のような流れを想定しています。

1. 先頭のレコードを選択する。これは「年月」フィールドの最初のグループ（202503）に属するレコードなので、そのレコードの「金額の合計」フィールドは集計されて、202503の合計金額（49734287797）が得られる
2. 取り出したレコードの所属するグループのレコードを全部省略する。つまり、ここでは年月が202503のレコードを省略する。そのために「対象レコードの絞り込み」スクリプトステップを利用する
3. 最初のレコードに移動すると、レイアウトでは2つ目に見えていた「年月」フィールドが202504のグループのレコードになる。「金額の合計」フィールドから、202504の金額の合計値が得られる
4. レコードの絞り込み後レコードがなくなるまで2,3を続ける

レコードを移動するのは諦めて、先頭のレコードから取り出せるものをうまく利用します。データを取り出したら、そのグループを集計対象外、つまり省略してしまうのです。いろんなやり方はあるでしょうけど、「対象レコードの絞り込み」スクリプトステップが便利です。変数$ymに現在の「年月」フィールドの値を入れておいて、検索条件を組み立てます。このように必ず6桁の数値になるようなフィールドの検索では==をつけるのはちょっとやりすぎかもしれませんが、念の為ということで。ポイントは、処理として「レコードを対象外に」を選択することです。つまり、条件に合ったレコードを対象外にして、集計対象から排除するのです。

このような処理は非常に遅いのでは無いかと危惧するかもしれませんが、結構早いです。集計時に一旦レコードを全部ロードしていて、対象外にする処理では、メモリの移動や増加、ネットワーク処理などは入らないからでしょう。すぐに終わります。10万件で試してみても、数秒もかからずに終わりました。

小計パートが2つある場合でも、同様にできました。ただし、レコードを対象外にする場合には、2つの小計パートのソート対象フィールドに対して、ANDで検索をかけます。ということで、ちょっとずつレコードを省略して最初のレコードの集計値を取り出せば良いので、小計パートがいくつになっても大丈夫です。

ちなみに、レコードの省略では、対象レコードが増えないので、ソート結果はそのまま保持されるという動作もこの方法での集計結果の取得を実現する仕様の1つです。

集計結果だけのテキストを得るにはいろんな方法がありますが、この方法は、FileMakerの中だけで完結しているというところが大きなポイントでしょう。外部にCSVを出すなどして処理するのは別システムの管理がかえって面倒になりますし、後々のメンテナンスを考えれば、システム基盤はシンプルな方が良いということになります。ちなみに、この仕組みがどんなところで必要になったかと言うと、複数の異なるテーブルの集計結果を1つの表にまとめたかったからです。ただ、その纏める部分は色々考えて、集計結果をJSONで得て、WebビューでJavaScript処理で表を作るということで解決しました。

テストしたデータベースも公開します。レコードは10万くらいにしてあります。あまり、使い勝手は良くないですが、ご了承ください。

FileMakerネタです。ログイン可能なアカウント作成はいいとして、自分で作ったレコードしか見えないようにしたいというのはニーズの多い仕組みです。結構いろんなことをしないと行けないのですが、その設定の肝となるは、アクセス権セットのレコードに対するアクセス権です。細かな設定はこの記事では説明しませんので、詳しい方はご安心ください。FileMakerのあの辺りの機能は、ダイアログボックスが開きまくる手間のかかるUIですので、ポイントだけ示します。

アクセス権セットの [データ入力のみ] の複製を作って、名前を付け替えるなどするのが最初の作業です。「入力のみ」という違和感ある名前はともかく、複製を作り、レコードのポップアップで、「カスタムアクセス権」を選択します。

すると、次のようなダイアログボックスが新たに開きます。ここで、該当するテーブルを選択して、リストの下に見えるポップアップメニューから「制限」を選択します。一般に、「表示」の設定を変えるのは必須ですが、「表示」で制限をかければ、編集/作成/削除に対しては何もできないようなので、全部の項目に設定は必要ありません。

この「制限」を選択すると、式を入力するダイアログボックスが表示されます。そこで、論理式を入力します。論理式がTrueなら表示可能ということで普通にレイアウトにレコードのデータが表示されます。論理式がFalseなら <アクセス権がありません> とフィールドに出ます。こうした表示するかどうかを、レコードごとに決めることができるというのが、カスタムレコードアクセス権の設定で実現します。

ここで対象となるテーブルに、テキスト型でusernameというフィールドを作っているとします。そして、そのフィールドには、レコード新規作成時に自動的に「GET ( アカウント名 ) 」という式の結果を入力します。「ユーザ名」ではなく「アカウント名」で、こちらがログインした認証情報に含まれるユーザ名になります。

そして、カスタムレコードアクセス権の制限の式に「username = GET ( アカウント名 ) 」という式を設定しておけばOKです。この辺り、私たちの書籍「FileMaker as a Relational Database」の「8.3 レコード単位のアクセス権」に記述があります。設定手順がちょっと込み入っとるやないか！と今見たら思ってしまいますが、基本はここで説明したものと同様、アクセス権設定に対するレコード単位のアクセス権指定を行っています。

さて、ここからが本番（長い）。ある案件で、単にユーザ単位でのレコードの分離（レコードごとのアクセス権設定を行なった状態）にするだけでなく、ユーザのグループを定義してグループだと、所属している全てのユーザのレコードが見えるようにするという要件がありました。ここでのグループは、OSのグループとは違って、形態としてはユーザです。要するに権限の高いユーザがグループであり、権限自体に段階があるというか、自分だけと、グループ全体の段階があるという状態です。

これを実現するために、2つの方法を考えました。1つは、ここでのユーザとグループそれぞれ別々のアクセス権セットを割り当てることです。もう1つは1つのアクセス権セットでの「制限」の論理式でうまく処理を組み込むということです。まあ、当然、後者の方が面白そうなので、まずはそちらでやってみました。なお、あるユーザがどのグループに所属するかを記録しないと行けないので、ユーザ管理のテーブルをFileMakerに作る必要があります。{主キー, username, password, is_group, groupname} がテーブル構成です。ユーザ名、グループ名、いずれもテキストで記録するようにします。ここでいちいち主キーでリレーションするのもやり過ぎ感があるので、テキストで十分です。グループは、is_groupがTrueにしておきます。そうすると、ユーザのレコードで所属グループの選択を行うときに、グループだけの（is_groupがTrueのレコードだけの）ポップアップ等が作れるので、このようにしました。このテーブルから、ユーザがどのグループに属するかがわかるという段取りです。passwordはいらんといえばいらんのですが、せっかくこんなテーブルを作るので、いちいち、セキュリティ管理のダイアログボックスを出さなくても、ボタン1つでユーザの作成や削除ができるようなUIを作りました。そのためにはパスワードも入力しておくのが便利です。

そして、大変重要なリレーションシップの設定が必要になります。あるテーブルにusernameフィールドを作って、そこにログインアカウント名を入力しておいて、誰のレコードなのかがわかるようになっているということになるのですが、さらにそのレコードはどのグループの所属なのかをわかるようにしなければなりません。つまり、テーブルから、前者のユーザテーブルのusernameに対してリレーションシップを定義し、ユーザテーブルのgroupnameとして、各レコードの所属グループが得られるようにしておく必要があります。

ここで、アクセス権セットの「制限」の式を構築するとき、要するにログインしているユーザがここでの普通のユーザの場合と、グループユーザの場合では、比較するフィールドが違ってくるということがあります。その処理をやりやすくするために、ログイン直後にユーザテーブルで自分自身のレコードを検索して、is_groupフィールドを見て、自分が通常のユーザなのか、グループユーザなのかを$$変数に置いておき、その変数値を使った計算式を立てれば良いのではと考えました。そして、「制限」の式に$$変数を使ったものを記述したのですが、画面上はなんとなく問題ないように見えるのですが、検索をしても１つもレコードが返ってきません。

なんでか？　よく考えてみると、$$変数というか、変数ってコンテキスト依存のものです。つまり、画面に出て来る段階で適用されるようなものと考えて良いでしょう。検索で変数は使えますが、変数の値を求めてそれを条件としてサーバに送っていると考えられます。検索処理そのものは、サーバで行われるとすれば、サーバでレコードを探っている段階では変数の値は全部未確定と考えるべきなので、変数が全部未定義でfalseになるのではと思われます。とは言え、デバッガを動かして検証できる状態ではないので、推測ですが、要するに制限の計算式ではグローバル変数は使えるようでいて使えない場面（この場合は検索時）があるというのが結論のようです。

では、実際にはどうすればいいのか？ 1つは、ターゲットテーブルに対してusernameフィールド同士が等しいというリレーションシップを設定したユーザテーブルをtarget_userとすれば、「username = GET(アカウント名) or target_user::groupname = GET(アカウント名)」という式を設定すればいいかと考えました。ですが、あえて、設定がわかりやすいように、アクセス権セットを2つ作り、通常ユーザ用とグループユーザ用として適用し、前述の式のorで区切った前半を前者、後半を後者の「制限」の式に設定しました。ユーザ作成部分はレイアウトをベースにボタンを作って対処してあるので、作り分けることは完全に自動にできます。1つのアクセス権セットにするのもいいのですが、分けておくほうが、後々、いろんな場面に対応できそうな気がして、アクセス権セットを2つに分ける方を選択しました。

ちなみに、「username = GET(アカウント名) or target_user::groupname = GET(アカウント名)」という式で大丈夫だろうと思われるかもしれませんが、ここでのユーザテーブルの整合性が合わない状況の場合、思いがけない結果になります。{is_group, username, groupname}というスキーマに対して{false, user1, boss}{false, user2, boss}{true, boss, boss}という3レコードがあったとします。これを元に、usernameフィールドにuser1、user2あるいはbossという文字列を入れて、ターゲットテーブルに対してどのユーザのレコードかを記録します。bossでログインをした場合は、前述の式のor以降がtrueになることから、user1やuser2が所有しているレコードに対してもbossは参照できます。しかし、ここで、bossがグループユーザではなくて通常のユーザになったとします。is_groupをfalseにしただけだとしたら、user1、user2→bossがグループという情報が残るので、相変わらずbossはuser1やuser2のレコードを参照できます。つまり、user1やuser2のグループを””にするなどの処置が必要となるわけです。計算式にグループユーザかどうかという分岐を入れれば、この問題は回避できると考えて$$変数を使おうとしたのですが、前述の通り、これはうごかない式になってしまいました。複数のアクセス権セットに分離している場合でも同様なことは発生します。その場合、アクセス権セットを別のものに設定し直しが必要になり、これはスクリプトではできない作業になります。同様に通常のユーザをグループユーザに昇格した場合も同様な懸念がありますが、その場合、他のユーザを新たにグループになったユーザに所属させる作業が発生するので、自然に整合性は取れるかと思われます。アクセス権設定が複数ある場合はその所属変更が必要になります。ということで、アカウント作成後は、is_groupの値を変更しないというのがシンプルな解決策になりそうです。いずれにしても、運用間違えるとセキュリティの問題が発生しそうな案件なので、諸々、注意深く実装する必要はありそうです。