計算プラグインのバージョン2.2.0から関連レコード一覧内のフィールドを計算式に含めることができるようになりました！
関連レコード一覧内のフィールドに対して、計算プラグインがサポートする関数（サポート関数一覧はこちら）を使用することができ、基本的な集計はもちろん、条件指定の集計や集計に限らない様々な計算ができます。
また、計算プラグインの一括再計算機能により、関連レコード一覧のレコードに変化があった場合に、編集画面を1つ1つ開いて保存しなくても、一覧画面から複数レコードを一括で再集計できます。
■計算式内での関連レコード一覧内のフィールドの扱い
計算式内で、関連レコード一覧内のフィールドは以下のように表現します。
フィールドコード.関連レコード一覧のフィールドコード
「フィールドコード」と「関連レコード一覧のフィールドコード」の間に.（ドット）が入ります。
「フィールドコード」は、関連レコード一覧の参照先アプリで設定したフィールドのフィールドコードになります。
「関連レコード一覧のフィールドコード」は関連レコード一覧を表示しているアプリで設定した関連レコード一覧のフィールドコードになります。
プラグイン設定画面での計算式設定時のフィールドコードの入力補完機能でも選択肢として表示されます。
関連レコード一覧内のフィールドは、計算式内では配列として扱われます。
■基本的な集計
よくある関連レコード一覧の集計と言えば、合計、平均、最大値、最小値、レコード数ではないでしょうか。
これらは以下の関数を使用することで求めることができます。計算式の例として、「関連見積一覧」という関連レコード一覧内の「合計金額」フィールドを計算する場合を併記します。
| 集計 | 関数 | 計算式例 |
|---|---|---|
| 合計 | SUM | SUM(合計金額.関連見積一覧) |
| 平均 | AVERAGE | AVERAGE(合計金額.関連見積一覧) |
| 最大値 | MAX | MAX(合計金額.関連見積一覧) |
| 最小値 | MIN | MIN(合計金額.関連見積一覧) |
| レコード数 | ROWS | ROWS(合計金額.関連見積一覧) |
■条件指定の集計
SUMIF、SUMIFS、AVERAGEIF、AVERAGEIFS、MAXIFS、MINIFS、COUNTIF、COUNTIFSなどの関数も使用できるため、他のフィールドを条件とした複雑な集計も可能です。
例えば、月別の集計を取りたいといったことも可能になります。
サンプルアプリとして、顧客別の見積を月別に集計する見積集計アプリを作成してみましょう。
見積集計アプリのフォームは以下のようなっており、顧客名を顧客管理アプリからルックアップすると顧客名が一致する見積アプリのレコードが「顧客別見積一覧」に表示されます。
集計はテーブルで行い、年月別に「月別件数」「月別合計」「月別平均」「月別最大」「月別最小」を集計します。
「年月」フィールドは日付タイプのフィールドで、年月表示にするために、年月入力プラグインを使用します。
「年月」フィールドを1月ずつ自動で加算させるために、テーブル拡張プラグインのコピーオプションを設定します。
計算プラグインの計算式を設定していきます。
以下の計算式になります。「年月」フィールドは日付フィールドで、実際には該当月の1日の日付が入ります。〇〇IFS関数の検索条件にフィールドコードや関数を含めたい場合はCONCAT関数で比較演算子と値を結合します。
| 計算式設定フィールド | 計算式 |
|---|---|
| 月別件数 | COUNTIFS(見積日.顧客別見積一覧,CONCAT(">=",年月),見積日.顧客別見積一覧,CONCAT("<=",DATESTRING(EOMONTH(年月,0)))) |
| 月別合計 | SUMIFS(合計金額.顧客別見積一覧,見積日.顧客別見積一覧,CONCAT(">=",年月),見積日.顧客別見積一覧,CONCAT("<=",DATESTRING(EOMONTH(年月,0)))) |
| 月別平均 | AVERAGEIFS(合計金額.顧客別見積一覧,見積日.顧客別見積一覧,CONCAT(">=",年月),見積日.顧客別見積一覧,CONCAT("<=",DATESTRING(EOMONTH(年月,0)))) |
| 月別最大 | MAXIFS(合計金額.顧客別見積一覧,見積日.顧客別見積一覧,CONCAT(">=",年月),見積日.顧客別見積一覧,CONCAT("<=",DATESTRING(EOMONTH(年月,0)))) |
| 月別最小 | MINIFS(合計金額.顧客別見積一覧,見積日.顧客別見積一覧,CONCAT(">=",年月),見積日.顧客別見積一覧,CONCAT("<=",DATESTRING(EOMONTH(年月,0)))) |
見積集計アプリのレコードを新規作成し、顧客名をルックアップから取得すると、顧客別見積一覧に顧客名が一致する見積の一覧が表示されます。
年月フィールドに、2024年1月を設定すると、「月別件数」「月別合計」「月別平均」「月別最大」「月別最小」に2024年1月の集計結果が計算され、各フィールドに設定されます。
次に、テーブル拡張プラグインによって追加された行のコピーアイコンをクリックすると、行が追加され、年月フィールドには自動的に2024年2月が設定されます。年月フィールドに値が設定されると、「月別件数」「月別合計」「月別平均」「月別最大」「月別最小」に該当月の集計結果が自動計算され、各フィールドに設定されます。
あとは同様に行のコピーアイコンをクリックしていくだけで、月別の集計が計算できます。とても便利ですね！
■その他の計算
中央値を取得するMEDIAN関数、 k 番目に大きな（小さな）データを取得するLARGE（SMALL）関数、指定の行番号の値を取得するINDEX関数、文字列を結合するCONCAT関数やTEXTJOIN関数なども使用できます。
もちろん、アプリの関連レコード一覧以外のフィールドを組み合わせた計算式も設定できます。
計算式を工夫することで関連レコード一覧の活用の幅が拡がります！
是非ご活用ください！