...
| Table of Contents | ||
|---|---|---|
|
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 |
|
ObjDates | Remarks | objectProductionNote |
|
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(d.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,
d.remarks objectProductionNote
FROM
ObjDates d
WHERE
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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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')
;
|