PLIF’s Driving You to Drink?: Tips and Tricks for Success in Getting Patron Info into Aleph

1. ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Andrew Perry and Dave Ritchie LiSUG 2008 – “Brewing Up Solutions” SUNY Institute of Technology at Utica Oct 10, 2008 PLIF’s Driving You to Drink?:Tips and Tricks for Success in Getting Patron Info into Aleph

2. What's the PLIFing Problem? • ANY duplicate ID causes the person's record not to load (across all types)‏ • ID sections with ID’s of all 0’s or blanks won't work • Loading records with NO “01 ID” causes Aleph to create one – a sequential number – which if left without updating to a real “01 ID” might eventually conflict with another type of sequential number (some Aleph dummy 01's conflicted with incoming real Banner PIDM “16 ID”'s)

3. What's the PLIFing Problem? continued • Match ID in PLIF does not exist in Aleph patron data file • QUIZ: What's the result if you load a garden-variety PLIF record when the match ID doesn't exist in the Aleph patron file? • (a) New Record? (b) No Load? • ANS: new record if no other problems with the incoming record (e.g., conflict with existing ID already in patron file)

4. What's the PLIFing Problem? continued • Poor quality control from data source? • Expiry date(s) missing? • QUIZ: How many expiry dates are required in v18? • (a) None (b) One (c ) Two (d) Three • ANS: one for each Address (z304) record + one for the Borrower (z305) record • Lack of testing? • Tab31, tab_bor_id.eng - tables not up to date?

5. PLIF Process • 1- Create/receive PLIF from data source • 2- Transfer PLIF to DEV server • 3- Review data • 4- If problems: Manipulate data, or make new PLIF and go back to a previous step • 5- Test & Load via p_file_20 Circ Service • 6- Interpret load report and log (errors?)‏ • 7- If errors: Manipulate data or make new PLIF, and go back to a previous step • Finished? Now do it on PROD!

6. Transfer PLIF to server • Sandbox Upload function – is PLIF file size an issue?? • If so, use Secure FTP to xxx_Dropbox/plifs/ directory – refer to Footprints # 5745 • If use Dropbox, must copy to DEV (and PROD), or OLIS can develop a copy script to look for a PLIF on regular basis

7. Reviewing Data • Is the data formatted correctly? • QUIZ: How many places are there to verify the format of a PLIF in Sandbox? • (a) One (b) Two (c ) Three • ANS: two (listed in the 2 bullets just below:) • Use Util-F-2-5-1 on PLIF in Sandbox OR • Open filename.plif in Sandbox OR • Download & open file in a good editor (EditPlus, UltraEdit, etc.) on your desktop

8. Sidebar: PLIF Structure • Match ID } a.k.a. • User Record } z303 section • Ends with numbers of: ID recs, Address recs, Borrower recs (e.g., 040201)‏ • ID Record(s) } z308's • Address Record(s) } z304's • Each includes Start date and Expiry Date • Borrower Record } z305 section • Includes Sublibrary (xxx50) & Expiry Date

9. Sidebar: PLIF Structure, User Rec

10. Sidebar: PLIF Structure, ID Recs

11. Sidebar: PLIF Structure, Address Recs

12. Sidebar: PLIF Structure, Bor Rec

13. Reviewing Data – More Issues • Is match ID logic correct? • If SSN being used to match, is correct SSN format used (hyphens or not)? • Do any ID sections contain blank or other duplicate default characters? • Clashing ID numbers • Have address dates been included? Valid date? • Are tab31 and tab_bor_id.eng correct?

14. Reviewing Data – Even More Issues • Is there a valid borrower expiration date? [REQUIRED in PLIF] • Are ‘X’, ‘D’, and ‘U’ used as action codes? • USER-REC-NO-ID, ADDRESS, BOR (counters can be wrong)‏ • Is the PLIF for v.18.02 ? (less of a problem now)‏

15. Load Patron Records (p_file_20) Circ Service • Input PLIF filename • Input filename for Report • Testing?: run in “Update=No” mode to get Report • Load should be iterative process — run without update, identify and correct errors, run again, ... • Automating PLIF loads?: Can be run from job_list (get parameters from Log)‏

16. Interpreting p_file_20 REPORT • Where is it? --> Circ Client Task Manager! • REPORT shows each record processed. • Indicates when a record has “successed” to load • Note that the part of the record “Unable” to load is NOT the problem part • Scan for words like “Can not” or “Cannot” –> that indicates the problem field in the record

17. Interpreting p_file_20 REPORT – Task Mgr example, no problem visible yet

18. Interpreting p_file_20 REPORT – Task Mgr example, z304 problem causes whole record not to load – but what’s the problem?

19. Interpreting p_file_20 REPORT , continued • QUIZ: What’s the biggest DISadvantage to viewing the Report in the Task Manager or in xxx50/print/ directory in Sandbox? • (a) Takes a long time to load (often large file) • (b) Cannot easily search for errors • How else can I get at it, and read it? • Task Mgr: Right-click on table, “Export to … Excel”, & use “Find” to search • Sandbox: Download into editor, use “Find” to search

20. Interpreting p_file_20 REPORT – downloaded file to Editor (z308 ID ERROR example) <section-02> <line-number>004247</line-number> <table-name>z308</table-name> <action>REWRITE</action> <match-id>1818607249</match-id> <patron-id>ID55668</patron-id> <msg-code>5003</msg-code> <msg-desc>Unable to process record due to problems in other records.</msg-desc> </section-02> <section-02> <line-number>004247</line-number> <table-name>z308</table-name> <action>REWRITE</action> <match-id>1818607249</match-id> <patron-id>ID55668</patron-id> <msg-code>5037</msg-code> <msg-desc>Cannot update/delete record. Login record belongs to other patron</msg-desc> </section-02>

21. Interpreting p_file_20 REPORT, continued • ID problem?: COUNT the ID's in the Report for that individual until you come to the problem one (“Can not...”)‏ • e.g., you load 4 ID's – an 01 (“barcode”), a 16 (Banner PIDM), an 07 (local ID), & an 05 (SUNYCard number) in that order – and in the Report for this individual all the z308 sections say “Unable...” except for the THIRD one = the 07 ID is the problem • Failures can be deceptive—errors may point to one record (e.g., ID) but the problem may be another one • Wrong spacing results in problems in LATER fields • Often resolving errors involves contact with data source (e.g., registrar, student systems support, PLIF creator)‏

22. Interpreting p_file_20 LOG • Where is it? --> alephe/scratch/xxx50_p_file_20.nnnnn ! • QUIZ: What are the advantages of the LOG file? • (a) Often can quickly review in Sandbox • (b) Smaller size than REPORT – downloads easily (1 MB vs. 15 MB) • (c) Gives “actual” error message • (d) All of the above • ANS: (d)

23. Interpreting p_file_20 LOG, z304 problem part of PLIF – as viewed in Sandbox

24. Interpreting p_file_20 LOG, z304 problem part of PLIF – as viewed in editor

25. Interpreting p_file_20 LOG • Useful sections: • 1st: gives p_file_20 program call with parameters  did you load it the way you thought? • 2nd: shows loading of tables needed to run PLIF table as possible source of error? • 3rd: Record listing  benchmark recs & ERROR recs • Scan for “Error” (download to desktop, open in editor)

26. Interpreting p_file_20 LOG, z305 has 3 problems

27. Interpreting p_file_20 LOG, z305 borrower expiration date missing

28. Interpreting p_file_20 LOG, z305 after adding borrower expiration date, now 2 problems

29. Tab 31 – what does it do? • Table includes Patron statuses and default permissions / privileges of each, including default borrower expiration date • See these in Circ client Local Information part of patron record, in tabs at bottom • Tab31 Problem: if first line has ALEPH ## or XXX50 ##, remove that first line! – Karen Gardner-Athey

30. Interpreting p_file_20 LOG, z305 comparing PLIF bor-rec-sublibrary w/tab 31 sublibrary

31. Interpreting p_file_20 LOG, z305 replacing PLIF bor-rec-sublibrary w/tab 31 sublibrary

32. Tab_bor_id – manages patron ID types • Problem: make sure that all the verifications are “N” (= verification optional) EXCEPT for the patron login ID type (usually the “barcode” 01 ID type) which you want to be “Y” (= mandatory) –Karen Gardner-Athey

33. Manipulating Data - examples • Date(s) – including or replacing • Sublibrary replacement: BOR-REC-SUB-LIBRARY • QUIZ: Where is the above sublibrary parameter? • (a) Borrower section (b) z305 (c ) Both (d) None • ANS: (c) since Borrower section = z305 • Creating test PLIF or isolating problem records • Footprints request: example – for clashing sequential numbers Aleph-assigned-01 and 16 ID’s, request OLIS to add a CAMPUS PREFIX with the utility (Util-G-2) which governs Aleph assignment of dummy 01’s for records added without an 01, [AND assign that prefix to existing Aleph-assigned-01’s]

34. Manipulating Data - steps • 1- What's the problem? And what do we know about it? And can it be solved by either REPLACING or INSERTING data? • Does the problem affect a certain class of records (i.e., only those with 1 address record/section)? • Where in the PLIF record is the problem located? • 2- [Download PLIF to desktop] & Load into editor • 3- Use Find to scan for first instance; continue through several patron records • 4- Use Find & Replace [exact same data!] to scan for potential number of problems • Example: Find A01% ; Replace A01% • 5- Use Find/Replace [different data] CAREFULLY!!!

35. HELP Resources • v18 PLIF documentation: “How to Use the Patron Loader Interface – PLIF (file-20) – v.18.01” – find it at OLIS Documentation site • Footprints to OLIS staff • SUNY LMS listserv message • Aleph CRM db and/or problem report

36. Discussion & Questions

37. Extra: What’s the PLIFing problem here?

38. Extra: Rec 5993 z304 & z305 “unsupported action”: usually caused by a formatting problem or extra spaces (but we don’t see it here)