カテゴリー内の他の記事

関連レコードの項目を条件付きで集計

フォローする

(著者:サイボウズ 佐藤 紅志)

今回のTipsは、顧客ごとの自動採番の続編といたしまして、引き続き「営業支援(SFA)パック」を利用したものです。「営業支援(SFA)パック」にある、顧客情報アプリには、その顧客に関連した案件情報が、標準機能である「関連レコード」にて参照可能になっておりますので、大変便利です。

ところで、この案件一覧を見ていると、やはり受注金額などの列項目は、この画面で集計した結果が見たくなりますね。アプリ内に設定されたテーブルパーツであれば、自動計算機能で集計が可能ですが、関連レコード一覧の項目を集計する機能は、残念ながら現在のバージョンにはありませんので、これをjsカスタマイズで計算してみましょう。

また、関連レコードパーツの標準設定では、関連付けられたアプリのレコードを全て取得するようになっていますが、これを条件付きで設定した場合の考慮も行い、今回の例で言えば、案件の一覧という名称を考慮し、「受注確度が受注以外」のものを条件としたいと思います。

フィールドの設定

まずは、顧客情報アプリのフォーム設定にて、関連レコードフィールド[案件一覧]の設定[さらに絞り込む条件]にて、下記のように[受注確度]が[受注]以外のものを検索するように設定します。

また集計結果の表示領域用に、スペースパーツを要素ID[TotalAmount]にて、案件一覧パーツの下部に、追加します。関連レコードの受注金額項目の位置に合うように、任意の幅のスペースパーツと、[小計]ラベルも合わせて追加して下さい。

コード

フォームの詳細画面、編集画面表示のタイミングで計算を行うサンプルです。
JavaScriptファイルができたら、「顧客情報」アプリに適用しましょう。

ソースコードの解説

関連レコードパーツに設定した条件と同様に顧客情報のアプリを検索するために、以下のクエリを作成しています。

受注確度から、受注のものを除外する点につきましては、こちらのドキュメントにありますように「ドロップダウン」パーツについては、not in句で検索するところがポイントです。
同期リクエストの発行、offsetを考慮したロジックはこちらのTipsを参考にしました。

コード適用後、以下のように小計計算の結果が出力されます。
 

注意事項など

[顧客情報レコード番号]パーツにつきましては前記事で作成したものを利用しておりますので、このTipsのみ実行する場合はそちらをご参照の上、設定をお願い致します。
今回のTipsでは条件付きということで、関連レコードパーツに条件を設定し、その条件と同様のqueryを発行するものとさせて頂きましたが、条件が特になく単に全レコードの合計をする場合は、該当条件部分を読み飛ばして頂ければ全体の集計が行われます。レコード件数をoffset(100)値にて除算した回数分のサーバーとのやりとりは発生しますので、条件の有無に関わらず、データ件数に応じた処理時間がかかる点と、関連レコードパーツは、標準で5件にてページングを行いますが、小計行の計算は全体の計算を実行しますので、その点はご留意のほど、よろしくお願い致します。
 

このTipsは、2014年9月版で確認したものになります。

<<顧客ごとの自動採番

デモ環境

(顧客情報) https://dev-demo.cybozu.com/k/85/

(案件情報) https://dev-demo.cybozu.com/k/86/

※デモ環境についての説明はこちら

記事に関するフィードバック

直接的に記事と関連がないご質問はcybozu developer コミュニティをご活用ください。

Avatar
Shibayama

有用な情報ありがとうございます。
プログラミング初心者です。

サンプルアプリではうまくいったのですが、
自前のアプリではうまくいかず、教えていただきたいです。
非常に初心者の質問ですみません。

「条件が特になく単に全レコードの合計をする場合は、該当条件部分を読み飛ばして頂ければ全体の集計が行われます。」
とありますが、具体的にはプログラムのどこの部分をカットすればいいでしょうか?

よろしくお願いします。

Avatar
Yuta Onishi

shibayama様

記事の著者ではありませんが、横から失礼致します。
株式会社ロケットスタートホールディングスの大西と申します。

このプログラムではクエリ指定である
var query =・・・・の部分(17行目〜19行目)で条件を指定しています。
ここで指定している条件は、
「別アプリ内のどのフィールドを関連づけさせるか」
「ドロップダウンに指定した値が入っているかどうか」
「100件ずつ確認して該当データを取得」
の3つです。
また、20行目で人間の言葉をパソコンの言葉に翻訳しています。
そして21行目の「+ '&query=' + query」によってこの条件を使って下さい。と指示しています。
なので、17行目〜20行目を
var query = '顧客情報レコード番号="' + client_rid +
'" and ドロップダウン not in ("受注")' +
' limit 100 offset ' + offset;
から
var query = '顧客情報レコード番号="' + client_rid +
 '" limit 100 offset ' + offset;
と書き換えて余計な条件をカットすればご所望の動作をするかと思われます。

Avatar
Shibayama

大西様

早速のご回答ありがとうございました。
試したところ、うまくいきました。
(clientrid -> client_ridの部分で少し悩みましたが)

ありがとうございました。

Avatar
Yuta Onishi

Shibayama様

うまくいって良かったです。
なぜか下線消えてますねw失礼しましたm(_ _)m

Avatar
N.abe

プログラミング初心者です。
上記の例を参考に関連ファイルの集計を2箇所でおこない、その2つを利用してさらに演算させたいのですが、やり方を御教授お願いいたします。

Avatar
Koji Sato

コメント、回答いただいた皆様、誠にありがとうございます。
阿部成利様、

関連ファイルの集計を2箇所でおこない、その2つを利用してさらに演算させたい

ですが、例えば、アプリAのレコードに、関連レコードとして登録されているアプリBの持っている項目を
集計した結果と、関連レコードとして登録されているアプリCの項目集計結果を合計したいという理解
でよろしいでしょうか?

この場合は、上記サンプルの計算をそれぞれの関連レコードに対して行ったものを合計すると可能かとは
思いますが、阿部様が躓かれている点についてご教示頂ければと思います。

Avatar
N.abe

佐藤様
 回答頂ましてありがとうございました。
 佐藤様御推察の通りでございます。
 試してみます。

 さらに1点確認させて頂きたいのですが、本集計でアプリを試したのですが、テスト的に利用したアプリを削除しようとすると双方で参照しているため、削除ができずにおります。
 初歩的な質問で申し訳ありませんが、削除する方法について御教授いただけると幸いです。
よろしくお願いいたします。

Avatar
Koji Sato

阿部様

早速のコメント、誠にありがとうございます。
複数の関連レコード集計の計算は、よくあるカスタマイズニーズですので、是非試してみてください!

関連レコードが設定されたアプリの削除ですが、関連される側(2つ上のコメントで言うと、アプリCとD)
のデータが削除されると双方のアプリの削除が行えます。件数が多い場合は一括削除が便利かと
思いますので、下記リンクを参照くださいませ♪

https://help.cybozu.com/ja/k/user/delete_bulk.html

Avatar
N.abe

佐藤様
 関連レコード集計はやはり必要と思われる方は多いでしょうね。
 集計および、不要アプリの削除も実現できました。
 ありがとうございました。

Avatar
murakami

佐藤様
こちらのサンプルコードを使用してみたのですが、動きませんでした。
アップデートなどで仕様が変わっていますでしょうか?
ご確認のほど、よろしくお願いいたします。

Avatar
murakami

追記
エラーメッセージは"ログインしてください"
エラーコードは"CB-AU01"と出ています。
よろしくお願いします。

Avatar
Koji Sato

murakami様

コメントありがとうございます。記事のアップから大分時間が経ったにも関わらず、参照の上、ご質問を頂き、
とても嬉しく思います。

さて、ご質問の件ですが、手元環境では、2015年7月版における動作確認はしておりまして、デザイン変更
の影響により合計出力位置が変なところにいってしまってますが(汗)、計算自体はできておりますので、原因
は別のところにあるように思います。

一般論で恐縮ですが、"CB-AU01"が出ている場合は、url文字列を組み立てている所やheader指定
周りに問題がある場合が多いと思いますので、お手数ですが、その周辺のコードをご確認頂けますでしょうか?

それでも現象が変わらないようでしたら、こちらにコードを提示頂きたく、よろしくお願い致します!

Avatar
murakami

佐藤様
早速のご返信ありがとうございます!
今回はここで書かれているものではない方法で解決できたのですが、
また参考にすることがありましたら確認してみます。
どうぞよろしくお願いします。

Avatar
Koji Sato

murakami様

こちらこそ、早速のご確認、ご返信ありがとうございます。解決できたとの事、何よりです♪

もしよろしければ、「ここに書かれているものではない方法」の内容の方を教えて頂けますと、
私も嬉しく思いますし、他の方々の参考にもなるかと思いますので、ご検討のほど、よろしく
お願い致します!

Avatar
matsu

関連レコードの項目を項目毎に集計したのですが、集計した値の計算方法をご教授お願いいたします。

・項目A集計値ー項目B集計値ー項目C集計値=F
・項目D集計値+項目E集計値=G
・F+G

また、集計した値もカスタマイズによって、アプリに表示する事は可能でしょうか?
よろしくお願いいたします。

Avatar
Koji Sato

matsu様、ご質問ありがとうございます。またレスが遅くなりまして申し訳ありません。

コード部分の解説が不足しており、恐縮です。

この例では、列項目として合計しているのは、関連レコードフィールドとして設定
された「案件情報」アプリのフィールド名「受注金額」(フィールドコード「数値」)
のみが、その計算対象となっております。

「顧客情報」アプリにおいて、関連レコードフィールドとして設定している「案件情報」
アプリへの表示フィールドに、上記「受注金額」が設定されておりますので「顧客情報」
アプリにおいて「案件情報」アプリのフィールドを計算することが可能となっております。

コード:42行目から45行目がその計算個所になります。

42: var amount = 0;
43: for (var i = 0; i < records.length; i++) {
44: amount = amount + parseFloat(records[i].数値.value);
45: }

「案件情報」アプリの計算可能フィールドは、フィールド名「受注金額」(フィールドコード
「数値」)のみですが、ここに matsu 様ご質問のフィールド名「項目A」(フィールドコード
「数値A」)を追加して頂くと仮定し、かつ関連フィールドの表示フィールドにおいても設定
されている事を前提としますと、

42: var amount = 0;
43: var amount_a = 0;
44: for (var i = 0; i < records.length; i++) {
45: amount = amount + parseFloat(records[i].数値.value);
46: amount_a = amount_a + parseFloat(records[i].数値A.value);
47: }

として計算が可能になります。集計結果の計算は、この for loop 処理後に任意の計算
をして頂けるかと思います。

48: var sum_amount = amount + amount_a; // 「数値」列の合計 + 「項目A」列の合計
49: var sbt_amount = amount - amount_a; // 「数値」列の合計 - 「項目A」列の合計

以上、簡単な説明で恐縮ですが、ご参考になりましたら、幸いです。
また、不明点などありましたら、お気軽にお問い合わせくださいませ。

Avatar
otsu

皆様

初心者です。
関連レコードが同じフィールドに2つある場合でそれぞれレコードの件数を表示させる場合は
どうしたらよいのでしょうか。

宜しくお願い致します。

Avatar
Koji Sato

otsu様、ご質問を頂き、ありがとうございます。

お手数ですが、もう少し具体的にご質問の内容を教えていただいてもよろしいでしょうか?

関連レコードが同じフィールドに2つある場合

kintoneの用語で言う[フィールド]とは、フォーム上に配置できる、各種のパーツになり、
[関連レコード一覧]自体も、一つのフィールドですが、otsu様が仰る同じフィールドと言う
意味を教えて頂きたく思います。

https://help.cybozu.com/ja/k/user/form_parts.html

レコードの件数を表示されたいとの事ですので、この記事では、[顧客情報]アプリの
[フォームの編集]にて設定している[関連レコード一覧]パーツより

1.参照するアプリとして、[案件情報]アプリを設定
2.表示するレコードの条件に、[顧客情報][会社名]=(等しい)[案件情報][顧客名]を設定
3.さらに絞り込む条件には、[受注確度] 次のいずれも含まない [受注]を設定
4.表示するフィールドとして、
(1)案件名、(2)受注確度、(3)受注予定日、(4)受注金額、(5)営業担当者

を設定しておりまして、これらは、kintone アプリストアにある「営業支援(SFA)パック」
にて提供させて頂いております。

ここに表示されている[(4)受注金額]の合計を計算する ように、Javascriptカスタマイズ
を適用しています。

もし、件数を計算される必要があるのでしたら、[コード]内の、42行目から始まる、

42: var amount = 0;
43: for (var i = 0; i < records.length; i++) {
44: amount = amount + parseFloat(records[i].数値.value);
45: }

を、以下のように追加コードを記入しまして、

42: var amount = 0;
43: var data_count = 0;
44: for (var i = 0; i < records.length; i++) {
45: amount = amount + parseFloat(records[i].数値.value);
46: data_count ++;
47: }

とやって、52行目(修正後は54行目)

52: divTotalAmount.innerHTML = "\" + wString + "- (" + data_count + "件)";

とすれば件数も表示されます。以上、ご確認のほど、よろしくお願い致します。

Koji Satoにより編集されました
Avatar
otsu

Koji Sato 様

ご連絡ありがとうございます。
用語が理解できておらず大変申し訳ございませんでした。

フォーム上に
関連レコードが2つあります。
①A部署の顧客名
②B部署の顧客名
①②それぞれの関連レコードに何人顧客が表示されているのかを計算したいです。

その場合は、上記のようにJavascriptを編集すればどちらも計算されるのでしょうか。

恐れ入りますが宜しくお願い致します。

Avatar
Koji Sato

otsu様、ご確認とご返信を頂きありがとうございます。

なるほど、同一フォームに複数の関連レコードフィールドが設定されており、それぞれの計算は可能か?
というご質問ということで、理解させて頂きました。(違っておりましたら、遠慮無くご指摘下さいませ)

JavaScript(JS)カスタマイズはチョツトしたことをやりたくても、そこそこのコード行数になるので、
二箇所もあって、計算できるのかと感じられたと想像いたします。もちろん、計算自体は可能ですが、
案じられている(と私が勝手に想像させて頂きました)ように、この記事のソースをそのまま拡張して、
計算することも、もう一つ別のJSファイルを作成してアップロードする事も可能です。私のお勧めとしては、
処理自体に関連性がないのであれば、別ファイルで記述する方が管理的にもよろしいのではと思います。

それでは、早速ですが、この記事で使用している「顧客情報」アプリのフォームに、もう一つ[案件情報]
への[関連レコード一覧]パーツを作成し、そちらを計算してみましょう。

少し長くなりますが、お付き合いの程、よろしくお願いいたします。

記事では案件一覧ということで、受注確度が"受注"以外の関連レコード一覧を、表示、計算しておりましたので、
今度は受注一覧を作成し、そこに受注確度が"受注"となっているもののみを表示、計算してみましょう。

また、せっかくですので、2015年7月版でリリースされた Promise 機能を利用し、モダン(?)なレコード取得
を行ってみたいと思います。

まずは、[顧客情報]アプリに[アプリの設定][フォームの編集]にて、[関連レコード一覧]パーツを追加し、
フィールド名に任意の名前で結構ですが[受注一覧]と設定します。参照するアプリ、表示するレコードの条件、
表示する フィールドは、それぞれ[案件一覧]同様に設定を行い、さらに絞り込む条件として[受注]のみをチェック
し、フィールドコード に[受注レコード一覧]を設定します。

続いて、やはり[案件一覧]の下部に設定したスペース、ラベルパーツも同様に設定を行い、小計結果を表示
するスペースパーツの要素IDを[OrdersAmount]とします。以上の設定を終えましたら、念のため一度保存を行い、
次に[詳細設定][JavaScript / CSSでカスタマイズ]にて、以下のソースを[アップロードして追加]にて設定、
保存します。

※文章が長くなってしまいましたので、ソース以降は次のコメントに記載させて頂きます。

Koji Satoにより編集されました
Avatar
Koji Sato

01 (function() {
02 "use strict";
03 //レコードの追加、編集、詳細画面で適用する
04 kintone.events.on(['app.record.detail.show', 'app.record.edit.show'], function(event) {
05 var client_rid = event.recordId;
06 var related = kintone.app.getRelatedRecordsTargetAppId('受注レコード一覧');
07
08 function fetchRecords(appId, opt_offset, opt_limit, opt_records) {
09 var offset = opt_offset || 0;
10 var limit = opt_limit || 100;
11 var allRecords = opt_records || [];
12
13 var s_query = '顧客情報レコード番号="' + client_rid + '" and ドロップダウン in ("受注") limit ';
14
15 var params = {app: related, query: s_query + limit + ' offset ' + offset};
16 return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
17 allRecords = allRecords.concat(resp.records);
18 if (resp.records.length === limit) {
19 return fetchRecords(related, offset + limit, limit, allRecords);
20 }
21 return allRecords;
22 });
23 }
24
25 fetchRecords(kintone.app.getId()).then(function(records) {
26 var amount = 0;
27 var data_count = 0;
28 for (var i = 0; i < records.length; i++) {
29 amount = amount + parseFloat(records[i].数値.value);
30 data_count++;
31 }
32 var divTotalAmount = document.createElement('div');
33 divTotalAmount.style.fontWeight = 'bold';
34 divTotalAmount.style.textAlign = 'right';
35 divTotalAmount.style.fontSize = 12;
36 var wString = String(amount.toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, '$1,'));
37 divTotalAmount.innerHTML = "\" + wString + "- (" + data_count + "件)";
38 kintone.app.record.getSpaceElement("OrdersAmount").appendChild(divTotalAmount);
39 });
40
41 return event;
42
43 });
44 })();
45

[案件一覧]との違いと致しましては、6行目の[関連レコード一覧]に設定された[参照するアプリ]のID取得
部分が、追加した受注一覧[関連レコードパーツ]に設定されている[参照するアプリ]から取得するように
しているのと、13行目のqueryを組み立てているところで、受注以外から受注を検索するようにしている点
、38行目で、計算した結果を設定するスペースパーツの要素IDになります。

Promise の利用にあたりましては、以下の記事を参考にしております。こちらの方がロジックが減った事
により、コードも短くなり、すっきりしていますね。是非ご参照ください。
https://cybozudev.zendesk.com/hc/ja/articles/204730600

いかがでしょうか?、またご不明な点などございましたら、お気軽にコメントくださいませ。
よろしくお願い致します。

Avatar
matsu

サトウ 様
関連レコード集計した値を一覧へカスタマイズで表示したいのですが、やり方はわかりますでしょうか?

ご教授頂けましたら幸いです。

宜しくお願いいたします。

Avatar
Koji Sato

matsuさん、いつもご質問を頂き、ありがとうございます!

関連レコード一覧の、集計の結果をビューに表示されてたいとの事ですが、このあたりの記事を参考にしますと
ビューを表示する際のイベントで計算は可能かと思います。

https://cybozudev.zendesk.com/hc/ja/articles/202341944

但し、この場合、データ自体はリアルタイムに集計されるものの、表示する行数分のapi問合せが発生してしまいますので、
パフォーマンスやapiコール数の増加といった観点からは、あまりお勧めできません。

そこで、この元フォーム上に、集計結果フィールドを追加し、保存時に計算した結果でそのフィールドの値を更新するように
されると良いのではないかと思います。更新については、こちらの記事に解説がございます。

https://cybozudev.zendesk.com/hc/ja/articles/203331050

今回は、参考となる記事のご紹介のみですが、もし不明点などありましたら、実例の提示もさせて頂きますので、
その旨、ご指摘頂きたく、よろしくお願い致します!

Avatar
R.Imamura

皆様、初心者です。
集計した値を一覧に表示してその金額をランキング形式にソートしたいと思っています。
具体的にどのようにすればよいでしょうか?ご教授頂けると助かります。
関連レコードの金額の集計まではできました。

Avatar
Koji Sato

R.Imamura様、コメントありがとうございます。

この記事では、[案件情報]アプリの、ルックアップフィールドにて設定された[顧客情報]を元に、逆に[顧客情報]アプリ側
から関連レコード一覧として設定した[案件情報]アプリの[受注金額]をその顧客に関連する案件として、集計しています。

従いまして[案件情報]アプリのそれぞれのレコードには、原則として[顧客情報]を保持していますので、

集計した値を一覧に表示してその金額をランキング形式にソートしたい

というようなニーズにつきましては、この場合ですと[案件情報]アプリに対して

「データの集計」機能や
https://help.cybozu.com/ja/k/user/view.html

「グラフ」機能
https://help.cybozu.com/ja/k/user/report.html

にて、関連する項目(この場合は分類する項目に顧客や案件確度)を活用することにより、実現できる可能性が高いと思います。
特にクロス集計につきましては、顧客別案件確度別受注金額を一定の日付範囲で集計し、その合計金額の降順にソートして
表示すること等が、ノンプログラミングで可能ですので、是非検討してみて下さい。

また、ご不明な点や、お気づきの点、私のコメントがお聞きになりたい事とは違うなどございましたら、こちらにレス頂けると嬉しいです。

Avatar
R.Imamura

佐藤様

早速のご返事ありがとうございます。
少し質問のニュアンスがわかりづらかったかと思います。申し訳ございません。
集計したいのは【顧客情報】アプリです。
データの集計をする際にスペースパーツに集計結果が表示されるため、ソートができない
(データの集計の対象パーツでないため)
数値のパーツに表示させればできるのかなと思ったのですが、それもできませんでした。
その点をクリアする方法が分かればご教授頂きたいです。

Avatar
matsu

佐藤様 毎度返信ありがとうございます。

集計結果フィールドを追加し、保存時に計算した結果でそのフィールドの値を更新するようにされると良いのではないかと思います
とのご回答を頂いたのですが、集計結果フィールドを追加したのですが計算した値が反映されませんでした。

具体的な方法をご教授頂ければ幸いです。 よろしくお願いいたします。

matsuにより編集されました
Avatar
Koji Sato

年明けからバタバタしてまして、返信が遅くなりまして、大変申し訳ありません。

R.Imamura様、「顧客情報」アプリでは、関連する「案件情報」の受注金額について特定の 受注確度のものを集計していますが、その結果が複数あり、その集計結果をソートしたいと いうように理解させて頂きました。

その場合はapiを利用してテーブルパーツに受注確度ごとの集計結果を保存し、以下の テーブルパーツのソートjsカスタマイズを適用すれば実現できると思います。

https://goo.gl/BEcU9L

但し、かなりのコード量になりますので、個人的にはあまりお勧めできないです。 もしよろしければ、集計されているアプリの情報について、具体的に教えて頂いても よろしいでしょうか?

繰り返しになり恐縮ですが、関連レコードパーツの元アプリの集計機能などが活用できないか を検討できるかと思いますので。

Koji Satoにより編集されました
Avatar
Koji Sato

matsu様 毎度こちらこそありがとうございます。一発で解決できず、すみません。

集計結果の保存につきましては、別途記事を起こしたほうがよいか検討しますので、恐縮ですが、少々 お時間を下さいませ。ちなみに、

> 集計結果フィールドを追加したのですが計算した値が反映されませんでした。

とのことですが、更新処理にて某かのエラーに遭遇されましたでしょうか?

もしそうでしたら、エラーメッセージなどを教えて頂けると、解決できるかもしれません。 以上、よろしくお願い致します。

Koji Satoにより編集されました
Avatar
R.Imamura
佐藤様 返信遅くなり申し訳ございませんでした。回答ありがとうございました。 いまだに完璧には解決はしておりませんが、もう少し自分なりに機能を勉強しながらやってみます。 それでも無理ならまたなにか質問させて頂くかもしれません。
ログインしてコメントを残してください。
Powered by Zendesk