PAHMA Object Inventory data mapping

This page contains information about PAHMA's data migration of Object Inventory and related info.
See parent page on PAHMA data migration ETL work.

Object Inventory

overview

Inasmuch as the migration of the locations of objects at PAHMA (and their history) is functionally similar to the (already implemented) PAHMA bar code scanner requirements inventory process implemented to support the use of barcode readers, it has been decided to "piggyback" the TMS to CSpace migration on this process.

Essentially, the Object Inventory crosswalk will be accomplished by creating file(s) of records in the "barcode file format" from TMS, and processing these records in the same way barcode files are processed.

An analysis of the existing TMS data reveals that a few more fields will need to be included; the barcode process will be extended to handle these additional fields.

field mapping, basic barcode format

TMS table name	TMS field name	CSpace field name	notes
ObjLocations	Handler	locationHandler	Constituents FK LocHandlers FK ObjLocations.Handler
ObjLocations	LocationString	currLocation	FK Locations.LocationID
ObjLocations	ObjectNumber	objectNumber	ObjComponents FK Objects FK ObjComponents.ComponentID
ObjLocations	TransDate	locationDate	Needs to become a real timestamp in CSpace

fields to be added to barcode format

TMS table name	TMS field name	CSpace field name	notes
LocPurposes	LocPurpose	reasonForMove
ObjLocations	AnticipEndDate	plannedRemovalDate
ObjLocations	DateOut	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-04-05 [may still need work])

SELECT
 j.ObjectNumber objectNumber,
 l.LocationString currLocation,
 o.Handler handlerString,
 h.MTB_ConstituentID1,h.MTB_ConstituentID2,h.MTB_ConstituentID3,h.MTB_ConstituentID4,h.MTB_ConstituentID5,
 o.TransDate locationDate,
 p.LocPurpose reasonForMove,
 o.AnticipEndDate plannedRemovalDate,
 o.DateOut removalDate,
 o.SearchContainer 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
;

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? 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" 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

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