...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
SiteObjXrefs | SiteID | fieldCollectionPlace | FK to Sites table to get MTB_PlaceTermID1; reference to Place Authority |
SiteObjXrefs | Subsite1/Subsite2/Subsite3 | fieldLocVerbatim |
|
sql query
view:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
x.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
;
|
notes
- NOTE: MTB_PlaceTermID1 should be used to construct refName for PlaceAuthority; we may need a mapping table in order to do this?
Field collector extract
field mapping
...