Excel upload / validation via catalog item variable before submitting the catalog request

[Sachin Gavhane]

hi @Ankur Bawiskar 
I’m working on a Service Catalog item with the following variables:

• Attachment variable – used to upload an Excel file

• List Collector variable – used to store users

The uploaded Excel file contains only two columns:

• User Name

• User ID (primary key; corresponds to sys_user.upn)

Requirement

When a user uploads the Excel file via the attachment variable:

1. The system should read and validate all rows in the Excel file against the sys_user table using the User ID.

2. If every entry in the Excel file is valid:

   • Populate the List Collector variable with the corresponding users.

3. If any single row contains an invalid or non-existent User ID:

   • Prevent the catalog item from being submitted.

   • Display a clear list of invalid entries on the catalog form so the user can correct the Excel file and re-upload it.

Key Constraint

• Validation must be all-or-nothing — partial population of the List Collector is not allowed.
  
  Requesting your help for this solution.
  
  Thanks !

[Ankur Bawiskar · Accepted solution]

@Sachin Gavhane 

it worked fine for me, see below alert and record in sys_attachment

[Ankur Bawiskar]

@Sachin Gavhane 

so what you tried and where are you stuck?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Sachin Gavhane]

hi @Ankur Bawiskar 

When I attach an Excel file to the catalog variable, the OnChange catalog client script is not getting triggered. I’m aiming to perform these computations before the form is submitted.

I’d like to understand how this implementation should start, and I’d really appreciate it if you could help by sharing the relevant details or guidance.

Thanks in advance.

[Ankur Bawiskar]

@Sachin Gavhane 

you can write onChange catalog client script on that attachment variable

share your script and what debugging did you do?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Sachin Gavhane]

hi @Ankur Bawiskar 

Once I upload excel sheet in the attachment variable of the catalog item, below onchange catalog client script is getting triggered but "newvalue" is empty [we cannot get sysid of the attachment]

________________________On change catalog client script on attachment variable_____

function onChange(control, oldValue, newValue, isLoading) {

    alert("onchange triggered");

    var attachmentSysId = newValue;

    alert("newvalue=="+attachmentSysId); //  Issue is no sys id here

    var users = new GlideAjax('GlobalCustomUtilsAjax');

    users.addParam('sysparm_name', 'validateUsersFromExcel');

    users.addParam('sysparm_attachment_sysid', attachmentSysId);

    users.getXMLAnswer(handleExcelValidationResponse);

}

function handleExcelValidationResponse(serverResponse) {

    if (!serverResponse) {

        return;

    }

    var parsedResult = JSON.parse(serverResponse);

    populateValidUsers(parsedResult.validUsers || []);

    populateInvalidUsers(parsedResult.invalidUsers || []);

}

function populateValidUsers(validUserSysIds) {

   g_form.setValue('valid_users', validUserSysIds.join(','));

}

function populateInvalidUsers(invalidUsers) {

    var mrvsRows = [];

    invalidUsers.forEach(function(userRow) {

        mrvsRows.push({

            user_name: userRow.userName,

            user_id: userRow.userId

        });

    });

    g_form.setValue('invalid_users', JSON.stringify(mrvsRows));

    g_form.addInfoMessage('completed');

}