簡単？ FileMakerで在庫管理（４） ―― ExecuteSQLによる在庫数算出

　これまで FileMaker Pro による在庫算出の基本、繰越処理の考え方、複数倉庫の在庫算出方法についてご紹介してきましたが、今回は商品在庫を求める方法として、3 つのパターンをご紹介したいと思います。

在庫算出の 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 ) & "' ) "; ""; "" )

)

　このように商品テーブルの[g在庫基準日1]フィールドと[g在庫基準日2]フィールド、在庫算出用のリレーションに加え、入出明細テーブルの[入出庫日]フィールドが不要となります。また、入出明細テーブルに[入出庫日]を設定するというスクリプトによる面倒な制御も不要になります。

　ExecuteSQL 関数のメリット

　ExecuteSQL を利用すると、本来不要なリレーションやグローバルフィールドを省けるため、データベースの正規化がしやすくなります。また、結合を使用すれば、スクリプトによる余分な制御も不要となります。

　ただ、実行速度的にパターン1～3 のどれが一番有利かは検証が必要です。それについては機会を改めてやりたいとは思っています。

　その他の在庫関連記事を読む

（亀澤）