Importer Field Descriptions
ThisThe Importer Field Descriptions article provides a comprehensive reference guide to the data fields used within the OPSCOMUsing the Database Importer (Beta). Fortool. eachIts supportedprimary table,purpose you'llis findto ahelp listadministrators ofsuccessfully prepare CSV files by detailing column names, their internal system names, their requirement levellevels, (Unique ID, Recommended, Optional),and data type,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 detailedsuccessful description,data ensuringimport yourequires can prepareformatting your CSV files accuratelyto match the system's exact specifications and understanding the strict requirements for successfuldifferent data imports.fields.
Understanding
Admin Field RequirementsSide:
WhenBefore preparingformatting your CSV filesfile, forit import, it'sis essential to understand the different levels of field requirements:
-
Unique ID FieldsFields:: These fields are required and are absolutely crucial for identifying a record as a unique entity in the system before it'sit is assigned an internal ID. They represent the bare minimum information needed to import a distinct record.
-
Recommended FieldsFields:: While not technically mandatory for a record to be created, these fields containcontain information that makes the record a genuinely useful entity within the system.system Examples include(e.g., a user's first and last names or the fine value on a violation.violation). A record can exist without them, but its administrative utility will be severely limited.
-
Optional FieldsFields:: These fields are not required,required, and a record without them can still function as a useful entity.normally. They contain supplemental information that can be filled in later or isn't essential for the basic functionality of the record, such as(e.g., a vehicle's manufacturing year.year).
User Importer Columns
Side:
BelowEnd-users isdo anot detailedinteract breakdown ofwith the columnsdatabase availableimporter foror eachits supportedCSV importformatting table,requirements.
includingUsing this Feature
Administrators reference the tables below to map their external data columns to the corresponding OPS-COM internal names, requirement levels, types,names and descriptions.data types.
UserProfile Table
| Column Name |
Internal Name |
Requirement |
Type |
Description |
Column Name
|
User Unique ID (UUID)
|
UserUUID
|
Unique ID
|
Standard
|
The primary identifier. If UUID is not supplied, email will beis used as the key identifier instead.
Either This value identifies the user as a unique record and itthis or the email is required to import a record.
required. |
Email Address
|
email
|
Unique ID
|
Standard
|
The secondary identifier. IfEither UUID is not supplied, email will be used as the key identifier instead.
This value identifies the user as a unique record and itthis 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
|
The user type of the user. Attaches to the UserTypes table.
Values Themust valuesexactly supplied by the user in the uploaded file are expected to be values frommatch 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,(case, spelling, and spacingspacing). 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 recordBlank if no matchesmatch areis found.
|
Street Address
|
street
|
Optional
|
Standard
|
The street addressaddress of the user.
|
City
|
city
|
Optional
|
Standard
|
The city of the user.
|
Province/
State
|
prov
|
Optional
|
Foreign Key
|
The province or state of the user. Attaches to the Provinces table.
Values Themust valuesexactly supplied by the user in the uploaded file are expected to be values frommatch 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 andnames, not theabbreviations abbreviation;like 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.
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
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
Standard |
The preferred name of the user.
|
Public Comment
|
publicComment
|
Optional
|
Standard
|
The publicPublic comment for the user. Visible to the user.
|
Private Comment
|
privateComment
|
Optional
|
Standard
|
The privatePrivate 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 setDefaults to OPSCOM byif default.
not supplied. |
OffenseLocations Table
For additional information on this table, refer to this wiki article.
| Column Name |
Internal Name |
Requirement |
Type |
Description |
ColumnLocation Name
|
Location Name
|
LocationName
|
Unique ID
|
Standard
|
The name of the location being imported. This value identifies the location as a unique record and is requiredRequired to import a record.
|
Writer Visible
|
WriterVisible
|
Optional
|
Standard
|
Indicates Whetherwhether 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 geographicGeographic location code and is only used by certain clients.clients Associated withfor GIS maps (https://www.gismaps.org/).
integration. |
Vehicle Table
| Column Name |
Internal Name |
Requirement |
Type |
Description |
Column Name
|
Licence Plate
|
Plate
|
Unique ID
|
Standard
|
The license plate number of the vehicle. This value identifies the vehicle as a unique record and is requiredRequired to import a record.
|
Vehicle Year
|
Year
|
Optional
|
Standard
|
The Themanufacturing year of the vehicle.
|
Active Vehicle
|
active
|
Optional
|
Standard
|
Indicates whether the vehicle is activeactive. or not.
If not supplied, vehicles will be setDefaults to active byif default.
not supplied. |
Plate Type Name
|
PlateTypeID
|
Optional
|
Foreign Key
|
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 mustMust 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.
column. |
Province/
State
|
ProvID
|
Optional
|
Foreign Key
|
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 mustMust be an exact match to the values in the ProvName column,column matching(long thenames 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.
only). |
Vehicle Make Name
|
MakeID
|
Optional
|
Foreign Key
|
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 mustMust 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.
column. |
Vehicle Type Name
|
TypeID
|
Optional
|
Foreign Key
|
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 mustMust 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.
column. |
Vehicle Colour Name
|
ColourID
|
Optional
|
Foreign Key
|
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 mustMust 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.
column. |
VIN Number
|
vin
|
Optional
|
Standard
|
The Vehicle Identification Number (VIN) of the vehicle.
. |
Permits Table
| Column Name |
Internal Name |
Requirement |
Type |
Description |
Column Name
|
Permit Number
|
PermitNo
|
Unique ID
|
Standard
|
This field is the firstFirst primary identifier. Both itthis 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.
permit. |
Lot Name (Long)
|
LotNameID
|
Unique ID
|
Foreign Key
|
Second Theprimary long name of the lot associated with the permit.identifier. 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 mustMust be an exact match to the values in thelong-form 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.
column. |
Visible
|
visible
|
Optional
|
Standard
|
Indicates whether the permit is visible or not.
|
Permit Status
|
status
|
Optional
|
Standard
|
The Theadministrative status of the permit.
|
Permit State
|
StateID
|
Optional
|
Foreign Key
|
Attaches The state ofto the permit.
PermitState table. |
Violations Table
| Column Name |
Internal Name |
Requirement |
Type |
Description |
Column Name
|
Ticket Number
|
Ticket
|
Unique ID
|
Standard
|
The unique identifier for the violation.
This value identifies the violation as a unique record and is requiredRequired to import a record.
|
Licence Plate
|
VehicleID
|
Recommended
|
Foreign Key
|
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 mustMust 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.
column. |
Ticket Type Name
|
TicketType
|
Recommended
|
Foreign Key
|
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 mustMust 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.
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
Standard |
The adminadministrator who wrote the ticket.
|
Violation Location Name
|
LocationID
|
Recommended
|
Foreign Key
|
The name of the location where the violation occurred. Attaches to the OffenceLocations table.
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 mustMust 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.
column. |
BestBest Practices &and Considerations
-
ExactEnsure Matchesexact matches for Foreign KeysKeys:: 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 OPSCOMOPS-COM table (e.g., TypeName, ProvName, MakeName, LocationName).table. Any mismatch will result in a blank or Null value in the imported record.
-
Prioritize core fields: Ensure all Unique ID and Recommended Fields: While optional fields offer additional detail, ensure all Unique ID fields are present and accurate, and Recommended fields are populatedheavily populated for maximum utility of the imported records.
-
Pre-Populatepopulate Referencereference Tablestables:: Before importing data that relies on foreign keys (e.g., Vehicles relying on Plate Types or Makes),keys, ensure the corresponding reference tables (VehiclePlateType, VehicleMake, etc.) are already populated in OPSCOMOPS-COM with all the necessary values. This prevents immediate Null values in your imported data.
-
DataMaintain Consistencydata consistency:: Maintain consistent formatting for dates, phone numbers, and other standard fields within your CSV to avoid syntax-related import errors.
-
Test with Smallsmall Batchesbatches:: 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.
