Tool Search: Data Extract Utilities
This guide outlines how to use the Data Extract Utilities tool to create a data extract for use with food service/Point-of-Sale systems. This extract provides student demographic, enrollment, homeroom, contact, address, and meal eligibility data so the system can match patrons and manage purchases.
Create the Food Service Extract
- Select the New icon.
- Select the Extract option and click Submit. A series of editors will appear.
- Enter Admin/Vendor Provided Data. The credentials for this process must be obtained from your food service/POS system. These credentials usually include username, password, server (IP Address or server name), port number, and sometimes the path directory.
- Select an Extract Method of SQL
- In the SQL/Prism field, enter EXEC get_foodservice
- This stored procedure accepts two parameters:
Parameter Type Default Description @date VARCHAR(10) Today's date Determines homeroom resolution AND which meal eligibility record is active @includeSSN BIT 1 When 0, both student and guardian SSN fields return NULL - Give it an Extract Instance Name (i.e., Food Service Extract).
- Determine the Output File Name.
- Fill out the rest of the File Information fields (as applicable).
- Determine the File Extract Format. Field definitions can be found on the Data Extract Utilities article.
- Set the Data Extract Schedule.
- Click the Save icon when finished.
At this time, a user can select the appropriate buttons to test the extract, test the database connection, or run the extract. Extracts will display in a new window in the selected format.
- The Test Extract button generates the requested information using SQL or Ad hoc. Data is not written to the database or stored anywhere.
- The Test Connection button checks the connection to the entered server by attempting to write a 1-byte file to the configured location. If it is successful, a success ribbon is displayed; if it is unsuccessful, a pop-up with the reason the connection failed is displayed.
- The Run Extract button runs the extract, sends and stores the file where needed (in case of an issue with an auto-run extract).
Example executions
| Example | Description |
EXEC get_foodservice | Get today's snapshot with SSNs included (default behavior) |
EXEC get_foodservice @includeSSN = 0 | Get today's snapshot without SSNs (privacy-safe for sharing with food service vendor) |
EXEC get_foodservice @date = '10/01/2025', @includeSSN = 1 | Get a snapshot for a specific date with SSNs |
| EXEC get_foodservice @date = '10/01/2025', @includeSSN = 0 | Get snapshot for a specific date without SSNs |
| EXEC get_foodservice @date = '07/01/2025' | Verify meal status for the start of a new eligibility period |
| EXEC get_foodservice @date = '03/15/2026', @includeSSN = 0 | Check what the extract looked like on a past date (useful when a vendor says data was wrong) |
How it functions
- Finds each student's "best" enrollment for the active school year: prioritizes Primary service type (P) over Secondary, then the most recent start date. Excludes summer school calendars and no-show enrollments.
- Resolves the student's primary household by selecting the household where the student is an active member, and, if available, the non-secondary household with a guardian relationship.
- Resolves the primary contact/guardian from the chosen household, picks the first guardian in the relatedPair table who is also a current household member.
- Resolves the mailing address: from the student's active household locations, prefers the primary household's mailing address, then falls back on the secondary flag.
- Resolves the homeroom section for @date: walks the schedule structure (roster > section > placement > period > day) to find the section the student is rostered into on the given date, prioritizing courses flagged as homeroom or homeroom sections, then by period sequence.
- Resolves the student's meal eligibility from the poseligibility table: finds the eligibility record for the active school year where the @date falls within the record's start/end date range. If multiple records overlap, it takes the most recent by start date. If no record exists, defaults to 'S' (standard/full pay).
Data fields
The data extracted will include the following fields in this order:
| Column | Source | Meaning |
|---|---|---|
| StudentDistrictID | person.studentnumber | District-assigned student number |
| StudentSSN | identity.ssn | Student's Social Security Number Returns NULL when @includeSSN = 0 |
| StudentStateID | person.stateID | State-assigned student identifier |
| StudentEnrollmentStatus | Computed from enrollment.enddate | 1 = currently enrolled (end date is today or later), 0 = enrollment has ended |
| StudentSiteCode | school.number | School's numeric code |
| StudentDistNum | district.number | District's numeric code |
| StudentHomeroom | room.name (from resolved section) | Name of the homeroom; defaults to 'NHR' if no homeroom is resolved |
| StudentHomeRoomTeacherLastname | identity.lastname (teacher) | Homeroom teacher's last name |
| StudentHomeRoomTeacherFirstname | identity.firstname (teacher) | Homeroom teacher's first name |
| StudentLastName | identity.lastname | Student's last name |
| StudentFirstName | identity.firstname | Student's first name |
| StudentMiddleName | identity.middlename | Student's middle name |
| StudentBirthdate | identity.birthdate | Formatted MM/DD/YYYY, trimmed |
| StudentGender | identity.gender | Gender code (typically M/F) |
| StudentGradeLevel | enrollment.grade | Grade level from the enrollment record |
| StudentRace | identity.raceEthnicity | Race/ethnicity code |
| StudentEmail | v_Contact.email | Student's email from the contact view |
| StudentPrimaryContactLastName | identity.lastname (guardian) | Guardian's last name |
| StudentPrimaryContactFirstName | identity.firstname (guardian) | Guardian's first name |
| StudentPrimaryContactRelationship | relatedPair.name | Relationship label (e.g., "Mother", "Father") |
| StudentPrimaryContactSSN | identity.ssn (guardian) | Guardian's SSN Returns NULL when @includeSSN = 0 |
| StudentPrimaryContactEmail | v_Contact.email (guardian) | Guardian's email |
| StudentPrimaryWorkPhone | v_Contact.workphone (guardian) | Guardian's work phone |
| StudentPrimaryContactCounty | address.county | County of the mailing address |
| StudentPrimaryContactStreetAddress | Computed from address fields | Full street address; handles PO Box vs. standard format (number + prefix + street + tag + dir + apt) |
| StudentPrimaryContactCity | address.city | City |
| StudentPrimaryContactState | address.state | State code |
| StudentPrimaryContactZip | address.zip | ZIP code |
| StudentPrimaryContactPhoneNumber | household.phone | Household phone number (empty string if null) |
| StudentMealStatus | poseligibility.eligibility | Meal eligibility code. Common values: 'F' = Free, 'R' = Reduced, 'S' = Standard/Full Pay (default if no record found) |
