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 

since you are already running onChange and doing validation, in your script include you can form an error message stating which users were not found

Then show that as alert or info message when Ajax completes

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

[Sachin Gavhane]

Yes, I understand, but what if list is huge list of invalid users and alert or info message will really not work.
Any other optima solution @Ankur Bawiskar 

[Ankur Bawiskar · Accepted solution]

@Sachin Gavhane 

then send email using gs.eventQueue() and pass those invalid users

Remember the user submitting form won't know the invalid users unless he/she receives email

It complete depends on your customer requirement

-> you can also populate those invalid users in multi-line text variable on same form

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

[Sachin Gavhane]

Sure @Ankur Bawiskar 
I will accept this as a solution for sending huge list of incorrect users.
Incase if you get any more good ideas to inform end user on the incorrect records in the excel sheet then kindly let me know.
Also, all these calculations are expected before we order catalog item.
So, incase even if single user is incorrect in the excel sheet, then user should not be able to submit the catalog form.

[Sachin Gavhane]

hi @Ankur Bawiskar 

for the invalid / incorrect users - can we generate excel file of all invalid users [write excel from ServiceNow] and give it to user to download?