...
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 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 |
...
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) locationNote FROM locations l ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Crates | Active | locationStatus | convert from '1/0' to 'activeaccepted/inactiverejects' 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: " |
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT 'cr'+ltrim(str(c.crateID)) shortIdentifier, (case when c.active = 1 then 'activeaccepted' else 'inactiverejected' 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.