Need to Notify stakeholders for failed records.

[SNOWTechie]

We have a SharePoint to ServiceNow integration that uses a staging table, Transform Map, and target table.

In the target table, we have a "Requested For" field, which is mapped from the "Customer" field in the staging table. A field script has been implemented on the Customer field to perform validation. During the transformation process, the script checks whether the email address received from SharePoint exists in the sys_user table and whether the corresponding user is active. The record is inserted or updated only when a valid active user is found. Otherwise, the transformation ignores the record, and no insert/update occurs. The Choice Action for the field mapping is set to Ignore.

The new requirement is that stakeholders will upload data in bulk to SharePoint, and some records may contain users who are inactive in ServiceNow or email addresses that do not exist in the system. In such cases, stakeholders need to be notified about the records that were not loaded into ServiceNow, along with the reason for the failure (for example, inactive user or invalid email address).

Is there any out-of-the-box (OOB) functionality in ServiceNow that can provide this type of notification or reporting? If not, what would be the recommended approach to configure this requirement?

Additionally, the organization does not permit the use of scheduled reports, so we are looking for alternative solutions to notify stakeholders about the rejected records.

[Suryansh Verma · Accepted solution]

@SNOWTechie 

ServiceNow provides OOTB visibility of ignored and failed rows through the Import Set execution and its Import Set Rows, but it does not provide a preconfigured stakeholder E-Mail containing all rejected rows and business-validation reasons.

The validation should be moved to an onBefore Transform Script. When the Customer E-Mail is blank, not found, inactive, or ambiguous, set ignore = true and populate status_message with the rejection reason. Do not rely on Choice Action = Ignore because it ignores only the invalid reference value, it does not reject the complete row.

Add an onComplete Transform Script that counts ignored rows for the current Import Set and fires one custom event when the count is greater than zero. Configure an event-based notification with a mail script that queries the rejected import rows and displays the source record, customer E-Mail, and rejection reason. This provides an immediate batch-summary notification without using scheduled reports.

If it is helpful, please mark it as helpful and accept the correct solution by referring to this solution in the future, it will be helpful to them.