...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
LocPurposes | LocPurpose | Reason for move reasonForMove |
|
ObjLocations | AnticipEndDate | Planned removal date plannedRemovalDate |
|
ObjLocations | DateOut | Removal date RemovalDate |
|
ObjLocations | concat(Sublevel, SearchContainer, TempText, Approver) | currentLocationNote | |
ObjLocations | CrateID | currentLocationNote | (not sure where to map this in CSpace yetperhaps this should not be just a note...) |
sql query
view:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT j.ObjectNumber, l.LocationString displayName, o.Handler locationHandler, o.TransDate Timestamp, p.LocPurpose reasonForMove, o.AnticipEndDate plannedRemovalDate, o.DateOut RemovalDate, o.Sublevel+o.SearchContainer+o.TempText+o.Approver+str(o.CrateID) currentLocationNote, l.LocationID locationShortID, (case when o.Inactive = 1 then 'active' else 'deleted' end) miscStatus FROM ObjLocations o JOIN Locations l ON l.LocationID = o.LocationID JOIN Objects j ON j.ObjectID = o.ComponentID JOIN LocPurposes p ON p.LocPurposeID = o.LocPurposeID ; |
...
Note from MB, saved for posterity
| Code Block |
|---|
---------------------------- Original Message ---------------------------- Subject: Storage location fields and barcode inventory fields From: "Michael T. Black" <mtblack@berkeley.edu Date: Thu, January 26, 2012 4:14 pm To: jblowe@berkeley.edu -------------------------------------------------------------------------- First, here are the fields produced by barcode inventory: * Person's name * Storage location * Object number * Timestamp And here are the fields we'll need to import from ObjLocations: * Handler (will give Person's name, when used as FK to LocHandlers and then to Constituents) * LocationID (will give Storage location when used as FK to Locations.LocationID) * ComponentID (will give Object number when used as FK to ObjComponents and then to Objects) * TransDate (will serve as Timestamp) * LocPurpose (to "Reason for move") * CrateID * AnticipEndDate (to "Planned removal date") * DateOut (to "Removal date") Need to go somewhere: * Sublevel (notes, some crates) * SearchContainer (notes, some crates) * TempText (notes, some reasons) * Approver (notes, some reasons) Not clear whether useful in CSpace: * Inactive (is working as a sort of soft delete) Not needed in CSpace: * TempFlag * LoginID * EnteredDate * TempTicklerDate * LocLevel * PrevObjLocID * NextObjLocID * SchedObjLocID * RequestedBy * ShipmentID (at least not needed now/soon) * TransStatus (at least not needed now/soon; 1=completed; 2=pending) |