Object Collection data mapping

This page contains information about PAHMA's data migration of Object Collection Information.
See parent page on PAHMA data migration ETL work.

Field collection dates extract

this data cannot be imported until v2.1

field mapping

TMS table name	TMS field name	CSpace field name
ObjDates	DateText	fieldCollectionDate_dateDisplayDate
ObjDates	DateBegSearch	fieldCollectionDate_dateEarliestSingleYear
ObjDates	DateEndSearch	fieldCollectionDate_dateLatestYear
ObjDates	DayBegSearch	fieldCollectionDate_dateEarliestSingleDay
ObjDates	DayEndSearch	fieldCollectionDate_dateLatestDay
ObjDates	MonthBegSearch	fieldCollectionDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	fieldCollectionDate_dateLatestMonth
ObjDates	Remarks	fieldCollectionNote
Objects	Dated	fieldCollectionDate_dateDisplayDate
Objects	DateBegin	fieldCollectionDate_dateEarliestSingleYear
Objects	DateEnd	fieldCollectionDate_dateLatestYear

sql query

view: cs_fieldcollectiondates

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 d.dateText fieldCollectionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) fieldCollectionDate_dateLatestYear,
 d.dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
 d.dayEndSearch fieldCollectionDate_dateLatestDay,
 d.monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
 d.monthEndSearch fieldCollectionDate_dateLatestMonth,
 d.remarks fieldCollectionNote
FROM
 ObjDates d
WHERE
 lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.dated fieldCollectionDate_dateDisplayDate,
 (CASE WHEN o.dateBegin != '0' THEN o.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN o.dateEnd != '0' THEN o.dateEnd END) fieldCollectionDate_dateLatestYear,
 null fieldCollectionDate_dateEarliestSingleDay,
 null fieldCollectionDate_dateLatestDay,
 null fieldCollectionDate_dateEarliestSingleMonth,
 null fieldCollectionDate_dateLatestMonth,
 null fieldCollectionNote
FROM
 Objects o
WHERE
 o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
;

notes

differences in eventType ('date collected', 'secondary collection date', 'tertiary collection date') are not important and do not need to be migrated, per Michael (12/15/11)
DONE: add new field pahmaFieldCollectionDate, to change fieldCollectionDate field into a repeating field
TODO: wait until fieldCollectionDate field is changed into a structured date (in v2.1; see CSPACE-4775)

Field collection places extract

field mapping

TMS table name	TMS field name	CSpace field name	notes

sql query

view:

notes

Field collectors extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	ID	reference to Object
ConXrefs	constituentID	fieldCollector	reference to Person or Organization

sql query

view:

SELECT
 o.objectID,
 x.constituentID fieldCollector
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 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 lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
;