Transcript PEIMS with Other HR Systems

PEIMS with Other HR Systems (STU213)
 Michael Barrett
 PEIMS\Records Department
 Technology Department
 Birdville ISD
 October 2014
PEIMS with Other HR Systems
1
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
2
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
3
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
4
Spreadsheet Setup
1. Produce 040, 050, 060 and 090 records from HR sys
2. Produce 090 records from Skyward.
3. Import each into separate, labeled worksheets for the
4.
5.
6.
7.
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
5
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
6
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
7
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
8
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
9
Questions?
 Michael Barrett
 Birdville ISD
 Technology
 [email protected]
 817.547.5600
PEIMS with Other HR Systems
10