Importer Field Descriptions

This article provides a comprehensive guide to the fields used within the OPS-COM Database Importer. For each supported table, you'll find a list of column names, their internal system names, their requirement level (Unique ID, Recommended, Optional), data type, and a detailed description, ensuring you can prepare your CSV files accurately for successful data imports.

Understanding Field Requirements

When preparing your CSV files for import, it's essential to understand the different levels of field requirements:

Unique ID Fields: These fields are required and are crucial for identifying a record as a unique entity in the system before it's 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. Examples include a user's first and last names or the fine value on a violation. A record can exist without them, but its utility will be limited.
Optional Fields: These fields are not required, and a record without them can still function as a useful entity. They contain supplemental information that can be filled in later or isn't essential for the basic functionality of the record, such as a vehicle's manufacturing year.

Importer Columns

Below is a detailed breakdown of the columns available for each supported import table, including their internal names, requirement levels, types, and descriptions.

UserProfile Table

Column Name	Internal Name	Requirement	Type	Description
User Unique ID (UUID)	UserUUID	Unique ID This or Email is Required	Standard	The primary identifier. If UUID is not supplied, email will be used as the key identifier instead. This value identifies the user as a unique record and it or the email is required to import a record.
Email Address	email	Unique ID This or UUID is Required	Standard	The secondary identifier. If UUID is not supplied, email will be used as the key identifier instead. This value identifies the user as a unique record and it 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 UserTypes Table	The user type of the user. Attaches to the UserTypes table. The values supplied by the user in the uploaded file are expected to be values from the TypeName column of the UserTypes table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the TypeName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The UserTypeID from the UserTypes table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are 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 Provinces Table	The province or state of the user. Attaches to the Provinces table. The values supplied by the user in the uploaded file are expected to be values from the ProvName column of the Provinces table, as these are the values that will be matched against. These are the long names of the province and not the abbreviation; as in, Ontario and not ON. The values in the uploaded file must be an exact match to the values in the ProvName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The ProvID from the Provinces table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
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 Number	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	The public comment for the user. Visible to the user.
Private Comment	privateComment	Optional	Standard	The 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. If a login source is not supplied for a user, it will be set to OPS-COM by default.

OffenseLocations Table

For additional information on this table, refer to this wiki article.

Column Name	Internal Name	Requirement	Type	Description
Location Name	LocationName	Unique ID Required	Standard	The name of the location being imported. This value identifies the location as a unique record and is required to import a record.
Writer Visible	WriterVisible	Optional	Standard	Whether the writer of a violation of a ticket made in the location is visible to the user.
GIS Number	GisNo	Optional	Standard	The GIS number is a geographic location code and is only used by certain clients. Associated with GIS maps (https://www.gismaps.org/).

Column Name

Internal Name

Requirement

Type

Description

Location Name

LocationName

Unique ID

Required

Standard

The name of the location being imported. This value identifies the location as a unique record and is required to import a record.

Writer Visible

WriterVisible

Optional

Standard

Whether the writer of a violation of a ticket made in the location is visible to the user.

GIS Number

GisNo

Optional

Standard

The GIS number is a geographic location code and is only used by certain clients. Associated with GIS maps (https://www.gismaps.org/).

Vehicle Table

Column Name	Internal Name	Requirement	Type	Description
Licence Plate	Plate	Unique ID Required	Standard	The license plate number of the vehicle. This value identifies the vehicle as a unique record and is required to import a record.
Vehicle Year	Year	Optional	Standard	The year of the vehicle.
Active Vehicle	active	Optional	Standard	Indicates whether the vehicle is active or not. If not supplied, vehicles will be set to active by default.
Plate Type Name	PlateTypeID	Optional	Foreign Key VehiclePlateType Table	The plate type of the vehicle. Attaches to the VehiclePlateType table. The values supplied by the user in the uploaded file are expected to be values from the TypeName column of the VehiclePlateType table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the TypeName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The TypeID from the VehiclePlateType table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Province/ State	ProvID	Optional	Foreign Key Provinces Table	The province or state of the user. Attaches to the Provinces table. The values supplied by the user in the uploaded file are expected to be values from the ProvName column of the Provinces table, as these are the values that will be matched against. These are the long names of the province and not the abbreviation; as in, Ontario and not ON. The values in the uploaded file must be an exact match to the values in the ProvName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The ProvID from the Provinces table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Vehicle Make Name	MakeID	Optional	Foreign Key VehicleMake Table	The make of the vehicle. Attaches to the VehicleMake table. The values supplied by the user in the uploaded file are expected to be values from the MakeName column of the VehicleMake table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the MakeName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The MakeID from the VehicleMake table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Vehicle Type Name	TypeID	Optional	Foreign Key VehicleType Table	The type of the vehicle. Attaches to the VehicleType table. The values supplied by the user in the uploaded file are expected to be values from the TypeName column of the VehicleType table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the TypeName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The TypeID from the VehicleType table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Vehicle Colour Name	ColourID	Optional	Foreign Key VehicleColours Table	The colour of the vehicle. Attaches to the VehicleColours table. The values supplied by the user in the uploaded file are expected to be values from the ColourName column of the VehicleColours table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the ColourName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The ColourID from the VehicleColours table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
VIN Number	vin	Optional	Standard	The Vehicle Identification Number (VIN) of the vehicle.

Permits Table

Column Name	Internal Name	Requirement	Type	Description
Permit Number	PermitNo	Unique ID Required	Standard	This field is the first primary identifier. Both it and LotNameID must be provided to create a new permit This value identifies the permit as a unique record and is required to import a record.
Lot Name (Long)	LotNameID	Unique ID Required	Foreign Key LotNames Table	The long name of the lot associated with the permit. Attaches to the LotNames table. This field is the second primary identifier. Both it and PermitNo must be provided to create a new permit. This value identifies the permit as a unique record and is required to import a record. The values supplied by the user in the uploaded file are expected to be values from the LotName column of the LotNames table, as these are the values that will be matched against. These are the long form version of the lot name, and not the lot short name. The values in the uploaded file must be an exact match to the values in the LotName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The LotNameID from the LotNames table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Visible	visible	Optional	Standard	Indicates whether the permit is visible or not.
Permit Status	status	Optional	Standard	The status of the permit.
Permit State	StateID	Optional	Foreign Key PermitState Table	The state of the permit.

Violations Table

Column Name	Internal Name	Requirement	Type	Description
Ticket Number	Ticket	Unique ID Required	Standard	The unique identifier for the violation. This value identifies the violation as a unique record and is required to import a record.
Licence Plate	VehicleID	Recommended	Foreign Key Vehicle Table	The license plate number of the associated vehicle. Attaches to the Vehicle table. The values supplied by the user in the uploaded file are expected to be values from the Plate column of the Vehicle table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the Plate column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The VehicleID from the Vehicle table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Ticket Type Name	TicketType	Recommended	Foreign Key TicketCategory Table	The type of the ticket. Attaches to the TicketCategory table. The values supplied by the user in the uploaded file are expected to be values from the TicketTypeName column of the TicketCategory table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the TicketTypeName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The TicketTypeID from the TicketCategory table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.
Fine Amount	Fine	Recommended	Standard	The amount of 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 admin who wrote the ticket.
Violation Location Name	LocationID	Recommended	Foreign Key OffenceLocations Table	The name of the location where the violation occurred. Attaches to the OffenceLocations table. The values supplied by the user in the uploaded file are expected to be values from the LocationName column of the OffenceLocations table, as these are the values that will be matched against. The values in the uploaded file must be an exact match to the values in the LocationName column, matching the capitalization, spelling, and spacing exactly, or else it won’t be counted as a match. The LocationID from the OffenceLocations table matching the name will inserted into the record in the base table. The value will be left blank in the imported record if no matches are found.

Best Practices & Considerations

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 (e.g., TypeName, ProvName, MakeName, LocationName). Any mismatch will result in a blank or Null value in the imported record.
Prioritize Unique ID and Recommended Fields: While optional fields offer additional detail, ensure all Unique ID fields are present and accurate, and Recommended fields are populated for maximum utility of the imported records.
Pre-Populate Reference Tables: Before importing data that relies on foreign keys (e.g., Vehicles relying on Plate Types or Makes), ensure the corresponding reference tables (VehiclePlateType, VehicleMake, etc.) are already populated in OPS-COM with all the necessary values. This prevents Null values in your imported data.
Data Consistency: Maintain consistent formatting for dates, phone numbers, and other standard fields within your CSV to avoid import errors.
Test with Small Batches: For large imports, consider testing with a small batch of records first to verify that your column matching and data formatting are correct before importing the entire dataset.

Resolve Duplicate Options

History Search

Purge Old - Explained

Purging Incidents

Handheld Devices and Commons

Managing Recurring and Onetime System Tasks

Using the Database Importer (Beta)

Importer Field Descriptions

Create or Refresh a Preview Space

Data Maintenance Articles

Manage Roles and Permissions

Manage Administrator Groups

Manage Admin User Accounts

IP Filtering for Admin Users

Guide to System Settings

Configuring SAML SSO with OPS-COM

Troubleshooting - Email Server Communication Errors

Alarms System Settings

Defining User Profile Settings

Account Creation Preferences

Configuring Multi-Factor Authentication on the User Portal

Password and Security Settings

Uploading and Managing Files

Dashboard Layouts and Custom Items

Email Headers and Footers

Email Templates

Translations

User Portal Branding and Setup

Pages and Content Blocks

Templates and Design

Form 11 Shortcodes

User Information Shortcodes

User Types

Limiting Active Vehicles

Manage User Departments

Locker User Types

Login Sources (SSO)

Vehicle Plate Types

Vehicle Colours / Colors

Vehicle Makes

Vehicle Types

Lot Groups Management

Managing Lot Zones

Common Lots

Permit States

Locations

Locations - Sub Locations

Ticket Categories

Ticket Offence Items

Sort Violations for Quick Access

Handhelds Devices Settings (Handheld Commons)

Managing Incident Categories and Sub-Categories

Managing Incident Flags

Managing Ethnic Types

Managing Incident Relations

Extended User Profile Options

Missing Property Types

Email Alert List

Manage Email Warnings

Manage Deposit Types

Managing Payment Types

Managing Convenience Fees

Taxes

The Invoice Template

Managing Dispatch Categories

Managing Dispatch Sources

Users - Distribution Admin Options

Alerts - Distribution

Departments

Search for a User

Actions - Distribution

Status - Distribution

Warnings - Distribution

Importer Field Descriptions

Understanding Field Requirements

Importer Columns

UserProfile Table

OffenseLocations Table

Vehicle Table

Permits Table