create scheduled job

[BanuMahalakshmi]

Hi,

Please advise to create scheduled job script for below requirement.
First step - i need to clear the location errormessage field(customized string type field) from cmn_location table.
Second Step - Step through each ACTIVE location (cmn_location) and evaluate the rules described below with ACTIVE contacts (u_location_point_of_contact customized table which is contain contact type, location, group\user field) for the location, and populate the relevant message(s) into location errormessage.
Refer to the contact type choice "value" column (not the "label" column).
Script will check only ACTIVE locations and ACTIVE contacts: Message to write to location's new string field
Call function 1, passing contact type "value" as input. If no rows returned for a location, capture message in the string field.
If location is missing Primary Site Contact -   insert the error message into location errormessage field like "Primary Site Contact is missing"
If location is missing Secondary Site Contact - insert the error message into location errormessage field like "Secondary Site Contact is missing"

Check for more than one contact of the given contact type.
Call function 2, passing contact type "value" as input. If rows returned > 1, capture message in the string field.
If location has more than one Primary Site Contact -error message "Can have only one Primary Site Contact"
If location has more than one BU IT Contact- error message "Can have only one BU IT Contact"

could you please how to check these condition and update error message into contact errormessage field.

[Ankur Bawiskar]

@BanuMahalakshmi 

I believe I have answered your original question and you can enhance it further.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@BanuMahalakshmi 

please try this, please enhance and debug if required

Also test in lower instance

// Clear the location_errormessage field for all locations
var locationGR = new GlideRecord('cmn_location');
locationGR.query();
while (locationGR.next()) {
    locationGR.setValue('location_errormessage', '');
    locationGR.update();
}

// Process active locations
locationGR.addQuery('active', true);
locationGR.query();
while (locationGR.next()) {
    var errorMessage = '';
    var locationId = locationGR.sys_id.toString();
    
    // Check contact counts
    errorMessage += checkContactCounts(locationId);
    
    // Check contact types
    errorMessage += checkContactTypes(locationId);
    
    // Update error message if needed
    if (errorMessage) {
        locationGR.setValue('location_errormessage', errorMessage.trim());
        locationGR.update();
    }
}

function checkContactCounts(locationId) {
    var message = '';
    
    // Primary Site Contact checks
    var primaryCount = getContactCount(locationId, 'primary_site_contact');
    if (primaryCount === 0) {
        message += 'Primary Site Contact is missing. ';
    } else if (primaryCount > 1) {
        message += 'Can have only one Primary Site Contact. ';
    }
    
    // Secondary Site Contact checks
    var secondaryCount = getContactCount(locationId, 'secondary_site_contact');
    if (secondaryCount === 0) {
        message += 'Secondary Site Contact is missing. ';
    }
    
    // BU IT Contact checks
    var buITCount = getContactCount(locationId, 'bu_it_contact');
    if (buITCount > 1) {
        message += 'Can have only one BU IT Contact. ';
    }
    
    return message;
}

function checkContactTypes(locationId) {
    var message = '';
    
    // Primary Site Contact type validation
    if (!validateContactType(locationId, 'primary_site_contact', ['user'])) {
        message += 'Primary Site Contact type must be user. ';
    }
    
    // Secondary Site Contact type validation
    if (!validateContactType(locationId, 'secondary_site_contact', ['user'])) {
        message += 'Secondary Site Contact type must be user. ';
    }
    
    // Primary Off-Hours Contact type validation
    if (!validateContactType(locationId, 'primary_off_hours_contact', ['user'])) {
        message += 'Primary Off-Hours Contact type must be user. ';
    }
    
    return message;
}

function getContactCount(locationId, contactType) {
    var gr = new GlideRecord('u_location_point_of_contact');
    gr.addQuery('location', locationId);
    gr.addQuery('contact_type', contactType);
    gr.addQuery('active', true);
    gr.query();
    return gr.getRowCount();
}

function validateContactType(locationId, contactType, allowedTypes) {
    var gr = new GlideRecord('u_location_point_of_contact');
    gr.addQuery('location', locationId);
    gr.addQuery('contact_type', contactType);
    gr.addQuery('active', true);
    gr.query();
    
    while (gr.next()) {
        var typeValue = gr.getValue('contact_type_value');
        if (!allowedTypes.includes(typeValue)) {
            return false;
        }
    }
    return true;
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ravi Gaurav]

Hello @BanuMahalakshmi 

Try This 

Clear all error messages:

var locGR = new GlideRecord('cmn_location');
locGR.addQuery('active', true);
locGR.query();
while (locGR.next()) {
locGR.u_errormessage = ''; // clear existing messages
locGR.update();
}

And Loop through ACTIVE locations and check:

We’ll write two helper functions:

getContactsByType(locationSysId, contactType)

appendErrorMessage(locGR, message)