Lunch Tracking Import

TABLE OF CONTENTS

• How it works
• Default Layout
• PaySchools Layout
• Commalex layout
• Alternate Horizon layout
• Alt CCP Layout
• Alt Nutrikids Layout
• LunchTime
• LunchTime 2
• Meals Plus Format
• PCS 1 Format
• Mealtime Format
• PrimeroEdge Format
• LINQ Layout
• Titan Format
• How the file is processed
• Setting up Lunch Tracking Import
• Lunchpin Updater Mode
• Other Options

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 rowDOES NOTcontain headers.
• All values should be surrounded in quotes.

PaySchools Layout

Commalex layout

• The file must be a CSV (Comma Separated Values) formatted text file. 
• The first rowDOES NOTcontain headers.
• All values should be surrounded in quotes.

Alternate Horizon layout

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

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

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.

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.

or

LunchTime 2

File is tab-delimited.

Meals Plus Format

File is tab-delimited.

 2 = FI

3 = R

4 = Student Pays for Lunch

PCS 1 Format

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

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.

LINQ Layout

Titan Format

Note: A new record will be created with a lunch code from column 4. However, if column 4 is blank, we will use a code from column 5 instead.

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.

Other Options