Skip to main content

Importer Field Descriptions

The Importer Field Descriptions article provides a comprehensive reference guide to the data fields used within the Using the Database Importer (Beta) tool. Its primary purpose is to help administrators successfully prepare CSV files by detailing column names, internal system names, requirement levels, and data types for each supported table. This article is intended for OPS-COM administrators responsible for large-scale data migrations or updates.

Setup and Configuration

Preparing for a successful data import requires formatting your CSV files to match the system's exact specifications and understanding the strict requirements for different data fields.

Admin Side: Before formatting your CSV file, it is essential to understand the different levels of field requirements:

  • Unique ID Fields: These fields are required and are absolutely crucial for identifying a record as a unique entity in the system before it is assigned an internal ID. They represent the bare minimum information needed to import a distinct record.

  • Recommended Fields: While not technically mandatory for a record to be created, these fields contain information that makes the record a genuinely useful entity within the system (e.g., a user's first and last names or the fine value on a violation). A record can exist without them, but its administrative utility will be severely limited.

  • Optional Fields: These fields are not required, and a record without them can still function normally. They contain supplemental information that can be filled in later or isn't essential for basic functionality (e.g., a vehicle's manufacturing year).

User Side: End-users do not interact with the database importer or its CSV formatting requirements.

Using this Feature

Administrators reference the tables below to map their external data columns to the corresponding OPS-COM internal names and data types.

UserProfile Table

Column Name Internal Name Requirement Type Description
User Unique ID (UUID) UserUUID Unique ID Standard The primary identifier. If UUID is not supplied, email is used as the key identifier instead. Either this or the email is required.
Email Address email Unique ID Standard The secondary identifier. Either this or the UUID is required to import a record.
Username username Recommended Standard The username of the user.
First Name firstName Recommended Standard The first name of the user.
Middle Name middleName Optional Standard The middle name of the user.
Last Name lastName Recommended Standard The last name of the user.
Password password Optional Standard The password of the user.
User Type UserTypeID Recommended Foreign Key Attaches to the UserTypes table. Values must exactly match the TypeName column (case, spelling, and spacing). Blank if no match is found.
Street Address street Optional Standard The street address of the user.
City city Optional Standard The city of the user.
Province/State prov Optional Foreign Key Attaches to the Provinces table. Values must exactly match the ProvName column (long names, not abbreviations like ON).
Postal Code postal Optional Standard The postal or ZIP code of the user.
Cellphone Number phonecell Optional Standard The cellphone number of the user.
Employee Number employNo Optional Standard The employee number of the user.
Student Number studentNo Optional Standard The student number of the user.
Secondary Phone sPhone Optional Standard The secondary phone number of the user.
Date of Birth DOB Optional Standard The date of birth of the user.
Preferred Name preferredname Optional Standard The preferred name of the user.
Public Comment publicComment Optional Standard Public comment for the user. Visible to the user.
Private Comment privateComment Optional Standard Private comment for the user. Not visible to the user.
Login Source loginSource Optional Standard The method by which the user logs into the system. Defaults to OPSCOM if not supplied.

OffenseLocations Table

Column Name Internal Name Requirement Type Description
Location Name LocationName Unique ID Standard The name of the location being imported. Required to import a record.
Writer Visible WriterVisible Optional Standard Indicates whether the writer of a violation ticket made in the location is visible to the user.
GIS Number GisNo Optional Standard Geographic location code used by certain clients for GIS maps integration.

Vehicle Table

Column Name Internal Name Requirement Type Description
Licence Plate Plate Unique ID Standard The license plate number of the vehicle. Required to import a record.
Vehicle Year Year Optional Standard The manufacturing year of the vehicle.
Active Vehicle active Optional Standard Indicates whether the vehicle is active. Defaults to active if not supplied.
Plate Type Name PlateTypeID Optional Foreign Key Attaches to the VehiclePlateType table. Must be an exact match to the TypeName column.
Province/State ProvID Optional Foreign Key Attaches to the Provinces table. Must be an exact match to the ProvName column (long names only).
Vehicle Make Name MakeID Optional Foreign Key Attaches to the VehicleMake table. Must be an exact match to the MakeName column.
Vehicle Type Name TypeID Optional Foreign Key Attaches to the VehicleType table. Must be an exact match to the TypeName column.
Vehicle Colour Name ColourID Optional Foreign Key Attaches to the VehicleColours table. Must be an exact match to the ColourName column.
VIN Number vin Optional Standard The Vehicle Identification Number (VIN).

Permits Table

Column Name Internal Name Requirement Type Description
Permit Number PermitNo Unique ID Standard First primary identifier. Both this and LotNameID must be provided to create a new permit.
Lot Name (Long) LotNameID Unique ID Foreign Key Second primary identifier. Attaches to the LotNames table. Must be an exact match to the long-form LotName column.
Visible visible Optional Standard Indicates whether the permit is visible or not.
Permit Status status Optional Standard The administrative status of the permit.
Permit State StateID Optional Foreign Key Attaches to the PermitState table.

Violations Table

Column Name Internal Name Requirement Type Description
Ticket Number Ticket Unique ID Standard The unique identifier for the violation. Required to import a record.
Licence Plate VehicleID Recommended Foreign Key Attaches to the Vehicle table. Must be an exact match to the Plate column.
Ticket Type Name TicketType Recommended Foreign Key Attaches to the TicketCategory table. Must be an exact match to the TicketTypeName column.
Fine Amount Fine Recommended Standard The amount of the fine associated with the ticket.
Towing Amount Towing Optional Standard The amount of towing charges associated with the ticket.
Tax Amount taxAmount Optional Standard The amount of tax associated with the ticket.
Issued Date Issued Recommended Standard The date the ticket was issued.
Due Date Due Recommended Standard The due date for payment of the ticket.
Ticket Writer Writer Optional Standard The administrator who wrote the ticket.
Violation Location Name LocationID Recommended Foreign Key Attaches to the OffenceLocations table. Must be an exact match to the LocationName column.

Best Practices and Considerations

  • Ensure exact matches for Foreign Keys: For all Foreign Key type columns, the values in your CSV file must be an exact match (including capitalization, spelling, and spacing) to the corresponding Name column in the referenced OPS-COM table. Any mismatch will result in a blank or Null value in the imported record.

  • Prioritize core fields: Ensure all Unique ID fields are present and accurate, and Recommended fields are heavily populated for maximum utility of the imported records.

  • Pre-populate reference tables: Before importing data that relies on foreign keys, ensure the corresponding reference tables are already populated in OPS-COM with all necessary values. This prevents immediate Null values in your imported data.

  • Maintain data consistency: Maintain consistent formatting for dates, phone numbers, and other standard fields within your CSV to avoid syntax-related import errors.

  • Test with small batches: For large imports, always consider testing with a small batch of records first to verify that your column matching and data formatting are correct before importing the entire historical dataset.

Back to top