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

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