...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
ObjLocations | Handler | locationHandler | Constituents FK LocHandlers FK ObjLocations.Handler |
ObjLocations | LocationString | displayName currLocation | FK Locations.LocationID |
ObjLocations | ObjectNumber ObjectNumber | objectNumber | ObjComponents FK Objects FK ObjComponents.ComponentID |
ObjLocations | TransDate | Timestamp locationDate | Needs to become a real timestamp in CSpace |
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
LocPurposes | LocPurpose | reasonForMove |
|
ObjLocations | AnticipEndDate | plannedRemovalDate |
|
ObjLocations | DateOut | RemovalDate removalDate |
|
ObjLocations | concat(Sublevel, SearchContainer, TempText, Approver) | currentLocationNote | |
ObjLocations | CrateID | currentLocationNote | (perhaps this should not be just a note...) |
sql query
view: cs_objectlocations (refreshed 2012-05-16)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT j.ObjectNumber objectNumber, l.LocationString displayNamecurrLocation, o.Handler locationHandler handlerString, h.MTB_ConstituentID1,h.MTB_ConstituentID2,h.MTB_ConstituentID3,h.MTB_ConstituentID4,h.MTB_ConstituentID5, o.TransDate TimestamplocationDate, p.LocPurpose reasonForMove, o.AnticipEndDate plannedRemovalDate, o.DateOut RemovalDateremovalDate, o.Sublevel+o.SearchContainer+o.TempText+o.Approver+str(o.CrateID) currentLocationNote, l.LocationID locationShortID, o.CrateID, (case when o.Inactive = 1 then 'active' else 'deleted' end) miscStatus FROM ObjLocations o JOIN LocHandlers h ON h.Handler = o.Handler JOIN Locations l ON l.LocationID = o.LocationID JOIN Objects j ON j.ObjectID = o.ComponentID JOIN LocPurposes p ON p.LocPurposeID = o.LocPurposeID ORDER BY locationDate; ; |
notes
- ConstituentID1 ... ConstituentID5 from TMS.LocHandlers are the IDs for up to five handlers. The ETL needs to look these Persons up by ID in the Person Authority and create appropriate CSpace elements to get them to show up in Object Inventory records.
- Relationship records between the Objects and the Movement records will also need to be created, and (apparently) the other way around, too. The logic for this will be added here shortly.
- Perhaps the barcode process should also use the notes field? Perhaps And perhaps some details of the scanner being used might be included?
- I noticed elsewhere that the barcode scanner seems not to be 100% accurate (sometimes the scanned items was incomplete or missing). Perhaps something could be included in the file to help detect this?
- MB suggests that perhaps "inactive" records be included, and a "soft delete" perfomed performed on them. This makes sense, I think, in which case we would want to map that field to whatever CSpace uses to mark a deleted record.
- Note that the field Timestamp is a Date (in CSpace); this is going to need to be addressed before we can make much more progress.
- The migration of this dataset is dependent on the cleanup of the notes by MB. The remarks below ("for posterity") suggest that four note fields need to be combined. In fact, only one field (SearchContainer) needs to be saved.
- CrateID is being extracted, and should be saved somewhere, if necessary by extending the schema.
values for customized dropdowns
These should appear in the Object Inventory dropdowns.
Method
dbvalue | value |
|---|---|
rubbermaid | by cart (rubbermaid) |
hand | by hand |
bathtubcart | by "bathtub" cart |
hydrauliclift | by hydraulic lift |
palletjack | by pallet jack |
forklift | by forklift |
vantruck | by van/truck |
crane | By crane |
other | Other (specify in notes) |
ReasonForMove
code | label | count |
|---|---|---|
|
| 906302 |
Inventory | Inventory | 169294 |
GeneralCollManagement | General Collections Management | 81478 |
Research | Research | 46431 |
NAGPRA | NAGPRA | 41992 |
pershelflabel | per shelf label | 31507 |
Loan | Loan | 16239 |
Exhibit | Exhibit | 14765 |
ClassUse | Class Use | 12269 |
PhotoRequest | Photo Request | 896 |
Tour | Tour | 837 |
Conservation | Conservation | 821 |
| ---------------------------- |
|
AsianTextileGrant | Asian Textile Grant | 8517 |
BasketryRehousingProj | Basketry Rehousing Project | 5288 |
BORProj | BOR Project | 1150 |
BuildingMaintenance | Building Maintenance: Seismic | 64 |
CaliforniaArchaeologyProj | California Archaeology Project (CAP) | 59535 |
CatNumIssueInvestigation | Catalogue Number Issue Investigation | 816 |
DuctCleaningProj | Duct Cleaning Project | 585 |
FederalCurationAct | Federal Curation Act | 4095 |
FireAlarmProj | Fire Alarm Project | 3958 |
FirstTimeStorage | First Time Storage | 1063 |
FoundinColl | Found in Collections | 738 |
HGB Surge | HGB Surge | 221960 |
Kro20MezzLWeaponProj2011 | Kro 20 Mezz Long Weapon Boxing Project 2011 | 552 |
MarchantFlood2007 | Marchant Flood, 12/2007 | 9283 |
NAAGVisit | Native American Advisory Group Visit | 1255 |
NEHEgyptianCollectionGrant | NEH Egyptian Collection Grant | 170 |
Regattamovein | Regatta move-in | 49555 |
Regattapremoveinventory | Regatta pre-move inventory | 5040 |
Regattapremoveobjectprep | Regatta pre-move object prep. | 3352 |
Regattapremovestaging | Regatta pre-move staging | 61171 |
SATgrant | SAT grant (Saving America's Treasures) | 5617 |
TemporaryStorage | Temporary Storage | 167 |
TextileRehousingProj | Textile Rehousing Project | 9522 |
YorubaMLNGrant | Yoruba MLN Grant | 593 |
Note from MB, saved for posterity
...