...
In theory, it would be appropriate to generate Display Names via an algorithmic (and reversible) concatenation of the locations path in the hierarchy. In this case, the Display Name key would be locked and not directly editable -- editing would be done on the underlying hierarchy. This would also make it possible to support the "crate movement functionality". Caution would be needed in permitting editing, as a change to an upper level of the hierarchy would render all the barcodes for locations beneath that level unrecognizable. This would seem to dictate that the values for nodes in the hierarchy have an attribute (perhaps "immutable") for locations that could not or would not change (e.g. "buildings", or "aisles").
Also, it is necessary to create Storage Location Authority records for the Crate objects in TMS, which become Storage Locations in CSpace. This is done via a separate query based on the TMS Crates table which creates additional records in the same format as those pulled from the Locations Table.
For crates, their immediate parent is the displayName of the Location record they were keyed to. For immovable locations, their parent is Site+Room of their location.
Anchor | ||||
---|---|---|---|---|
|
Storage Location extract
The
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Locations | Active | locationStatus | convert from '1/0' to 'active/inactive' to used in dropdown in UI |
Locations | LocationID | shortIdentifier | numeric value, prepended with 'sl' |
Locations | LocationString | displayName |
|
Locations | l.Site+', '+l.Room | Parent | for use in create "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Locations | Description | locationNote | prepended with "Description: " if not empty |
sql query
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT 'sl'+ltrim(str(l.LocationID)) shortIdentifier, (case when l.active = 1 then 'active' else 'inactive' end) locationStatus, l.LocationString displayName, l.Site+', '+l.Room Parent, l.Site Site, l.Room Room, l.UnitType UnitType, l.UnitNumber UnitNumber, l.UnitPosition UnitPosition, (case when l.Description = '' then '' else 'Description: '+l.Description end) locationNote FROM locations l ; |
notes
- the five levels of the hierarchy will need to be mapped into some sort of CSpace hierarchy. The BT/NT relationships may be sufficent, but this remains to be demonstrated. At any rate the schema (and UI?) need to be modified to hold and render these values.
- there is probably some cleaning that can be identified and performed as part of this migration. TBD!
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Crates | Active | locationStatus | convert from '1/0' to 'active/inactive' to used in dropdown in UI |
Crates | crateID | shortIdentifier | numeric value, prepended with 'cr' |
Locations | LocationString+CrateNumber | displayName | CrateNumber is appended to LocationString if not appended al |
Locations | LocationString | Parent | for use in create "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Crates | CrateNumber | locationNote | prepended with "Crate: " |
sql query
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
'cr'+ltrim(str(c.crateID)) shortIdentifier,
(case when c.active = 1 then 'active' else 'inactive' end) locationStatus,
(case when CharIndex(replace(c.CrateNumber,'-',', '),l.LocationString) > 0 then l.LocationString else l.LocationString+', '+c.CrateNumber end) displayName,
l.LocationString Parent,
l.Site Site,
l.Room Room,
l.UnitType UnitType,
l.UnitNumber UnitNumber,
l.UnitPosition UnitPosition,
'CrateNumber: '+' '+c.CrateNumber CrateNumber
FROM
crates c
JOIN Locations l ON l.LocationID = c.LocationID
;
|
notes
- In some cases the CrateNumber is already included in the locationString field, in which case there is no need to append it again to the display name. A little bit of tricky SQL checks for this case.
- Nevertheless, the CrateNumber is being kept as a note, just in case.