...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
ObjectPackages | name | title |
|
ObjectPackages | notes | scopeNote |
|
ObjectPackages | owner | owner | FK to Users; reference to Person Authority |
Objects | objectNumber | title | for Burial Lot |
sql query
view: cs_groups (refreshed 2012-05-24)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT CAST(p.objectPackageID AS VARCHAR) groupID, p.name title, p.notes scopeNote, (CASE WHEN u.MTB_ConstituentID = -1 THEN NULL ELSE u.MTB_ConstituentID END) owner, p.entereddate dateCreated FROM ObjectPackages p LEFT OUTER JOIN Users u ON p.owner = u.login UNION ALL SELECT 'bur' + CAST(o.objectID AS VARCHAR) groupID, 'Burial lot ' + o.objectNumber title, null scopeNote, null7252 owner, o.entereddate dateCreated FROM Objects o WHERE o.IsTemplate = 0 and o.IsVirtual=1 and o.DepartmentID = 34 ; |
notes
- 7252 is the ConstituentID of "PAHMA-NAGPRA staff"
- "global" field does not need to be imported, per Michael (1/12/12)
- Capture burial lots as groups (Object table, isTemplate=0, isVirtual=1 & DepartmentID=34) per Michael's email (5/10/12)
...
view: cs_objectgrouprelationships (refreshed 2012-05-11)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, CAST(o.objectPackageID AS VARCHAR) groupID FROM objPkgList o UNION ALL SELECT CAST(obj.objectID AS VARCHAR) objectID, 'bur' + CAST(bl.objectID AS VARCHAR) groupID FROM Objects obj, Associations oa, Objects bl WHERE obj.IsVirtual = 0 AND obj.IsTemplate = 0 AND bl.IsVirtual = 1 AND bl.IsTemplate = 0 AND bl.DepartmentID = 34 AND obj.objectID = oa.ID2 AND oa.ID1 = bl.objectID ; |
...