在庫算出の 3 つのパターン
パターン1： FileMaker Pro で在庫算出専用の TOG を構成する方法
弊社で配布しているシンプルな入出庫・在庫管理システム／テンプレート 「FMEasy 在庫」では、以下のようなリレーションシップを構成することによって、特定時点の商品在庫を算出しています。
 |
| ここでは |×| 以外のリレーションは無視して下さい。 |
※「簡単？ FileMakerで在庫管理（1） ―― 在庫算出の基本」に類似リレーションの解説がありますので、参考にしてみてください。
ユーザが商品レイアウト上の [g在庫基準日1]に任意の日付を指定すると、在庫算出開始点となる日付[g在庫基準日2]を決定します。
[g在庫基準日2]～[g在庫基準日1]の範囲内の日付で発生した商品の[入庫数]と[出庫数]が対象となるようにリレーションシップを組み、対象となった[入庫数]、[出庫数]、および商品側の[繰越在庫数]を使って[g在庫基準日1]の在庫を求めます。
このように考えた場合、商品テーブルの [c在庫数]の計算式は、たとえば次のようになります。
Case(IsEmpty(繰越情報::繰越日付) or g在庫基準日1 < 繰越情報::繰越日付; 導入時在庫数;繰越在庫数) - Sum(入出明細_商品::出庫数量) + Sum(入出明細_商品::入庫数量)
この計算式は比較的単純ですが、リレーションが不細工なことと、[g在庫基準日1]や[g在庫基準日2]という管理フィールドを商品テーブルに持たせざるを得ない点には、スマートさがあまり感じられませんね。
パターン2：ExecuteSQL関数を使用する方法（JOINなし）
FileMaker Pro 12 に新たに追加された ExecuteSQL 関数を使うことによって、商品の在庫を算出します。
この方法を採用すると、入出庫数絞り込みのためのリレーションシップが不要となります。
また、商品テーブルの[g在庫基準日1]フィールドおよび [g在庫基準日2]フィールドも不要となります。
ユーザが日付を指定する[g在庫基準日1]は、ユーザインタフェース専用テーブル UI に移動してしまいましょう。
上記のように、 入出明細TO さえあればリレーションは不要となります。単純なのがいいですね！
それでは、商品テーブルの方に在庫算出用計算フィールド[cNoJoin在庫数]を追加し、計算式を以下のようにしてみましょう。
//$date は期間開始日、$date～ UI::g在庫基準日1 の入出庫の計を加減して在庫数を算出
Let (
$date =
Case(
IsEmpty(繰越情報::繰越日付) or UI::g在庫基準日1 < 繰越情報::繰越日付; Date( 1; 1; 1 );
繰越情報::繰越日付 = UI::g在庫基準日1; 繰越情報::繰越日付 +1;
繰越情報::繰越日付 +1
);
Case( IsEmpty( 繰越情報::繰越日付 ) or UI::g在庫基準日1 < 繰越情報::繰越日付; 商品::導入時在庫数;商品::繰越在庫数 )
- ExecuteSQL ( "SELECT SUM( \"出庫数量\" ) FROM \"入出明細\" WHERE \"商品ID\" =" & 商品::商品ID & " AND (\ "入出庫日\" BETWEEN '" &
GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "') "; ""; "")
+ ExecuteSQL ( "SELECT SUM( \"入庫数量\" ) FROM \"入出明細\" WHERE \"商品ID\" =" & 商品::商品ID & " AND ( \"入出庫日\" BETWEEN '" &
GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "') "; ""; "")
)
ここでは $date が [g在庫基準日2]フィールドに代わって期間開始日となるため、 [g在庫基準日2]フィールドは不要となります。
パターン3：ExecuteSQL関数でJOINする方法
最後は、ExecuteSQL に結合（LEFT JOIN）を組み込み在庫を算出する方法です。ここでは出庫テーブルと入庫テーブルを入出明細テーブルに JOINし、出庫.出庫日 と 入庫.入庫日 を WHERE 句で使用できるようにするため、入出明細テーブルの［入出庫日］も不要となります。
パターン2と同様、配置するのは 入出明細TO のみで、リレーションは不要です。
結合にはLEFT JOIN を使用していますが、INNER JOIN も使えます。RIGHT JOIN は FileMaker が非対応のようです。商品テーブルで商品在庫を求める計算フィールド[cSqlLeftJoin在庫数]を追加し、以下のような計算式を組み立てます。
Let (
$date =
Case(
IsEmpty( 繰越情報::繰越日付 ) or UI::g在庫基準日1 < 繰越情報::繰越日付; Date( 1;1;1 );
繰越情報::繰越日付 = 商品::g在庫基準日1; 繰越情報::繰越日付 +1;
繰越情報::繰越日付 +1
);
Case( IsEmpty( 繰越情報::繰越日付 ) or UI::g在庫基準日1 < 繰越情報::繰越日付; 商品::導入時在庫数;商品::繰越在庫数 ) -
(
ExecuteSQL ( "SELECT SUM( \"出庫数量\" ) FROM \"入出明細\" LEFT JOIN \"出庫\" ON \"入出明細\".\"出庫No\" = \"出庫\".\"出庫No\" WHERE \"商品ID\" =" & 商品::商品ID & " AND ( \"出庫日\" BETWEEN '" & GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "' ) "; ""; "" )
)
+
ExecuteSQL ( "SELECT SUM( \"入庫数量\" ) FROM \"入出明細\" LEFT JOIN \"入庫\" ON \"入出明細\".\"入庫No\" = \"入庫\".\"入庫No\"WHERE \"商品ID\" =" & 商品::商品ID & " AND ( \"入庫日\" BETWEEN '" & GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "' ) "; ""; "" )
)
$date =
Case(
IsEmpty( 繰越情報::繰越日付 ) or UI::g在庫基準日1 < 繰越情報::繰越日付; Date( 1;1;1 );
繰越情報::繰越日付 = 商品::g在庫基準日1; 繰越情報::繰越日付 +1;
繰越情報::繰越日付 +1
);
Case( IsEmpty( 繰越情報::繰越日付 ) or UI::g在庫基準日1 < 繰越情報::繰越日付; 商品::導入時在庫数;商品::繰越在庫数 ) -
(
ExecuteSQL ( "SELECT SUM( \"出庫数量\" ) FROM \"入出明細\" LEFT JOIN \"出庫\" ON \"入出明細\".\"出庫No\" = \"出庫\".\"出庫No\" WHERE \"商品ID\" =" & 商品::商品ID & " AND ( \"出庫日\" BETWEEN '" & GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "' ) "; ""; "" )
)
+
ExecuteSQL ( "SELECT SUM( \"入庫数量\" ) FROM \"入出明細\" LEFT JOIN \"入庫\" ON \"入出明細\".\"入庫No\" = \"入庫\".\"入庫No\"WHERE \"商品ID\" =" & 商品::商品ID & " AND ( \"入庫日\" BETWEEN '" & GetAsText ( $date ) & "' AND '" & GetAsText ( UI::g在庫基準日1 ) & "' ) "; ""; "" )
)
このように商品テーブルの[g在庫基準日1]フィールドと[g在庫基準日2]フィールド、在庫算出用のリレーションに加え、入出明細テーブルの[入出庫日]フィールドが不要となります。また、入出明細テーブルに[入出庫日]を設定するというスクリプトによる面倒な制御も不要になります。
ExecuteSQL 関数のメリット
ExecuteSQL を利用すると、本来不要なリレーションやグローバルフィールドを省けるため、データベースの正規化がしやすくなります。また、結合を使用すれば、スクリプトによる余分な制御も不要となります。
ただ、実行速度的にパターン1～3 のどれが一番有利かは検証が必要です。それについては機会を改めてやりたいとは思っています。
（亀澤）
