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.

[Sohail Khilji]

Hi @BanuMahalakshmi ,

Firstly, Clear the error. Perform gliderecord query on the u_location_point_of_contact table to get all active contacts related to each location. Try to execute the logic of the below script. Script my not be 100% correct you need to fix were ever required.

    var locationGr = new GlideRecord('cmn_location');
    locationGr.addQuery('active', true);  
    locationGr.query();

    while (locationGr.next()) {
        locationGr.errormessage = '';

        var contactGr = new GlideRecord('u_location_point_of_contact');
        contactGr.addQuery('location', locationGr.sys_id);
        contactGr.addQuery('active', true); // Only active contacts
        contactGr.query();

        var primaryCount = 0;
        var secondaryCount = 0;
        var buItCount = 0;
        var errorMessages = [];

        while (contactGr.next()) {
            var contactType = contactGr.contact_type; 
            
            if (contactType == 'Primary Site Contact') {
                primaryCount++;
            } else if (contactType == 'Secondary Site Contact') {
                secondaryCount++;
            } else if (contactType == 'BU IT Contact') {
                buItCount++;
            }
        }

        if (primaryCount == 0) {
            errorMessages.push("Primary Site Contact is missing");
        }
        if (secondaryCount == 0) {
            errorMessages.push("Secondary Site Contact is missing");
        }

        if (primaryCount > 1) {
            errorMessages.push("Can have only one Primary Site Contact");
        }
        if (buItCount > 1) {
            errorMessages.push("Can have only one BU IT Contact");
        }

        if (errorMessages.length > 0) {
            locationGr.errormessage = errorMessages.join(", ");
            locationGr.update(); 
        }
    

I hope this helps...

[Ankur Bawiskar]

@BanuMahalakshmi 

try this but please test in lower instance, verify results and then move to UAT

// 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();
}

// Step through each active location
locationGR.addQuery('active', true);
locationGR.query();
while (locationGR.next()) {
    var errorMessage = '';
    
    // Check for Primary Site Contact
    var primaryContactCount = getContactCount(locationGR.sys_id, 'primary_site_contact');
    if (primaryContactCount === 0) {
        errorMessage += 'Primary Site Contact is missing. ';
    } else if (primaryContactCount > 1) {
        errorMessage += 'Can have only one Primary Site Contact. ';
    }
    
    // Check for Secondary Site Contact
    var secondaryContactCount = getContactCount(locationGR.sys_id, 'secondary_site_contact');
    if (secondaryContactCount === 0) {
        errorMessage += 'Secondary Site Contact is missing. ';
    }
    
    // Check for BU IT Contact
    var buITContactCount = getContactCount(locationGR.sys_id, 'bu_it_contact');
    if (buITContactCount > 1) {
        errorMessage += 'Can have only one BU IT Contact. ';
    }
    
    // Update the location_errormessage field
    if (errorMessage) {
        locationGR.setValue('location_errormessage', errorMessage.trim());
        locationGR.update();
    }
}

// Function to get the count of contacts for a specific contact type
function getContactCount(locationId, contactType) {
    var contactGR = new GlideRecord('u_location_point_of_contact');
    contactGR.addQuery('location', locationId);
    contactGR.addQuery('contact_type', contactType);
    contactGR.addQuery('active', true);
    contactGR.query();
    return contactGR.getRowCount();
}

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

[Ankur Bawiskar]

@BanuMahalakshmi 

Thank you for marking my response as helpful.

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

[BanuMahalakshmi]

Can you pls advise how to Call function 3, passing contact type value (choices - user, group, others) as input.  If contact type is NOT user, capture message in string field.

If any Primary Site Contact type <> user                 Primary Site Contact type must be user

If any Secondary Site Contact is type <> user        Secondary Site Contact type must be user

• call function 4 - passing contact type "value" as input.  If contact type must be user, capture message in string field.

If any Primary Off-Hours Contact type = group \other - string error message - "Primary Off-Hours Contact type must be user"