請求書アプリを作りながら、AppSheetの使い方を学びましょう。
前回（第3回）はテーブル間のリレーションシップを利用して、請求書と品目明細の親子関係を設定しました。
今回は、SELECT関数を使って品目明細から金額の値リストを取得して、SUM関数を使って合計します。
SELECT関数はよく使う関数なので、使いこなせばAppSheetアプリ開発の幅が広がりますよ！
目次
1. 「品目マスタ」Viewを追加する
ここまでテーブルと列の設定を進めてきましたが、まだ請求書アプリのイメージが湧いてこないのではないでしょうか。
サンプルデータを追加することでアプリのイメージが掴めるようになります。
データを追加する前に、必要なViewを追加しておきます。
「品目マスタ」テーブルにデータを追加するためのViewがまだありません。
Viewの設定は次回（第5回）行いますが、「品目マスタ」Viewだけ先取りして作っておきましょう。
それでは、Viewを設定しましょう。
AppSheetエディタ左側メニューにある「UX」をクリック、タブメニュー「Views」をクリックします。
「New View」をクリックして新しいView「品目マスタ」を作成します。
ここでは「品目マスタ」Viewの設定を以下のように変更しました。
View type は「deck」または「Card」の「list」あたりがよいと思いますが、ここでは「deck」にしました。
| 設定項目 | 値 |
|---|---|
| View name | 「品目マスタ」 |
| For this data | 「品目マスタ」 |
| View type | 「deck」 |
| Position | 「right」 |
| View Options | |
| Primary header | 「品目」 |
| Secondary header | 「単価」 |
| Nested table column | 「単位」 |
| Actions | 「Edit」 |
| Display | |
| Icon | 「boxes」 |
2. サンプルデータを追加してみる
では、サンプルデータを追加してみましょう。
「取引先」と「品目マスタ」を追加します。
AppSheetから取引先を追加する
取引先を1件、追加してみましょう。
「取引先」をクリックして選択、「+」ボタンで入力フォームを開いてください。
架空の取引先でよいので、入力後に「Save」をクリックして保存してください。
電話番号等は、スマホでアイコンを触るとAppSheetが相手に電話をかけてしまうので、あり得なそうな番号（9999-9999など）にしておくとよいでしょう。
スプレッドシートにはこのように記録され、レコードが1件作成されます。
AppSheetから品目マスタを追加する
品目マスタを何件か追加してみましょう。
「品目マスタ」をクリックして選択、「+」ボタンで入力フォームを開いてください。
「品目」「単価」「単位」「税区分」の順で入力していきます。
例は食品なので税区分が「軽減8％」ですが、通常は「10％」でよいです。
「Save」をクリックして保存します。
スプレッドシートにはこのように記録されます。
3〜4件、作成しておくとよいでしょう。
3. AppSheet関数、SELECTとSUMで金額を合計する
請求書には品目明細の金額を合計して、請求金額や小計などを表示する欄があります。
「請求書」テーブルと「品目」テーブルにはリレーションシップが設定されています。
「請求書」レコードの「ID」と同じ「請求書ID」を持つ「品目」レコードを抜き出して、その金額を合計すれば小計などを計算できます。
SELECT関数
ここで、SELECT関数とSUM関数を用いて、合計金額を計算する式を設定します。
SELECTというAppSheet関数は、列の値をリストで取得できる関数です。
第1引数にテーブル名と列名を設定し、第2引数に条件式を設定します。
第3引数を「TRUE」にすると重複する値を削除します。第3引数は省略可です。
SELECT( テーブル名 [列目], 条件式) => 条件に合致するレコードの列の値をリストで返す
値のリストというのは、PHPやJavaScriptのようなスクリプト言語でいうところの配列です。
AppSheetには配列（Array）という用語がなく、代わりにリスト（List）という用語を使用します。
リストは「{ }」（中括弧、波括弧）内に、数値や文字列を「,」（コンマ）で区切って並べます。
文字列の場合は、「""」（ダブルクォーテーション）で囲みます。
- { 100, 200, 300 }
- {"香川", "徳島", "愛媛", "高知"}
LISTというAppSheet関数も用意されており、「{ }」の代わりに使用できます。
- LIST(100, 200, 300)
- LIST("香川", "徳島", "愛媛", "高知")
では、請求書アプリの実例でSELECT関数の使い方を学びましょう。
試しに「請求書」テーブルに3件の請求書を追加してみました。スプレッドシートは以下のようになります。
「ID」列の値が「854b939c」の請求書について、小計を計算してみます。
「品目」テーブルで「請求書ID」が「854b939c」の品目を探します。
4〜6行目が探している請求書の品目であることが分かります。
小計を計算したいので、欲しいのは「金額」列の値のリストです。
{ 69445, 20834, 55556 }が欲しい値のリストになります。
以上の実例をSELECT関数で式にすると以下のようになります。
「_THISROW」というのは「請求書」テーブル3行目のレコードのことを指します。つまり、
ということになります。
品目明細から金額のリストを取得する
SELECT( 品目[金額], [請求書ID] = [_THISROW].[ID] )
式の意味は以下のようになります。
返ってくる値は、{ 69445, 20834, 55556 } です。
- 「品目」テーブルの「金額」列から値のリストを取得する
- 「品目」テーブルの「請求書ID」列の値が、「請求書」テーブルの現在レコード行の「ID」列と同じ値である
SUM関数
SELECT関数で取得した値リストを合計すれば小計を計算できます。
数値の合計にはSUMというAppSheet関数を使用します。
SUM( リスト ) => リストの値の合計する
品目明細から小計を計算する式は、以下のようになります。
品目明細から小計を計算する
SUM( SELECT( 品目[金額], [請求書ID] = [_THISROW].[ID] ) )
実例でこの式を実行すると、SUM( { 69445, 20834, 55556 } ) で、「145835」が返ります。
この請求書の小計は、145,835円ということになります。
4. AppSheetではVirtual Columnで計算するのが便利
実は、「請求書」テーブルには「小計」「消費税」「合計」を表示する列がありませんでした。
AppSheetでは、計算結果を表示する列としてVirtual Columnを使うのが簡単です。
「小計」「消費税」「合計」は、Virtual Columnで表示することにします。
Virtual Column とは文字通り「仮想的な列」です。
品目明細を追加、更新、削除したときに、「請求書」テーブルのスプレッドシートを更新しなくても「小計」「消費税」「合計」の計算結果を更新できるため便利です。
AppSheetエディタの左側メニュー「Data」をクリックします。
タブメニュー「Columns」をクリックして、「請求書」をクリックして列設定パネルを開きます。
「Add Virtual Column」をクリックして、Virtual Columnを追加します。
「小計」列
Virtual Column設定パネルが開きます。
Column nameに「小計」と入力します。
次に、App formula の三角フラスコアイコンをクリックします。
Expression Assistant に以下のように入力して、「Save」をクリックします。
入力する式は、【品目明細から小計を計算する】の式をコピペできます。
「小計」はお金を扱う列なので、Typeを「Price」にします。
あとは日本円の設定を、Type Detailsで次のように設定します。
- Decimal digits：「0」
- Show thousands separator：チェックする
- Currency symbol：「¥」
「消費税」列
続けて、「Add Virtual Column」をクリックして、Virtual Columnを追加します。
Column nameに「消費税額」と入力し、App formulaの三角フラスコアイコンをクリックします。
Expression Assistantに以下のように入力して、「Save」をクリックします。
「消費税」列は「小計」列の式を少し変えるだけです。[金額]を[消費税額]に置き換えます。
あとは、Typeを「Price」にして、Type Details を「小計」と同じように設定します。
- Decimal digits：「0」
- Show thousands separator：チェックする
- Currency symbol：「¥」
「合計」列
最後に「合計」列を追加します。
「Add Virtual Column」をクリックして、Virtual Columnを追加します。
Column nameに「合計」と入力し、App formulaの三角フラスコアイコンをクリックします。
Expression Assistantに以下のように入力して、「Save」をクリックします。
「合計」列は「小計」列と「消費税額」列を足し合わせるだけです。
これで請求書を作成する準備はできました。試しに1件、請求データを作成してみてください。
以下のように、「小計」「消費税額」「合計」の金額が計算され、表示されるようになります。
以上で品目明細から請求書の小計、消費税額、合計を計算できるようになりました。
リレーションシップを利用して、親テーブルに関連する子テーブルの値リストを取得して合計するテクニックは様々なアプリで応用が効きます。
もちろん、見積書や納品書にも応用ができるので、この機会に是非習得してください。
次回（第5回）はViewを設定してアプリの見た目を整えます。
