...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(xo.objectID AS VARCHAR) objectID, s.MTB_PlaceTermID1 fieldCollectionPlace, COALESCE(x.subsite1, '') + (CASE WHEN x.subsite1 IS NOT NULL AND x.subsite2 IS NOT NULL THEN ', ' ELSE '' END) + COALESCE(x.subsite2, '') + (CASE WHEN x.subsite3 IS NOT NULL THEN ', ' + x.subsite3 ELSE '' END) fieldLocVerbatim FROM SiteObjXrefs x JOIN Objects o ON x.objectID = o.objectID JOIN Sites s ON x.siteID = s.siteID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
ObjDates | Remarks | fieldCollectionNote |
|
SiteObjXrefs | Remarks | fieldCollectionNote |
|
ConXrefs | Remarks | fieldCollectionNote |
|
...
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 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
;
|
...