2013-01-29

簡単? FileMakerで在庫管理(4) ―― 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 のどれが一番有利かは検証が必要です。それについては機会を改めてやりたいとは思っています。



(亀澤)

0 件のコメント: