AppSheetで在庫管理アプリ（第5回）コードをスキャンして入出庫記録

在庫管理アプリを作りながら、AppSheetの使い方を学びましょう。
前回（第4回）は「品目」テーブルの設定をしました。
今回はもうひとつのテーブル「入出庫履歴」を設定します。
実際にQRコードやバーコードをスキャンして、入出庫や棚卸を記録できるようになります。

【お知らせ】
2022年10月以降、AppSheetエディタの画面が新しく変更されています。
AppSheetのアップデートが速すぎて（汗）、このブログ記事はまだ新エディタの画面には対応していません。
エディタ画面右上にある「Switch to the legacy editor」というアイコンをクリックすると、新エディタと旧エディタの切り替えができます。
記事のアップデートができるまでは、とりあえず、旧エディタに切り替えて解説を読んでください。

1. AppSheetで入出庫や棚卸を記録する

AppSheetで入庫、出庫、棚卸を記録するために使用する「入出庫履歴」テーブルの列を設定します。
基本設定は第2回で終わっています。
今回は「操作内容（入庫、出庫、棚卸）」に合わせて表示や計算式を変える設定を行います。

AppSheetエディタの左側メニューにある「Data」をクリック、タブメニュー「Columns」をクリックします。
「入出庫履歴」をクリックして、列設定パネルを開きます。

まずは、「変更前の数量」列を設定します。
「変更前の数量」の右にあるペン型の編集アイコンをクリックして設定パネルを開きます。

「変更前の数量」を取得するための計算式を設定します。
Auto Compute → Initial Value の三角フラスコアイコンをクリックして式を入力します。

「変更前の数量」の式は、次のような手順で作成しました。

1. 「入出庫履歴」テーブルで、スキャンした品目と同じ品目のレコードの最終行を探す
2. 1のレコードの「変更後の数量」のリストを取得する
3. 2のリストに値があるか調べる
   1. 値がある =>リスト内の任意の値を「変更前の数量」 にする
   2. 値がない => 「品目」テーブルの「数量」の値を「変更前の数量」にする

以下、順を追って解説します。

まずは、「入出庫履歴」テーブルで、スキャンした品目と同じ品目のレコードの最終行を探しましょう。
ここでは、SELECT関数とMAXROW関数を使って以下のような式を作りました。

【式1】「変更後の数量」の値のリストを取得する式

SELECT(
 入出庫履歴[変更後の数量],
 [ID] = MAXROW("入出庫履歴", "_RowNumber", [_THISROW].[品目] = [品目])
)

SELECT関数

SELECT関数の使い方については、請求書アプリで詳しく解説しています。
こちらを参照してください。

SELECTというAppSheet関数は、列の値をリストで取得できる関数です。
第1引数にテーブル名と列名を設定し、第2引数に条件式を設定します。
第3引数を「TRUE」にすると重複する値を削除します。第3引数は省略可です。

【式1】の意味は、

• 「入出庫履歴」テーブルの「変更後の数量」列から値のリストを取得する
• 「入出庫履歴」テーブルの「ID」列の値が、MAXROW関数が返す値と同じ値である

となります。「MAXROW関数が返す値」とは何でしょうか？

MAXROW関数

MAXROWというAppSheet関数は、KEYの値を返します。
第1引数にテーブル名、第2引数に列名、第3引数（省略可能）に条件式を設定します。

MAXROW関数では、第2引数に設定した列の中で最大値を持つ行（レコード）のKEYの値が返ります。
第3引数を設定している場合は、その条件式に合致する列の中での最大値を持つ行（レコード）になります。

実例でMAXROW関数の使い方を学びましょう。
「品目」テーブルの「品目名」が「アミスター20フロアブル」について、これから入出庫記録を作成すると仮定します。

「入出庫履歴」テーブルに、以下スプレッドシートのように6件の記録があります。
「アミスター20フロアブル」の「ID」は「4543887300215」なので、「品目」列が「4543887300215」の行（レコード）を探すと、3件見つかりました。

見つかった3件のうち「_RowNumber」（行番号）が最大なのは「6」ですね。
なので、「_RowNumber」の中で最大値を持つ行（レコード）は「6」の行になります。

MAXROW関数は上の「6」の行のKEYの値を返します。
「入出庫履歴」テーブルのKEYは「ID」なので、返ってくる値は「bc758b1f」です。

【式1】のMAXROW関数の部分は以下のようになります。

【式2】スキャンした品目と同じ品目のレコードの最終行を取得する式

MAXROW("入出庫履歴", "_RowNumber", [_THISROW].[品目] = [品目])

「_THISROW」は、今やろうとしている入出庫記録のことです。
上の実例では、 [_THISROW].[品目] = 「4543887300215」（アミスター20フロアブルのID）となります。
つまり、【式1】のMAXROW関数を実例に当てはめると

• 「入出庫履歴」テーブルの中で
• [_THISROW].[品目] = [品目]の条件式に合致する => 「品目」列の値が「4543887300215」の行（レコード）
• 「_RowNumber」列の値が最大の行（レコード） => 「6」の行（レコード）
• KEYの値 => 「bc758b1f」

となります。

では、MAXROW関数で取得した列（レコード）の「変更後の数量」のリストを取得してみましょう。
【式1】のSELECT関数に当てはめると、

SELECT(
 入出庫履歴[変更後の数量],
 [ID] = bc758b1f
)

となります。SELECT関数が返す値は「変更後の数量」の値のリストなので、 { 30 } です。

ANY関数

「変更後の数量」の値のリストが取得できました。
次に、取得したリスト、{ 30 }から任意の値を取り出します。

今、設定している「変更前の数量」列のTYPEは「Number」です。
{ 30 }は「List」なので、データTYPEが異なるためこのままだとエラーになります。
「List」TYPEのデータから値を取り出して、「Number」にする必要があります。

ここでは、ANYというAppSheet関数を使用します。
ANY関数は、（）内のLISTから任意の値を取り出すことができます。

{ 30 }内にはひとつしか値がないので、ANY関数で取り出す値は「30」です。
SELECT関数とMAXROW関数を組み合わせて取得したリストには値がひとつしかないので、ANY関数で大丈夫です。

ここまでを式で表すと以下のようになります。

【式3】リスト内の任意の値を取り出す式

ANY(
 SELECT(
 入出庫履歴[変更後の数量],
 [ID] = MAXROW("入出庫履歴", "_RowNumber", [_THISROW].[品目] = [品目])
 )
)

COUNT関数

品目ID「4543887300215（アミスター20フロアブル）」には入出庫履歴がありました。
よって、【式3】で「変更後の数量」の値を取り出すことができました。

ということは、入出庫履歴がない品目の場合は「空の値」が返ってきてしまいます。
そうなると、現時点で在庫がないことになり不都合が生じます。

これから入出庫記録を作成しようとする品目について、入出庫履歴があるかないかを事前に調べておく必要がありそうですね。
COUNTというAppSheet関数を使って調べてみましょう。
COUNT関数は、（）内のLIST内にある値の数を返します。

【式1】のSELECT関数が返すリスト内に、値が1つ以上あるかないかをCOUNT関数で調べればよいということです。

• COUNT( 【式1】) > 0 => 入出庫履歴がある => 【式3】で「変更後の数量」を取得
• それ以外 => 入出庫履歴がない => 「品目」テーブルの「数量」の値を取得

以上を、IF関数を使って式にすると、

IF(COUNT(【式1】) > 0, 【式3】,[品目].[数量])

となります。

以上、まとめると「変更前の数量」の式は以下のようになります。コピペして使ってください。

IF(
 COUNT(
  SELECT(
   入出庫履歴[変更後の数量],
   [ID] = MAXROW("入出庫履歴", "_RowNumber", [_THISROW].[品目] = [品目])
  )
) > 0,
ANY(
 SELECT(
  入出庫履歴[変更後の数量],
  [ID] = MAXROW("入出庫履歴", "_RowNumber", [_THISROW].[品目] = [品目])
 )
),
[品目].[数量]
)

Expression Assistant に入力すると以下のようになります。
ここまで長くなりましたが「変更前の数量」のInitial Valueに入力しましょう。
入力したら「Save」をクリックして保存します。

次に、「数量」列を設定します。
Auto Compute → Initial Value の三角フラスコアイコンをクリックして式を入力します。

Initial Value（初期値）を「1」に設定しましょう。
Expression Assistantに「1」を入力、「Save」をクリックして保存します。

「数量」列は、「操作内容」で選択した値によって分かりやすく表示を変えたいと思います。
Display → Display nameの三角フラスコアイコンをクリックして、Expression Assistantを開きます。

以下のように入力してください。コピペして使えます。IFSという AppSheet関数を使って、条件分岐しています。

IFS(
 [操作内容] = "入庫", "入庫数量",
 [操作内容] = "出庫", "出庫数量",
 [操作内容] = "棚卸", "棚卸後の数量"
)

次に、「数量の増減」列を設定します。
「数量の増減」列は入庫と出庫の時だけ表示して、棚卸の時は表示しないようにします。

前回（第4回）で学んだ「SHOW?」を使って表示非表示を設定しましょう。
ペン型の編集アイコンをクリック → 設定パネルを開いて「Show?」の三角フラスコアイコンをクリックします。

Expression Assistantに以下の式を入力します。コピペして使えます。
ORというAppSheet関数を使って、「操作内容が入庫、または、操作内容が出庫」を式にしています。
操作内容が棚卸の時は非表示になります。

OR([操作内容] = "入庫", [操作内容] = "出庫")

 

「数量の増減」の計算式を設定します。
Auto Compute → App formulaの三角フラスコアイコンをクリックして、Expression Assistantを開きます。

以下の式を入力します。コピペして使えます。

IFS(
 [操作内容] = "入庫", [数量],
 [操作内容] = "出庫", -([数量]),
 [操作内容] = "棚卸", [数量] - [変更前の数量]
)

ここもIFS関数を使って、入庫、出庫、棚卸で計算式を分岐しています。
出庫は、「数量」列の値をマイナスにします。
棚卸は、「数量」列の値から「変更前の数量」の値を差し引くと、棚卸の数量増減になります。

最後に、「変更後の数量」列の計算式を設定します。
Auto Compute → App formula の三角フラスコアイコンをクリックして、Expression Assistantを開きます。

計算式は以下の通りです。
入庫は「変更前の数量」に「数量」を加算。出庫は逆に減算。棚卸は「数量」の値がそのまま「変更後の数量」になります。

IFS(
 [操作内容] = "入庫", [変更前の数量] + [数量],
 [操作内容] = "出庫", [変更前の数量] - [数量],
 [操作内容] = "棚卸", [数量]
)

2. AppSheetでコードをスキャンして入出庫棚卸を記録する

実際に、入出庫記録について画面を見てみましょう。
AppSheetエディタのプレビューでは「品目」列のスキャン機能が使えません。
実際にQRコードやバーコードをスキャンするためには、スマホアプリ版やタブレットアプリ版で試してみてください。

「品目」のアイコンをクリックするとスキャン画面になります。
コードをスキャンすると品目名が表示されると思います。

「操作内容」で入庫を選択すると、「数量」列が「入庫数量」と表示されるようになります。

「入庫数量」を動かすと、「数量の増減」と「変更後の数量」が計算されます。

「操作内容」で出庫を選択すると、「数量」列が「出庫数量」と表示されるようになります。

「出庫数量」を動かすと、「数量の増減（マイナス）」と「変更後の数量」が計算されます。

「操作内容」で棚卸を選択すると、「数量の増減」列は非表示になります。

「棚卸後の数量」を動かすと、「変更後の数量」が計算されます。

3. 入出庫履歴Viewを設定する

最後に、「入出庫履歴」のViewを設定しましょう。

AppSheetエディタのプレビュー、メニュータブで「入出庫履歴」を選択します。
プレビュー画面の下に「View: 入出庫履歴」というリンクがあるのでクリックします。

「入出庫履歴」Viewの設定パネルが開きます。
ここでは「入出庫履歴」Viewを以下のように設定しました。

設定項目	値
View name	「入出庫履歴」
For this data	「入出庫履歴」
View type	「table」
Position	「left」
View Options
Sort by	「Add」→「作成日時」,  Descending
Column order	「Add」→「作成日時」「品目」「操作内容」「変更前の数量」「数量の増減」「変更後の数量」「メモ」
Display
Icon	「cart-arrow-down」

Column order の画面は以下のようになります。

以上で、「入出庫履歴」テーブルに関連する設定が終了し、入出庫記録ができるようになりました。
難しかったのは「変更前の数量」列の計算式の設定でした。「変更前の数量」=「在庫」ですから、この式は重要です。
なので、できるだけ丁寧に解説しました。
SELECT関数とMAXROW関数の組み合わせは使えるTipsなので、是非マスターしてください！

次回（第6回）は、「品目」テーブルに在庫量を表示します。