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 

but how will you send that excel/csv file? where will you upload that file so that users can download.

I already informed it varies from customer to customer how they wish to share this list.

[Sachin Gavhane]

hi @Ankur Bawiskar 

the complete discussion going on in this thread:
attaching excel file -- validating it using [on change client script and glide Ajax] and populating valid and invalid users in respective variables of the catalog item.

Now client wants this via widget.
I am not sure why the easy way of using script include is routed to widget.
@Ankur Bawiskar  requesting your help here from architecture point of view {and performance perspective} how developing a widget can help here rather than the already discussed approach.
Your help in giving rough design of how widget can replace the same ask is highly appreciated.

thanks in advance

[Ankur Bawiskar]

@Sachin Gavhane 

It's part of consultant's job to guide and inform best practices.

if widget is used and it's added to variable of type Custom it will be too much customization.

Widget has client controller, server script so it will have to be maintained after instance upgrade.

Widget requirement is feasible

I shared something similar earlier, check that and enhance

It has a widget and upload option and it validates the header for CSV, this will help you to start

How to check csv column header name in client controller script of the widget? 

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

[Ankur Bawiskar · Accepted solution]

@Sachin Gavhane 

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