# 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)](https://opscom.wiki/books/setup-configuration-for-admins/page/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.