...
TMS table name | TMS field name | CSpace field name | notes | ||||
---|---|---|---|---|---|---|---|
Objects | ObjectID | pahmaObjectID |
| ||||
ObjComponents | ComponentID | pahmaObjectID | must ensure no duplicates with Objects.ObjectID | ||||
Objects | ObjectNumber | objectNumber |
| ||||
ObjComponents | ComponentNumber | objectNumber |
| ||||
Objects | 'no' | isComponent |
| ||||
ObjComponents | 'yes' | isComponent |
| ||||
Objects | CatRais | pahmaCollection | NEW field (repeating; controlled list) | ||||
Objects | DepartmentID | pahmaTmsLegacyDepartment | FK to Departments; NEW field (controlled list) | ||||
Objects | HistAttributions | objectHistoryNote |
| ||||
Objects | LoanClass | pahmaTms2003DataSource | NEW field (controlled list) | ||||
Objects | Markings | distinguishingFeatures |
| ||||
Objects | Medium | material |
| ||||
Objects | Notes | comments |
| Objects | ObjectStatusID | pahmaObjectStatus | FK to ObjectStatuses; NEW field (controlled list) |
Objects | ObjectTypeID | collection | FK to ObjectTypes | ||||
Objects | PaperFileRef | nagpraInventoryName | FK to MTB_NagpraInvNames; controlled list | ||||
Objects | Portfolio | portfolioSeries | FK to MTB_PortfolioSeries; NEW field (controlled list) | ||||
Objects | MTB_ResponsibleDept | responsibleDepartment | controlled list | ||||
Objects | Signed | inscriptionContent | FK to MTB_NagpraBurialCodes | ||||
Objects | State | pahmaNagpraCodeLegacy | controlled list | ||||
ObjContext | Integer3 | inventoryCount | NEW field |
sql query
view: cs_objects (NEEDS UPDATE)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, o.ObjectNumber objectNumber, 'no' isComponent, o.CatRais pahmaCollection, d.Department pahmaTmsLegacyDepartment, o.HistAttributions objectHistoryNote, (CASE WHEN lower(o.LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN o.LoanClass END) pahmaTms2003DataSource, o.Markings distinguishingFeatures, o.Medium material, o.Notes comments, s.ObjectStatus pahmaObjectStatus, t.ObjectType collection, i.CSpaceID nagpraInventoryName, o.MTB_Portfolio portfolioSeries, (CASE WHEN o.MTB_ResponsibleDept = 1 THEN 'Natasha Johnson' WHEN o.MTB_ResponsibleDept = 2 THEN 'Leslie Freund' WHEN o.MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw' WHEN o.MTB_ResponsibleDept = 4 THEN 'Alicja Egbert' WHEN o.MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)' WHEN o.MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment, o.Signed inscriptionContent, b.optionID pahmaNagpraCodeLegacy, c.integer3 inventoryCount FROM Objects o LEFT OUTER JOIN Departments d ON o.departmentID = d.departmentID LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID LEFT OUTER JOIN ObjectTypes t ON o.objectTypeID = t.objectTypeID LEFT OUTER JOIN MTB_NagpraInvNames i ON o.paperFileRef = i.LongTMSname LEFT OUTER JOIN MTB_NagpraBurialCodes b ON o.state = b.code LEFT OUTER JOIN ObjContext c ON o.objectID = c.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 UNION ALL SELECT 'c' + CAST(c.componentID AS VARCHAR) objectID, c.componentNumber objectNumber, 'yes' isComponent, NULL pahmaCollection, NULL pahmaTmsLegacyDepartment, NULL objectHistoryNote, NULL pahmaTms2003DataSource, NULL distinguishingFeatures, NULL material, NULL comments, NULL pahmaObjectStatus, NULL collection, NULL nagpraInventoryName, NULL portfolioSeries, NULL responsibleDepartment, NULL inscriptionContent, NULL pahmaNagpraCodeLegacy, NULL inventoryCount FROM ObjComponents c WHERE c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1) ; |
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.termID contentPlace
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 ('subject')
;
|
notes
Comments extract
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Objects | Notes | comment |
|
TextEntries | textEntry | comment | where textTypeID is 27 |
sql query
view: (NEEDS UPDATE)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
o.Notes comment
FROM
Objects o
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND o.Notes IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
t.TextEntry comment
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 = 27
;
|