部品表(BOM) |

部品表(Bill of Materials; BOM)は、データベースシステムの定番アプリケーションの一つです。
FileMakerのエキスパートが、部品表のしくみを新たに構築するときに活用したのがExecuteSQL関数だったというお話を、今回は紹介します。

Bill of Materials

(元記事はこちら)

Kevin Frank
2019/3/27

2019/3/29: デモファイルをv2にアップデートしました。詳細は(元記事の)コメント欄を参照してくだい。

編集者注: Creative SolutionsのGeoff Gerhardは、20年以上にわたりFileMakerのオンラインフォーラム上においてその知識で多くの人を助けてきた「静かな」開発者の一人です。私は個人的に彼の専門知識から多大な恩恵を受けているので、このFileMaker Hacksでゲストライターとして彼の記事を初めて掲載できることを嬉しく思います。

デモファイル: BillOfMaterialsDemo-v2.zip

部品表(BOM)

FileMaker 3が出たばかりの頃、私はFileMakerテンプレートに在庫管理機能を追加しました。それは、「品目」を組み合わせたまとまりを「組立品目」として一つの品目として扱うことができ、なおかつ組立品目の構成部品自体が組立品目であることも許すものでした。組立品目(その構成品目が組立品目である場合もある)を含むということは、「親」の組立品目の部品表全体が、階層リストとして部品間の関係(ある部品の祖先と子孫)を表現するものになるということです。このような表は、「展開された」あるいは「多階層の」BOMと呼ばれ、次のようになります。

親
- 子
  - 孫
  - 孫
  - 孫
- 子
  - 孫
    - ひ孫
    - ひ孫
  - 孫
  - 孫
- 子
- 子…

「親」組立品目を在庫に追加するには、それを構成する数量のすべての「子孫」品目および組立品目が必要です。そのために、多階層のBOMの全体を走査して、必要な数の各品目の親組立品目を生産するのにさらに必要な数量の各品目があることを確認しなくてはいけません。

FM3でこれを実現するのは簡単ではありませんでした。その処理のために、複数のグローバルフィールド、複数のリレーションシップ、似通ってはいるが同一ではないいくつものスクリプトステップが必要でした。親の組立品目で必要とされる子品目および子組立品目のリストを下に向かって走査する各レベルで必要となる(ユーティリティ的に使われる)リレーションシップ、フィールド、スクリプトステップが足かせとなり、階層の深さは制限されていました。

私は最近、深い階層のBOMを持つ製品を作るクライアント向けに、この方法を再検討する機会に恵まれました。彼らの製造工程は、在庫を持たないカンバン方式(just-in-time operation)のため、2つのパラメータで必要資材を特定したいと考えました。親品目の完成予定日に間に合うための、必要な部品のユニット数と、各部品を発注する(そして組立品を作り始める)日です。

一部の組立品目は半製品として購入できるため、組立品目を内製する場合の「開始日」から「目標完成日」までの日数が足りない場合に購入する組立品目の「家系図」を特定します。

サンプルファイルでは、グローバルフィールドの代わりに$変数、複数のリレーションシップの代わりにSQL、そして多階層のBOMで無制限の世代を処理できる再帰的なスクリプトを使用しています。 FileMaker開発者として比較的遅くSQLクエリに触れた私は、このような状況でSQLが開発者にどれだけのパワーを与えてくれるかを発見できて嬉しく思いました。その内の3つの例を以下に示します。

リレーションシップグラフに影響を与えない
クエリによって返されるレコード/行の各値に、接頭辞(prefix)と接尾辞(suffix)のテキスト文字列を追加できる
リレーションシップグラフに存在しない、その場で定義したリレーションから取得できるデータを、(JOINを使って)返されるリストに含めることができる

スクリプト間で複数の引数をやり取りするために、私は名前/値ペアの手法を使用します。ExecuteSQL関数のSELECT部分で上記2を活用して、evaluateに渡すための名前/値ペア関数のリストを結果として生成します。これが、スクリプトが進行するにしたがって、一致する各レコードの対応する値と属性を提供します。このリストを、名前/値ペア関数の値の引数として名前の引数の “children”と共に使用し、その結果(他の名前/値ペアと連結したもの)をサブスクリプト”SubMakeBOMList”に渡すスクリプト引数として使用します。そのサブスクリプトがリストを変数$childrenに入れて、1行ずつ処理します。各ループで、ターゲット行が組立品目である場合にそれ自身を再帰的に呼び出します。

サンプルにはDaysフィールドが含まれていて、すべての子品目と組立品目の在庫があるときに、その品目の購買発注を受ける(または組立品目を作成する)のに必要なリードタイムを表します。またQty(数量)フィールドは、1つの親ユニットを作るのに必要とされる子ユニットの数を表します。

各行を処理すると、必要な日数と数量(その行のQtyと親のQtyの積)が累積されて合計値が更新されます。このサブスクリプトはループ全体の繰り返し回数をカウントし、現在の繰り返し回数を各名前/値ペアの名前の引数の接尾辞(suffix)として追加します。これは、バーチャルリストが各レコードの値を特定するキーとなります。

サブスクリプトはまた、現在の再帰レベルも監視し、名前/値ペアの1つにその値を保持します。これもバーチャルリストを表示するときに使われます。このループは、変数$childrenのすべての行が処理されるまで、更新された行固有の名前/値ペアを(evaluateされるテキスト文字列として)$arrayに追加します。これは、そのリストを「retArray」の名前/値ペアの値として返します。これがスクリプトの結果として取得され、現在の$array変数に追加されます。

親組立品目の変数$childrenの最後の行が処理されると、$arrayは多階層のBOMのすべての子の行を含み、親スクリプトに返されて、バーチャルリストのレコードで使用されるか表示されるために変数に入れられます。

興味深い、あるいは注目すべきと思うこと:

このスクリプトには、無限ループを防ぐエラートラップが含まれています。子品目のBOMにはその祖先の品目を含むことはできず、すべての祖先をリストアップして問題を引き起こした品目を特定するメッセージが表示されます。
このスクリプトはまた、(内製された場合に)開始日と目標完了日の間の日数が必要作業日数を超える組立品目(およびその子孫)を識別し、メッセージを表示します。
スクリプトの結果を活用して、バーチャルリストレイアウトを追加して材料と組立品目を開始日ごとに集約したり、期限を含めるように拡張することもできます。
evaluateするための名前/値ペアの配列の生成は、再帰的なカスタム関数として書けるかも知れません。

サンプルファイルは、巨人の肩に立って(先人の知恵の上に成り立って)います。多階層のBOMを表示するのに、Bruce Robertsonのバーチャルリスト技術を使い、そして他の人の共同作業とアイデアから生まれた計算式が含まれています。(Work Daysは、Ray Cologonによる計算式を使って、日付範囲内の週末を差し引いています。)

多階層のBOMを構築する別の手法として、Daniel Shanahanはより包括的なサンプルファイルを公開し、生産現場で多階層のBOMに直面したときの在庫管理について解説しています。

補遺：名前/値ペア + ExecuteSQL

私は最近までプロジェクトでExecuteSQLをあまり活用してきませんでしたが、ある新しいプロジェクトがそれを使うのに非常に適していて、それが私が今まで見たことも聞いたこともないおもしろい使用法であることを「発見」しました。SQLクエリの連結(concatenation)を使って、evaluateするための名前/値ペアの文字列を生成するという方法です。

私は、開発者が独自の文字列をフィールド区切りとして挿入し、FileMaker関数またはカスタム関数を使用して行から個々のフィールドの値を取り出す例をいくつか見てきました。

私は長年、名前/値ペアを使ってスクリプト引数とスクリプトの結果を渡すという方法を採用してきたので、これにSQLの連結(concatenation)を使ってみることにしました。私は、# ( “Name” ; “Value” ) という、2つの引数を取るカスタム関数(簡単に入力できるように「#」という名前にしました)を持っています。これを使って変数$Nameに値Valueを設定し、引数として別のカスタム関数setLocalVarsに渡します。準備された複数の名前/値ペアは、連結してsetLocalVarsの単一の引数とすることができます。これらは、評価されると一度に変数が設定され、変数$localVarsListでその内容を見ることができます。

これによりデバッグ時に、フィールド区切り引数をスキャンしてカウントすることなく、返された各フィールドの値を簡単に確認することができます。また、名前付きの値を利用するのに”dictionary”型のカスタム関数を使用する必要がなく、渡された名前/値ペアの順序(並びの中での位置)にも依存しないことになります。

私の最近のプロジェクトでは、処理対象の子レコードが、子・孫・ひ孫などを持つ可能性がありました。そこで、スクリプト引数として渡すことができる名前/値ペアの行を作成するのが理想的だと考えました。そうすることで、スクリプトがループ内で1行ごとに処理できます。evaluateする文字列を準備するためには、SQLクエリから返された各値をテキストと連結して、名前/値ペア関数のテキスト版を作成する必要がありました。

また各行は、含まれているすべての関数の結果を連結したテキスト文字列になるように、関数文字列の間にFMP演算子を挿入する必要もありました。1行ごとを引数とするEvaluate()関数は、フォーマットされた名前/値ペアの連結文字列を生成します。その結果は、カスタム関数SetLocalVarsの引数として使用され、すべての行の名前/値ペアが変数$Nameに入れられます。

連結(concatenation)を試したことがない人のために、連結とはExecuteSQL関数のSQLクエリ引数のSELECT部分で使用されます。FileMakerは連結に”＆”を使用しますが、SQLでは”+”または”||”を使用します(私は”||”を使いました)。SQLは、SELECTで返される値と連結されるテキスト文字列をシングルクオートで識別します。Evaluate関数に渡すためには、以下のように名前/値ペアの引数を引用符で囲む必要があります。

# ( "fieldIdentifyingName" ; "responseValueFromSQL" )

そのため、それらを連結するテキスト文字列の中でエスケープします。childID、qty、leadDaysの各フィールド用の連結文字列を含む、比較的単純なSELECTは、次のようになります。

ExecuteSQL ( 
"
SELECT '# ( \"myID\" ; \"'||childID||'\" ) & # ( \"myQty\" ; \"'||qty||'\"
) & # ( \"leadTime\" ; \"'||leadDays||'\" )'
FROM BoM 
WHERE parentID = ? "

; "" ; "" ; Item::pk
)

結果は以下のとおりです。

# ( "myID" ; "10010" ) & # ( "myQty" ; "5" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10015" ) & # ( "myQty" ; "3" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10013" ) & # ( "myQty" ; "2" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10024" ) & # ( "myQty" ; ".001" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10025" ) & # ( "myQty" ; ".001" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10007" ) & # ( "myQty" ; ".1" ) & # ( "leadTime" ; "0" )
# ( "myID" ; "10023" ) & # ( "myQty" ; "3" ) & # ( "leadTime" ; "0" )

これ全体がValueとしてName “children”とペアになり、フォーマットされた名前/値ペアの1つとしてサブスクリプトに渡されます。このサブスクリプトはすべての名前/値ペアを分解し、ループごとに1行の$childrenの名前/値ペアを処理します。サンプルファイルでは、Itemテーブルにのみ存在する追加のフィールド値を取得するためにJOINを使用して、少し複雑なSQLクエリ引数を使用しています。

Geoff Gerhardは、1992年からFileMaker Proを使用して複雑な在庫管理・製造工程管理・会計処理のシステムを構築してきました。2003年以降は自身(および他の開発者)のクライアントのためにFileMakerソリューションとQuickBooksの統合に関わり、さまざまな開発者向けオンラインフォーラムやメーリングリストでこのテーマに関する質問に頻繁に答えています。

Bill of Materials

部品表(BOM)

補遺：名前/値ペア + ExecuteSQL

Leave a Reply Cancel Reply