2016年9月20日火曜日

リレーションを使ってデータを集計してみよう | FileMaker Pro

こんにちは!

ちょっと複雑なリレーションに挑戦してみように引き続き、リレーションについてです。

リレーションの目的はいろいろとありますが、その中の一つに「集計」するということがあります。

今回はそのリレーションを使った集計方法です。

元データ

下図のような日付と売上を保存しているテーブルがあるとします。

今回はこれを月ごとに集計していきたいと思います。


準備(フィールドの作成)

まず、どの基準で集計するかという基準を作ってあげる必要があります。

今回の場合は月ごとに集計をしたいので、それぞれのレコードがどの月に属するかを判別するフィールドが必要になります。

下図のように「年月」というフィールドを用意しました。



次にこのフィールドにオプションの計算式を設定します。


このように計算式を設定します。

この計算式は日付フィールドが「2016/09/10」の場合「201609」を返します。

後半のRight関数は、月の桁数を合わせるためのものです。8月は08、11月は11と常に二ケタになるようにしています。


途中で計算オプションのフィールドを変更したり、追加する場合

今回のように、後で計算オプションを設定したフィールドを追加しても、計算がされません。

再計算を行い、年月フィールドに値を書き込む必要があります。

「フィールドに既存の値が存在する場合は置き換えない」チェックを外している場合、再計算が行われるのは、式に含まれる同じテーブルのフィールドが変更になったか、新しくレコードさ作成された場合の2つです。

既存のレコードの場合は「式に含まれる同じテーブルのフィールド」が変更になると再計算されます。

今回は「フィールド内容の全置換」を使って再計算を促します。

日付フィールドを選択し、「レコード」→「フィールド内容の全置換」を選択します。

下図のように計算式を設定します。

これを実行すると、年月フィールドに値が書き込まれます。


集計用の新しいテーブルの作成


次に新しいテーブルを作成します。

ここでは下図のように集計というテーブルを作成します。さらにリレーションも設定します。その後に「売上」テーブルの計算式を設定します。


図のように3つのフィールドを作ります。


※「このリレーションシップによるレコードの作成を許可する」にチェックを入れておきましょう。


また売上フィールドには下記の計算式を設定します。





集計用のテーブルにレコードを作成する

実際に集計値を取得するにはレコードを作る必要があります。

手動で作っても構いませんが、スクリプトを使ってみます。

先ほどテーブルの作成で「レコード作成用」というフィールドを作っています。これがミソです。

売上テーブルをコンテキストとするレイアウトに集計テーブルのこのフィールドを追加します。


このレイアウトからレコード作成用のフィールドに何でもいいので値を書き込むと集計テーブルにレコードが作成されます。

これを自動で行うスクリプトを作成します。


このスクリプトを動かせば、必要なレコードが集計テーブルで作成できます。

集計テーブルではこんな感じになります。


問題点

この方法にも問題があります。

集計するレコード数が多くなってくると、重くなり使い勝手が悪くなります。

次回はその点について取り上げてみたいと思います。

2016年9月12日月曜日

ちょっと複雑なリレーションに挑戦してみよう

こんにちは!

今回は「ちょっと複雑なリレーションに挑戦してみよう!」です。

FileMaker Pro(ファイルメーカー)を始めると誰もがぶつかる壁がリレーションではないでしょうか。

「<」「>」などを使用したリレーションは使っているうちにだんだん何をしているのかわからなくなってくるかもしれません。

今回はそんなリレーションについて紹介したいと思います。


「<」「>」のリレーション


「<」「>」のリレーションはキーより小さい、大きいものを関連レコードとします。

例えば、日別の売上を保存するテーブルがあったとします。昨日の売上と今日の売上を比較したい場合にこのリレーションを使用します。

と言葉で説明してもわかりづらいので、図で説明してみたいと思います。


「date」という日付のフィールドがある「テーブル」というテーブルがあります。さらに同じテーブルからできるテーブルオカレンス「テーブル 2」とリレーション(自己リレーション)をします。

「date」同士を上の画像のように「>」でつなぎます。

さらに、レコードのソートにて下図のようにソートを設定します。


「date」フィールドを降順でソートします。

複雑なリレーションを作るうえで、このソートがカギになります。

このソートをしないと、このリレーション上で「テーブル 2」ではレコードが作成された順に並びその中で、「テーブル 2」の「date」で最も小さいものを最初の関連レコードとします。


ソートしないとこんな感じになります。

このレイアウトは「テーブル」に基づいていて、右側のフィールドは関連する「テーブル 2」の「date」を表示します。

この場合は、どの日でも最初の関連レコードとして「2016/09/06」を返します。

今度はソートをしてみます。


ソートをしたものです。

関連するレコードとして、どのレコードも前日のレコードを関連しています。このリレーションを使って計算を行えば、昨日よりどのくらい売り上げが多かったか、少なかったが計算できます。