...
sql query
view: cs_productiondates (refreshed 2012-03-13)
Code Block | ||||
---|---|---|---|---|
| ||||
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' ; |
...
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)
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') ; |
...
view: cs_productionorganizations (refreshed 2012-03-13)
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') ; |
...
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: cscs_productionnotes (refreshed 2012-04-20)
Code Block | ||||
---|---|---|---|---|
| ||||
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