Improving SharePoint Data Quality with Validation Checkpoints

SharePoint will happily store an email address with a trailing space, a URL without https://, or a Banner ID copied with an invisible character. The list looks fine to a person, but the integration cannot match accounts reliably.

The problem: “almost correct” data

Banner ID:  @00123456 
Name:       Jordan  Lee
Email:      JLEE@EXAMPLE.EDU 
Profile:    www.example.edu/directory/jordan-lee

Every value is understandable to a human. Two of them will fail a strict comparison, and the URL is not absolute.

The quirk: required does not mean valid

SharePoint’s “required” setting only proves that something was entered. It does not prove the email belongs to an approved domain, the Banner ID matches the expected pattern, or the URL is safe.

$bannerId = strtoupper(trim($item['BannerID'] ?? ''));
$email = strtolower(trim($item['EmployeeEmail'] ?? ''));
$url = trim($item['ProfileUrl'] ?? '');

if (!preg_match('/^@d{8}$/', $bannerId)) {
    $errors[] = 'Banner ID must look like @00123456';
}

if (!filter_var($email, FILTER_VALIDATE_EMAIL)
    || !str_ends_with($email, '@example.edu')) {
    $errors[] = 'Email must use the example.edu domain';
}

if (!filter_var($url, FILTER_VALIDATE_URL)
    || !preg_match('/^https?:///i', $url)) {
    $errors[] = 'Profile URL must be absolute';
}

The workaround: report errors instead of silently dropping rows

My validation report includes the SharePoint item ID, Banner ID, field, reason, and timestamp. That gives the content owner something actionable and keeps bad records out of the downstream system without making them disappear mysteriously.