Page Comparison

...

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

	sql
	sql

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

	sql
	sql

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

	sql
	sql


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

Versions Compared

Old Version 92

New Version 93

Key

notes

Inscription content extract

field mapping

sql query

notes