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	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

	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,
 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

	sql
	sql

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

	sql
	sql


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
;

Versions Compared

Old Version 89

New Version 90

Key

sql query

notes

Comments extract

field mapping

sql query

notes