Versions Compared

Old Version 3

changes.mady.by.user John W Keller

Saved on

compared with

New Version 4

changes.mady.by.user John W Keller

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
maxlevel3

Field collection

...

date extract

this data cannot be imported until v2.1

...

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

 

ObjDates

Remarks

fieldCollectionNote

 

Objects

Dated

fieldCollectionDate_dateDisplayDate

 

Objects

DateBegin

fieldCollectionDate_dateEarliestSingleYear

 

Objects

DateEnd

fieldCollectionDate_dateLatestYear

 

...

Code Block
sql
sql
SELECT
 CAST(do.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.remarks
 fieldCollectionNoteJOIN FROMObject o ObjDateson 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,
 null fieldCollectionNote
FROM
 Objects o
WHERE

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

...

  • 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

...

view:

Code Block
sql
sql
notes
Field 
...
collector 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 
...
view: 
 Code Block
sql
sql
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: 
 Code Block
sql
sql

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