by nomfjmt

3ステップで簡単バーチャルリスト

ExecuteSQLとセットで使われるバーチャルリストについては、今までにいくつも紹介記事が書かれているようですが、シンプルでわかりやすい記事を見つけたので紹介します。

3ステップで簡単バーチャルリスト
( Virtual List in Three Easy Steps )

Anders Monsen
2013/4/12

バーチャルリストの手法は、2009年頃にBruce Robertsonが発表して以降、広く使われています。その後のFileMakerの機能追加とともに、この手法も進化を続けています。バーチャルリストをどう設定して使いこなすかを知れば、特にFileMaker Pro 12からの機能であるExcuteSQLで値を取得する場合に、レポートの作成がとても簡単になります。関連の記事を検索すると、Lisette WilsonのTaming the Virtual List (バーチャルリストを使いこなす) Part I, Part IIとSeedCodeの応用例をすぐに見つけることができます。ではなぜまた新たな記事を書くのかと問われれば、既存のサンプルや記事ではユーザ固有の例を使ってバーチャルリストを説明していますが、この記事では3つのシンプルなステップに整理してみます。このプロセスの基本的な部分をFileMakerファイルに落とし込めば、さらに複雑なレポートを作成するときにもテンプレートとして利用できるでしょう。ソリューションごとにテーブルとスクリプトをコピーして、テンプレートとして再利用してください。この手法では、レポート専用のテーブル1つと、グローバル変数に値を設定するスクリプトを使用します。レポート用テーブルのフィールドは、計算フィールドとして設定され、グローバル変数の中の配列から値を取得します。

手順を説明するために、Starter Solutionの中の「Contacts」にテーブルを追加したものをサンプルデータベースとしてここからダウンロードできるようにしました。

ステップ1：レポート用テーブル

レポートは通常、行と列で構成されます。各行はテーブル内のレコードに、各列はフィールドに、それぞれ対応します。少なくとも2つのフィールドを使用します。1つ目のフィールドは非表示のidフィールドです。これはレポートの重要な構成要素ですが、表示はされません。2つ目のフィールドはレポートに表示され、スクリプトによって生成されたデータが含まれています。下のシンプルな2列のサンプルは、小計レ​​ポートとして作成することも可能です。しかしバーチャルリストを使えば、複数のテーブルからのデータを結合することができます。これは小計レポートの機能ではそう簡単には作れません。デモファイルには3つのフィールドがあり、1つはidで、残りの2つは実際の列データ(州と人数)です。

ステップ2：フィールド

表に現れる2つのフィールドは、idフィールドによって決められます。このidフィールドは数値として設定され、1から始まり1ずつ増加します。大抵のレポートは100行を超えることはないので、経験則として100レコードを生成します(もしそれ以上の行が必要なことがわかっていたら、必要分を作成します)。レポートのレイアウトをテーブルビューで表示させた場合、100行のidフィールドには1から100までの値が表示されているはずです。

2つ目のフィールドは、レポートに表示される最初の列です。レポートは複数の列を持つことができるので、必要に応じて新しいフィールドを同じ原理で作成します。汎用的に使えるように、私のレポートテンプレートではフィールド名を「col1」,「col2」とし、テキストフ​​ィールドとして設定しています。データを数値としてフォーマットする(例えば$43,000.00のように)必要がある場合も、テキスト関数で対応が可能です。列フィールドは非保存の計算フィールドとして、式 GetValue($$COLUMN1; id) が設定されています。これは、ステップ3のグローバル変数$$COLUMN1を作成するスクリプトの後によりよく理解できるでしょう。変数$$COLUMN1が設定されるまでは、「col1」のフィールドには値が入っていません。変数が設定されると、フィールドが配列である$$COLUMN1の内容を計算し、その行のid番号に対応する値を導きだします。これが、バーチャルリストという手法の魔法の部分です。

たとえば$$COLUMN1に以下の値が設定されているとします。

  • Banana
  • Apple
  • Orange
  • Pear
  • Grape

この配列には5つの値が含まれています。最初の5レコードではidフィールドの値が1〜5になり、GetValue()関数が対応する果物を「col1」フィールドに抽出します。

ステップ3：行を埋める

ここがプロセスの中で最も自由度が高い部分です。複雑なループの入れ子の場合から、シンプルなスクリプトステップの実行だけの場合まで、いろいろな方法で行と列に値を埋め込みます。まず、レポート用テーブルが正常に動作するのを確認するため、単純なループのスクリプトを作成します。

変数を設定[ $Limit ; 値: 100 ] Loop
Exit Loop If [ Let( $i = $i + 1 ; $i < $Limit ) ] 変数を設定[ $$COLUMN1 ; 値: If( IsEmpty($$COLUMN1) ; $i ; $$COLUMN1 & ¶ & $i ) ] End Loop
ウインドウ内容の再表示 [キャッシュ結合結果を書き込む]

このループは、GetValue関数のために最初の列に数字を入れます。

レポートに実際の値を挿入するために、「変数を設定」の行を変更してみましょう。ContactsVLデータベースでは、州ごとの人数を求めていました。私の場合はすべての州を変数に入れ、この配列をループしました。その後、州ごとのループ内でSQLクエリを実行しました。

#Column1
変数を設定 [$$COLUMN1 ; 値: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ] #Column2
変数を設定 [ $Result ; 値: ExecuteSQL( “select count(*) from contacts where \”Work State\” = ‘” & $State & “‘” ); “” ; “” ) ] 変数を設定 [ $$COLUMN2 ; 値: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ] #End

別の例では、配列をループする代わりに、レポートごとにサブヘッダ用のテキストを加工します。ここではそれぞれの行で、結果を変数$$COLUMNxに設定するだけです。配列に値を追加していき、その後でこの配列を解析してレポート用テーブルの各行に値を入れます。サブヘッダ用には、書式設定関数を使ってボールドにしたりフォントサイズを大きくしたりして目立たせます。

まとめ

ステップ1,2のようにレポート用テーブルを一度設定すれば、この手法はいろいろな種類のレポートに利用できるでしょう。シンプルなレポート用テーブルと1,2本のスクリプトを雛形として準備するだけで、この手法を簡単に個別のソリューションに適用することができます。

Leave a Reply