Row count of related list table and then copy the result into a field of the parent table

[George53]

My customer care table (u_customer_care) is a parent table for  Service requests table (u_service_requests). One-to-many relationship. Service Requests table is a related list to Customer Care table. I want to be able to count the number of Service request records for any Customer Care record and then pass the result into a random string field (u_child_counter) in the customer care form. Ideally use either a BR or a client script and not a UI action, i would like to avoid having to push a button for this function. 

Thanks in advance!

George

[Stewe Lundin]

To count result of a query in any table just just user getRowCount 
In this example I'm counting the number of active users in sys_user table.   
See below:

var user_gr = new GlideRecord('sys_user');

user_gr.addActiveQuery();
user_gr.query();
var number_of_active_users = user_gr.getRowCount();

gs.info('Number of Active users:' + number_of_active_users)

So build you query and then user getRowCount to get the number of records.

Regarding a BR or a CS 
it depends on how you want to execute this!
And from where. 

If you need further assistance just ask. 

Glide Record Cheat Sheet

Good Luck 

[George53]

Hello Stewe 

Thank you for getting back, appreciated. Here is how I have it counting the rows in the child table through  UI action at themoment. Ideally, what i am trying to achieve is to use a BR instead and also be able to copy the result of the count in a random field (I have one already created one 'u_child_counter'). Any ideas how would you go about it is greatly appreciated.

var count = 0;
var ga = new GlideAggregate('u_service_requests');
ga.addAggregate('COUNT');
ga.addQuery('u_customer_care', current.sys_id);
ga.query();

if (ga.next())

count = ga.getAggregate('COUNT');

gs.addInfoMessage('There are ' + count + ' service requests on this CCQ ticket');
action.setRedirectURL(current);

Thank you

George

[Stewe Lundin]

When do you want this field to be updated, what condition?

'u_child_counter'

If I guess on that you would want this field to be updated in u_customer_care when a new record is added to the u_service_requests. 

Then I'd build a BR for the u_service_requests. 

That triggers on insert

Build a function that counts the requests  and updates the related record in the u_customer_care,u_child_counter. 

Regarding the GlideAggregate vs GlideRecord, I'd use the GlideRecord for this solution.  

With out testing this I guess you can even dot walk into the u_customer_care,u_child_counter via the GlideRecord call and do an update. 

But this solution is triggered on the insert of en new row in the u_service_requests table