How to count number of records based on values in fields

[cai]

I have a table containing two fields, Field A stores the name of the organization and Field B stores a corresponding value. An organization (in Field A) can have many records, each with potentially different values and there are many organizations. What is the best way to query that table, count the number of occurrences where the values are within a certain range for each of the organizations, and insert them into a second table (one record for each organization)? Also, is that via a Business Rule script? Sorry, this may be an easy one but I am relatively new to SN.

[Pradeep Sharma]

Hello Cairo,

You are correct. This has to be the business rule. Please refer sample ex 8.9 for more info.

Reference.

https://wiki.servicenow.com/?title=GlideRecord#gsc.tab=0

http://wiki.servicenow.com/index.php?title=Business_Rules#gsc.tab=0

Please let me know if you have any questions.

[Priyankar Hald1]

Hi Cairo,

Business Rule will be a perfect one!

Suppose, you have a table with name: "Test_1 [u_test_1]" where each record contains two fields (field A contains name of organization and field B contains the organization's corresponding value) as shown below

-------------------------------------

                          TEST_1

-------------------------------------

Field A                                       Field B

Org_1                                                 5

Org_2                                                 7

Org_1                                                 3

Org_1                                                 2

Org_3                                                 4

--------------------------------------

What I have understood is that there is a seperate table say of name: "Test_2 [u_test_2]" where there should be only one entry for each organization and the occurence of each record from Test_1 (that meet the criteria where range is >2 and <7) should be stored in another field in Test_2. (as shown below:)

-----------------------------------

                          TEST_2

-----------------------------------

Field A                                       Field B

Org_1                                                     2

Org_3                                                     1

------------------------------------

[NOTE: Org_2 is not there in this table since it did not meet the range criteria]

Let's try the following Business Rule:

Name of Business Rule: Move data to another table

Table: Test_1 [u_test_1]

When to run?: After

On: Insert and Update (you can modify as per your requirement )

Advanced: True

Script:

(function executeRule(current, previous /*null when async*/) {

  var org_name = current.u_field_a;

  var count = 0;

  var gr1 = new GlideRecord('u_test_1');

  gr1.addQuery('u_field_a',org_name);

  gr1.addQuery('u_field_b', '>', 2);

  gr1.addQuery('u_field_b', '<', 7);

  gr1.query();

  while(gr1.next()){

  count += 1;

  }

  var gr2 = new GlideRecord('u_test_2');

  gr2.addQuery('u_field_a',org_name);

  gr2.query();

  if (gr2.next()){

  gr2.u_field_b = count;

  gr2.update();

  }

  else{

  gr2.initialize();

  gr2.u_field_a = org_name;

  gr2.u_field_b = count;

  gr2.insert();

  }

})(current, previous);

What does the script do? : After update/insert of record in Test_1, it counts the updated/inserted organization name in Test_1 which satisfy the range criteria: >2 and <7. If the organization name exists in Test_2, it updates the count (in Field B of Test_2) against the name. If not found, it will create a new record for the organization in Test_2 and will update the count.

Let me know if this helps.

Thanks,

Priyankar Halder

PS: Hit like, Helpful or Correct depending on the impact of the response.

[cai]

This looks it will work perfectly. Thanks a bunch, Priyankar.

[Priyankar Hald1]

Pleasure is all mine..