ADOT database changes and the new 2017 ADOT crash form

ADOT database changes and the new 2017 ADOT crash form

Issue

With the redesign of the ADOT crash form some fields were preserved, and others were removed or consolidated. During this process, new lookup values were added to ADOT’s database to standardize and accommodate for the changes to the form. ADOT will not be converting old data to match with the new format, some fields and or values could be blank. ADOT’s internal database lookup values DO NOT MATCH with the current crash form.

Explanation

Since ADOT will not be converting old data, they need to keep all of the lookup values, new and old, in their database and in the lookup tables.  In order to accommodate this, the internal lookups will not match up with the lookup numbers on the ADOT crash forms. Therefore, Crash Magic will need to use the internal database lookups given by ADOT.

While making the additions to the Crash Magic lookups, we encountered some values that matched, or closely matched previous values in the lookups.  One example of this is the Angle values for the Manner of Collision field.  There are now several ones available:
2. Angle (front to side) (other than left turn)
102. Angle (front to side) Same Direction
103. Angle (front to side) Opposite Direction
104. Angle (front to side) Right Angle
105. Angle – Direction not specified

In these cases, we tried to match up the beginning of the text values so that they would be sorted alphabetically when viewing them during the setup of a filter or report.  We also included the number value at the end of the text in order differentiate them.  That way when a filter is created, you will see all the values that could be a possibility, and are able to include appropriate ones in the filter.  This will be important if you are filtering on data that could cover several years where multiple values are used in the database (and you’ll want to include all of them).

In this example, we created an _Angle calculated field that can be used to make it easier to grab all of the Angle crash values.  Let us know if you need help using this.

 

IMPORTANT – Some existing filters and reports in Crash Magic will not work with the new changes to the lookups.  You may have to re-select fields / values to update the filter or report.  If you have questions regarding this, please let us know.

Solution
If you would like to see details of the changes, as well as some examples of values that are duplicated, please visit our document at:
The first column of this file is values from the old ADOT crash form, second column is notes about changes, third column is the new ADOT 2017 form, and last column is the internal state lookup values.
If you have any questions about the ADOT database, please contact John Riemer at JRiemer@azdot.gov.
Crash Magic has been updated to match the changes with ADOT’s database at this time.  If you have any questions regarding Crash Magic, please contact us at help@pdmagic.com.
Was this article helpful?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
How Can We Improve This Article?