Scheduled Job to close table records

[HaowenX]

Hi, I need to make scheduled jobs to close records for 2 tables.

I tried for 1st table with below codes, it works.

var transaction = new GlideRecord('x_shlt_shinsei_request_transaction');

transaction.addQuery('active', 'True');

transaction.addQuery('sub_state', 'waiting for approval');

transaction.addQuery('sys_created_on', '<=', gs.daysAgoStart(300));

transaction.query();

while(transaction.next()){

  transaction.active = false;

    transaction.comments = 'Test';

  transaction.update();

}

Then I went the 2nt table, I made the code in similar logic but it doesn't work.

var transaction = new GlideRecord('x_shlt_shinsei_request_approval');

transaction.addQuery('request_number.state', 'IN' ,'Resubmitted,Submitting,requested,Return to Requestor');

transaction.addQuery('request_number.sub_state', 'waiting for approval');

transaction.addQuery('request_number.sys_created_on', '<=', gs.daysAgoStart(300));

transaction.query();

while(transaction.next()){

    transaction.state = 'Cancelled';

    transaction.update();

}

in addQuery, request_number is a reference type and state/sub_state/sys_create_on are columns in request_number.

for transaction.state = 'Cancelled';

state is a choice type with below choices.(in attachment 0313.PNG)

can anyone help me with this problem?

regards!

[J Siva]

No problem ✌️

[HaowenX]

I tried Flow design.
It looked up for target records but we have no write access to table so it cannot update the state.

Flow design works and helps me find out the cause.
thank you so much!

[Ehab Pilloor]

Hi @HaowenX,

Can you try building the query on list view, copy that query and use it as String (by right clicking on the query breadcrumb) in the following manner?

transaction.addEncodedQuery('PASTE_YOUR_COPIED_QUERY_HERE');

Note that you would need to click on Show Related Fields in the Field dropdown while querying to access the reference record's fields. 

If you found my response helpful, please mark it as Solution and Helpful.

Thanks and Regards,

Ehab