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 v2.1
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
|---|---|---|---|
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 |
|
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 Object 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
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'
;
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 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:
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