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.

CalibrationTasks

Column	Required	Description	Relates to
CalibrationTaskId	Yes	-	-
WorkOrderId	Yes	-	-
IsDone	Yes	-	-
SequenceNumber	Yes	-	-
Description	Yes	-	-
EstimatedHours	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-
CalibrationSpecId	Yes	-	-

CalibrationTasksPerformed

Column	Required	Description	Relates to
CalibrationTaskPerformedId	Yes	-	-
CalibrationTaskId	Yes	-	-
ActualHours	Yes	-	-
PerformedById	Yes	-	-
CompletedOn	Yes	-	-
Notes	No	-	-
CostCenterId	No	-	-
CostCodeId	No	-	-
ServiceClassId	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-
AsFound	Yes	-	-
AsLeft	Yes	-	-
IsPassedCalibration	Yes	-	-

InspectionTasks

Column	Required	Description	Relates to
InspectionTaskId	Yes	-	-
WorkOrderId	Yes	-	-
IsDone	Yes	-	-
SequenceNumber	Yes	-	-
Description	Yes	-	-
EstimatedHours	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-
PassFailValue	Yes	-	-

InspectionTasksPerformed

Column	Required	Description	Relates to
InspectionTaskPerformedId	Yes	-	-
InspectionTaskId	Yes	-	-
ActualHours	Yes	-	-
PerformedById	Yes	-	-
CompletedOn	Yes	-	-
CostCenterId	No	-	-
CostCodeId	No	-	-
ServiceClassId	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-
IsPassedInspection	Yes	-	-

ServiceEventsDaily

The ServiceEventsDaily contains the precalculated results of the current service events. It is updated nightly.

Service Schedule - Open

SELECT *
FROM ServiceEventsDaily sed
JOIN WorkOrders wo
ON sed.LastWorkOrderId = wo.WorkOrderId
WHERE wo.deleted = 0

Service Schedule - Scheduled

SELECT *
FROM ServiceEventsDaily sed
WHERE LastWorkOrderId is null

Service Schedule - All

SELECT *
FROM ServiceEventsDaily sed

Column	Required	Description	Relates to
ServiceEventId	Yes	The unique ID of this service event	-
ModelId	Yes	Reference to the type of model that the service event refers to.	Models
ItemId	Yes	Reference to the item that the service event refers to.	Items
InsertedOn	No	-	-
TemplateWorkOrderId	Yes	The work order that is used to generate new work orders.	WorkOrders
IsScheduled	Yes	Indicates if the service event is scheduled currently.	-
NextDate	No	The next date that will trigger the service request.	-
NextMeter	Yes	The next meter reading that will trigger the service request.	-
NextTurn	Yes	The next turn (Out and back) that will generate the service request.	-
Explanation	Yes	The explanation of the current status.	-
LastWorkOrderId	No	The last work order that was generated.	-
DataAsOf	Yes	Used to determine the last time that data was updated. (GMT)	-

ServiceRequests

Service Requests - Open

SELECT sr.Number, *
FROM ServiceRequests sr
WHERE sr.Status NOT IN ('Template', 'Complete', 'Partial','Cancelled')
AND sr.Number is not null
AND sr.IsStandAlone = 1
AND sr.deleted = 0
ORDER BY sr.number

Service Requests - Closed

SELECT sr.Number, *
FROM ServiceRequests sr
WHERE sr.Status IN ('Partial','Cancelled', 'Complete')
AND sr.Number is not null
AND sr.IsStandAlone = 1
AND sr.deleted = 0
ORDER BY sr.number

Service Requests - All

SELECT sr.Number, *
FROM ServiceRequests sr
WHERE sr.Status NOT IN ('Template')
AND sr.Number is not null
AND sr.IsStandAlone = 1
AND sr.deleted = 0
ORDER BY sr.number

Column	Required	Description	Relates to
ServiceRequestId	Yes	The unique ID of this service request.	-
RequestedById	Yes	The employee making the request.	Entities
InventoryKeyId	No	Reference to the InventoryKey.	InventoryKeys
DescriptionId	No	The identifier to the description text for the Requested Service.	Use supplied RequestedService column rather than joining.
RequestedService	Yes	-	-
PriorityLevel	Yes	Indicates if the priority level of the request.	-
Note	No	Note on the request.	-
Status	No	The status of the service request.	-
Number	No	The service request number.	-
CreatedOn	Yes	When the service request was created.	-
CreatedById	Yes	The user that created the service request.	Entities
ModifiedOn	Yes	When the service request was last modified.	-
ModifiedById	Yes	The user that last modified the service request.	Entities
IsStandalone	Yes	-	-
deleted	Yes	Indicates if this record has been deleted	-
ItemId	No	The item this record is for.	Items

ServiceTasks

Column	Required	Description	Relates to
ServiceTaskId	Yes	-	-
WorkOrderId	Yes	-	-
IsDone	Yes	-	-
SequenceNumber	Yes	-	-
Description	Yes	-	-
EstimatedHours	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-
ItemId	No	The item this record is for.	Items

ServiceTasksPerformed

Column	Required	Description	Relates to
ServiceTaskPerformedId	Yes	-	-
ServiceTaskId	Yes	-	-
ActualHours	Yes	-	-
PerformedById	Yes	-	-
CompletedOn	Yes	-	-
Notes	No	-	-
CostCenterId	No	-	-
CostCodeId	No	-	-
ServiceClassId	No	-	-
CreatedById	Yes	The user that created the record.	Entities
CreatedOn	Yes	When the record was created.	-
ModifiedById	Yes	The user that last modified the record.	Entities
ModifiedOn	Yes	When the record was last modified.	-

WorkOrderCharges

Column	Required	Description	Relates to
WorkOrderChargeId	Yes	The unique ID of work order charge.	-
WorkOrderId	Yes	The unique ID of parent work order.	-
Description	Yes	Description of the charge.	-
Quantity	Yes	The quantity of this charge used by the work order.	-
CostEach	Yes	The charge per quantity.	-
CostCenterId	No	The cost center assigned to the charge.	CostCenters
costCodeId	No	The cost code assigned to the charge.	CostCodes
ServiceClass	No	The description of the service class.	-
ServiceClassNumber	No	The number of the service class.	-
WorkOrderTaskNumber	No	The sequence of the tasks on the work order.	-
CreatedOn	Yes	The date the charge was created.	-
UomId	No	The unit of measure for the charge.	-

WorkOrders

Open Work Orders

SELECT wo.WorkOrderNumber,
sr.Status,
wo.CreatedOn,
sr.note,
i.number
FROM WorkOrders wo
JOIN ServiceRequests sr
ON wo.ServiceRequestId = sr.ServiceRequestId
JOIN InventoryKeys ik
ON sr.InventoryKeyId = ik.InventorykeyId
JOIN Items i
ON ik.ItemId = i.itemId
JOIN Models m
ON i.modelId = m.modelId
WHERE Status NOT IN ('Template', 'Complete', 'Partial')
AND wo.deleted = 0
AND sr.deleted = 0
ORDER BY wo.WorkOrderNumber

Closed Work Orders

SELECT wo.WorkOrderNumber,
sr.Status,
wo.CreatedOn,
sr.note,
i.number
FROM WorkOrders wo
JOIN ServiceRequests sr
ON wo.ServiceRequestId = sr.ServiceRequestId
JOIN InventoryKeys ik
ON sr.InventoryKeyId = ik.InventorykeyId
JOIN Items i
ON ik.ItemId = i.itemId
JOIN Models m
ON i.modelId = m.modelId
WHERE Status IN ('Partial','Cancelled', 'Complete')
AND wo.deleted = 0
AND sr.deleted = 0
ORDER BY wo.WorkOrderNumber

All Work Orders

SELECT wo.WorkOrderNumber,
sr.Status,
wo.CreatedOn,
sr.note,
i.number
FROM WorkOrders wo
JOIN ServiceRequests sr
ON wo.ServiceRequestId = sr.ServiceRequestId
JOIN InventoryKeys ik
ON sr.InventoryKeyId = ik.InventorykeyId
JOIN Items i
ON ik.ItemId = i.itemId
JOIN Models m
ON i.modelId = m.modelId
WHERE Status NOT IN ('Template')
AND wo.deleted = 0
AND sr.deleted = 0
ORDER BY wo.WorkOrderNumber

Work Order History by Item Number

SELECT wo.CreatedOn,
wo.WorkOrderNumber,
sr.RequestedService,
ate.Description as AssignedTo,
cbe.Description AS CompletedBy,
wo.CompletedOn
FROM WorkOrders wo
JOIN ServiceRequests sr
ON wo.ServiceRequestId = sr.ServiceRequestId
JOIN InventoryKeys ik
ON sr.InventoryKeyId = ik.InventorykeyId
JOIN Items i
ON ik.ItemId = i.itemId
JOIN Models m
ON i.modelId = m.modelId
LEFT JOIN Entities cbe
ON wo.completedById = cbe.EntityId
LEFT JOIN Entities ate
ON wo.AssignedToId = ate.EntityId
WHERE Status NOT IN ('Template')
AND i.Number = '1000'
AND sr.Status NOT IN ('Pending', 'In Progress')
AND wo.deleted = 0
AND sr.deleted = 0
ORDER BY wo.CreatedOn`

Column	Required	Description	Relates to
WorkOrderId	Yes	The unique ID of work order.	-
WorkOrderNumber	No	The work order number that is used to track the work.	-
ServiceRequestId	Yes	The related ServiceRequest that the work order is intended to complete.	ServiceRequests
Instructions	No	Instructions for completing the work order.	-
AssignedToId	No	The employee that is currently assigned to the work order.	Entities
ScheduledDate	No	The date that the work order is scheduled to begin on.	-
CreatedById	Yes	The user that created the work order.	Entities
CreatedOn	Yes	When the work order was created.	-
ModifiedById	Yes	The user that last modified the work order.	Entities
ModifiedOn	Yes	When the work order was last modified.	-
PONumber	No	The purchase order number that relates to the work order.	-
CompletedById	No	The employee that completed the work order.	Entities
CompletedOn	No	The date that the work order was completed on.	-
CompletedNote	No	Notes that relate to completion of the work order.	-
Deleted	Yes	Indicates if this record has been deleted	-
ItemId	No	The item this record is for.	Items

Reporting Cloud Tables: Service & Calibration