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

this data cannot be imported until CSPACE-4775 is done

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
Objects	Dated	fieldCollectionDate_dateDisplayDate
Objects	DateBegin	fieldCollectionDate_dateEarliestSingleYear
Objects	DateEnd	fieldCollectionDate_dateLatestYear
ConXrefs	DisplayDate	fieldCollectionDate_dateDisplayDate
ConXrefs	DateBegin	fieldCollectionDate_dateEarliestSingleYear
ConXrefs	DateEnd	fieldCollectionDate_dateLatestYear

sql query

view: cs_fieldcollectiondates

SELECT
 CAST(o.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
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION ALL
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
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN LEN(x.displayDate) > 0 THEN x.displayDate END) fieldCollectionDate_dateDisplayDate,
 (CASE WHEN x.dateBegin != '0' THEN x.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN x.dateEnd != '0' THEN x.dateEnd END) fieldCollectionDate_dateLatestYear,
 NULL fieldCollectionDate_dateEarliestSingleDay,
 NULL fieldCollectionDate_dateLatestDay,
 NULL fieldCollectionDate_dateEarliestSingleMonth,
 NULL fieldCollectionDate_dateLatestMonth
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')
 AND (x.dateBegin != 0 OR (x.displayDate IS NOT NULL AND len(x.displayDate) > 0))
;

notes

differences in ObjDates.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 (see CSPACE-4775)

Field collection place extract

field mapping

TMS table name	TMS field name	CSpace field name	notes

sql query

view:

notes

Field collector extract

field mapping

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

sql query

view: cs_fieldcollectors

SELECT
 CAST(o.objectID AS VARCHAR) 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')
;

notes

Field collection note extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDates	Remarks	fieldCollectionNote
ConXrefs	Remarks	fieldCollectionNote

sql query

view:

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection date: ' + d.remarks fieldCollectionNote
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collector: ' + x.remarks fieldCollectionNote
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')
 AND x.remarks IS NOT NULL
ORDER BY objectID
;

notes

NOTE: multiple notes per object should be concatenated into single (non-repeating) field fieldCollectionNote, separated by line breaks