# Data Feedback for Mille Lacs Band of Ojibwe
## 1/25/2024 data re-submission
The files submitted on 1/25/2024 for FY23Q4 could not be processed. To help resolve this, we conducted a more extensive review of the re-submitted files and have included details and recommendations for each file below.
In brief:
* the records in each file do not follow the expected record layout, especially in terms of record length.
* The section 1 file is missing `T2` and/or `T3` records for a given `Item 4: Reporting Month` and `Item 6: Case Number`. See list of cases with missing records [**below**](#--The-following-T1-cases-do-not-have-corresponding-T2-adult-andor-T3-child-records-associated-with-it).
* The section 1 file is not properly sorted. For every case, the records should be sorted in ascending order by report month, case number and record type.
* the values reported for certain items are out-of-range. Please review **Transmissions_Report_405_20240131.xlsx** for more information about these types of errors.
*
### Section 1
- The expected length for the `T1`, `T2`, and `T3` records is **`122`**, but the records in the file have lengths that range between **`114`** and **`124`**. See table below :arrow_down:
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>RecordType</th>
<th>RecordLength</th>
<th>RecordCount</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>HE</td>
<td>23</td>
<td>1</td>
</tr>
<tr>
<th>1</th>
<td>T1</td>
<td>123</td>
<td>52</td>
</tr>
<tr>
<th>2</th>
<td>T1</td>
<td>124</td>
<td>3</td>
</tr>
<tr>
<th>3</th>
<td>T2</td>
<td>123</td>
<td>50</td>
</tr>
<tr>
<th>4</th>
<td>T3</td>
<td>114</td>
<td>3</td>
</tr>
<tr>
<th>5</th>
<td>T3</td>
<td>123</td>
<td>60</td>
</tr>
<tr>
<th>6</th>
<td>TR</td>
<td>23</td>
<td>1</td>
</tr>
</tbody>
</table>
</div>
- The case numbers are out of sequence. The records should be sorted in **ascending** order by month, case number and record type.
```Shell=
# correctly sorted records
HEADER20233A00405TAN1 N
T120230712345678900...
T220230712345678900...
T320230712345678900...
...
...
...
T120230745678912300...
T220230745678912300...
T320230745678912300...
# incorrectly sorted records
HEADER20233A00405TAN1 N
T120230878912345600...
T220230878912345600...
T320230878912345600...
...
...
...
T120230845678912300...
T220230845678912300...
T320230845678912300...
```
#### - The following cases have `T1` (family) records but do not have corresponding `T2` (adult) and/or `T3` (child) records:
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>CASE_NUMBER</th>
<th>RPT_MONTH_YEAR</th>
</tr>
</thead>
<tbody>
<tr>
<th>2</th>
<td>00000001221</td>
<td>202307</td>
</tr>
<tr>
<th>11</th>
<td>00000001497</td>
<td>202307</td>
</tr>
<tr>
<th>17</th>
<td>00000000426</td>
<td>202307</td>
</tr>
<tr>
<th>22</th>
<td>00000000063</td>
<td>202307</td>
</tr>
<tr>
<th>27</th>
<td>00000000880</td>
<td>202307</td>
</tr>
<tr>
<th>42</th>
<td>00000001221</td>
<td>202308</td>
</tr>
<tr>
<th>61</th>
<td>00000000426</td>
<td>202308</td>
</tr>
<tr>
<th>66</th>
<td>00000000063</td>
<td>202308</td>
</tr>
<tr>
<th>67</th>
<td>00000000079</td>
<td>202308</td>
</tr>
<tr>
<th>75</th>
<td>00000000880</td>
<td>202308</td>
</tr>
<tr>
<th>90</th>
<td>00000001221</td>
<td>202309</td>
</tr>
<tr>
<th>107</th>
<td>00000000368</td>
<td>202309</td>
</tr>
<tr>
<th>108</th>
<td>00000000426</td>
<td>202309</td>
</tr>
<tr>
<th>109</th>
<td>00000000048</td>
<td>202309</td>
</tr>
<tr>
<th>114</th>
<td>00000000079</td>
<td>202309</td>
</tr>
<tr>
<th>122</th>
<td>00000000880</td>
<td>202309</td>
</tr>
</tbody>
</table>
</div>
-
### Section 2
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>RecordType</th>
<th>RecordLength</th>
<th>RecordCount</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>HE</td>
<td>23</td>
<td>1</td>
</tr>
<tr>
<th>1</th>
<td>T4</td>
<td>71</td>
<td>55</td>
</tr>
<tr>
<th>2</th>
<td>T5</td>
<td>72</td>
<td>171</td>
</tr>
<tr>
<th>3</th>
<td>TR</td>
<td>23</td>
<td>1</td>
</tr>
</tbody>
</table>
</div>
### Section 3
<div>
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>RecordType</th>
<th>RecordLength</th>
<th>RecordCount</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>HE</td>
<td>23</td>
<td>1</td>
</tr>
<tr>
<th>1</th>
<td>T6</td>
<td>403</td>
<td>1</td>
</tr>
<tr>
<th>2</th>
<td>TR</td>
<td>23</td>
<td>1</td>
</tr>
</tbody>
</table>
</div>
## 12/20/2023 data submission
The files submitted on 12/20/2023 for FY23Q4 could not be processed. To help resolve this, we have included details and recommendations for each file below. In brief, the records in each file do not follow the expected record layout.
### Section 1
Below is a summary of the Section 1 file (`ActiveFiles (6).txt`). It includes record counts and lengths by record type.
This table indicates what is preventing this file from being processed successfully.
- the `HEADER` record --which is the first record in the file-- is too short (`RecordLength is 21`). As documented [**here**](https://www.acf.hhs.gov/sites/default/files/documents/ofa/transmission_file_header_trailer_record.pdf), the expected length of this record is 23. To help resolve this, please see below for details on how the record appears in the file vs the recommended correction, which includes the tribe code for Mille Lacs (405). Please also ensure that there aren't any trailing spaces after the update indicator (`N`).
```Shell
# Current
HEADER20233A00TAN1 N
# Correction
HEADER20233A00405TAN1 N
```
- The `TRAILER` record --which is the last record in the file--is also too short (`RecordLength is 20`). The expected length of this record is also 23 (Note: this expected length includes 9 trailing spaces after the number of records). Additionally, the trailer record is does not follow the record layout guidelines. See below and the reference above for recommendation on how to resolve:
```Shell
# Current
TRAILER168
# Correction
TRAILER0000168
```
- Finally, the expected length for the `T1`, `T2`, and `T3` records is 122, but the records in the file have lengths that range between 110 and 134.
### Section 2
Below is a summary of the Section 2 file (`ClosedFiles (3).txt`). It includes record counts and lengths by record type.
Similar to the section 1 file, this table indicates what is preventing this file from being processed successfully.
- The `HEADER` record is not the expected length (23) and does not follow the record layout guidelines.
- The `TRAILER` is not the expected length (23) and does not follow the record layout guidelines.
- The expected length of the `T4` and `T5` records is 71 but the records in the file have lengths that range between 64 and 73. See [**here**](https://www.acf.hhs.gov/sites/default/files/documents/ofa/tanf_data_report_section2.pdf) for the record layout guidelines for these types of records.
### Section 3
Below is a summary of the Section 3 file (`AggregateFiles (3).txt`). It includes record counts and lengths by record type.
Similar to the sections 1 and 2 files, this table indicates what is preventing this file from being processed successfully.
- The `HEADER` record is not the expected length (23) and does not follow the record layout guidelines.
- The `TRAILER` is not the expected length (23) and does not follow the record layout guidelines.
- The expected length of the `T6` record is 379 but the `T6` record length in the file is 404. See [**here**](https://www.acf.hhs.gov/sites/default/files/documents/ofa/tanf_data_report_section3.pdf) for the record layout guidelines for these types of records.
Sign in with Wallet
Connect another wallet