alumni_lookup

Alumni Contact ID Integration

This feature adds Salesforce-compatible Contact ID support to the alumni_lookup application, enabling seamless CRM integration while maintaining BUID as the primary identifier.

Overview

Added: August 14, 2025 Purpose: Support CRM integration with Salesforce-style Contact IDs (format: C-000000000)

The Contact ID system:

• Adds contact_id field to alumni records
• Maintains BUID as primary identifier throughout the application
• Includes Contact ID in all export functionality for CRM import
• Handles Contact ID import via CSV with flexible field name mapping

Key Features

🔧 Database Schema

• New Field: contact_id (string) with unique constraint
• Format Validation: Must match pattern C-\d{9} (e.g., “C-000198612”)
• Optional Field: Can be blank, uniqueness only enforced when present

📥 Import Functionality

• Field Mapping: Recognizes “AdvRM - Contact ID” as well as variations like “AdvRM-Contact ID”
• CSV Support: Standard alumni import at /settings/alumni/upload_alumni processes Contact ID
• Field Normalization: Flexible header mapping handles different naming conventions
• Backwards Compatible: Existing CSV files without Contact ID continue to work

📤 Export Functionality

• Standard Header: All exports use “AdvRM - Contact ID” column header
• Comprehensive Coverage: Contact ID included in:
  • Main alumni export (Csv::AlumniExporter)
  • Batch search results export
  • Champion signup export
  • Engagement statistics export
  • API responses

🖥️ User Interface

• Alumni Show Page: Displays Contact ID with “AdvRM - Contact ID:” label
• Edit Form: Includes Contact ID field with proper validation

• Search Results: Shows “BUID

  Contact ID” format in all alumni listings

• API Endpoints: Contact ID included in Alumni API JSON responses

Implementation Details

Model Validation

class Alumni < ApplicationRecord
  validates :contact_id, format: { with: /\AC-\d{9}\z/, message: "must be in format C-000000000" }, allow_blank: true
  validates :contact_id, uniqueness: true, allow_blank: true
end

CSV Field Mapping

The import system automatically maps these field names to contact_id:

• “AdvRM - Contact ID”
• “AdvRM-Contact ID”
• “AdvRM_Contact_ID”
• “advrm - contact id” (case insensitive)

Export Headers

All CSV exports use the standardized header: AdvRM - Contact ID

Sample Data Format

Import CSV

BUID,AdvRM - Contact ID,First Name,Last Name
B00558855,C-000198612,Annie,Stewart
B00520725,C-000181963,Erin,Lawrence

Export CSV

buid,AdvRM - Contact ID,pref_name,last_name,...
B00558855,C-000198612,Annie,Stewart,...
B00520725,C-000181963,Erin,Lawrence,...

Files Modified

Database

• db/migrate/20250814145917_add_contact_id_to_alumni.rb - Migration
• app/models/alumni.rb - Validation

Import/Export Services

• app/services/csv/alumni_importer.rb - Field normalization and import
• app/services/csv/alumni_exporter.rb - Export header and data

Controllers

• app/controllers/alumni_controller.rb - Permit contact_id parameter
• app/controllers/batch_search_controller.rb - Export header
• app/controllers/engagement_stats_controller.rb - Export header
• app/controllers/api/alumni_controller.rb - JSON response

Views

• app/views/alumni/show.html.erb - Display and edit form
• app/views/alumni/search.html.erb - Search results
• app/views/alumni/top_engaged.html.erb - Top engaged display
• app/views/engagement_activities/index.html.erb - Activity listings
• app/views/engagement_stats/_alumni_table.html.erb - Stats table
• app/views/batch_search/create.turbo_stream.erb - Batch search results

Testing Verification

✅ Database migration successful
✅ Model validation works correctly
✅ Import processes “AdvRM - Contact ID” field
✅ Import updates Contact IDs for existing alumni
✅ Export includes “AdvRM - Contact ID” header
✅ UI displays Contact ID appropriately
✅ API includes Contact ID in responses

Bug Fixes Applied

Contact ID Import Update Issue (August 14, 2025)

Issue: The CSV importer was validating Contact IDs but not actually updating them for existing alumni records. The process_existing_alumni method was missing Contact ID update logic.

Fix: Added Contact ID update logic to process_existing_alumni method in app/services/csv/alumni_importer.rb:

# Handle contact_id
new_contact_id = normalized_row['contact_id']&.strip
if should_update_field?(alumni.contact_id, new_contact_id, 'contact_id', alumni, original_data)
  alumni.contact_id = new_contact_id
  updated = true
  puts "  Updated contact_id: #{new_contact_id}" if @total_processed % 100 == 0
end

Verification: Import now properly updates existing alumni Contact IDs when CSV contains “AdvRM - Contact ID” data.

Usage

Importing Contact IDs

1. Navigate to Settings → Alumni → Upload Alumni
2. Upload CSV with “AdvRM - Contact ID” column
3. System automatically maps and validates Contact IDs
4. View results in alumni records

Exporting with Contact IDs

1. Use any alumni export function (search, champion signups, etc.)
2. CSV will include “AdvRM - Contact ID” column
3. Import directly into Salesforce or other CRM systems

Manual Entry

1. Open any alumni record
2. Click “Edit Alumni Data”
3. Enter Contact ID in “AdvRM - Contact ID” field (format: C-000000000)
4. Save changes

CRM Integration

This implementation provides:

• Salesforce Ready: Contact IDs use standard Salesforce format
• Bi-directional Sync: Import from CRM, export to CRM
• Data Integrity: Unique constraint prevents duplicates
• Backwards Compatibility: Existing BUID workflows unchanged