Each table below lists its columns, whether a value is required, a description, and the table it relates to. For the terms used in these tables (tools, materials, assets, unique, and quantity), see Conventions in the Reporting Cloud Articles in the ToolWatch Reporting Cloud Overview.
Inventories
Column | Required | Description | Relates to |
ItemId | Yes | The item this record is for. | Items |
TrackingMethod | No | - | - |
EntityId | Yes | - | - |
OwnerEntityId | Yes | - | - |
Quantity | Yes | - | - |
IsPersonalIssue | Yes | - | - |
ReturnDate | No | - | - |
LastCountDate | No | - | - |
PurchaseCost | No | - | - |
PurchaseDate | No | - | - |
ReturnToEntityId | Yes | - | - |
CreatedOn | No | When the record was created. | - |
InventoryKeyId | No | - | - |
RecordId | No | - | - |
NextCountDater | No | - | - |
InventoryKeys
The InventoryKeys table provides a single, convenient ID -- an InventoryKeyId -- for each group of assets displayed in inventory. A more in depth explanation follows... ToolWatch tracks essentially two types of assets: unique and quantity. Unique assets have an individual identity, and therefore are simple to track. In ToolWatch, a unique asset can be identified by simply referring to its ItemId. Quantity assets are more complicated. Because quantity assets don't have an individual identity, we can't refer to any specific quantity tool with a unique ID. Instead, we must describe the asset we are referring to. For example, instead of saying "the hammers with ID = 2" we say something like "the used Craftsman hammers that are assigned to Joe". In ToolWatch there are six different attributes that can be used to describe a quantity asset. They are:
ItemId: specifies which item these asset(s) are an instance of (e.g. Craftsman hammers)
EntityId: the entity the asset(s) are assigned to
KitItemId: the kit the asset(s) are contained in
StatusCodeId: the status of the asset(s)
IsPersonalIssue: whether these assets were personally issued to an entity
OwnerEntityId: the stock location that owns these assets
It would be noisy to see these six columns all over the database. Instead, ToolWatch tables refer to an asset by specifying a single value that uniquely identifies the combination of the six fields. This value is the InventoryKeyId.
Column | Required | Description | Relates to |
InventoryKeyId | Yes | A unique value that represents an asset that can be described by the following six fields | - |
EntityId | Yes | The entity this asset is assigned to | Entities |
ItemId | Yes | Which item this asset is an instance of | Items |
StatusCodeId | No | The status of the asset | StatusCodes |
IsPersonalIssue | Yes | If the asset has been personally issued | - |
KitItemId | No | The kit containing this asset | Items |
OwnerEntityId | Yes | The owner of this asset | Entities |
Items
Contains a record for each instance of an asset in ToolWatch. For uniquely-tracked assets, there is a single item record for every unique asset. For quantity-tracked assets, there is just one record. This is because quantity-tracked assets are not individually distinguishable, so the item information for all instances is the same. (see the conventions section for an explanation of uniquely-tracked vs. quantity-tracked.)
Column | Required | Description | Relates to |
ItemId | Yes | The unique ID of this item | - |
ModelId | Yes | The model this item is an instance of. | Models |
Number | Yes | A shorthand string used to refer to this Item | - |
BarCode | No | A short text string used to find this item when scanning. Is usually a printed barcode label physically on the asset. | - |
SerialNumber | No | The serial number for this instance. | - |
RFID | No | The RFID identification number for this item. | - |
IsDeleted | Yes | Indicates if this record has been deleted | - |
Notes | No | Custom notes for this item | - |
CreatedOn | Yes | When the item was created. | - |
CreatedById | Yes | The user that created the item. | Entities |
ModifiedOn | Yes | When the item was last modified. | - |
ModifiedById | Yes | The user that last modified the item. | Entities |
Models
Column | Required | Description | Relates to |
ModelId | Yes | The unique ID of this model | - |
Type | Yes | 'T' for unique tools, 'Q' for quantity tools, 'K' for kits, 'M' for materials | - |
Number | Yes | A shorthand string used to refer to this number | - |
CategoryId | Yes | The category this model is organized into | Categories |
DefaultClassId | No | The class that all assets derived from this model belong to. Can be overridden at the Items level for unique assets. | Classes |
DescriptionId | Yes | The description of this model | Descriptions |
ManufacturerId | No | The manufacturer of this model | Manufacturers |
UnitOfMeasureId | No | The units this model is counted in (e.g. pallets, boxes) | UnitsOfMeasure |
ReplacementCost | No | Cost to replace one item, taking into account time, delivery fees, effort, etc. | - |
Msrp | No | The Manufacturer's Suggested Retail Price | - |
ItemBaseValue | No | The base value to use in billing rate sheets | - |
IsDoNotBill | No | If set, new Rate Sheets will be defaulted to not bill for assets derived from this model | - |
IsDeleted | Yes | Indicates if this record has been deleted | - |
ManufacturerModel | No | The manufacturer's name for the model. | - |
ManufacturerYear | No | The manufacturer's year for the model. | - |
ManufactuerPartNumber | No | - | - |
UPCNumber | No | The UPC identifier for the model. | - |
DefaultCertificationId | No | The default certification for the model. | Certifications |
CreatedOn | Yes | When the work order was created. | - |
CreatedById | Yes | The user that created the work order. | Entities |
ModifiedOn | Yes | When the work order was last modified. | - |
ModifiedById | Yes | The user that last modified the work order. | Entities |
MaterialsViewDaily
Column | Required | Description | Relates to |
asOfDate | Yes | - | - |
assignedToId | No | - | - |
itemId | No | The item this record is for. | Items |
personalIssue | No | - | - |
itemStatusId | No | - | - |
kitId | No | - | - |
ownerId | No | - | - |
masterId | No | - | - |
statusNoTransfer | No | - | - |
model | No | - | - |
itemType | No | - | - |
itemStatus | No | - | - |
description | No | - | - |
itemNumber | No | - | - |
meterLevel | No | - | - |
assignedTo | No | - | - |
lostFoundId | No | - | - |
statusCodeNumber | No | - | - |
quantity | No | - | - |
owner | No | - | - |
supervisor | No | - | - |
manager | No | - | - |
managerId | No | - | - |
kitNumber | No | - | - |
deleted | No | - | - |
lostAtId | No | - | - |
descriptionId | No | - | - |
assetHash | No | - | - |
manufacturer | No | - | - |
manufacturerId | No | - | - |
barcode | No | - | - |
category | No | - | - |
masterCategoryId | No | - | - |
rfid | No | - | - |
lastTransferLineId | No | - | - |
lastCountAdjustmentId | No | - | - |
masterNumber | No | - | - |
enclosure | No | - | - |
assignmentIsStockpoint | No | - | - |
meterUomId | No | - | - |
serialNumber | No | - | - |
itemTypeId | No | - | - |
upc | No | - | - |
uomId | No | - | - |
entitytype | No | - | - |
classId | No | - | - |
departmentId | No | - | - |
className | No | - | - |
department | No | - | - |
assetId | No | - | - |
lastTransferNumber | No | - | - |
lastTransferDate | No | - | - |
lastTransferDeliveredBy | No | - | - |
lastCountDate | No | - | - |
returnDate | No | - | - |
responsibleParty | No | - | - |
responsiblePartyId | No | - | - |
lastScannedOn | No | - | - |
MeterTransactions
Column | Required | Description | Relates to |
MeterTransactionId | Yes | The unique ID of this meter transaction. | - |
ItemId | No | The reference to the Item being invoiced. | Items |
MeterLevel | No | A shorthand string used to refer to this manufacturer | - |
CreatedOn | Yes | When the Meter Transaction was created. | - |
ScanHistory
Column | Required | Description | Relates to |
ItemId | Yes | The item this record is for. | Items |
ItemNumber | No | - | - |
TimeScanned | Yes | - | - |
ScanType | Yes | - | - |
ScannedBy | Yes | - | - |
ScannedLocation | Yes | - | - |
ScanArea | Yes | - | - |
Lat | Yes | - | - |
Long | Yes | - | - |
MacAddress | No | - | - |
StockView
Column | Required | Description | Relates to |
EntityId | Yes | - | - |
ItemId | Yes | The item this record is for. | Items |
IsPersonalIssue | Yes | - | - |
StatusCodeId | No | - | - |
KitId | No | - | - |
OwnerEntityId | Yes | - | - |
StockViewHash | Yes | - | - |
Quantity | Yes | - | - |
IsUnstockOn | No | - | - |
ItemTypeId | Yes | - | - |
LastTransferLineId | No | - | - |
LastCountAdjustmentId | No | - | - |
ToolBrowser
Contains a record for each asset as it appears within ToolWatch's Tool Browser.
Column | Required | Description | Relates to |
ModelNumber | Yes | The asset model number | Models |
Model | Yes | The asset model | Models |
ModelId | Yes | The unique ID of this model | Models |
Cost | No | The price or worth of an item | - |
ReplacementCost | No | Cost to replace one item, taking into account time, delivery fees, effort, etc. | - |
ItemType | Yes | The type of item (Q = Quantity, T = Unique, K = Kit) | - |
ManufacturerDescription | No | A brief phrase describing this manufacturer (typically the name of the manufacturer) | Manufacturer |
TrackingMethod | No | An asset's classification, uniquely-tracked or quantity-tracked | - |
ItemId | Yes | The unique ID of this item | Items |
ItemNumber | No | The shorthand string used to refer to this asset | Items |
BarCode | No | A short text string used to find this item when scanning. This is usually a printed barcode label physically on the asset. | Items |
SerialNumber | No | The serial number for this instance | Items |
Meter | No | If present, the meter reading recorded at the time of the transfer. The asset's meter was set to this value when the transfer was processed | - |
ItemNotes | No | Free-form text notes | - |
ItemCreatedOn | Yes | Item's created on date | - |
Quantity | No | The number of assets this entry is for. Quantity is multiplied by ChargeEach to get the total charge for the entry. | - |
IsPersonalIssue | Yes | If the asset has been personally issued | - |
VehicleLicenseNo | No | If the item is a vehicle, its license plate number | - |
VehicleFuelCard | No | If the item is a vehicle, its fuel card | - |
VehicleLicenseExpiration | No | If the item is a vehicle, its license plate expiration date | - |
VehicleLicenseState | No | If the item is a vehicle, the state that it's licensed. | - |
PurchaseCost | No | The unique tool's purchase cost | - |
PurchaseDate | No | The unique tool's purchase date | - |
WarrantyExpiration | No | The unique tool's warranty expiration date | - |
PurchaseOrderNumber | No | Item's purchase order number | - |
Manufacturer | No | The manufacturer name | - |
Category | Yes | The category description | Categories |
Kit | No | The description of the kit containing this asset | - |
Description | Yes | The asset description | - |
Status | No | The status code description | StatusCodes |
StatusCodeNumber | No | A shorthand string used to refer to this status code | StatusCodes |
Assignment | Yes | The description of the asset's entity assignment | - |
AssignmentEntityNumber | Yes | The assigned to entity's unique ID | Users |
Owner | No | The description of the asset's owner | - |
Manager | No | The asset manager description | - |
Department | No | A brief phrase describing this department | Department |
DepartmentId | No | The unique ID of the department associated with this asset | Departments |
Supervisor | No | The description of the entity's department supervisor | - |
Class | No | A brief phrase describing this class | Classes |
BinLocation | No | Bin location of the asset | - |
Vendor | No | The asset's vendor name | - |
ManagerId | Yes | The asset's manager id | - |
StockLocationId | Yes | The asset's stock location id | - |
StatusId | No | The asset's status id | - |
CategoryId | Yes | - | - |
AssignmentId | No | The asset's assignment id | - |
OwnerId | No | The asset's owner id | - |
ManufacturerId | No | The asset's manufacturerId | - |
UPC | No | UPC | - |
DescriptionId | No | Tool description id | - |
LastTransferLineId | No | Tool's last transfer line id | - |
LastTransferNumber | No | Tools' last transfer number | - |
LastTransferDate | No | Tool's last transfer date | - |
DeliveredBy | No | - | - |
LastTransferDeliveredById | No | The id of the last transfer | - |
ReturnDate | No | The date the tool is return to stock | - |
UOM | No | The unit of measure of the item. See ItemMeter UOM for the item's meter unit of measure | UOM |
ResponsibleParty | No | The name of the responsible party | - |
ResponsiblePartyId | No | The id of the responsible party | - |
ItemMeterUOM | No | The unit of measure of the item's meter. See UOM for the unit of measure of the item | UOM |
ToolInformation
Column | Required | Description | Relates to |
ModelRecordId | Yes | - | - |
TrackingMethod | No | - | - |
Manufacturer | No | - | - |
Category | Yes | - | - |
Model | No | - | - |
YourDescription | Yes | - | - |
ManufacturerDescription | No | - | - |
BinLocation | No | - | - |
ItemNumber | No | - | - |
BarCode | No | - | - |
AssignedTo | Yes | - | - |
Owner | Yes | - | - |
Manager | Yes | - | - |
Status | No | - | - |
Quantity | Yes | - | - |
IsPersonalIssue | Yes | - | - |
SerialNumber | No | - | - |
Department | No | - | - |
Class | No | - | - |
LicenseNo | No | - | - |
FuelCard | No | - | - |
ExpirationDate | No | - | - |
StateId | No | - | - |
Meter | No | - | - |
ToolPurchaseCostInfo
Column | Required | Description | Relates to |
ModelRecordId | Yes | - | - |
TrackingMethod | No | - | - |
Manufacturer | No | - | - |
Category | Yes | - | - |
Model | No | - | - |
YourDescription | Yes | - | - |
ManufacturerDescription | No | - | - |
BinLocation | No | - | - |
ItemNumber | No | - | - |
BarCode | No | - | - |
Cost | No | - | - |
ReplacementCost | No | - | - |
Vendor | No | - | - |
PurchaseCost | No | - | - |
PurchaseDate | No | - | - |
WarrantyExpiration | No | - | - |
PurchaseOrderNumber | No | - | - |
ItemId | Yes | The item this record is for. | Items |
ItemCreatedOn | Yes | - | - |
VehicleInformation
This table extends the Items table with information on Items that are Vehicles.
Column | Required | Description | Relates to |
ItemId | Yes | The unique ID of the item that this information relates to. | Items |
LicensePlate | No | The license plate number for the vehicle. | - |
Expiration | No | The date that the registration expires on. | - |
FuelCard | No | The fuel card number for the vehicle. | - |
LostAndFound
Column | Required | Description | Relates to |
ItemId | Yes | The reference to the Item being retired. | Items |
EntityId | Yes | The entity this asset was assigned to | Entities |
OwnerEntityId | No | - | - |
ReturnToEntityId | No | - | - |
Quantity | Yes | - | - |
IsPersonalIssue | Yes | - | - |
LastTransferId | No | - | - |
ReturnDate | No | - | - |
ItemStatusId | No | - | - |
DepartmentId | No | - | - |
RetiredByEntityId | No | - | - |
RetiredDate | Yes | The Date this asset was retired | - |
RecycleBin
Column | Required | Description | Relates to |
RecordId | Yes | - | - |
DeletedRecordType | Yes | - | - |
DeletedRecordId | Yes | - | - |
DeletedRecordNumber | No | - | - |
OriginalDescription | Yes | - | - |
DeletedOn | Yes | - | - |
DeletedBy | Yes | - | - |