Need to revoke requestor's access if the user do not have active record in custom table

[Community Alums]

Hello,

I need help to write a schedule job which runs once in a week for the following condition:

If NO requests created linked as "Requested by" or "Requested for": remove him/her from the group "XYZ_REQUESTORS" after 2 weeks since adding to the group.

I wrote following script in order to meet above condition:

var grMember1 = new GlideRecord('sys_user_grmember');

grMember1.addEncodedQuery('group=group_sys_id^sys_updated_onRELATIVEGT@dayofweek@ago@14');

grMember1.query();

while (grMember1.next()) {

    var requests1 = new GlideRecord("custom_table_name");

    requests1.addEncodedQuery('requested_for=' + grMember1.user.sys_id + '^ORopened_by=' + grMember1.user.sys_id);

    requests1.query();

    if (!requests1.next()) {

        grMember1.deleteRecord();

    }

}

The code is not working as expected. In fact every week, the user is removed from the group even though a request record exists for the user. I assume I have wrongly added the 14 days condition on 'sys_user_grmember' table whereas it should be on custom table while checking records.

Please suggest corrections required in the above code to meet the requirement.

Thanks.

Please help 

[Josh Pirozzi]

Hi @Community Alums,

Have you tried to accomplish this through Flow Designer? You should be able to trigger it once per day and look up the Group Members associated to a specific Group and then query the Request or Request items Table looking for the Group Members in either field. 

Once the query is done, you can add an If statement where, if the Group Member hasn't placed a Request in XX number of days, then delete their Group Membership.

Hope this helps!

-Josh Pirozzi