Object Production data mapping

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

Production date extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjDates

DateText

objectProductionDate_dateDisplayDate

 

ObjDates

DateBegSearch

objectProductionDate_dateEarliestSingleYear

 

ObjDates

DateEndSearch

objectProductionDate_dateLatestYear

 

ObjDates

DayBegSearch

objectProductionDate_dateEarliestSingleDay

 

ObjDates

DayEndSearch

objectProductionDate_dateLatestDay

 

ObjDates

MonthBegSearch

objectProductionDate_dateEarliestSingleMonth

 

ObjDates

MonthEndSearch

objectProductionDate_dateLatestMonth

 

sql query

view: cs_productiondates (refreshed 2012-03-13)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 d.dateText objectProductionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) objectProductionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) objectProductionDate_dateLatestYear,
 d.dayBegSearch objectProductionDate_dateEarliestSingleDay,
 d.dayEndSearch objectProductionDate_dateLatestDay,
 d.monthBegSearch objectProductionDate_dateEarliestSingleMonth,
 d.monthEndSearch objectProductionDate_dateLatestMonth
FROM
 ObjDates d
 JOIN Objects o ON d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) = 'date made'
;

notes

Production person extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

objectProductionPerson

reference to Person

Roles

role

objectProductionPersonRole

 

sql query

view:  cs_productionpersons (refreshed 2012-03-13)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID objectProductionPerson,
 LOWER(r.ROLE) objectProductionPersonRole
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 ('accompanist', 'artist', 'designer', 'maker', 'manufacturer', 'narrator', 'painter', 'performer', 'photographer', 'recorder')
;

notes

Production organization extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

objectProductionOrganization

reference to Organization

Roles

role

objectProductionPersonRole

 

sql query

view:  cs_productionorganizations (refreshed 2012-03-13)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID objectProductionOrganization,
 LOWER(r.ROLE) objectProductionOrganizationRole
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 ('accompanist', 'artist', 'designer', 'maker', 'manufacturer', 'narrator', 'painter', 'performer', 'photographer', 'recorder')
;

notes

Production note extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjDates

Remarks

objectProductionNote

 

ConXrefs

Remarks

objectProductionNote

 

TextEntries

textEntry

objectProductionNote

where textTypeID is 84

sql query

view: cs_productionnotes (refreshed 2012-04-20)

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 'Production date: ' + d.remarks objectProductionNote
FROM
 ObjDates d
 JOIN Objects o ON d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) = 'date made'
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
  'Production person/org: ' + x.remarks objectProductionNote
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 ('accompanist', 'artist', 'designer', 'maker', 'manufacturer', 'narrator', 'painter', 'performer', 'photographer', 'recorder')
 AND x.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry objectProductionNote
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 = 84
;

notes

  • NOTE: multiple notes per object should be concatenated into single (non-repeating) field objectProductionNote, separated by line breaks