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