...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
ConXrefs | constituentID | fieldCollector | reference to Person or Organization |
sql query
view: cscs_fieldcollectors
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, x.constituentID fieldCollector 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 ('collector', 'secondary collector', 'tertiary collector') ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
ObjDates | Remarks | fieldCollectionNote | pertains to fieldCollectionDate |
SiteObjXrefs | Remarks | fieldCollectionNote | pertains to fieldCollectionPlace |
ThesXrefs | Remarks | fieldCollectionNote | pertains to fieldCollectionPlace |
ConXrefs | Remarks | fieldCollectionNote | pertains to fieldCollector |
sql query
view:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, 'Field collection date: ' + d.remarks fieldCollectionNote FROM ObjDates d JOIN Objects o on d.objectID = o.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date') AND d.remarks IS NOT NULL UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, 'Field collection place: ' + x.remarks fieldCollectionNote FROM SiteObjXrefs x JOIN Objects o ON x.objectID = o.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND x.remarks IS NOT NULL UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, 'Field collection place: ' + x.remarks fieldCollectionNote FROM ThesXrefs x JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID 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 x.thesXrefTableID = 346 AND lower(t.thesXrefType) IN ('archaeological site', 'card header', 'provenience') AND x.remarks IS NOT NULL AND x.remarks != '' UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, 'Field collector: ' + x.remarks fieldCollectionNote 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 ('collector', 'secondary collector', 'tertiary collector') AND x.remarks IS NOT NULL ORDER BY objectID ; |
notes
- NOTE: multiple notes per object should be concatenated into single (non-repeating) field fieldCollectionNote, separated by line breaks