PEIMS with Other HR Systems (STU213)

1. PEIMS with Other HR Systems  (STU213) • Michael Barrett • PEIMS\Records Department • Technology Department • Birdville ISD • October 2014 PEIMS with Other HR Systems

2. Initial Determinations • Which records from which system? • All 040, 050, 060 records from HR • 090 Staff Responsibility Records depend on location/assignment • Central administration likely from HR system • Campus based personnel likely from Skyward • Who owns/updates the data? • 040, 043, 050 and 060 – HR and Payroll • 090 likely split between systems PEIMS with Other HR Systems

3. 090s - System of Record • 090 Staff Responsibility records for Central Admin staff produced by HR system – Supt., Asst. Supts., Directors, Coordinators, Instructional Leaders, etc. • 090 records for teachers produced by scheduling in Skyward • Manual addition of non-teaching duties • 090 records for other campus staff – if added manually in Skyward, campus can use Pre PEIMS 090 report to verify staff location and coding PEIMS with Other HR Systems

4. Use of Excel for Bridging Systems • Excel can be used to combine records from the HR and Skyward systems AS LONG AS the limitations of this approach are understood • TEA records formats variations (one or more 060s and 090s) result in imperfect matches • Can work around this with sorting and filtering options PEIMS with Other HR Systems

5. Spreadsheet Setup • Produce 040, 050, 060 and 090 records from HR sys • Produce 090 records from Skyward. • Import each into separate, labeled worksheets for the record type: Rec040, Rec050, Rec 060 and Rec090. • Delete first two columns (Rec Type and District) so that SSN is first column. • Sort sheets on SSN field from Low to High. • Add worksheets as desired for different data views. • Use formulas with absolute references to pull data from different tabs. PEIMS with Other HR Systems

6. Excel formulas • Copy SSN column of a record type into a new worksheet • Add VLOOKUP formula to pull data onto new sheet. • =VLOOKUP(A2,'Rec040'!$A$1:$H$5001,2,FALSE) • $ before column, row are absolute references • Add formula for first row • Confirm that right data is being pulled • Use the Fill, Down function to copy formula into all rows with data PEIMS with Other HR Systems

7. VLOOKUP formula • VLOOKUP(A2,'Rec040'!$A$1:$H$5001,2,FALSE) • 4 settings/variables • What to look up – column A is SSNs • Where to look – ‘Rec040’ is sheet, then :, then columns and rows • Which field in matching row – SSN is always 1 • Exact match required – FALSE • Get this working in first row, then use Fill, Down for the rest PEIMS with Other HR Systems

8. Use of Sorts and Filters • VLOOKUP is limited to the first match found • Use Sort to change what is found – must use SSN as first level, but can change second level of sort • Use Filter on columns in source sheet • Rec060 Payroll Activity Code – good example • Filter on 79 to see supplements • Filter on 80 to see base pay • If something is missing from a view, check filters first to see if excluded PEIMS with Other HR Systems

9. Use of Sorts and Filters • Use SSN list from different records as base for building other views • Record bases and different sorts can alleviate most of the mismatch issues caused by data set, though it is important to use different views so that all coding is reviewed • Once you have a base sheet working, SAVE a backup before you get creative PEIMS with Other HR Systems

10. Questions? • Michael Barrett • Birdville ISD • Technology • Michael.Barrett@birdvilleschools.net • 817.547.5600 PEIMS with Other HR Systems