evg_asset_copy_statuses
Statuses
evergreen
0
Copy Count.Migrating Status.New Evergreen Status.Notes
SELECT COUNT(ac.l_id), cs.l_name FROM asset_copy_legacy ac JOIN config_copy_status_legacy cs ON cs.l_id = ac.l_status GROUP BY 2 ORDER BY 2
You only need to fill this sheet out if you use custom statuses that need to be migrated.
evg_asset_circ_mods
Circulation Modifiers
evergreen
0
Copy Count.Migrating Circ Mod.New Circ Mod.Notes
SELECT COUNT(ac.l_id), ac.l_circ_modifier FROM asset_copy_legacy ac GROUP BY 2 ORDER BY 2
evg_asset_copy_locs
Copy Locations
evergreen
0
Count.Library.Migrating Copy Location.New Copy Location.Notes
SELECT COUNT(ac.l_id), aou.l_name, acl.l_name FROM asset_copy_location_legacy acl JOIN actor_org_unit_legacy aou ON aou.l_id = acl.l_owning_lib JOIN asset_copy_legacy ac ON ac.l_location = acl.l_id GROUP BY 2, 3 ORDER BY 2, 3
Any locations not mapped can be moved over as their existing locations.
evg_permission_grps
Permission Groups
evergreen
0
Count.Migrating Permission Group.New Permission Group.Notes
SELECT COUNT(au.l_id), pgt.l_name FROM actor_usr_legacy au JOIN permission_grp_tree_legacy pgt ON pgt.l_id = au.l_profile GROUP BY 2 ORDER BY 2
tlc_load_branches_list
tlc
0
Branches Present in Extract
Name.Evergreen Org Unit.Notes
SELECT l_name FROM ctbranches_tsv_clean ORDER BY 1
tlc_load_asset_notes
Item Notes
tlc
0
Count.Note Type.Notes
SELECT COUNT(l_itemcomment)::TEXT, 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2
UNION ALL SELECT COUNT(l_physicalcondition)::TEXT, 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2
UNION ALL SELECT COUNT(l_checkinoutnote)::TEXT, 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2
UNION ALL (SELECT DISTINCT 'Sample Item Comment', l_itemcomment FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL LIMIT 20)
UNION ALL (SELECT DISTINCT 'Sample Physical Condition', l_physicalcondition FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL LIMIT 20)
UNION ALL (SELECT DISTINCT 'Sample Circ Note', l_checkinoutnote FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL LIMIT 20)
tlc_load_asset_holdings_codes
Holdings Codes
tlc
0
Count.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione
SELECT COUNT(l_barcode), l_activeholdingscode FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2
tlc_load_money_migrating_bills
Migrating Bills By Bill Type
tlc
0
Count.Billing Type.Evergreen Bill Type
SELECT COUNT(a.l_chargenumber), b.l_description FROM itpayment_tsv_clean a JOIN itpaymenttype_tsv_clean b ON b.l_paymenttype = a.l_paymenttype WHERE a.l_dueamount::INTEGER - (a.l_waiveamount::INTEGER + a.l_tenderamount::INTEGER) > 0 GROUP BY 2 ORDER BY 2
tlc_load_usrs_bygroup
Patrons by Agency Type
tlc
0
Count.Permission Group.Evergreen Permission Group
SELECT COUNT(l_agencynumber), l_agencytype FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2
tlc_load_usrs_byexpiration
Patrons by Expiration Date
tlc
0
Count.Year of Expiration.Do Not Migrate?
SELECT COUNT(l_agencynumber), LEFT(l_expirationdate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2
tlc_load_usrs_byactive
Patrons by Last Active Date
tlc
0
Count.Year Last Active.Migrate as Active Flag?
SELECT COUNT(l_agencynumber), LEFT(l_lastactivedate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2
We can set the active flag based on this if desired.
tlc_load_usrs_blocks
Patrons by Block Status
tlc
0
Count.Block Status.Migration Note
SELECT COUNT(l_agencynumber), l_blockstatus FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2
tlc_load_usrs_gender
Patrons by Gender
tlc
0
Count.Gender.Migrate as Stat Cat
SELECT COUNT(l_agencynumber), l_gender FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2
Evergreen can load these as statistical categories
tlc_load_active_usr_passwords
Count of Patrons w Passwords
tlc
0
Count of NULL PINs.Default Password
SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_pin IS NULL
If any PINS are blank we will have to choose a method to supply them as Evergreen requires passwords.
tlc_load_usr_notes
Count of Patron Notes
tlc
0
Count.Note Type.Action
SELECT COUNT(l_agencynumber), 'Patron Note' FROM itagency_tsv_clean WHERE l_agencycomment IS NOT NULL UNION ALL SELECT COUNT(l_agencynumber), 'Address Comments' FROM itaddress_tsv_clean WHERE l_addresscomment IS NOT NULL
tlc_load_usr_balance
Count and Sum of Balances
tlc
0
Count.SUM in Pennies.Migrate?
SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0'
If this is being migrated there are a few options of how to do it and each will have different workflows.
tlc_load_usr_stat_cats
Patron Stat Cats and Counts
tlc
0
Patron Count.Stat Cat.Migrate?
SELECT COUNT(b.l_agencynumber), a.l_description FROM itagencyfields_tsv_clean a JOIN itagencydata_tsv_clean b ON b.l_agencyfieldnumber = a.l_agencyfieldnumber GROUP BY 2 ORDER BY 1
destiny_load_usr_by_gradelevel
Patrons by Destiny Grade Level
destiny
0
Count.Graduation Year
SELECT COUNT(*), grade_level FROM patrons_csv GROUP BY 2 ORDER BY 2
Transfer to note or stat cat?
destiny_load_usr_by_gender
Patrons by Destiny Gender
destiny
0
Count.Gender
SELECT COUNT(*), gender FROM patrons_csv GROUP BY 2 ORDER BY 2
Transfer to stat cat?
destiny_load_usr_by_patrontype
Patrons by Destiny Patron Type
destiny
0
Count.Patron Type.Permission Group
SELECT COUNT(*), patron_type FROM patrons_csv GROUP BY 2 ORDER BY 2
destiny_load_usr_by_status
Patrons by Destiny Status
destiny
0
Count.Status
SELECT COUNT(*), status FROM patrons_csv GROUP BY 2 ORDER BY 2
destiny_load_usr_by_municipality
Patrons by Municipality
destiny
0
Count.Municipality
SELECT COUNT(*), municipality FROM patrons_csv GROUP BY 2 ORDER BY 2
State cat?
destiny_load_usr_notes
Types of and Counts of Notes
destiny
0
Type of Note.Count
SELECT 'General Note', COUNT(*) FROM patrons_csv WHERE note_general IS NOT NULL and note_general != '' UNION ALL SELECT 'Important Note', COUNT(*) FROM patrons_csv WHERE note_important IS NOT NULL and note_important != ''
Messsage / alert / note?
destiny_load_usr_userdefined5
User Defined Field 5
destiny
0
Count.Values
SELECT COUNT(*), user_defined_5 FROM patrons_csv GROUP BY 2 ORDER BY 2
Retain somewhere?
destiny_load_usrs_pswdconfigured
Patrons by Password Configured
destiny
0
Count.Password Configured
SELECT COUNT(*), password_configured FROM patrons_csv GROUP BY 2 ORDER BY 2
Need how they want passwords set since we don't have them to migrate and do they want these that are configured with some special note?
destiny_load_usrs_phonefields
Phone Fields
destiny
0
Phone Field.Count.Evergreen Phone Field
SELECT 'Primary Phone 1', COUNT(*) FROM patrons_csv WHERE primary_phone_1 IS NOT NULL AND primary_phone_1 != '' UNION ALL SELECT 'Primary Phone 2', COUNT(*) FROM patrons_csv WHERE primary_phone_2 IS NOT NULL AND primary_phone_2 != '' UNION ALL SELECT 'Secondary Phone 1', COUNT(*) FROM patrons_csv WHERE secondary_phone_1 IS NOT NULL AND secondary_phone_1 != '' UNION ALL SELECT 'Secondary Phone 2', COUNT(*) FROM patrons_csv WHERE secondary_phone_2 IS NOT NULL AND secondary_phone_2 != ''
destiny_load_asset_categories
Count of Categories
destiny
0
Count.Category.Circ Mod?
SELECT COUNT(*), category FROM copies_csv GROUP BY 2 ORDER BY 2
destiny_load_asset_notes
Copies by Note Types
destiny
0
Note Type.Count
SELECT 'General Note', COUNT(*) FROM copies_csv WHERE note_general IS NOT NULL and note_general != '' UNION ALL SELECT 'Important Note', COUNT(*) FROM copies_csv WHERE note_important IS NOT NULL and note_important != ''
Retain?
destiny_load_asset_sublocation
Copies by Sub Location
destiny
0
Count.Sub Location.Shelving Location?
SELECT COUNT(*), sublocation FROM copies_csv GROUP BY 2 ORDER BY 2
destiny_load_asset_vendor
Copies by Vendor
destiny
0
Count.Vendor
SELECT COUNT(*), vendor FROM copies_csv GROUP BY 2 ORDER BY 2
Retain?
destiny_load_asset_descriptions
Copies with Description Fields
destiny
0
Description Field.Count
SELECT 'Description Field 1', COUNT(*) FROM copies_csv WHERE description_1 IS NOT NULL and description_1 != '' UNION ALL SELECT 'Description Field 2', COUNT(*) FROM copies_csv WHERE description_2 IS NOT NULL and description_2 != '' UNION ALL SELECT 'Description Field 3', COUNT(*) FROM copies_csv WHERE description_3 IS NOT NULL and description_3 != ''
Need report? Retain?
destiny_load_fines_byreason
destiny
0
Fines by Reason
Count.Reason
SELECT COUNT(*), reason FROM fines_csv GROUP BY 2 ORDER BY 2
circ_bystatus
Circulations by Status
apollo
0
Count.Status.Type
SELECT COUNT(id), l_status, l_type FROM action_circulation_legacy GROUP BY 2, 3
Circulations will only not be migrated if they can't be attached to a migrated patron and holding.
hold_bystatus
Holds by Status
apollo
0
Count.Status
SELECT COUNT(id), l_status FROM action_hold_request_legacy GROUP BY 2
Only unfilled holds are being migrated.
asset_pending_bibs
Pending Records
apollo
0
Count.Year of Last Edit.Count of Copies Attached
SELECT COUNT(bre.id), CASE WHEN LENGTH(bre.l_edited) > 1 THEN EXTRACT(YEAR FROM bre.l_edited::TIMESTAMP) ELSE '1900' END, COUNT(ac.id) FROM biblio_record_entry_legacy bre LEFT JOIN asset_copy_legacy ac ON ac.l_biblio = bre.l_id WHERE bre.l_status = 'pending' GROUP BY 2 ORDER BY 2
asset_copies_by_status
Copies by Status
apollo
0
Count.Status
SELECT COUNT(id), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2
Deleted copies with a delete date were not exported additionally those with the deleted status won't get loaded.
asset_pending_copies
Pending Copies by Last Edit
apollo
0
Count.Last Edited
SELECT COUNT(id), CASE WHEN LENGTH(l_edited) > 1 THEN EXTRACT(YEAR FROM l_edited::TIMESTAMP) ELSE '1900' END FROM asset_copy_legacy WHERE l_status = 'pending' GROUP BY 2 ORDER BY 2
assets_by_memberships
Copies by Memberships
apollo
0
Count.Membership Number.Membership Name
SELECT COUNT(ac.id), acl.l_membership_number, acl.l_membership_name FROM asset_copy_legacy ac JOIN asset_copy_location_legacy acl ON acl.l_membership_name = ac.l_memberships GROUP BY 2,3 ORDER BY 2
money_bills
Bills
apollo
0
Count.Status
SELECT COUNT(id), l_status FROM money_billing_legacy GROUP BY 2 ORDER BY 2
Unless there is a good reason to do so forgiven and paid bills will not be migrated.
actor_groups
Patron Membership Groups
apollo
0
Membership List Name.Membership List ID.Membership Number.Membership Name.Membership Length in Monthst
SELECT l_membership_list_name, l_membership_list_id, l_membership_number, l_membership_name, l_membership_length_months FROM actor_usr_legacy_groups_tsv ORDER BY 1, 3
Age looks like a good target for a stat cat and / or juvenile setting while the patron type can map to profiles.
actor_by_groups
Patrons by Membership Groups
apollo
0
Count.Membership List Name.Membership Number
SELECT COUNT(*), l_membership_list_name, l_membership_number FROM (SELECT id, UNNEST(STRING_TO_ARRAY(l_memberships,'|')) AS m FROM actor_usr_legacy ) x JOIN actor_usr_legacy_groups_tsv t ON t.l_membership_name = x.m GROUP BY 2, 3 ORDER BY 2, 3
actor_addresses_nulls
Patron Addresses
apollo
0
Address Field.Nulls
SELECT 'Street Address', COUNT(id) FROM actor_usr_address_legacy WHERE l_lines IS NULL UNION ALL SELECT 'City', COUNT(id) FROM actor_usr_address_legacy UNION ALL SELECT 'State', COUNT(id) FROM actor_usr_address_legacy WHERE l_country_division IS NULL UNION ALL SELECT 'Postal Code', COUNT(id) FROM actor_usr_address_legacy WHERE l_postal_code IS NULL
If any of these fields are null then we need defaults to fill in, note the extract had no city data.
actor_phones
Patron Phones
apollo
0
Count.Type
SELECT COUNT(*), l_type FROM actor_usr_phones_tsv GROUP BY 2 ORDER BY 2
These need to be mapped to Evergreen phone types.
hz_borrowersbybtypes
Borrowers by Borrower Types
horizon
0
Count.Borrower Type
SELECT COUNT(id), l_borrower_btype FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
hz_borrowerpinsamples
Borrower PINs Samples
horizon
0
Sample PINs
SELECT l_borrower_pin FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1 LIMIT 20;
Only 16 PINS defined.
hz_borrowernotesample
Borrower Note Field Samples
horizon
0
Count
SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20;
19,473
hz_borrowernotesample2
Borrower Note Field 2 Samples
horizon
0
Count
SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20;
78
hz_borrowernotesample3
Borrower Note Field 3 Samples
horizon
0
Count
SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20;
Only 6.
hz_phones
Borrower Phones
horizon
0
Count.Borrower Phone Type
SELECT COUNT(*), b
FROM (SELECT l_borrower_phone_1_phone_type AS b FROM actor_usr_legacy
UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM actor_usr_legacy
UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM actor_usr_legacy
UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x
GROUP BY 2 ORDER BY 2
Ordinal phones will have to be combined.
hz_bstats
Borrower B-Stats
horizon
0
Count.BStat
SELECT COUNT(*), b
FROM (SELECT l_borrower_bstat_1_bstat AS b FROM actor_usr_legacy
UNION ALL
SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy
UNION ALL
SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x
GROUP BY 2 ORDER BY 1;
hz_copybycollection
Copies by Collection
horizon
0
Count.Collection
SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
hz_copyfieldexamples
Sample Copy Field Values
horizon
0
Field
SELECT l_copy FROM asset_copy_legacy WHERE LENGTH(l_copy) > 1 LIMIT 30;
hz_itemsbyitype
Items by IType
horizon
0
Count.Item Type (itype)
SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
hz_volfieldexamples
Sample Volume Field Values
horizon
0
Field
SELECT l_volume FROM asset_copy_legacy WHERE LENGTH(l_volume) > 1 LIMIT 30;
hz_iteminhouseuses
Item In House Uses
horizon
0
Count.Number of Uses
SELECT COUNT(id), l_n_inhouse_uses FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
hz_internalnotesample
Internal Item Notes
horizon
0
Sample Note
SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 30;
rm_load_circ_count
rm
0
Circs by Status
Count of Circs.Status
SELECT COUNT(id), l_is_checked_out FROM asset_copy_legacy GROUP BY 2
rm_load_asset_by_resource_type
Resource Type
rm
0
Count.Resource Type
SELECT COUNT(*), l_resource_type FROM asset_copy_legacy GROUP BY 2 ORDER BY 2
rm_load_asset_by_location
Copies by Location
rm
0
Count.Location
SELECT COUNT(*), l_location FROM asset_copy_legacy GROUP BY 2 ORDER BY 2
rm_load_asset_by_category
Copies by Category
rm
0
Count.Category
SELECT COUNT(*), l_category FROM asset_copy_legacy GROUP BY 2 ORDER BY 2
rm_load_asset_by_status
Copies by Status
rm
0
Count.Status
SELECT COUNT(*), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2
rm_actor_groups
Patrons by User Groups
rm
0
Count.Group
SELECT COUNT(id), l_user_group FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
rm_actor_access
Patrons by Access Field
rm
0
Count.Access
SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
rm_actor_comments
Patron Comments
rm
0
Count.Sample
SELECT COUNT(id), 'All Comments' FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1
UNION ALL SELECT NULL, l_comments FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10
rm_actor_circulation_note
Patron Circ Notes
rm
0
Count.Sample
SELECT COUNT(id), 'All Notes' FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1
UNION ALL SELECT NULL, l_circulation_note FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10