Lunch Tracking Import

Modified on Thu, Jun 29, 2023 at 10:03 AM

Lunch Tracking Import

How it works

There is a task in Genesis that runs a schedule determined by the user. It will import a file from the server's local hard disk into the LunchTracking (LUNCH_TRACKING) table within Genesis.

Default Layout

The file must be a CSV (Comma Separated Values) formatted text file.
The first row DOES NOT contain headers.
All values should be surrounded in quotes.

Column	Description	Sample Value
School Year	School Year of the record to update	"2010-11"
Student ID	Student id	"00123456"
Start Date	Start Date of the record as dd/mm/yyyy	"22/10/2010" - CANNOT BE NULL
End Date	Exit Date of the record as mm/dd/yyyy. An empty exit date is valid; and should be used instead of an actual date (such as end of school year)	"" "05/22/2010"
Lunch Code	A lunch code to assign to this record. This code must exist in the Student Data.Lunch.Setup.Codes screen.	FT

PaySchools Layout

Column	Description	Sample Value
School Year	School Year of the record to update	"2010-11"
Student ID	Student id	"00123456"
Start Date	Start Date of the record as mm/dd/yyyy	"09/01/2010" - CANNOT BE NULL
End Date	Exit Date of the record as mm/dd/yyyy. An empty exit date is valid; and should be used instead of an actual date (such as end of school year)	"" "05/22/2010"
Lunch Code	A lunch code to assign to this record. This code must exist in the Student Data.Lunch.Setup.Codes screen.	FT

Commalex layout

The file must be a CSV (Comma Separated Values) formatted text file.
The first row DOES NOT contain headers.
All values should be surrounded in quotes.

Column	Description	Sample Value
A	Student id	"00123456"
B	Student Name (Ignored by the import process)	Chris Zinn
C	A lunch code to assign to this record. This code must exist in the Student Data.Lunch.Setup.Codes screen.	FT
D	School code of the student (Ignored by the import process)
E	Any value (Ignored by the import process)
F	Start Date of the record as mm/dd/yyyy	"10/22/2010"

Alternate Horizon layout

Note: Start date is hardcoded to be September 1st of the current school year.

Column	Sample Value
Student ID	"00123456"
Eligibility	"FC"
Student Case	"SNAP"
Certified	"Y"

How Lunch Codes are determined in layout...

For Eligibility F or TF

If STUDENT CASE = “DC” and CERTIFIED= “Y” then Lunch Code = DC
If STUDENT CASE = “FOSTER” then Lunch Code = FE

For Eligibility of R or TR,

Lunch Code = R

For Eligibility of N

Lunch Code = N

For Eligibility of D

Lunch Code = D

For Eligibility of FC

If STUDENT CASE = ”SNAP” and CERTIFIED=”Y”, Lunch Code = DC
If STUDENT CASE = ”SNAP” and CERTIFIED=”N”, Lunch Code = FT
If STUDENT CASE = “TANF” and CERTIFIED = ”Y” Lunch Code = DCT
If STUDENT CASE = “TANF” and CERTIFIED = ”N” Lunch Code = FT
If STUDENT CASE = “FOSTER” and CERTIFIED = ”Y” Lunch Code = DCF
If STUDENT CASE = “FOSTER” and CERTIFIED = ”N” Lunch Code = FT

Alt CCP Layout

Start Date of records default to today or 9/1 if prior to start of school

Column	Description	Sample Value
1	Student id	"00123456"
4	Lunch Type	D R or F
7	Lunch Pin	123
10	Lunch Balance	10.25

If Lunch Type =D lunch code becomes D

If Lunch Type = R lunch code becomes R

If Lunch Type = F lunch code becomes FI

Alt Nutrikids Layout

If student has user text field of APP_ID, the Application number on import will be recorded there.

Column	Description	Sample Value
1	Student id	"00123456"
4	Level	P F or R
5	Start Date	10/15/2018
7	Application ID	12345
9	Type	SNAP
11	Lunch Balance	10.22

If Application ID = -4 and Type = SNAP then Lunch Code becomes DC

If Application ID = -4 and Type = TANF then Lunch Code becomes DCT

If Application ID = -4 and Type = FOSTER then Lunch Code becomes DCF

If Application ID does not equal -4 and Level = P then Lunch Code becomes D

If Application ID does not equal -4 and Type = INCOME or TEMP and Level = F then Lunch Code becomes F

If Application ID does not equal -4 and Type = INCOME or TEMP and Level = R then Lunch Code becomes R

If Application ID does not equal -4 and Type = FOSTER or TANF or SNAP then Lunch Code becomes R

If Application ID does not equal -4 and Type = HOMELESS or MIGRANT or RUNAWAY then Lunch Code becomes R

If a record does not have a Type the Lunch Code is set to Denied

LunchTime

File is tab-delimited or Comma if option "Student id, Lunch Balance (Lunch Balance Only)" selected.

Column	Description
1	Student Id
2	Lunch Balance

Column	Description
1	Student Id
2	Any Value
3	Lunch Balance

LunchTime 2

File is tab-delimited.

Column	Description	Sample Values
1	Student Id
2	Last Name
3	Lunch Code - This code must exist in the Student Data.Lunch.Setup.Codes screen
4	School Code
5	Start Date	1/1/1900 12/13/2016

Meals Plus

File is tab-delimited.

Column	Description	Sample Values
1	Student Id
2	First Name
3	Last Name
4	Lunch Type	2, 3, or 4
5	Start Date	yyyyMMdd

2 = FI

3 = R

4 = Student Pays for Lunch

PCS 1 Format

Column	Description	Sample Value
School Year	School Year of the record to update	"2010-11"
Student ID	Student id	"00123456"
Start Date	Start Date of the record as dd/mm/yyyy	"22/10/2010" - CANNOT BE NULL
End Date	Exit Date of the record as mm/dd/yyyy. An empty exit date is valid; and should be used instead of an actual date (such as end of school year)	"" "05/22/2010"
Lunch Code	PCS Codes will be translated to Genesis Codes: DI -> I F DC -> DC FDCF -> FF FDCT -> DCT F E -> FE FDDC -> DC FDDCT-> DCT FI -> FI FT -> FT FTDC -> DCF N -> N RI -> R NH Code Will be Ignore
Lunch Balance		1.00

Warning

The date formats are different for the start and end dates on some layouts. Be sure that you format them correctly

Mealtime Format

Same as default format. The only difference is the school year column was removed and the start date's format is MM/DD/YYYY.

PrimeroEdge Format

Same as default format. The only difference a lunch balance column is added to the end.

How the file is processed

Student IDs that do not exist in Genesis; are skipped (and logged)
If a row; does not have all 5 fields; it is skipped
It is very important that the dates are in DD/MM/YYYY format where noted.
If the start date is blank - the record is skipped
The process looks at all existing lunch records for the given student:
- If a record with a start date AFTER the Start Date of the import row exists; that record is deleted in Genesis.
- If a record exists with the SAME start date of the import row; it is updated with the lunch code and the end date from the import file.
- If a record exists; and the start date is BEFORE the import row; the existing record is capped (an end date of the imported start date -1 is set); and a new record is created with the parameters of the row in the database.
The process will then update all existing AttendanceDaily2 records to align with the updated / imported lunch tracking records.

Sample File (LunchTracking.csv)

"2009-10","1004663","10/12/2009","","FI"

"2009-10","1003651","09/30/2009","","FI"

"2009-10","1013359","09/30/2009","","FT"

Setting up Lunch Tracking Import

Step 1: Configure the Task in Genesis

To setup the lunch tracking import; go to this screen: Core.Scheduler

Enable the Lunch Tracking Import Task (click the check box; and click the save button at the bottom of the screen).

Enter the fully qualified file name of the file you wish to process into the "Path (including file name) to import file" field. Some examples might be:

c:\pcsexport\lunchtracking.csv

/home/pcs/pcsexport.txt

Avoid using directories with a space in it's name.

Step 2: Schedule The Task in Genesis

Set the task to run at the desired interval.

Step 3: Test the task

Manually run the task from the Core.Scheduler screen. Review the log file and the output to make sure it processed the file correctly.

Lunchpin Updater Mode

When using this option, the import will expect a csv file with 2 columns. Column 1 is student id. Column 2 is the new lunch pin.