Object History and Association data mapping

This page contains information about PAHMA's data migration of Object History and Association Information.
See parent page on PAHMA data migration ETL work.

Associated people (culture) extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjContext

Culture

assocPeople

should be mapped to Culture Authority

TextEntries

textEntry

assocPeopleNote

where textTypeID is 32, 49, 97, or 99

TextEntries

textType

assocPeopleType

where textTypeID is 49

sql query

view: cs_associatedpeoples (refreshed 2012-04-20)

SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.culture assocPeople,
 NULL assocPeopleType,
 NULL assocPeopleNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.culture IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL assocPeople,
 (case when t.textTypeID = 49 then 'nagpraCulturalAffiliation' end) assocPeopleType,
 t.TextEntry assocPeopleNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (32, 49, 97, 99)
;

notes

  • TODO: map this data to new Culture Authority (to be populated from TMSThesaurus.Terms table)

Associated date extract

this data cannot be imported until CSPACE-4775 is done (v2.2)

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjDates

EventType

assocDateType

 

ObjDates

DateText

assocDate_dateDisplayDate

 

ObjDates

DateBegSearch

assocDate_dateEarliestSingleYear

 

ObjDates

DateEndSearch

assocDate_dateLatestYear

 

ObjDates

DayBegSearch

assocDate_dateEarliestSingleDay

 

ObjDates

DayEndSearch

assocDate_dateLatestDay

 

ObjDates

MonthBegSearch

assocDate_dateEarliestSingleMonth

 

ObjDates

MonthEndSearch

assocDate_dateLatestMonth

 

ObjDates

Remarks

assocDateNote

 

TextEntries

textEntry

assocDateNote

where textTypeID is 100

sql query

view: cs_associateddates (refreshed 2012-04-20)

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 (CASE WHEN d.eventType != '[not entered]' THEN lower(d.eventType) END) assocDateType,
 d.dateText assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) assocDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) assocDate_dateLatestYear,
 d.dayBegSearch assocDate_dateEarliestSingleDay,
 d.dayEndSearch assocDate_dateLatestDay,
 d.monthBegSearch assocDate_dateEarliestSingleMonth,
 d.monthEndSearch assocDate_dateLatestMonth,
 d.remarks assocDateNote
FROM
 ObjDates d
 JOIN Objects o ON d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) NOT IN ('accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
 AND ((d.dateText IS NOT NULL AND d.dateText != '') OR d.dateBegSearch != 0)
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 c.period assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 NULL assocDateNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.period IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 c.reign assocDate_dateDisplayDate,
 'produced during reign of' assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 NULL assocDateNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.reign IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 NULL assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 t.TextEntry assocDateNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 100
;

notes

  • DONE: populate controlled list options for assocDateType field

Associated organization extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

assocOrganization

reference to Organization

Roles

role

assocOrganizationType

 

ConXrefs

remarks

assocOrganizationNote

 

sql query

view: cs_associatedorganizations (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID assocOrganization,
 lower(r.role) assocOrganizationType,
 x.remarks assocOrganizationNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
 JOIN constituents c ON x.constituentID = c.constituentID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.constituentTypeID > 1
 AND lower(r.role) IN ('donor', 'seller')
;

notes

Associated person extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

assocPerson

reference to Person

Roles

role

assocPersonType

 

ConXrefs

remarks

assocPersonNote

 

sql query

view: cs_associatedpersons (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID assocPerson,
 lower(r.role) assocPersonType,
 x.remarks assocPersonNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
 JOIN constituents c ON x.constituentID = c.constituentID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.constituentTypeID = 1
 AND lower(r.role) IN ('in memoriam', 'in-urned', 'informant', 'inspector', 'patron', 'photograph requestor', 'dealer', 'transferree', 'transferrer', 'donor', 'seller')
;

notes

Associated objects extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

TextEntries

textEntry

assocObject

where textTypeID is 104 or 106

sql query

view:  cs_associatedobjects (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry assocObject
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (104, 106)
;

notes

Associated place extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

TextEntries

textEntry

assocPlace

where textTypeID is 45

sql query

view:  cs_associatedplaces (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry assocPlace,
 'used' assocPlaceType
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 45
;

notes

Previous owner extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

previousOwner

reference to Person or Organization

ConXrefs

remarks

ownershipNote

 

TextEntries

textEntry

ownershipNote

where textTypeID is 93

sql query

view: cs_previousowners (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID previousOwner,
 x.remarks ownershipNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(r.ROLE) IN ('original owner', 'previous owner', 'previous donor (to other museum)')
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL previousOwner,
 t.TextEntry ownershipNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 93
;

notes

  • should Role field be imported? (it distinguishes "owners" from "previous donors")