...
TMS table name | TMS field name | CSpace field name | notes | ||||
---|---|---|---|---|---|---|---|
Objects | ObjectID | shortIdentifier |
| ||||
ObjAccession | ?? | shortIdentifier |
| ||||
Objects | ObjectNumber | acquisitionReferenceNumber |
| ObjAccession | acquisitionNumber | acquisitionReferenceNumber |
|
sql query
view:
...
SELECT
acc.objectID acquisitionID,
acc.objectNumber acquisitionReferenceNumber
FROM
Objects acc
WHERE
acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
UNION
SELECT DISTINCT
NULL acquisitionID,
upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) acquisitionReferenceNumber
FROM
ObjAccession oa
WHERE
oa.acquisitionNumber IS NOT NULL AND len(oa.acquisitionNumber) > 0
AND upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) NOT IN
(SELECT acc.objectNumber
FROM Objects acc
WHERE acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
)
ORDER BY acquisitionReferenceNumber
;
notes
...
...
ObjAccession |
...
Accessions detail extract
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
ObjAccession | AccessionMethodID | acquisitionMethod | FK to AccessionMethods table |
ObjAccession | AcqJustification | acquisitionReason |
|
ObjAccession | AcquisitionTerms | acquisitionNote |
|
ObjAccession | Source | acquisitionNote |
|
sql query
view:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT acc.objectID acquisitionID, acc.objectNumber acquisitionReferenceNumber, (CASE WHEN acc2.AccessionMethodID != 0 THEN m.AccessionMethod ELSE 'unknown' END) acquisitionMethod, -- acc2.AccessionValue, acc2.AcqJustification acquisitionReason, acc2.AcquisitionTerms acquisitionNote_1, acc2.Source acquisitionNote_2 FROM Objects acc, ObjAccession acc2, AccessionMethods m WHERE acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%' AND acc.objectID = acc2.objectID AND acc2.AccessionMethodID = m.AccessionMethodID ; |
notes
- this query is separate from the primary Accessions one above because it includes fields of type TEXT that cannot be included in a union distinct query
- AccessionValue field can be imported manually later, because only a few records have values ObjAccession.AcquisitionLot duplicates data in AcquisitionNumber, and so does not need to be imported
- ObjAccession.AccessionValue has data for only a few (<60) records, and we aren't sure where to put it; this data can be updated manually later, per Michael (1/19/12)
- the acquisitionNote_1 and acquisitionNote_2 fields in this query should be concatenated into a single field acquisitionNote, but that cannot be done in this query because fields of type TEXT do not allow concatenation
- TODO: include AccessionISODate field (here or in a separate query?)...
Accessions-Objects Relationships extact
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Objects | ObjectID | shortIdentifier for reference to Object |
|
Objects | ObjectID | shortIdentifier for reference to Accession |
|
sql query
view:
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT obj.objectID objectID, acc.objectID acquisitionID FROM Objects obj, Associations oa, Objects acc WHERE obj.IsVirtual = 0 AND obj.IsTemplate = 0 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%' AND obj.objectID = oa.ID2 AND oa.ID1 = acc.objectID ; |
notes
- TODO: include relationships for accessions created from ObjAccession.acquisitionNumber...