This lesson provides an overview on importing your student program data into DnA.
About Program Data
In DnA, programs are student based. Programs are very open ended and are managed through a code table: Student Programs. There are a number of programs pre-loaded into every DnA installation for things like lunch programs, instructional programs, and attendance programs.
About Importing Programs
The programs.txt file is how students are placed into the various programs in which they participate. For Data and Assessment (DnA) the programs file will create and maintain all of your student program participation records. For Special Education (ISE) the programs file is usually uploaded during data implementation and then student program participation is managed through the tools in the user interface. There are, however, exceptions to this for student program participation that is managed in external systems such as lunch programs.
The unique key on a program record is: Import Student ID, Program ID, Program Start Date, and Site ID. If Site ID is not provided, then the value of NULL for Site ID becomes a part of the unique key. These four fields together are what constitute a unique program record. Any subsequent changes to the other fields will update the original record. For example, if a student is placed in a program with a specific start date, then a record with the same Import Student ID, Program ID, and Program Start Date is imported later with a Program Exit Date, the student will be exited from the program.
NOTE: If your file contains records for students in which any of those four fields (Import Student ID, Program ID, Program Start Date, and Site ID) do not match, importing a file with those records will not update records in DnA. Instead, duplicate records will be created.
Required Fields
- Import Student ID
- Last Name
- First Name
- Birth Date
- Program ID
- Program Start Date
- Academic Year
Import Student ID is the key identifier for this dataset. The Import Student ID should be a unique value for each student. It is not required that the value is the Student Information System (SIS) ID. It is required that for the entire installation the value is guaranteed to be unique. Traditionally, in a single district instance, the Import Student ID is the SIS generated student ID. In installations that span multiple districts, such as a consortium or a county, the Import Student ID is usually the State Student ID because this value is expected to be unique across multiple SIS.
Student Last Name, Student First Name, and Birth Date are actually soft requirements within this dataset. It is expected that every student should have these additional three basic data elements available in this file. In the programs.txt file, these fields are only used to assist in troubleshooting data. Ensuring that the data is available in the file allows for more efficient troubleshooting when reviewing import logs. This data is required to be present in the import file, however, the fields are not imported. These fields are updated only through the studemo.txt import.
Program ID should reference a program ID from the Student Programs code table. It is required that the Program ID that is provided in the programs.txt file matches a Code Key from the code table. If a code is provided in the programs.txt file that does not exist in the code table, then the record will be rejected.
Program Start Date identifies the date that the student first entered the program. The Program Start Date must align to other key dates in DnA such as term dates and student enrollment dates.
Academic Year is currently required to identify the academic year to which the student data belongs. Generally, after historic data loads and for regular automation, the Academic Year field simply identifies the current academic year. The data, as stated in the spec, should be represented as a hyphenated two year range such as: 2016-2017.
If any of the required fields are not provided or if any of the required fields do not align to the designated field types or values, the entire record will not import. Problems with required fields will generate import errors.
Whether or not a field is required refers to basic, minimal operation of the system. With only the required fields your DnA installation will work, however, it may not be configured optimally. Many non-required fields are used in basic reports and compliance reporting.
Non-Required but Highly Recommended Fields
- State Student ID
- Eligibility Start Date
- Eligibility End Date
- Program End Date
State Student ID is the students unique identifier for state data reporting. It is used extensively as an alternate identifier for students. It is strongly recommended that this information is provided. In this import this ID is used solely for troubleshooting and student identifying information. It is not updated or imported from this file. It is only updated or importing via the studemo.txt import.
Eligibility Start Date and Eligibility End Date are both used to identify when a student was first identified as being eligible for a program. It does not necessarily need to match the dates of when a student actually began participation in the program.
Program End Date is not a required field, however, without it students will never exit a program. It is a very important field to help to establish students time in programs. The Program End Date should represent the date a students participation in the program ended.
Non-Required Fields
- Middle Name
- Eligibility Code
- Program Exit Code
- Site ID
Eligibility Code is an optional field that can be leveraged to identify how a student was identified to be eligible for participation in a program. The values provided here must match the Code Keys within the Program Eligibility Codes code table.
Program Exit Code is essentially the opposite of the Eligibility Code.The exit code is used to identify why a student may have exited the program. Like Eligibility Code, the Program Exit Code must align to the values in a code table. Reference the Program Exit Codes code table for this.
Site ID is currently not a requirement in this dataset, however, this is subject to change over time. The Site ID field has been added to the programs.txt import file in order to help identify which site a student is at when they are receiving particular program services. This site/program association has been identified as a requirement in the state of California for state reporting. When the Site ID is provided, it is also part of the unique key on a program record.
Common Issues
This is a frequent occurrence when data is initially loaded. It is very easily resolved by visiting Code Management in your DnA installation and making sure that the programs that you are providing in your programs.txt file are present in the code table. Existing codes can be edited or new codes can be added.
In some cases, systems are able to generate or only track an Eligibility Start Date. In DnA the Program Start Date is required. The Eligibility Start Date can be used in place of the Program Start Date in these cases. Or a default date can be used as the Program Start Date by adding an import configuration transformation to the Program Start Date field.
Some program data may not be managed all in the same place. In many instances, schools or districts have an external meal program to manage their lunch program data. In situations such as this - for DnA, ISI and ISE - a separate import automation profile can be deployed to import a secondary (or more) program file on a nightly basis. The only requirement here is that the file that is being imported is still named programs.txt. To accommodate this, new directories can be setup on the SFTP location to house additional programs.txt files for automated import.
Next Steps
To learn more about available files to import into DnA, visit Optional Datasets.