簡単？ FileMakerで在庫管理（２） ―― 繰越処理の考え方

　今回は、繰越処理に伴う在庫算出について考えていきましょう。　在庫算出の基本については、以下の記事をご覧ください。

　簡単？　FileMakerで在庫管理（1） ―― 在庫算出の基本

繰越処理の考え方

　入出庫のレコードが増えてくると、在庫算出時に合計する入出庫明細レコードが膨大になり、処理速度が低下します。
　そこで、在庫数の繰越処理を行うことによって、在庫算出処理を行うことにより、在庫数算出を高速化する必要があります。
　
　たとえば、商品Aの2011年6月～2012年5月の入出庫明細データが10万件あったとします。
　2012年5月31日時点の商品Aの在庫を算出しようとして、入出庫明細レコードを10万件を対象に、Sum(入庫明細::入庫数)-Sum（出庫明細::出庫数） をやっていては、処理時間が膨大になってしまいますが、繰越処理を実行することによって2012年4月末時点の商品Aの在庫数を商品テーブルに保管しておき、5月1日～5/31の入出庫明細レコードのみをSumの対象となるようにリレーションを組めば、Sumの対象となるデータは、単純試算では10分の1以下となります。

　次に在庫算出の計算式を、以下のように繰越処理を行った場合と、行ってない場合とでは、在庫算出の計算式が違ってきます。

A式： 導入時在庫数+（4/25以前の入庫計）-（4/25以前の出庫計）

B式：　繰越処理日≦在庫基準日の場合の在庫算出式
繰越在庫数+（4/1～4/25の入庫計)-（4/1～4/25の出庫計)

C式：　繰越処理日＜在庫基準日の場合の在庫算出式
　　導入時在庫数+（2/28以前の入庫計）-（2/28以前の出庫計）

　要はA、B、Cの式をCase分けして、ひとつにまとめる必要があるわけです。

繰越処理と制御

　さて、繰越処理で考えておかなければいけない重要なことが一つあります。それは、繰越処理日（上記例では3/31）以前のデータ、在庫に関していえば、3/31以前の入出庫レコードの出庫日と入庫日、出庫数と入庫数を変更不可にしておく必要があります。また、3/31以前の同レコードは削除させてはならないし、繰越処理日以前の入出庫日を持つ同レコードは作成させてもいけません。　

　その理由は、［繰越在庫数］との整合性がなくなってしまうからです。

やがて訪れるデータ削除の時期について：
　長期間使用するシステムでは、繰越処理を行ってもレコードはどんどん蓄積されていきます。当社が開発・保守を担うシステムでは百数十万件の入出明細データを持つテーブルがありますが、ほぼ定期的にデータ削除を行っています。小社では、FileMaker のテーブルの最大レコード蓄積数は200～300百万件を一つの目安としていますが、データの削除は、システム仕様、実行速度要求、保持データ数要求、バックアップポリシー等の諸事情を勘案し、システムが安定動作することを大前提に実行してください。一般ユーザがデータ削除を行う場合は、一括データ削除機能も必要になります。
　また、削除したデータを照会したい、といった要望も必ずといっていいほどユーザより提示されるので、できれば過去データの照会についても予め考慮してシステム設計を行います。

在庫管理は簡単か？

　残念ながら答えは NO です。
　システム的には、繰越処理とそれに伴う伝票制御、旧データ削除時の処理を考慮せざるを得ず、在庫管理システムを作るのはやはり簡単ではない、というのが結論になります。

　また、繰越処理関連を適正に実装できたとしても、1カ月に同一商品の取引が数百回以上あるような業務では在庫算出処理の遅延も予想され、別の在庫算出手法（拙稿の「FileMaker V10による在庫管理、一考」の1、2、3を参照）の採用も選択肢に入れなければならないでしょう。

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

土屋

簡単？ FileMakerで在庫管理（1） ―― 在庫算出の基本

　本稿は2012年5月に投稿されたものです。2018年2月現在、最新の FileMaker のバージョンは 16 となっていますが、本稿及び続稿で記述した在庫に関するの設計手法は現在も有効と思われます。
　ただ、iBeacon/EddyStone の登場等や、記事に気になる点があったため、今回、加筆修正することにしました。また、SQLを使った在庫の繰越処理などについても加筆したいと思っているのですが、これについては別の機会にしたいと思います。
（土屋企画）

　本稿では FileMaker を使用した在庫算出の方法について考えます。ただ本論に入る前に、在庫算出の3つの手法を大雑把に見てみます。それぞれの手法に便宜的に名前を付けていますが、これらは筆者が勝手に名付けたもので、社会、業界で一般的に利用されている名称ではありませんので、他所で「入出庫小計差分法を採用したいのですが…」とか言っても、相手の方は「？？？」となるでしょう。

在庫算出３つの手法

入出庫小計差分法

 　この手法は当方の様々な在庫管理システムで使用している方法で、FileMaker で在庫算出する際の一般的な手法と思われます。いつの時点の在庫を知りたいのか（以下［在庫基準日］）を決め、その時点までの入庫数計と出庫数計を計算、その差分が在庫となります。下図の4/30時点の在庫は、4/30迄の入庫計＝20と出庫計＝6の差分で［在庫］＝14となります。
　Excelでは、出庫数の横に在庫列を作成して「D4＋B5－C5」などとすれば簡単に在庫が求められますが、リレーショナルデータベースではそうした計算はできません。なぜならリレーションの無い行同士の演算はできないからです。無理に外部キーを振ったり、スクリプトのループで加減演算しても、結局は破綻します。

この方法は比較的単純ですが、特定の商品が頻繁に取引されるようなシステムでは、在庫算出処理に想定外の時間を要する可能性があります。

在庫マスタ逐次書換法

本手法は下図のように入出庫に変動があった場合、在庫マスタの［在庫］フィールドを即時更新する、というものです。下図を例に説明すると、3/31に[入庫数]＝10を入力してコミットすると、在庫マスタテーブルの商品Aの［在庫］は即刻10に更新され、4/1に［出庫数］＝1を入力すると［在庫］は9に即更新されます。

この方法は、入出庫伝票更新時に在庫マスタを即更新するため、その後発生する在庫照会や在庫表印刷の処理では各商品の在庫算出を行うことはなく、高速です。ただ、実装方法が難しいこと、上述の在庫基準日を指定できないこと、入庫日と出庫日には未来の日付を入力できないことなど、デメリットは少なからずあります。　ただ、同一商品の入出庫が頻繁に発生し、リアルタイムに在庫を把握する必要がある業態では、この手法を採用することが多いでしょう。実は2009年に本手法に関する投稿とサンプルファイル（現在リンク切れ）を公開しています。興味があるかたはこちらをどうぞ。

ビーコン信号カウント法

近年リリースされた iBeacon/EddyStone（以下、ビーコン） は少ない電力（電池）でユニークな値（UUID/UID）を含む信号を数カ月～数年、0m～100mほどの範囲で発信する装置です。

土屋企画で使用するビーコン　―　左の小さいタグ型ビーコンは 40mm×28mm 

このビーコンを製品に取り付け、その信号を取得・処理することでほぼリアルタイムに商品の在庫を把握することができます。以下のシステム図は 、Raspberry Pi と呼ばれる超小型Linux機（価格は約3000円/台）を広い倉庫内に万遍なく配置、ビーコン信号を漏れなく取得し、データベースの在庫テーブルを更新するというものです。
　入出庫システムからは独立して、在庫数の把握が可能となります。

本手法はビーコンが高価（500円～数千円～）であること、取り付けが不能・不向きな製品があること、電池に寿命があり交換が必要なこと等、ビーコン特有の問題があります。ビーコンについてはこちらを参照してください。

在庫算出の基本

さて、それでは上述の「入出庫小計差分法」について以下そのしくみを見ていきます。

　「2012年6月20日時点の在庫」というような特定時点（在庫基準日）の在庫数を算出するために、以下のようなTOG（テーブル・オカレンス・グループ）を作成してみます。

　商品マスタと出庫明細と入庫明細をリレートするだけですのでシンプルですね。
実際アプリケーションでは、出庫明細や入庫明細を格納するための出庫と入庫のテーブルが必要ですが、ここでは省略します。
　以下は上記リレーション |×| で示された部分のリレーション編集イメージです。

　［g在庫基準日］は日付のグローバルフィールドという特殊なフィールドで、詳しくは FileMaker のマニュアルを参照してください。ユーザはこのフィールドで在庫算出の起点となる日付 ― 在庫基準日 を指定します。

　そして、こちらが在庫算出用フィールド＝［c在庫数］の計算式です。

繰越在庫数－ Sum(出庫明細_商品::数量) ＋ Sum(入庫明細_商品::数量)

　システム導入時、［繰越在庫数］には各商品の導入時点での在庫数を入力します。導入時、在庫が無い商品については「0」を入力しておきます。
　上記の計算式もシンプルで、［繰越在庫数］（＝ここでは導入時点の在庫数）から［g在庫基準日］以前に出庫した商品数量を差し引いて、さらに［g在庫基準日］以前に入庫した商品数量を加え、在庫を算出しているだけです。
　［g在庫基準日］より後の日付の入出庫が［c在庫数］の計算式の対象とならないのは、上図「在庫算出TOG」のリレーションで「≧」によりそのように定義されているからです。

注：

1. 本稿読者で「出庫明細/入庫明細テーブルに［出庫日］/［入庫日］があるのはおかしいではないか。 　本来、この二つのフィールドは出庫ヘッダ/入庫ヘッダテーブルにあるべきで、冗長で正規化されていないではないか？」と考えた方 、 御尤もです。ただ、FileMaker 内蔵DBを使用し、在庫算出基準日を任意に指定可という仕様を前提にした場合、正規化を崩しても明細に日付を持たせるのが良い、というのが小社の知見です。
2. 但し、ExecuteSQL 関数によるSQLクエリを使用して出庫と出庫明細、入庫と入庫明細を結合（JOIN）すれば、［出庫日］/［入庫日］がそれぞれの明細テーブルに存在しなくても、在庫数を取得することができます。ただ、FileMaker の結合は実行速度が非常に遅くなることがあるので、留意が必要です。この手法についてはこちらを参考にしてください。
3. 上記1の通りに設計する場合、出庫ヘッダ/入庫ヘッダの［出庫日］/［入庫日］と、出庫明細/入庫明細テーブルの［出庫日］/［入庫日］は常に一致するようにシステム的に保証しなければなりません。ユーザがヘッダ部の［出庫日］/［入庫日］を変更することは頻繁にあるので、スクリプトトリガを使用して、うまく制御してください。

　上記の注2 の部分が少し面倒ですが、ここまでは比較的簡単にアプリを作成できると思います。

　ただ、入出庫レコードが数千、数万と増えるに従い、在庫（c在庫数）の表示には時間がかかるようになります。在庫算出処理の時間を短くするためには、繰越処理を行い、［c在庫数］の計算対象となる入出庫レコードを減らす必要があります。　繰越処理とそれに伴う処理については、　簡単？　FileMakerで在庫管理（2）―― 繰越処理の考え方 をご覧ください。

TOG（テーブル・オカレンス・グループ） とは？

FileMaker Pro 7 以降に導入されたリレーションシップグラフでは、テーブルを別名、または同一名で定義して配置できます。
　たとえば「商品」というテーブルは、リレーションシップグラフ上では「商品Table1」、「商品テーブル」、「商品」など自由に名前をつけて配置したり、同一のテーブルを別名で複数配置したりできます。

　リレーションシップシップウィンドウに配置されたテーブルは、単に”テーブル”と呼ばれたり、テーブルのエイリアスあるいはTO（テーブル・オカレンス）と呼ばれることもあります。

　FileMaker界隈 では TO という呼び名が一般的になってきているようです。また、処理別やレイアウト別に TO をグループ化したものを TOG （テーブル・オカレンス・グループ）と呼んでいます。

　以下の動画では『FMEasy在庫 IWP/WD 1.5』という製品を使用し、在庫算出に関して説明しています。




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

（土屋）

FileMaker の分離モデル - 2

　前回、分離モデルのメリットは以下の4点と書いた。

1. 仕様変更時のダウンタイムを最小限にする
2. 同一システムが複数の拠点やユーザにより運用される場合、アップグレードが容易
3. データベースに重大な損傷が発生した場合、データファイルのみ再構築すれば良い
4. 公開マスタ（郵便番号、宅配便仕訳コード等）の更新・入替が容易

　2.は、多数のユーザが運用するアプリケーション、パッケージやネットからダウンロードするようなアプリケーションで大いに役立つ。

今日は上記1.について。

　FileMaker Ver6以前は1ファイルに1テーブルしか付属させることができず、更にそのファイル毎にリレーション、スクリプト、値一覧、アクセス権限といったオブジェクトも作成・管理をしていた。

　小社のある得意先は、FileMaker Ver5.5のデータベースを現在も運用しており、そのファイル数は約60、そのうち数千～200万弱のレコードを保持するファイルが数十ある。　FileMaker Ver3で開発を初め、納品後に仕様変更と追加を十数年にわたり重ねてきた。　FileMakerのバージョンが上がりアップグレードを行いたいのは山々であったが、規模が大きくなり過ぎアップグレードには多くの費用がかかるため、今日まで旧システムを延命してきている。　

　さて、このようなシステムで仕様変更が発生し、その変更が多くのファイルに及んだ場合、どうやって納品するか？　システム屋は大変である。　まず最初に考えられるのは、開発を行ったデータベース（開発版）のクローン（ファイルのデータ無コピー）を作成し、そのクローンに運用中のデータベース（運用版）の全データを取り込むことが考えられる。この方法は単純だが、レコード数が数十万を超えるファイルが多くそのファイルに仕様変更が含まれる場合、非常にデータ移行作業に時間がかかり、システムのダウンタイムも長くなる。　また、作業中のミス、例えばデータ取込ミスや、取込後の主キーの設定漏れ、などの重大なリスクも抱えている。

　次に考えられるのが、仕様変更があったファイルを、a.レコード数が少ないファイル、b.仕様変更が多いファイル、3.仕様変更が少なくデータが多いファイルに分け、a.とb.については上述のクローンへのデータ取り込みで、c.については、運用版のファイルに開発版で行ったのと同じ変更を直接入れる、という方法である。　この方法は「データの多いファイルに変更が少ない」場合、システムのダウンタイムを大幅に減らせる可能性があるが、逆に作業のリスクは増すことになる。つまり、FileMaker Ver6以前で規模の大きな仕様変更が発生すると、簡単な納品方法はなかったのである。

　もし、上記のような仕様もデータも大規模なシステムが、データとアプリケーションに厳密に分離されているシステムであったらどうだろうか。　仕様変更がデータ側、つまりテーブルに及ばないのであれば、アプリケーションファイルを入れ替えるだけ、ダウンタイムはせいぜい1分前後となる。　仕様変更が複数のテーブルに及んだり、テーブルの新規追加があったとしても、一部フィールドを追加し、新規テーブルをコピーすればよいだけである。この作業はオンラインでバックアップを取った後であればサーバを落とすことなく実行可能である。　データファイルの作業を行った後、アプリケーションファイルを入れ替えれば作業終了である。

　ダウンタイムを最小限にしたい、“落せないシステム”において、分離モデルは特に有用である。

（土屋）



【関連リンク】
分離モデルにおけるSUM/ExcecuteSQL関数の問題と対策
土屋企画の講習 ― 分離モデルに基づく請求書システムを作る（対象者：中級、4時間×2日）
FileMaker の分離モデル



【分離モデルに基づく在庫管理テンプレートのご案内】
分離モデルに基づき開発された在庫管理システムテンプレート「FMEasy在庫」の紹介記事は→こちら。
「FMEasy在庫」フリー版/開発版のダウンロードは→こちら

【FMEasy在庫 の画面】

（2012/12/20追記）