...
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 | 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 State | inscriptionContent pahmaNagpraCodeLegacy | 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, 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 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, t.TextEntry contentNote 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 = 85 ; |
notes
Inscription content extract
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Objects | Signed | inscriptionContent |
|
TextEntries | textEntry | inscriptionContent | where textTypeID is 19, 20, 22, 78, or 87 |
sql query
view: (NEEDS UPDATE)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
NULL inscriptionContent_prefix,
o.Signed inscriptionContent
FROM
Objects o
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND o.Signed IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
(CASE WHEN t.textTypeID IN (19, 20, 22) THEN substring(tt.TextType, 7, 1) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' ELSE '' END) inscriptionContent_prefix,
t.TextEntry inscriptionContent
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 (19, 20, 22, 78, 87)
;
|
notes
- NOTE: 'inscriptionContent_prefix' should be prepended to 'inscriptionContent' prior to import (this can't be done here because some fields are type TEXT
which cannot be concatenated in SQL) - NOTE: multiple notes per object should be concatenated into single (non-repeating) field inscriptionContent, separated by line breaks