...
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 | ||||
---|---|---|---|---|
|
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Locations | Active | locationStatus | convert from '1/0' to 'activeaccepted/inactiverejected' 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 creating "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Locations | Description | locNote | prepended with "Description: " if not empty |
sql query
...
view: cs_storagelocations1 (refreshed 2012-04-26)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT 'sl'+ltrim(str(l.LocationID)) shortIdentifier, (case when l.active = 1 then 'activeaccepted' else 'inactiverejected' 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) locNote 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. At least the following should be done, either as part of this SQL or as part of other ETL:
- "Dedupe" the locations. Since Crates are now locations in their own right, they should be checked against the locations drawn from the Locations table. There seem to be some cases where Crates have been represented as locations in the Locations table.
- There are cases where the displayName for a location includes the name of the crate. Some of these cases are handled (see note below), but more investigation is warranted.
- More TBD!
field mapping
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Crates | Active | locationStatus | convert from '1/0' to 'accepted/rejects' 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 creating "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Crates | CrateNumber | locNote | prepended with "Crate: " |
sql query
view: cs_storagelocations2 (refreshed 2012-04-26)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
'cr'+ltrim(str(c.crateID)) shortIdentifier,
(case when c.active = 1 then 'accepted' else 'rejected' 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 locNote
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 locatNote, just in case.