Update user table fields if start date is greater than 50 days

[sanvi]

Hi All,

There is a field called 'start date' on user table, the requirement is to update certain fields on user table when 'start date is >50 days'

Tried this on scheduled job with this filter '

var usrqry = 'active=true^start_dateRELATIVEGT@dayofweek@ahead@50';

but the filter is not giving proper data that i need.

Can someone help me with the filter for above requirement.

[Astik Thombare]

Hi @sanvi ,

To achieve your requirement of updating certain fields on the user table when the 'start date' is more than 50 days ahead, you can use a scheduled job with a script that correctly calculates the date 50 days from now and filters the users based on this date. The 'RELATIVE' conditions might not always give the desired results due to the way they interpret the relative time.

Here’s a detailed approach to solve this problem:

Steps:

1. Calculate the Date 50 Days from Now: Use JavaScript to get the date that is 50 days ahead of the current date.

2. Query the User Table: Filter users whose start date is greater than this calculated date.

3. Update Fields as Required: Update the necessary fields for the filtered users.

Scheduled Job Script:

Here's a sample script that you can use in a scheduled job:

(function() {
    // Calculate the date 50 days from now
    var now = new GlideDateTime();
    now.addDays(50);
    var targetDate = now.getValue();

    // Create a GlideRecord query to find users with start_date > 50 days from now
    var grUser = new GlideRecord('sys_user');
    grUser.addEncodedQuery('active=true^start_date>' + targetDate);
    grUser.query();

    // Iterate over the filtered users and update fields as necessary
    while (grUser.next()) {
        // Perform the necessary updates
        grUser.field_to_update = 'new_value';  // Replace 'field_to_update' and 'new_value' with actual field name and value
        grUser.update();
    }
})();

Explanation:

1. Calculate Date 50 Days from Now:
• Use GlideDateTime to get the current date and time.
• Add 50 days to the current date using addDays(50).
2. Query the User Table:
• Use GlideRecord to query the sys_user table.
• Use addEncodedQuery with the appropriate filter: 'active=true^start_date>' + targetDate.
• This ensures that only users with a start_date greater than 50 days from now are retrieved.
3. Update the Fields:
• Iterate through the results and update the required fields using grUser.field_to_update = 'new_value' and grUser.update().

Creating the Scheduled Job:

1. Navigate to System Definition > Scheduled Jobs.
2. Click New and select Automatically run a script of your choosing.
3. Name your scheduled job and set the execution time as required.
4. Copy and paste the script provided above into the script field.
5. Save and activate the scheduled job.

This script should help you achieve the desired functionality of updating certain fields on the user table when the start_date is more than 50 days ahead

If my reply helped with your issue please mark helpful 👍 and correct ✔️ if your issue is resolved.

By doing so you help other community members find resolved questions which may relate to an issue they're having

Thanks,

Astik

[sanvi]

Hi @Astik Thombare ,

Thank you for the reply, i will try the above solution and get back to you.

[sanvi]

Hi @Astik Thombare ,

I think i might have asked my question incorrectly above.

The requirement is to purge a field value 10 days after the user 'Start date' and the above solution does not work for this.