question Re: Get row count of related records in ITSM forum

Get row count of related records

Priya75 — Fri, 14 Aug 2020 11:30:03 GMT

Hi All,

I have a field on the form as test. i want to populate this field on the basis of a related list, so i have a related list of business service which shows the records from cbdb_ci_service tables.i want to count of services related to that particular task and update that field.

Re: Get row count of related records

Ankur Bawiskar — Fri, 14 Aug 2020 11:51:05 GMT

Hi Priya,

When you want to populate that? when any new record gets added in related list?

if yes then you would require BR on that related list table to increment or decrement the count based on insert or delete

Regards
ankur

Re: Get row count of related records

Priya75 — Fri, 14 Aug 2020 11:55:55 GMT

No, when a task is created or updated and a business service is added to that task.i can't have run a business rule on cmdb_ci_service table.i want that count from task table and need to update the field .could you provide some scripting

Re: Get row count of related records

Sajilal — Fri, 14 Aug 2020 11:56:39 GMT

Have the field as a calculated field maybe? and query your respective table and get the rowcount.

var categoryCount = 0;
var gr = new GlideAggregate('your table');
gr.addQuery(your query);
gr.addAggregate("COUNT");
gr.query();
while(gr.next()) {
categoryCount = gr.getAggregate("COUNT");
}
return categoryCount;

Please Mark as ✅ Correct if this solves your issue and also mark ???? Helpful if it helps resolve your problem.

Thanks,
Saji

Re: Get row count of related records

Servicenow Use4 — Fri, 14 Aug 2020 11:58:43 GMT

Hello Priya,

By what I understood from your requirement, apart from the 'test' field, you have a reference field 'Business Service' on the from that fetches data from cmdb_ci_service table. Now my question is: is the data being retrieved in the reference field dynamic? what is the condition?

In your statement "i want to count of services related to that particular task", what task are we talking about?

Kindly clarify.

Counting the number of services can be done easily via script once we know the condition that filters data.

Thanks!

Re: Get row count of related records

Priya75 — Fri, 14 Aug 2020 12:04:07 GMT

Hi ,

I have a certificate task form, in this the business services(cmdb_ci_services)is the related list record.i have a field on the certificate task form as test. i want to populate this field, for that i want to count all the service record in business service related list.

for eg:- for each task created there are 2,3 services are there in the related list record. this i have to calculate and populate in test field

Re: Get row count of related records

Ankur Bawiskar — Fri, 14 Aug 2020 12:11:35 GMT

Hi Priya,

So you would require before update BR on the current table.

Sample script below

var gr = new GlideRecord('cmdb_ci_service');

gr.addQuery('task', current.sys_id);

gr.query();

current.<fieldName> = gr.getRowCount();

Regards
Ankur

Re: Get row count of related records

Priya75 — Fri, 14 Aug 2020 12:16:15 GMT

i want it on cert_task table. when a related service is added or updated .

what i have to mention in current.field name.. when it is a table record.

Re: Get row count of related records

Ankur Bawiskar — Fri, 14 Aug 2020 12:23:27 GMT

Hi Priya,

So since you want field to be updated based on service is added/updated or deleted then BR should be on cmdb_ci_service table

There must be some field on cmdb_ci_service which holds certificate information

query with that field and then get the count

Regards
Ankur

Re: Get row count of related records

Priya75 — Fri, 14 Aug 2020 12:26:02 GMT

Hi,

so in cmdb_ci_service table.. the certification is the related list. so we query same way like we query a table?

Re: Get row count of related records

Ankur Bawiskar — Fri, 21 Aug 2020 12:41:45 GMT

Hi Priya,

Hope you are doing good.

Let me know if that answered your question.

If so, please mark appropriate response as correct & helpful so that this thread can be closed and others can be benefited by this.

Regards
Ankur

Re: Get row count of related records

Ankur Bawiskar — Sun, 23 Aug 2020 11:37:45 GMT

Hi Priya,