...
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.constituentID assocPerson,
lower(r.role) assocPersonType,
x.remarks assocPersonNote
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 ('in memoriam', 'in-urned', 'informant', 'inspector', 'patron', 'photograph requestor', 'dealer', 'transferree', 'transferrer', 'donor', 'seller')
;
|
notes
Associated objects extract
field mapping
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
TextEntries | textEntry | assocObject | where textTypeID is 104 or 106 |
sql query
view: (NEEDS UPDATE)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
t.TextEntry assocObject
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 IN (104, 106)
;
|
notes
Previous owner extract
field mapping
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
ConXrefs | constituentID | previousOwner | reference to Person or Organization |
ConXrefs | remarks | ownershipNote |
|
sql query
view: cs_previousowners (refreshed 2012-03-12)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.constituentID previousOwner,
x.remarks ownershipNote
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 ('original owner', 'previous owner', 'previous donor (to other museum)')
;
|
notes
- should Role field be imported? (it distinguishes "owners" from "previous donors")