Skip to main content

Data Migration Enhancement

1. Business Rules

General Rules

  • The system must only accept CSV file format with maximum size of 20MB
  • Migration names must be unique and descriptive for tracking purposes
  • All mandatory fields specified for each data type must be present in the uploaded file
  • File processing must complete within 30 seconds for files under 10MB

File Upload Rules

  • The system must validate file format before processing begins
  • Processing time must be displayed showing actual time taken (e.g., "9s")
  • Row count must be accurately calculated and displayed (e.g., "4,092 rows processed")
  • Column count must be automatically detected (e.g., "7 columns found")
  • File size must be calculated and displayed in appropriate units (e.g., "0.04 MB")

Data Type Specific Rules

  • Consumers: Must include Customer ID, Name, Address, Service Connection
  • Consumer Meter Mapping: Must include Customer ID, Meter ID, Connection Date
  • Payments: Must include Payment ID, Customer ID, Amount, Payment Date, Payment Method
  • Complaints: Must include Complaint ID, Customer ID, Issue Type, Date Reported, Status

Column Mapping Rules

  • Target fields marked as "Required" (red indicator) must be mapped to source columns
  • Confidence levels must be calculated and displayed: High (Green), Medium (Yellow), Low (Red)
  • Source column dropdowns must only show available columns from uploaded file
  • Transformation column must default to "No Transformation" for all mappings

Transformation Rules

  • No Transformation: Direct mapping with no data modification
  • Split: Must specify delimiter and target fields for split operation
  • Merge (Concat): Must specify source columns to merge and separator character
  • Add Prefix: Must specify prefix text and application conditions
  • Add Suffix: Must specify suffix text and application conditions
  • Remove Substring: Must specify text to remove and match type
  • Uppercase/Lowercase: Must specify application scope (entire text, first letter, etc.)
  • Title Case: Must specify word separators and preservation rules
  • Trim Whitespace: Must specify trim type (both sides, leading, trailing)
  • Find & Replace: Must specify find text, replacement text, and match criteria

Template Management Rules

  • Template names must be descriptive and unique within the system
  • Templates must capture: Original filename, total mappings count, transformations count
  • Usage tracking must increment each time template is applied
  • Templates must be associated with specific data types for appropriate reuse
  • Template creation date and creator must be tracked for audit purposes

Data Relation Mapping Rules

  • Invalid relationships must be flagged and displayed in red (e.g., "Invalid Plan A")
  • Valid relationships must be displayed in green (e.g., "Basic Plan", "Premium Plan")
  • Bulk assignment functionality must allow selecting multiple records for same relationship
  • Search functionality must enable quick location of specific records
  • Filter capabilities must allow narrowing records by various criteria

Migration History Rules

  • Total Migrations count must reflect all migration processes in the system
  • Completed count must show successfully finished migration processes
  • Total Records must sum all data records processed across all migrations
  • Success Rate must be calculated as percentage of records successfully migrated
  • Each migration record must track: Migration Name, Date, Data Category, Status, Total Records, Success Records, Error Records
  • Status must accurately reflect: COMPLETED (green), IN PROGRESS (yellow), FAILED (red)
  • Success and Error record counts must calculate percentages based on total records

Validation and Error Handling Rules

  • High confidence mappings (95%+ confidence) can proceed automatically
  • Medium confidence mappings (50-94% confidence) require user review
  • Low confidence mappings (<50% confidence) must be manually verified
  • Error records must be clearly identified with specific error descriptions
  • Success records must be validated against system constraints before import
  • All validation errors must provide actionable resolution steps

Progress Tracking Rules

  • Overall Progress percentage must be calculated based on completed steps
  • Step progress must be clearly indicated (e.g., "3 of 7" steps completed)
  • Processing status must show real-time updates during file processing
  • Success metrics must be displayed prominently (e.g., "92% (+6%)" completion rate)

Saved Templates Screen Business Rules

Template ID Field:

  • Format: Must follow pattern "TPL-XXX" where XXX is a 3-digit sequential number
  • Generation: System auto-generates in sequential order (TPL-001, TPL-002, TPL-003, etc.)
  • Uniqueness: Must be unique across entire system
  • Display: Always displayed in read-only format to users

Template Name Field:

  • Length: Minimum 3 characters, maximum 100 characters
  • Uniqueness: Must be unique within the same data type category
  • Format: Alphanumeric characters, spaces, hyphens, and underscores allowed
  • Examples: "Customer Data Import", "Meter Readings", "Payment Records"
  • Validation: Cannot contain special characters except hyphens and underscores

Data Type Field:

  • Values: Must match predefined system data types (Consumer, Meter Data, Payments, etc.)
  • Display: Shows as colored tag/badge for visual categorization
  • Validation: Must correspond to actual data categories available in the system
  • Consistency: Must match the data type selected during template creation

Created By Field:

  • Source: Automatically populated from current logged-in user
  • Format: Display user's full name (First Name + Last Name)
  • Examples: "John Admin", "Sarah Manager", "Mike Davis"
  • Immutable: Cannot be changed after template creation
  • Required: Always populated, never empty

Created On Field:

  • Format: MM/DD/YYYY (e.g., "1/15/2025", "1/10/2025", "1/5/2025")
  • Timezone: System timezone (typically utility's local timezone)
  • Auto-population: Automatically set when template is saved
  • Immutable: Cannot be modified after creation
  • Sorting: Default sort should be by most recent creation date

Usage Field:

  • Format: Number followed by "times" text (e.g., "12 times", "8 times", "15 times")
  • Calculation: Increments by 1 each time template is successfully applied to a migration
  • Initial Value: Starts at "1 times" for newly created templates
  • Display: Right-aligned for better readability
  • Tracking: Only counts successful applications, not failed attempts

Actions Column:

  • Dropdown Menu: Triggered by three-dot (⋮) icon
  • Available Actions: "View Details", "Use Template"
  • Permissions: Actions available based on user role and template ownership
  • View Details: Shows complete template configuration and mapping details
  • Use Template: Redirects to the upload process with the template pre-loaded

Table Behavior Rules:

  • Pagination: Display standard number of templates per page with pagination controls
  • Sorting: Allow sorting by Template ID, Name, Data Type, Created On, Usage
  • Filtering: Provide filters for Data Type, Created By, Date Range
  • Search: Global search across Template Name and Template ID
  • Empty State: Show informative message when no templates exist
  • Loading State: Show skeleton/loading animation during data retrieval

Category Tabs (Customer Management, Meter Management, etc.)

  • Visual states and selection behavior
  • Data type count validation and formatting
  • Dynamic calculation requirements
  • Display mandatory fields that are required for this data type
  • Display the number of entries we have in the system for that specific data type
  • On click upload navigate user to the next screen

Processing Statistics Display

Statistics Layout:

  • Format: 4-column grid layout with equal spacing
  • Alignment: Center-aligned numbers with labels below
  • Typography: Large bold numbers with smaller descriptive labels

Rows Processed Statistic:

  • Value: "4,092" with comma separator for thousands
  • Label: "Rows Processed" in smaller gray text
  • Color: Green text color indicating successful processing
  • Format: Must use comma separators for numbers ≥1,000

Columns Found Statistic:

  • Value: "7" as integer
  • Label: "Columns Found" in smaller gray text
  • Color: Green text color
  • Validation: Must match actual column count detected in uploaded file

File Size Statistic:

  • Value: "0.04 MB" with two decimal places
  • Label: "File Size" in smaller gray text
  • Color: Green text color
  • Format: Display in MB with appropriate decimal precision
  • Calculation: File size converted from bytes to MB (bytes ÷ 1,048,576)

Processing Time Statistic:

  • Value: "9s"
  • Label: "Processing Time" in smaller gray text
  • Color: Green text color
  • Format: Display seconds with "s" suffix
  • Precision: Whole seconds for processing times under 60s

Data Preview Section

Section Header:

  • Title: "Data Preview (First 5 Rows)"
  • Purpose: Clearly indicates limited row display for verification
  • Font: Bold header text to distinguish from data content

Preview Table Structure:

  • Rows Displayed: Exactly 5 rows of data (excluding header)
  • Headers: Must match column names from uploaded CSV file
  • Styling: Standard table format with alternating row colors for readability
  • Scrolling: Horizontal scroll if columns extend beyond container width

Table Column Data:

  • Customer_ID Column: Format "CUST001", "CUST002", etc. - alphanumeric customer identifiers
  • Name Column: Full names like "John Smith", "Jane Doe" - proper case formatting
  • Address Column: Street addresses like "123 Main St", "456 Oak Ave" - standard address format
  • Service_Connection Column: Service codes like "SC001", "SC002" - alphanumeric service identifiers
  • Status Column: Status values like "Active", "Inactive", "Pending" - standardized status terms

Data Validation Display:

  • Consistency: All displayed data must match exactly what was uploaded
  • Format Preservation: Maintain original data formatting from CSV
  • Error Indication: No error highlighting in preview (errors handled in later steps)

Navigation Controls

Back Button:

  • Position: Bottom left of screen
  • Icon: Left-pointing arrow
  • Text: "Back"
  • Style: Secondary button styling (outline or light background)
  • Functionality: Returns to previous file upload step

Next Button:

  • Position: Bottom right of screen
  • Icon: Right-pointing arrow after text
  • Text: "Next: Map Columns"
  • Style: Primary blue button
  • State: Enabled since file processing completed successfully
  • Functionality: Proceeds to column mapping interface

Column Mapping & Transformations Screen Business Rules

Page Header Section

Section Title:

  • Text: "Column Mapping & Transformations"
  • Icon: Transformation/mapping icon (interconnected arrows or similar)
  • Subtitle: "Map your source columns to target fields and configure data transformations"
  • Font: Bold header with descriptive subtitle in smaller gray text

Mapping Table Structure

Target Field Column

Field Names:

  • Display: System-defined target field names (Customer ID, First Name, Last Name, Email, Phone, Address, City, State, Zip Code)
  • Format: Proper case with spaces for readability
  • Required Indicator: Red "Required" badge for mandatory fields
  • Validation: Target fields must match system schema definitions

Required Field Indicators:

  • Customer ID: Shows red "Required" badge
  • Visual: Red background badge with white text
  • Position: Adjacent to field name
  • Purpose: Clearly identifies fields that must be mapped for successful import

Transformation Column

Default State:

  • Initial Value: "No..." (indicating "No Transformation")
  • Dropdown Icon: Down arrow indicating expandable dropdown
  • State: Enabled for all rows

Dropdown Options (when expanded):

  • "No Transformation" (default, with checkmark)
  • "Split"
  • "Merge (Concat)"
  • "Add Prefix"
  • "Add Suffix"
  • "Remove Substring"
  • "Uppercase"
  • "Lowercase"
  • "Title Case"
  • "Trim Whitespace"
  • "Find & Replace"

Selected Transformation Display:

  • Email row shows "Add Prefix" with gear/settings icon
  • Format: Transformation name + configuration icon
  • Click behavior: Opens transformation configuration popup

Source Column Dropdown

Column Options:

  • Must populate from actual uploaded file columns
  • Examples shown: "first_name", "last_name", "phone_number", "street_address", "city_name", "state_code", "postal_code"
  • Format: Exact column names as they appear in CSV file
  • Empty State: Blank dropdown for unmapped fields
  • Validation: Only show columns that exist in uploaded file

Dropdown Behavior:

  • Single selection per target field
  • Disable column options already selected in other rows (to prevent duplicate mappings)
  • Show column preview data on hover

Confidence Level Column

Confidence Scoring:

  • High Confidence: Green background, "High Confidence" text, 95% score
  • Medium Confidence: Yellow/amber background, "Medium Confidence" text, 75% score
  • Low Confidence: Red background, "Low Confidence" text, 50% score

Confidence Calculation Rules:

  • Based on AI matching algorithm between source and target field names
  • High (90-100%): Exact or near-exact name matches
  • Medium (60-89%): Partial matches or common patterns
  • Low (0-59%): Poor matches or manual mappings

Visual Indicators:

  • Color-coded backgrounds for immediate recognition
  • Percentage values should be displayed for precision
  • Icons optional but recommended for accessibility

File Data Column

Sample Data Display:

  • Format: "Sample 1, Sample 2..." indicating multiple sample values
  • Content: First few values from the source column
  • Truncation: Use ellipsis (...) for longer content
  • Purpose: Allows user to verify mapping accuracy

Transformed Data Column

Preview Content:

  • Shows result of applied transformation on sample data
  • Format: "Sample 1, Sample 2..." matching File Data format
  • Real-time Updates: Must update when transformation changes
  • No Transformation: Shows identical content to File Data column

Sample Data Column (rightmost)

Additional Samples:

  • Extended sample data beyond first preview
  • Format: "Sample 1,5" or similar truncated format
  • Scrollable: Allow horizontal scroll if more samples needed
  • Validation: Help users verify data quality

Transformation Popup Business Rules

No Transformation Popup

Content:

  • Title: "No Transformation"
  • Message: "Data will be copied directly without any modifications"
  • Buttons: "Cancel" and "Apply"
  • No additional configuration needed

Sample Data Display:

  • Before: Shows original source data
  • After: Shows identical data (no changes)

Split Transformation Popup

Configuration Fields:

  • Delimiter: Text input field
    • Placeholder: "Enter delimiter (e.g., comma, semicolon, space)"
    • Examples: "," ";" "|" " " or custom string
    • Validation: Cannot be empty
  • Split into Target Fields: Multi-select dropdown
    • Options: All available target fields
    • Validation: Must select at least 2 target fields
    • when selected split and selected multiple target filed columns and applied then display the same transformation rule for the other target fileds and do not let user access the those fields unless user changes the original one

Sample Data:

  • Before: "John,Smith,Manager"
  • After: Shows split result across selected fields

Merge (Concat) Transformation Popup

Configuration Fields:

  • Columns to merge: Multi-select dropdown of available source columns
    • Options: All mapped source columns
    • Validation: Must select at least 2 columns
  • Separator: Text input
    • Examples: " " (space), "," "-" or custom string
    • Option: "No separator" checkbox
    • When selected, merge in the source column fields display multiple source columns selected

Sample Data:

  • Before: Shows selected columns - "John" + "Smith"
  • After: "John Smith" (with space separator)

Add Prefix Transformation Popup

Configuration Fields:

  • Prefix text: Text input (required)
  • Apply to: Radio buttons
    • "All rows" (default)
    • "Non-empty rows only"
    • "Rows matching condition"
  • Condition dropdown (if "Rows matching condition" selected):
    • Contains text, Starts with, Ends with, Equals, Does not contain, Length greater than, Length less than, Matches pattern
  • Value input: Text field for condition criteria

Sample Data:

  • Before: "john.smith@email.com"
  • After: "user_john.smith@email.com" (with "user_" prefix)

Add Suffix Transformation Popup

Configuration Fields:

  • Suffix text: Text input (required)
  • Apply to: Radio buttons (same as prefix)
  • Condition options: Same as prefix transformation

Sample Data:

  • Before: "john.smith@email.com"
  • After: "john.smith@email.com_verified" (with "_verified" suffix)

Remove Substring Transformation Popup

Configuration Fields:

  • Text to remove: Text input (required)
  • Match type: Radio buttons
    • "Exact match" (default)
    • "Case-insensitive"
    • "Regular expression"

Sample Data:

  • Before: "Mr. John Smith"
  • After: "John Smith" (removing "Mr. ")

Case Transformation Popups (Uppercase/Lowercase)

Uppercase Configuration:

  • Apply to: Radio buttons ("Entire text", "First letter only", "First letter of each word")
  • Handle special characters: Checkbox for preserving spaces/punctuation

Lowercase Configuration:

  • Apply to: Radio buttons ("Entire text", "Keep first letter uppercase")
  • Handle special characters: Checkbox

Title Case Transformation Popup

Configuration Fields:

  • Word separators: Checkboxes (Space, Hyphen, Underscore, Custom)
  • Custom separators: Text input (enabled if Custom checked)
  • Preserve existing capitals: Checkbox for acronyms/abbreviations

Trim Whitespace Transformation Popup

Configuration Fields:

  • Trim type: Radio buttons ("Both sides", "Leading only", "Trailing only")
  • Also remove: Checkboxes ("Extra spaces between words", "Tabs", "Line breaks")

Find & Replace Transformation Popup

Configuration Fields:

  • Find: Text input (required)
  • Replace with: Text input (can be empty for removal)
  • Match type: Radio buttons ("Exact match", "Case-insensitive", "Regular expression", "Whole words only")
  • Replace: Radio buttons ("First occurrence", "All occurrences")
  • Preview: Show before/after examples with highlighting

Sample Data:

  • Before: "John Smith III"
  • After: "John Smith Jr." (replacing "III" with "Jr.")

Field Categorization

Identifiers (IDs, Codes)

  • consumer_no
  • meter_number
  • device_no

Names / Text

  • first_name
  • last_name
  • meter_make
  • utility_service
  • remarks

Email

  • email

Phone

  • contact_number
  • alternate_contact_number

Address

  • street
  • address_line
  • billing_premise
  • service_premise
  • premise
  • unit
  • zipcode
  • floor
  • floor_unit

Dates

  • connect_date
  • current_reading_date
  • installed_on

Numeric / Balance

  • opening_balance
  • current_reading
  • long
  • lat
  • reading_oem

Status / Category

  • ownership
  • Plan
  • Category
  • Sub Category
  • Status
  • category (meter)
  • sub_category (meter)
  • meter_type

Field Categorization Asset Management

Identifiers (IDs, Codes)

  • Facility ID
  • System/Network ID
  • Serial Number
  • Asset ID

Names / Text

  • Asset Name/Description
  • Facility Name
  • System Name
  • Network Name
  • Asset Class
  • Asset Type
  • System Type Name
  • Network Type
  • Facility Type
  • Location
  • Manufacturer
  • Network Operator
  • Facility Manager
  • Service Zone/Division
  • Description/Purpose
  • Description/Notes
  • Parameter Name
  • Parameter Description
  • Dimension

Email

  • Emails in Facilities, systems and networks

Phone

  • (None in asset data)

Address

  • Address/Site Label (facilities/systems/networks/Assets)
  • Location (asset location within facility)

Dates

  • Installation Year (convert to date)
  • Commissioned Date
  • Commissioned/Planned Date

Numeric / Balance

  • Useful Life
  • Installation Cost
  • Replacement Cost
  • Current Value
  • Latitude
  • Longitude
  • Design Capacity (MLD)
  • Annual O&M Budget
  • Design Capacity
  • Current Throughput

Status / Category

  • Status (assets)
  • Operating Status
  • Operational Status
  • Depreciation Method
  • Operational Hours
  • Backup Status
  • Design Capacity Unit
  • Current Throughput Unit
  • Unit
  • Unit of Measurement

Asset Relation Tabs

Facility Assignment Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • Facility ID - The facility being validated/assigned
  • Location - Physical location within facility
  • Asset Class - For facility compatibility checking
  • Asset Type - For facility type appropriateness
  • Installation Year - For facility timeline context
  • Status - Current operational status

Why these columns: Users need to verify that assets belong to the correct facilities and that the asset type is appropriate for that facility's purpose.

System/Network Assignment Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • System/Network ID - The system being validated/assigned
  • Facility ID - Parent facility context
  • Asset Class - System compatibility verification
  • Asset Type - System-asset type matching
  • Status - Operational alignment checking

Why these columns: System assignments must be logical within the facility hierarchy, and asset types must be compatible with system types.

Asset Class Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • Asset Class - The class being validated/assigned
  • Asset Type - Must be compatible with selected class
  • Manufacturer - Class-manufacturer relationship context
  • Installation Year - Historical classification context
  • Useful Life - Class-based life expectancy validation

Why these columns: Asset class and type must be compatible combinations, and manufacturer/age context helps validate appropriate classifications.

Asset Type Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • Asset Type - The type being validated/assigned
  • Asset Class - Parent classification for compatibility
  • Manufacturer - Type-manufacturer compatibility
  • Serial Number - Type-specific identification
  • Installation Cost - Type-cost relationship validation

Why these columns: Asset type must align with asset class, and manufacturer/cost information helps validate type assignments.

Status Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • Status - The status being validated/assigned
  • Installation Year - Age context for status appropriateness
  • Useful Life - Life expectancy vs current status
  • Depreciation Method - Status impact on depreciation
  • Replacement Cost - Status-value relationship

Why these columns: Asset status should align with age, depreciation, and replacement planning considerations.

Manufacturer Tab

Primary Columns:

  • Asset Name/Description - Asset identifier
  • Manufacturer - The manufacturer being validated/assigned
  • Asset Type - Manufacturer-type compatibility
  • Serial Number - Manufacturer's serial number format validation
  • Installation Year - Manufacturer's operational period
  • Installation Cost - Manufacturer-cost relationship

Why these columns: Manufacturer assignments must be compatible with asset types, and serial numbers should follow manufacturer-specific formats.

Facility Relation Tabs

Facility Type Tab

Primary Columns:

  • Facility Name - Facility identifier
  • Facility Type - The type being validated/assigned (Treatment Plant, Pumping Station, Storage, Distribution)
  • Design Capacity (MLD) - Capacity alignment with facility type
  • Operating Status - Type-status compatibility
  • Commissioned Date - Type evolution context
  • Service Zone/Division - Type-service area appropriateness

Why these columns: Facility type must align with capacity, operational status, and service purpose. Different types have different capacity expectations and service roles.

Operating Status Tab

Primary Columns:

  • Facility Name - Facility identifier
  • Operating Status - The status being validated/assigned (Active, Inactive, Under Construction, Decommissioned)
  • Commissioned Date - Status-timeline validation
  • Design Useful Life - Status vs. expected lifespan
  • Annual O&M Budget - Budget alignment with operational status
  • Facility Manager - Management assignment for operational facilities

Why these columns: Operating status must be logical given the facility's age, budget allocation, and management assignment. Active facilities should have managers and budgets.

Operational Hours Tab

Primary Columns:

  • Facility Name - Facility identifier
  • Operational Hours - Hours being validated/assigned (24/7, Business Hours, Custom)
  • Facility Type - Type-hours compatibility
  • Design Capacity (MLD) - Capacity utilization vs. hours
  • Service Zone/Division - Service demand vs. operational schedule
  • Operating Status - Status-hours alignment

Why these columns: Operational hours must match facility type requirements, service demands, and current operational status.

Service Zone/Division Tab

Primary Columns:

  • Facility Name - Facility identifier
  • Service Zone/Division - The zone being validated/assigned
  • Facility Type - Type-service zone compatibility
  • Address/Site Label - Geographic context for zone assignment
  • Design Capacity (MLD) - Capacity vs. zone size/demand
  • Operating Status - Zone service capability

Why these columns: Service zones must be geographically logical, match facility capacity, and align with facility type and operational capability.

Facility Manager Tab

Primary Columns:

  • Facility Name - Facility identifier
  • Facility Manager - The manager being validated/assigned
  • Operating Status - Management requirement based on status
  • Facility Type - Manager expertise alignment
  • Annual O&M Budget - Budget management responsibility
  • Design Capacity (MLD) - Facility complexity for management assignment

Why these columns: Facility managers should be assigned based on operational status, facility complexity, and budget responsibility requirements.

System Data Relation Tabs

Based on the Systems data fields, here are the relation tabs and their required columns:

Facility Assignment Tab

Primary Columns:

  • System Name - System identifier
  • Facility ID - The facility being validated/assigned
  • System Type - System-facility type compatibility
  • Address/Site Label - Geographic alignment with facility
  • Status - System status vs facility operational status
  • Design Capacity - Capacity alignment with facility capacity

Why these columns: Systems must belong to appropriate facilities, and system types should be compatible with facility types. Geographic and capacity alignment ensures logical facility-system relationships.

System Type Tab

Primary Columns:

  • System Name - System identifier
  • System Type - The type being validated/assigned
  • Design Capacity - Type-capacity compatibility
  • Current Throughput - Performance alignment with type
  • Commissioned/Planned Date - Type evolution timeline
  • Description/Purpose - Type-purpose alignment

Why these columns: System type must align with design capacity, actual performance, and stated purpose. Timeline context helps validate type assignments.

Status Tab

Primary Columns:

  • System Name - System identifier
  • Status - The status being validated/assigned (Active, Inactive, Under Construction, Maintenance)
  • Commissioned/Planned Date - Status-timeline validation
  • Current Throughput - Performance vs status alignment
  • Operational Hours - Status-hours compatibility
  • Backup Status - Status impact on backup requirements

Why these columns: System status must be consistent with commissioning timeline, performance levels, operational schedules, and backup availability.

Backup Status Tab

Primary Columns:

  • System Name - System identifier
  • Backup Status - The backup status being validated/assigned (Available, Not Available, Partial)
  • System Type - Critical systems require backup
  • Status - Operational status affects backup needs
  • Design Capacity - High capacity systems need backup
  • Current Throughput - Performance criticality assessment

Why these columns: Backup requirements depend on system criticality (type), operational status, and capacity/throughput levels. Critical systems need backup coverage.

Operational Hours Tab

Primary Columns:

  • System Name - System identifier
  • Operational Hours - Hours being validated/assigned (24/7, Business Hours, Custom)
  • System Type - Type-hours requirements
  • Status - Status-hours alignment
  • Current Throughput - Demand-hours relationship
  • Backup Status - Hours impact on backup needs

Why these columns: Operational hours must match system type requirements, current status, and performance demands. Backup needs vary with operational schedules.

Network Data Relation Tabs

Facility Assignment Tab

Primary Columns:

  • Network Name - Network identifier
  • Facility ID - The facility being validated/assigned
  • Network Type - Network-facility type compatibility
  • Design Capacity - Capacity alignment with facility capacity
  • Operational Status - Network status vs facility operational status
  • Description/Notes - Context for facility assignment

Why these columns: Networks must be assigned to appropriate facilities where the network type makes sense for the facility's purpose and capacity requirements.

Network Type Tab

Primary Columns:

  • Network Name - Network identifier
  • Network Type - The type being validated/assigned (Distribution, Transmission, Collection, Communication)
  • Design Capacity - Type-capacity relationship validation
  • Facility ID - Facility context for type appropriateness
  • Network Operator - Operator expertise for network type
  • Backup Status - Type-specific backup requirements

Why these columns: Network type must align with facility purpose, capacity requirements, and operator capabilities. Different types have different backup and operational requirements.

Operational Status Tab

Primary Columns:

  • Network Name - Network identifier
  • Operational Status - The status being validated/assigned (Active, Inactive, Under Construction, Maintenance)
  • Network Operator - Status-operator assignment relationship
  • Backup Status - Status impact on backup requirements
  • Design Capacity - Performance expectations vs status
  • Network Type - Critical network types vs status

Why these columns: Operational status must be consistent with operator assignment, backup availability, and performance expectations based on network criticality.

Backup Status Tab

Primary Columns:

  • Network Name - Network identifier
  • Backup Status - The backup status being validated/assigned (Available, Not Available, Partial)
  • Network Type - Type-specific backup criticality
  • Operational Status - Status-backup requirement alignment
  • Design Capacity - High capacity networks need backup
  • Network Operator - Operator backup management capability

Why these columns: Backup requirements vary by network type criticality, operational status, and capacity. Critical networks (transmission, communication) typically require backup coverage.

Network Operator Tab

Primary Columns:

  • Network Name - Network identifier
  • Network Operator - The operator being validated/assigned
  • Network Type - Operator expertise alignment
  • Operational Status - Operator assignment for operational networks
  • Design Capacity - Operator capability vs network complexity
  • Facility ID - Geographic/organizational operator assignment

Why these columns: Network operators should have appropriate expertise for the network type and capacity, and should be assigned based on operational status and geographic location.

Business Rules for Transformation Restrictions

No Transformation

  • Allowed for all field types
  • Rule: Default option available for every field, regardless of data type

Split

  • Allowed: Names/Text, Address, Status/Category
  • Not Allowed: Identifiers, Email, Phone, Dates, Numeric/Balance
  • Rule: Only allow splitting for fields where logical subdivision makes sense (e.g., full names, combined addresses, compound status values)

Merge (Concat)

  • Allowed: Names/Text only
  • Not Allowed: All other types
  • Rule: Only allow merging text fields like first_name + last_name; prevent merging IDs, numbers, or structured data

Add Prefix

  • Allowed: Names/Text, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Email, Dates, Numeric/Balance
  • Rule: Allow prefixes for descriptive fields but protect unique identifiers and structured data integrity

Add Suffix

  • Allowed: Names/Text, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Email, Dates, Numeric/Balance
  • Rule: Similar to prefix - allow for descriptive enhancement but protect critical data structures

Remove Substring

  • Allowed: Names/Text, Email, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Dates, Numeric/Balance
  • Rule: Allow cleanup of text fields but prevent modification of IDs and precise numeric/date values

Uppercase

  • Allowed: Names/Text, Email, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Dates, Numeric/Balance
  • Rule: Standardization allowed for text fields but not for case-sensitive IDs or numeric data

Lowercase

  • Allowed: Names/Text, Email, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Dates, Numeric/Balance
  • Rule: Same as uppercase - text standardization only

Title Case

  • Allowed: Names/Text, Address only
  • Not Allowed: Identifiers, Email, Phone, Dates, Numeric/Balance, Status/Category
  • Rule: Only for proper nouns and addresses where title case is appropriate; not for technical fields

Trim Whitespace

  • Allowed for all field types
  • Rule: Data cleanup allowed universally as it doesn't alter content meaning

Find & Replace

  • Allowed: Names/Text, Email, Phone, Address, Status/Category
  • Not Allowed: Identifiers, Dates, Numeric/Balance
  • Rule: Allow pattern replacement in descriptive fields but protect unique identifiers and precise numeric values from corruption

Navigation and Action Buttons

Progress Scroll Bar

Visual Indicator:

  • Horizontal scroll bar showing progress through mapping table
  • Position: Below main table content
  • Behavior: Updates as user scrolls through columns

Back Button

Properties:

  • Position: Bottom left
  • Icon: Left arrow
  • Text: "Back"
  • Style: Secondary button (outline or gray)
  • Action: Returns to previous step (file upload)

Save as Template Button

Properties:

  • Position: Bottom center-right
  • Icon: Save/template icon
  • Text: "Save as Template"
  • Style: Secondary button with icon
  • Action: Opens template saving popup
  • State: Enabled when valid mappings exist

Data Relation Mapping Button

Properties:

  • Position: Bottom right
  • Icon: Right arrow or relation icon
  • Text: "Data Relation Mapping"
  • Style: Primary blue button
  • Action: Proceeds to next step
  • State: Enabled when all required fields are mapped

Save Template Popup Business Rules

Template Name Field

Input Properties:

  • Label: "Template Name"
  • Placeholder: "Enter a descriptive name for this template"
  • Validation: 3-100 characters, unique name required
  • Required: Cannot be empty

Source File Information Section

Display Fields:

  • Original File: Shows uploaded filename (e.g., "fortworth_rfp_gap_analysis.csv")
  • Total Mappings: Shows count of mapped fields (e.g., "9 fields mapped")
  • Transformations: Shows count of applied transformations (e.g., "1 applied")
  • Format: Read-only informational display

Mapping Configuration Summary

Scrollable List Display:

  • Format: "source_column → Target Field" with confidence percentage
  • Examples: "cust_id → Customer ID 95% confidence"
  • Transformation Indicator: Purple "add_prefix" tag for transformed fields
  • Confidence Color Coding: Green (95%), Yellow (75%), Red (50%)
  • Scroll Behavior: Vertical scroll for long lists

Template Benefits Section

Information Display:

  • Background: Light blue informational background
  • Bullet Points:
    • "Reuse this configuration for files with similar column structures"
    • "Save time by automatically applying proven mappings and transformations"
    • "Ensure consistency across multiple data imports"
    • "Reduce errors by using tested mapping configurations"

Template Action Buttons

Cancel Button:

  • Style: Secondary/outline button
  • Position: Bottom right, before Save
  • Action: Closes popup without saving

Save Template Button:

  • Style: Primary blue button
  • Position: Bottom right
  • Icon: Save icon
  • Action: Saves template and closes popup
  • Validation: Requires valid template name

Data Relation Mapping Screen Business Rules

Source Data Section Header

Section Title:

  • Text: "Source Data"
  • Font: Bold, larger heading text
  • Position: Top of main content area

Section Description:

  • Text: "Map your source columns to target relationships and configure data connections"
  • Font: Smaller gray descriptive text below title
  • Purpose: Explains the function of this step

Action Buttons (Top Right)

Clear All Button:

  • Position: Top right, left of Save Template
  • Style: Secondary/outline button
  • Text: "Clear All"
  • Function: Resets all relationship mappings and selections
  • Confirmation: Should prompt user before clearing all data

Column Headers For Customer Data Type

Plan Column:

  • Header Text: "Plan"
  • Purpose: Shows plan assignments for each customer record
  • Sort: Should allow sorting by plan type

Category Column:

  • Header Text: "Category"
  • Purpose: Shows data categorization
  • Display: Currently empty in sample data

Sub Category Column:

  • Header Text: "Sub Category"
  • Purpose: Shows sub-categorization
  • Display: Currently empty in sample data

Status Column:

  • Header Text: "Status"
  • Purpose: Shows record processing status
  • Display: Currently empty in sample data

Invalid Records Warning

Warning Message:

  • Text: "2 out of 4 rows have invalid plan details"
  • Color: Red text indicating errors/issues
  • Position: Above the plan assignment dropdown
  • Format: "{count} out of {total} rows have invalid {field} details"
  • Purpose: Alerts user to data quality issues requiring attention

Select Plan Values Dropdown:

  • Position: Right of warning message
  • Text: "Select Plan Values"
  • Function: Bulk assignment dropdown for plan values
  • Options: Should populate with valid plan options from system
  • State: Enabled when records are selected

Search and Filter Controls

Search Field:

  • Placeholder: "Search records..."
  • Icon: Search/magnifying glass icon
  • Position: Left side of filter bar
  • Function: Text search across all visible columns
  • Behavior: Real-time search as user types

Filters Button:

  • Text: "Filters"
  • Icon: Filter icon
  • Style: Secondary button with icon
  • Function: Opens advanced filtering options
  • Purpose: Allow filtering by specific criteria

Assign to Selected Button:

  • Text: "Assign to Selected (0)"
  • Background: Blue (primary action color)
  • State: Shows count of selected records
  • Format: "Assign to Selected ({count})"
  • Function: Applies selected plan to checked records
  • State: Disabled when no records selected

Bulk Assignment Dropdown:

  • Position: Right side of action bar
  • Default Text: "Bulk Assignment"
  • Function: Select assignment action for multiple records
  • Options: Various bulk actions for selected records

Data Table Structure

Selection Column (Checkboxes)

Checkbox Behavior:

  • Position: First column, left-most
  • Function: Multi-select rows for bulk operations
  • Header Checkbox: Select/deselect all visible rows
  • Individual Checkboxes: Select specific rows
  • State Tracking: Update "Assign to Selected" count dynamically

Consumer No Column

Data Format:

  • Pattern: "CON001", "CON002", "CON003", "CON004"
  • Format: Alphanumeric consumer identifier
  • Sort: Should allow alphanumeric sorting
  • Link: Should be clickable to view consumer details

First Name Column

Data Display:

  • Examples: "John", "Jane", "Bob", "Alice"
  • Format: Proper case names
  • Validation: Must be non-empty for valid records
  • Edit: Should allow inline editing if permissions allow

Last Name Column

Data Display:

  • Examples: "Doe", "Smith", "Johnson", "Williams"
  • Format: Proper case surnames
  • Validation: Must be non-empty for valid records
  • Edit: Should allow inline editing if permissions allow

Billing Premise Column

Data Display:

  • Examples: "Main Street", "Oak Avenue", "Pine Road", "Elm Street"
  • Format: Street address or location identifier
  • Purpose: Identifies billing location for customer
  • Validation: Must match valid premise addresses in system

Service Premise Column

Data Display:

  • Examples: "Main Street", "Oak Avenue", "Pine Road", "Elm Street"
  • Format: Street address or location identifier
  • Purpose: Identifies service delivery location
  • Note: Often matches Billing Premise but can differ

Plan Column (Critical Field)

Valid Plan Display:

  • Examples: "Basic Plan", "Premium Plan"
  • Color: Green text indicating valid relationship
  • Background: Light green or normal background
  • Format: Plan names must match system-defined plans

Invalid Plan Display:

  • Examples: "Invalid Plan A", "Invalid Plan B"
  • Color: Red text indicating validation error
  • Background: Light red or error highlighting
  • Format: Shows original imported value with "Invalid" prefix
  • Validation: Must be resolved before proceeding

Plan Validation Rules

Valid Plan Criteria:

  • Must exist in system's plan master data
  • Must be active and available for assignment
  • Must match exactly (case-sensitive or normalized)
  • Must be compatible with customer type/category

Invalid Plan Handling:

  • Display original value with error indication
  • Provide dropdown of valid plan options for correction
  • Block progression until all invalid relationships resolved
  • Show count of invalid records prominently

Plan Assignment Options:

  • Dropdown should populate with all active system plans
  • Options should be filtered based on customer eligibility
  • Should allow bulk assignment to multiple selected records
  • Should validate assignments before applying

Navigation Controls

Back to Column Mapping Button:

  • Position: Bottom left
  • Icon: Left arrow
  • Text: "Back to Column Mapping"
  • Style: Secondary button
  • Function: Returns to previous mapping step
  • Behavior: Should preserve current relationship data

Continue to Validation Button:

  • Position: Bottom right
  • Icon: Right arrow or checkmark
  • Text: "Continue to Validation"
  • Style: Primary blue button
  • State: Disabled if invalid relationships exist
  • Function: Proceeds to final validation step
  • Validation: Must ensure all required relationships are valid

Data Quality Requirements

Relationship Validation:

  • All plan assignments must reference valid system plans
  • Customer-premise relationships must be logically valid
  • Any required relationship fields must be populated
  • Foreign key constraints must be satisfied

Error Prevention:

  • Provide real-time validation feedback
  • Show available options in dropdowns
  • Highlight conflicts immediately
  • Prevent progression with unresolved errors

Bulk Operations:

  • Support multi-select for efficient data management
  • Provide bulk assignment capabilities
  • Allow bulk validation and correction
  • Enable bulk clear/reset operations

Progress Tracking

Validation Status:

  • Track percentage of valid vs invalid relationships
  • Show progress toward completion requirements
  • Update counts in real-time as corrections are made
  • Provide clear indication when ready to proceed

Error Resolution Tracking:

  • Count invalid relationships by type
  • Track resolution progress
  • Highlight priority issues requiring attention
  • Show completion criteria clearly

Consumer Template Changes

  • Remove
    • Ownership
    • Approved Date
    • Activate Date
    • Payment Received

Data Relation Tabs for Meter Data Type

Utility Service Tab

Primary Columns:

  • meter_number - Unique identifier for tracking
  • utility_service - The service being validated/assigned
  • premise - Location context for service validation
  • category - Service category for grouping
  • sub_category - More specific service classification
  • current_reading_date - Shows meter activity/status

Why these columns: Users need to see the meter identifier, current service assignment, location, and service categories to make informed utility service relationship decisions.

Meter Type Tab

Primary Columns:

  • meter_number - Unique identifier
  • meter_type - The type being validated/assigned
  • utility_service - Service context (different services may require different meter types)
  • current_reading - Indicates if meter is functional
  • installed_on - Installation date for type validation
  • premise - Location for compatibility checking

Why these columns: Meter type decisions depend on the service type, installation date, and current functionality. Location helps verify compatibility with service requirements.

Meter Make Tab

Primary Columns:

  • meter_number - Unique identifier
  • meter_make - The make being validated/assigned
  • meter_type - Make must be compatible with type
  • device_no - Device-specific identifier from manufacturer
  • installed_on - Age/vintage information
  • current_reading_date - Shows if device is operational

Why these columns: Meter make validation requires seeing the relationship between manufacturer, device number, meter type compatibility, and operational status.

2. Sample Data

Before Transformation (Source File):


customer_id,first_name,last_name,email_address,phone_number,street_address,city_name,state_code,postal_code
CUST001,John,Smith,john.smith@email.com,555-0123,123 Main St,Springfield,IL,62701
CUST002,Jane,Doe,jane.doe@email.com,555-0456,456 Oak Ave,Springfield,IL,62702
CUST003,Bob,Johnson,bob.johnson@email.com,555-0789,789 Pine Rd,Springfield,IL,62703

After Transformation (Target System Format):


Customer ID: CUST001
First Name: John  
Last Name: Smith
Email: john.smith@email.com
Phone: 555-0123
Address: 123 Main St
City: Springfield
State: IL
Zip Code: 62701

Template Configuration Sample:


Template: Customer Data Import
Source File: customer_data.csv
Mappings: 9 fields mapped
Transformations: 1 applied (Email prefix transformation)
Confidence Levels: 95% Customer ID, 95% First Name, 95% Last Name, 75% Email (with transformation)

3. Acceptance Criteria

  1. The system must support CSV file uploads up to 20MB in size
  2. The system must process uploaded files and display row count, column count, file size, and processing time
  3. The system must provide data preview showing first 5 rows of uploaded data
  4. The system must automatically suggest field mappings with confidence scores (High/Medium/Low)
  5. The system must require mapping of all mandatory fields before proceeding
  6. The system must provide transformation options: No Transformation, Split, Merge, Add Prefix, Add Suffix, Remove Substring, Uppercase, Lowercase, Title Case, Trim Whitespace, Find & Replace
  7. The system must show real-time preview of transformed data in the "Sample Data" column
  8. The system must allow saving mapping configurations as reusable templates
  9. The system must display template information including name, data type, creator, creation date, and usage count
  10. The system must track migration history with name, date, data category, status, total records, success records, and error records
  11. The system must calculate and display success rates as percentages
  12. The system must provide "View Data" action for each completed migration
  13. The system must validate data relationships and flag invalid connections in red
  14. The system must enable bulk assignment of relationships to multiple selected records
  15. The system must provide search and filter capabilities in data relation mapping
  16. The system must display overall progress percentage throughout the migration process
  17. The system must show step progression (e.g., "3 of 7" steps completed)
  18. The system must provide help links for exporting CSV files from Excel
  19. The system must display mandatory field warnings with specific field requirements
  20. The system must enable cancellation at any step with confirmation prompts
No Comments
Back to top