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

folder_openAppSheet

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

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

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

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

AppSheetエディタで「入出庫履歴」テーブルの列を設定する。

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

操作内容で列の表示非表示設定、数量を計算するための式を変える。

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

変更前の数量を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引数は省略可です。

SELECT関数

SELECT( テーブル名 [列目], 条件式) => 条件に合致するレコードの列の値をリストで返す

【式1】の意味は、

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

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

MAXROW関数

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

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

MAXROW関数

MAXROW( テーブル名 , 列名, 条件式) => (条件式に合致する)列の中で最大値を持つ行(レコード)のKEY

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

ある品目についてAppSheetで入出庫を記録する。

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

該当するレコードが3件ある。

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

「_RowNumber」が最大の行を探す。

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

KEYの値を取得する。

【式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 } です。

{ 30 }が返る。

ANY関数

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

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

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

ANY関数

ANY( 値のリスト ) => SELECT関数などで取得したリスト内の任意の値を取り出す

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

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

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

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

COUNT関数

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

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

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

COUNT関数

COUNT( 値のリスト ) => リスト内の値の数を返す

【式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」をクリックして保存します。

SELECTとMAXROWというAppSheet関数で変更前数量を表示する。

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

「数量」のInitial Valueを設定する。

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

Initial Value(初期値)は1にする。

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

「数量」の表示名を操作内容で変える。

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

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

複数の条件分岐をIFSというAppSheet関数で設定する。

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

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

「数量の増減」を操作内容で表示非表示設定する。

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

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

 

「数量の増減」は入庫、出庫時にのみ表示する。

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

「数量の増減」の計算式をApp formulaに設定する。

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

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

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

App FormulaにIFSというAppSheet関数を使って操作内容別に計算式を設定する。

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

「変更後の数量」をApp formulaに設定する。

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

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

App FormulaにIFSというAppSheet関数を使って操作内容別に計算式を設定する。

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

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

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

AppSheetエディタのメニュータブで「入出庫履歴」をクリックして、記録してみる。

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

操作内容で「入庫」を選択すると、「入庫数量」と表示される。

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

入庫数量を入力すると、「数量の増減」はプラス、変更後の数量は変更前の数量プラス入庫数量になる。

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

操作内容で「出庫」を選択すると、「出庫数量」と表示される。

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

出庫数量を入力すると、「数量の増減」はマイナス、変更後の数量は変更前の数量マイナス出庫数量になる。

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

操作内容で「棚卸」を選択すると、「数量の増減」列は表示されない。

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

棚卸後の数量を入力すると、変更後の数量は棚卸後の数量と同じになる。

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

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

AppSheetエディタのプレビュー、メニュータブで「入出庫履歴」を選択します。
プレビュー画面の下に「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」

「入出庫履歴」Viewを設定する。

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

Column orderを追加した。

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

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

Share Me!

前の投稿
AppSheetで在庫管理アプリ(第6回)Virtual Columnで在庫量を表示する
次の投稿
AppSheetで在庫管理アプリ(第4回)SHOWで列の表示を切り替える

関連記事

メニュー