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
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

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

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

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

sql query

view: cs_productionnotes

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
 ;

notes

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