...
| 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 'accepted/rejected' 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 creating "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 |
...
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 create creating "Storage Location hierarchy" |
Locations | Site | Site |
|
Locations | Room | Room |
|
Locations | UnitType | UnitType |
|
Locations | UnitNumber | UnitNumber |
|
Locations | UnitPosition | UnitPosition |
|
Crates | CrateNumber | locationNote | prepended with "Crate: " |
...
| 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 CrateNumbercrateNumber FROM crates c JOIN Locations l ON l.LocationID = c.LocationID ; |
notes
- In some cases the CrateNumber 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 crateNumber is being kept as a note, just in case.