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