...
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 | MTB_PlaceTermID | fieldCollectionPlace | reference to Place Authority | 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 | Provenance | fieldLocVerbatim |
| Objects | MTB_ResponsibleDept | responsibleDepartment | controlled list |
Objects | Signed | inscriptionContent | FK to MTB_NagpraBurialCodes | ||||
Objects | State | pahmaNagpraCodeLegacy | controlled list | ||||
ObjContext | Integer3 | inventoryCount | NEW field |
...
| 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, MTB_PlaceTermID fieldCollectionPlace, o.Notes comments, s.ObjectStatus pahmaObjectStatus, t.ObjectType collection, i.CSpaceID nagpraInventoryName, o.MTB_Portfolio portfolioSeries, Provenance fieldLocVerbatim, (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 fieldCollectionPlace, NULL comments, NULL pahmaObjectStatus, NULL collection, NULL nagpraInventoryName, NULL portfolioSeries, NULL fieldLocVerbatim, 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) ; |
...