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