Data validation rules

The following table details the data validation rules for Fleet Planner:

Validation rule	Description
.csv file names	The file names must conform to the defined data templates: aircraft_usage.csv fleet_details.csv forecast_model.csv last_maintenance_done.csv task_applicablility.csv task_definition.csv location_capability.csv check_hierarchy_rules.csv automerge_rules.csv extraction_rules.csv extraction_rule_allocation.csv
UTF8 encoding	The file must be a legitimate .csv file.
Data type check	The cell properties within the .csv file must match against what is present in the database. For example, the date type must use the following format: "YYYY-MM-DD"
Data range check	Data ranges must be valid. For example, the Min Yield range must be between 0.00-1.00 and Span must be >0 in task_definition.csv.
Row null check	All required fields must contain data. The aircraft_usage.csv file must contain data in the following fields: Aircraft Usage date Current hours Current cycles The fleet_details.csv file must contain data in the following fields: Fleet Aircraft Manufactured date In service date
Row null check (continued)	The forecast_model.csv file must contain data in the following fields: Aircraft Average daily hours Average daily cycles The last_maintenance_done.csv file must contain data in the following fields: Aircraft Task Id Completion Date Hours Cycles Due Date (only required for Fixed Interval Scheduling)
Row null check (continued)	The task_applicablility.csv file must contain data in the following fields: Applicability Id Task Id Fleet, Subfleet, OR Aircraft The task_definition.csv file must contain data in the following fields: Task Id Name Schedule Calculation from Schedule by EOM
Row null check (continued)	The location_capability.csv file must contain data in the following fields: Track Id Hangar Name Track Name Task Type Sunday Monday Tuesday Wednesday Thursday Friday Saturday Leading Ferry Days Trailing Ferry Days
Row null check (continued)	The check_hierarchy_rules.csv file must contain data in the following fields: Name Primary Task Definitions Inclusive Task Definitions The automerge_rules.csv file must contain data in the following fields: Source Source Type Destination Destination Type Ignore Location Capabilities Priority Move Destination Visit Visit Span
Row null check (continued)	The extraction_rules.csv file must contain data in the following fields: Rule Name Start Date End Date at least 1 entry in either of the following: Fleets, Subfleets Min Allocation Max Allocation
Row null check (continued)	The extraction_rule_allocation.csv file must contain data in the following fields: Extraction Rule ID Name at least 1 entry in either of the following: Task Types, Tracks Minimum Maximum
Data integrity check	The Max Allocation of each extraction rule in extraction_rules.csv must correspond to the sum of all corresponding allocations' Maximum value in extraction_rule_allocation.csv. The Min Allocation of each extraction rule in extraction_rules.csv must correspond to the sum of all corresponding allocations' Minimum value in extraction_rule_allocation.csv.
Unique primary key	The primary key must be unique (no duplicates) for each .csv file. The following list details the primary key for each file: aircraft_usage.csv: aircraft fleet_details.csv: aircraft forecast_model.csv: aircraft last_maintenance_done.csv: aircraft, task_id task_applicablility.csv: id task_definition.csv: task id location_capability.csv: track_identifier check_hierarchy_rules.csv: primary task, inclusive task automerge_rules.csv: source, source type, destination, destination type extraction_rules.csv : Rule ID extraction_rule_allocation.csv : Allocation ID
The Task ID in task_applicablility.csv, last_maintenance_done.csv, etc. must exist in task_definition.csv.	Any text ID provided in a field that references an object must exist. For example, a chained task ID must point to a valid task ID.
Aircraft in aircraft_usage.csv and forecast_model.csv must exist in fleet_details.csv.	In order for an aircraft to have a schedule generated for it, the unique tail number must be present in the forecast_model.csv and fleet_details.csv files. Forecast model is required for all aircraft.
Subfleet must be unique across all fleets in the fleet_details.csv.
Fleet and Subfleet in the location_capability.csv file must exist in the fleet_details.csv file.
Task Definition: Terminating days, terminating usage hours, and terminating usage cycles	Terminating days, terminating usage hours, or terminating usage cycles must be 0 or a positive number.
Automerge: "Source" and "Destination" must exist in the corresponding Task Type or Task ID columns in the Task Definition table.	This validation will check to see if the value in the "Source" and "Destination" columns exist in the Task Definition table in the corresponding TASK_ID or TASK_TYPE columns. Note: This validation will validate the combination of the "Source" and "Source Type" fields. The system will allow the Source or Destination values to be uploaded when: the Automerge file has a Source of "Mod" with a Source Type of "TASK_ID" and the uploaded automerge_rules.csv file has "Mod only exists in "Task_ID" within the Task Definition table. The system will NOT allow the Source or Destination values to be uploaded when: the Automerge file has a Source of "Mod" with a Source Type "TASK_ID" and the uploaded automerge_files.csv has "Mod only exists in the "Task_Type" columns within the Task Definitions table.
Check Hierarchy	The referenced Task IDs must exist in the task definition. Both columns are required.
Last done and aircraft usage cannot contain future dates	The system will NOT allow upload of the aircraft_usage.csv or the last_maintenance_done.csv file(s) if they contain future dates.
Extraction Rules: Max Allocation	Maximum allocation must be greater than or equal to the minimum allocation.
Extraction Rules: Fleets and Subfleets are optional	At least one fleet or subfleet must be selected for the rule. When both fleets and subfleets are selected, a union is done on the set.