１回目のテーマは売上集計にしようと思います。
まずはこれを見てもらいたいです。
架空の飲食店の売上データを、FileMakerで作ってみました。
飲食店やスーパーなんかで使われているPOSシステムというのがあります。
お会計した後、POSレジが記録していくデータは、こんな感じだと思います。
いつ、どこで、どの商品を、どれだけ売りました、その積み重ねです。
さて、このとにかくたくさん貯めておいたデータの塊から、何がわかるでしょうか？
もちろん、このままの状態では、何もわかりません。
このよくわからないデータの羅列を、「使えるデータ」にしましょう。
これが売上の集計になります。
飲食店で言えば、集計をすることで、たとえば、こんなことがわかると思います。
今日一日で一番売れた商品ベスト１０、一番お客さんが少なかった座席、複数ある支店の中で、一番よくがんばったお店、ランチタイムの集客数を昨日と今日で比較する……などなど。
可能性は無限大です。
へ～。集計したらこんなことがわかった。だから、明日はこうしようかな。
計算はコンピュータ、その後を決めるのは人間ですけど、何もわからない状態では、「明日はこうしようかな」という感情も生まれないですよね。
当たり前ですが、売上を集計・分析することは、とても大切なことだと思います。
ちょっと話が逸れました。
今回はFileMakerを使って、ごく簡単な売上の集計をしてみたいと思います。
その方法として、FileMakerには集計用のちゃんとした機能があるんですが、FileMakerをやったことがない人には、少しわかりにくい機能かもしれません。
また、ちょっと複雑で、かついろんなパターンの集計をしたい場合は合わない気がするので、今回はExecuteSQL関数という、別の方法を使います。
Exeなんたら～というのは、FileMakerに搭載されている機能の１つです。
※FileMakerのバージョン12以上でないと使えないと思います。
どういうものかというと、FileMakerで作ったデータの塊に対して、魔法の言葉を叫び、ほしいデータを抽出できるというものです。
魔法の言葉と言いましたが、これは正式には「SQL」と言う言葉です。
SQLは、データベース操作言語として、広く使われている技術です。
SQLについて簡単に説明すると、たとえば、ここに１万人の名簿を入れた箱があります。
この中から、「鈴木さん」の名簿だけを作りたいとき、人間の力でこれをすると発狂するのがオチです。
なので、人間はこの箱に向かってSQLという呪文を唱えます。
正しい呪文を唱えると、コンピュータが鈴木さんだけの名簿を作ってくれます。
僕自身まだ勉強中なので、こんな説明が限界です。すみません。
そういうわけで、FileMakerで作られたデータの箱に対して、このSQLを、Execute=実行するという機能。
それが、ExecuteSQL関数になります。
ところで、SQLはデータを抽出するだけではなく、複数のデータの合計を出したり、平均を出したりといったこともできます。
ExecuteSQL関数においてもそれができるため、売上データの集計に使うことができるのです。
ただ、今回の趣旨はこの関数をとりあえず使ってみるということにしておきます。
集計はまたの機会にして、データの抽出だけをやってみたいと思います。
使用するデータの箱についても、集計には最初の売上データを使うつもりですが、今回は単純に、飲食店のメニューの一覧を使います。
ここからは、デモ用のファイルをダウンロードしてもらうと、わかりやすいかと思います。
２つのファイルを同じフォルダに置いて、main.fmp12の方を開いてください。
※FileMakerのバージョン12以上の環境が必要です。
レイアウトを「Demo1」にして、「集計」のボタンを押してみてください。
メニューの一覧らしきものが出てきたと思います。
左から、商品ID、商品名、商品分類ID、商品分類名、単価という並びです。
「初期化」ボタンを押すと消えます。
何をしているかというと、FileMakerの別ファイルにあらかじめ、メニューの情報を登録した箱を用意しておいて、ボタンを押した瞬間、その箱からデータを取ってきています。
ここでちょっと、図を見てもらいたいです。
FileMakerでは、データの箱のことを「テーブル」と呼びます。
「商品Mt.」というテーブルには、この飲食店のメニューが１２個、あらかじめ登録されています。
この商品Mt.テーブルに向かって、「商品Mt.さんに入っているデータ全部をください」という意味合いのSQLを唱えると、ご要望のデータが返ってきます。
Demo1の画面には、その結果が表示されているというわけです。
えっ、でもちょっと、その商品Mt.とかいうやつを見れば、どんなメニューがあるかわかると思うんですけど、こんなことして何になるんですか？
はい、その通りです。
でもこれが、ExecuteSQL関数の一番簡単な基本形だと思うので、今回はあえてこんなことをしました。
※余談ですが、本来、FileMakerは、別テーブルにあるデータを使う場合は、リレーションシップというものを使って、テーブルとテーブルを紐付けなければいけません。
ところがExequteSQL関数を使うと、そんなことをしなくても、別テーブルのデータを簡単に呼び出すことができます。
これはFileMakerの使い方としては道を踏み外したも同然だと思うんですが、データベースをやったことのない人には、こっちのほうが直感的でわかりやすい気がします。
ただし後々、ExecuteSQL関数とリレーションシップは組み合わせて使います。
ここからは、ExecuteSQL関数の中身についてです。
呪文が嫌いな人には辛いかもしれないですが、やっていることは単純です。
上のメニューから「スクリプト」を選んで、左のリストから「Demo1_集計」を選んでください。
変数を設定、ExequteSQLという部分が見えてくると思います。
ダブルクリックすると、詳細が見れます。
ExecuteSQL関数は、
ExecuteSQL ( "SQL文（呪文）" ; "フィールド区切り" ; "行区切り" )
という形をしています。
フィールド区切りと行区切りは、", " や " - " などの文字を指定することで、データに区切りをつけて、見やすくするためのものです。
たとえばフィールド区切りに ", "、行区切りに "@@" を指定すると、結果は、
IT_001, 苺のショートケーキ, IC_01, スイーツ, 400@@IT_002, ザッハトルテ, IC_01, スイーツ, 390@@……
こんなふうになります。
行区切りは、指定するとかえって見にくくなります。
"" にすると、今回の場合、商品ごとで改行になるので、そのほうがいいと思います。
そんなことよりも重要なのは、()の中で最初に出てくる、SQL文（呪文）というところです。
どのテーブルから、どのデータを取ってくるか、という命令を、ここに書きます。
当然、何がしたいのか、どんなデータをどのように取りたいのかによって、内容は変わってきます。
SQLを勉強すると、好き勝手書けるようになると思います。
さて、今回の命令は、「商品Mt.テーブルのデータを全部ください」ということでした。
これを呪文に翻訳すると、以下のようになります。
SELECT \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\", \"単価\" FROM \"商品Mt.\"
ちょっと見にくいんですが、FROMの後に、テーブルの名前を入れます。
SELECTの後には、項目を入れます。
これだけです。
項目について、少しだけ補足します。
今回の商品Mt.テーブルならば、１つの商品（FileMakerでは１レコードといいます）に対して、商品ID、商品名、商品分類名など、いくつかの項目があります（FileMakerではフィールドといいます）。
飲食店のメニューの一覧というデータベースには、商品名以外にも、必要な項目がありますよね。
それぞれのメニューのカテゴリとか、値段とかですね。
これは最初に、商品Mt.テーブルというデータの箱自体を作るときに設定します。
商品１つ１つのデータは、こうして箱の大枠を作った後に入れていきます。
ということで、SELECTの後には、この項目の名前を全部書いています。
もし分類に関しての情報がいらないなら、どうすればいいでしょうか。
SELECTの後に続く、商品分類IDと商品分類名を消去すればいいです。
その場合、その項目のデータは取ってこないということになります。
当然、「商品ID」と書くところを、「商品番号」や「商品コード」と書くと、うまくいきません。
そんな名前の項目はありませんので、データを取ってくることができないからです。
ちなみに「商品id」と書くと動くみたいですが、同じにしておいたほうが安全だと思います。
また、面倒なんですけど、項目やテーブル名の両サイドに\"を入れないと、これまたうまくいきません。
これは仕様のようなので、仕方ありません。
今回はすでにある商品の一覧から、同じ形式のデータを取ってくるという、あまり意味がないことをしました。
次回からは、ここに計算を織り交ぜて、意味のあるデータを取ってくるようにしていきたいと思います。
