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.
BillingHeaders
Column | Required | Description | Relates to |
BillingHeaderId | Yes | - | - |
CreatedByEntityId | Yes | The user that created the record. | Entities |
CreatedOn | Yes | When the record was created. | - |
CutOffOn | Yes | - | - |
IsPostable | Yes | - | - |
PostedOn | No | - | - |
PostedByEntityId | No | - | - |
PostingNumber | No | - | - |
ItemGroup | Yes | - | - |
BillingHourlyUsage
Column | Required | Description | Relates to |
recordId | Yes | - | - |
number | No | - | - |
startDate | Yes | - | - |
endDate | Yes | - | - |
OperatorEntityId | No | - | - |
Project | No | - | - |
CostCenterId | Yes | - | - |
CostCodeId | No | - | - |
ItemId | Yes | The item this record is for. | Items |
hoursUsed | Yes | - | - |
temp | Yes | - | - |
BillingLines
This view contains a record for each line in the billing browser.
Column | Required | Description | Relates to |
BillingLineId | Yes | The unique ID of this billing entry | - |
BillingHeaderId | Yes | The BillingEntryHeader this entry belongs to | BillingEntryHeaders |
RateSheetHeaderType | Yes | - | - |
CalculationMethod | Yes | 'Calendar (Minutes)', 'Assignment', 'Hourly', 'Calendar (Hourly)', or 'Calendar (Date)' | - |
RateSheetHeaderId | Yes | - | - |
Type | Yes | 'Charge', 'Credit', 'Retirement', 'Hourly Usage', 'Misc. Charge', 'Misc. Credit', 'Idle Charge', 'Loss Charge', 'Related Charge', 'Initial Charge', 'Return Credit' | RateSheetHeaders |
ItemId | Yes | The item this billing entry is for | Items |
Quantity | Yes | The number of assets this entry is for. Quantity is multiplied by ChargeEach to get the total charge for the entry. | - |
ChargeEach | Yes | The calculated charge per unit | - |
CostCenterId | Yes | The cost center associated with this entry | CostCenters |
CostCodeId | Yes | The cost code associated with this entry | CostCodes |
OpenedOn | Yes | The time this billing stream was opened | - |
ClosedOn | No | The time this billing stream was closed | - |
OpeningTransactionId | Yes | The ID of the transaction that caused this stream to be opened. | TransferLine, LostFound (not yet in Reporting Cloud), or BillingHourlyUsage (not yet in Reporting Cloud) |
OpeningTransactionType | No | Generally 'Transfer' or 'Retirement'. NULL for ad-hoc hourly usage entries. | - |
ClosingTransactionId | Yes | The ID of the transaction that caused this stream to be closed. | TransferLine, LostFound (not yet in Reporting Cloud), or BillingHourlyUsage (not yet in Reporting Cloud) |
ClosingTransactionType | No | Generally 'Transfer' or 'Retirement'. NULL for ad-hoc hourly usage entries. | - |
IsOnHold | Yes | Indicates that the row in the billing browser was placed on hold when the cost center was posted. | - |
RelatedChargeDescription | No | Free-text description of the related charge, e.g. 'cleaning' or 'delivery'. Only present if RelatedBillingEntryId is set. | - |
RelatedBillingLineId | No | - | - |
Message | No | - | - |
ChargeAccountNo | No | Free-text copied from the RateSheetLine | RateSheetLines |
CreditAccountNo | No | Free-text copied from the RateSheetLine | RateSheetLines |
RateSheetLineId | No | The rate sheet line that specified this charge | RateSheetLines |
Hours | No | Total hours that were billed in the period | - |
PotentialHours | No | The total number of hours in the period (both billable and un-billable) | - |
BreakdownHours | No | The hours part of Hours, when Hours is broken down into X months, X weeks, X days, and X hours. | - |
BreakdownDays | No | The days part of Hours, when Hours is broken down into X months, X weeks, X days, and X hours. | - |
BreakdownWeeks | No | The weeks part of Hours, when Hours is broken down into X months, X weeks, X days, and X hours. | - |
BreakdownMonths | No | The months part of Hours, when Hours is broken down into X months, X weeks, X days, and X hours. | - |
RatePerHour | No | The (hourly, daily, weekly, etc.) rate the asset was billed as | - |
HoursInADay | No | The number of hours in a day, as defined by the job calendar | - |
BillingRate | No | - | - |
CreatedByEntityId | Yes | The user that requested the calculation | Entities |
CreatedOn | Yes | When the calculation was requested | - |
CutOffOn | Yes | This entry was calculated for activity up until this date | - |
IsPostable | Yes | Indicates if the entry can be posted. E.g. you can calculate entries into the future, but you can only post entries created from present (or past) data. | - |
PostedOn | No | If set, then this entry was posted, at the specified time | - |
PostedByEntityId | No | The ID of the user that posted the entry | Entities |
PostingNumber | No | The number of the posting that contains this transaction | - |
ItemGroup | No | The number of the posting that contains this transaction | 'Tools & Equipment' or 'Materials & Consumables' |
BillingMiscCharges
Column | Required | Description | Relates to |
BillingMiscChargeId | Yes | - | - |
ItemId | No | The item this record is for. | Items |
CostCenterId | Yes | - | - |
CostCodeId | No | - | - |
Charge | Yes | - | - |
ChargeAccountNo | No | - | - |
CreditAccountNo | No | - | - |
Description | No | - | - |
CreatedOn | Yes | When the record was created. | - |
RateSheetHeaderType | Yes | - | - |
Type | Yes | - | - |
TransactionType | No | - | - |
TransactionId | No | - | - |
PostingBillingHeaderId | No | - | - |
PostedOn | No | - | - |
PostedByEntityId | No | - | - |
PostingNumber | No | - | - |
ItemGroup | Yes | - | - |
CostCenters
Cost Centers enable you to group and manage all job cost and billing charges. They contain links to the rate sheets and project calendar used when calculating transaction charges. Cost centers also contain other configuration options that will impact charge calculations. Cost Centers can optionally be subdivided into Cost Codes to provide a greater level of information granularity.
Column | Required | Description | Relates to |
CostCenterId | Yes | The unique ID of this Cost Center | - |
Number | Yes | A shorthand string used to refer to this Cost Center | - |
NumberSuffix | No | A suffix for the Cost Center number, typically used to represent a subjob or "extra". | - |
Description | Yes | A brief phrase describing this Cost Center | - |
IsDeleted | Yes | Indicates if this record has been deleted. | - |
Notes | No | Free-form text notes | - |
ManagerEntityId | No | Unique ID of Manager | Entities |
CustomerId | No | Unique ID of Customer | Customers |
StartDate | No | Cost Center Start Date | - |
CompletionDate | No | Cost Center Completion Date | - |
RateSheetIdTools | No | Id of Tool & Equipment Rate Sheet | RateSheetHeaders |
RateSheetIdMaterials | No | Id of Materials & Consumables Rate Sheet | RateSheetHeaders |
RateSheetIdLabor | No | Id of Labor Rate Sheet | RateSheetHeaders |
DontBillIfAssignedAfterTime | No | Tools assigned after this time are not billed for the day | - |
DontBillIfReturnedBeforeTime | No | Tools returned before this time are not billed for the day | - |
IsQuantityCreditReturnLastInFirstOut | No | 1 if LIFO is true, 0 if FIFO is true | - |
IsInactive | Yes | 1 if inactive, 0 if active | - |
AllowSelectGlobalCostCodes | Yes | 1 if allowed, 0 if not | - |
CodeGroup | No | Code Group | - |
CreatedOn | Yes | When the record was created. | - |
CreatedById | Yes | The user that created the record. | Entities |
CreatedBy | Yes | The name of the user that created the record. | - |
ModifiedOn | Yes | When the record was last modified. | - |
ModifiedById | Yes | The user that last modified the record. | Entities |
ModifiedBy | Yes | The name of the user that last modified the record. | - |
CostCodes
Column | Required | Description | Relates to |
CostCodeId | Yes | The unique ID of this CostCode | - |
Number | Yes | A shorthand string used to refer to this CostCode | - |
Description | Yes | A brief phrase describing this class | - |
IsDeleted | Yes | Indicates if this record has been deleted | - |
IsInactive | Yes | Indicates if this record is inactive. | - |
CostCenterId | No | The CostCenter this CostCode is generally associated with | CostCenters |
CodeGroup | No | Code Group | - |
CreatedOn | Yes | When the record was created. | - |
CreatedById | Yes | The user that created the record. | Entities |
CreatedBy | Yes | The name of the user that created the record. | - |
ModifiedOn | Yes | When the record was last modified. | - |
ModifiedById | Yes | The user that last modified the record. | Entities |
ModifiedBy | Yes | The name of the user that last modified the record. | - |
Customers
Column | Required | Description | Relates to |
CustomerId | Yes | Unique Id of this customer | - |
Number | Yes | A shorthand string used to refer to this customer | - |
Name | Yes | Customer Name | - |
Address1 | No | - | - |
Address2 | No | - | - |
City | No | - | - |
State | No | - | - |
Zip | No | - | - |
Country | No | - | - |
Contact | No | - | - |
Phone1 | No | - | - |
Phone2 | No | - | - |
Notes | No | - | - |
InvoiceHeaders
Column | Required | Description | Relates to |
InvoiceHeaderId | Yes | The unique id for the invoice | - |
InvoiceNumber | Yes | Number used to identify the invoice in the system | - |
InvoiceDate | Yes | The date that the invoice was created for. | - |
PaymentDue | Yes | The date that the payment is due on. | - |
BillingPeriodStart | Yes | The beginning date that the invoice covers. | - |
BillingPeriodEnd | Yes | The ending date that the invoice covers. | - |
Terms | No | The payment terms for this invoice. | - |
SalesTax | No | The sales tax rate for this invoice. | - |
EntityIdFrom | No | The entity that is sending the invoice. | Entities |
CostCenterId | Yes | The reference to the cost center that the invoice is for. | CostCenters |
StockPointId | Yes | The stock point that is generating the invoice | Entities |
IsPosted | Yes | Indicates if the invoice is based on posted billing information (0 - No, 1 - Yes). | - |
IsRetiredIncluded | Yes | Indicates if the invoice includes retired items (0 - No, 1 - Yes). | - |
CreatedOn | Yes | When the invoice was created. | - |
CreatedById | Yes | The user that created the invoice. | Entities |
ModifiedOn | Yes | When the invoice was last modified. | - |
ModifiedById | Yes | The user that last modified the invoice. | Entities |
InvoiceLines
Column | Required | Description | Relates to |
InvoiceLineId | Yes | The unique id for the InvoiceLine | - |
InvoiceHeaderId | Yes | Reference to the invoice header for this line. | Invoice Header |
Quantity | No | - | - |
ChargeEach | Yes | The cost per item that is being charged. | - |
ItemId | No | The reference to the Item being invoiced. | Items |
InventoryKeyId | No | The reference to the InventoryKey for the item being invoiced. | InventoryKeys |
CostCenterId | Yes | The reference to the cost center this line is for. | CostCenters |
CustomerId | No | The reference to the customer for this line. | Customers |
BillingLineId | No | The reference to the billing record that the invoice was generated from. The line might be missing if the invoice was generated off of unposted billing records and the billing was recalculated. Also it might be missing if the billing posting was rolled back and recalculated. | - |
CreatedById | Yes | The user that created the invoice line. | Entities |
CreatedOn | Yes | When the invoice line was created. | - |
ModifiedById | Yes | The user that last modified the invoice line. | Entities |
ModifiedOn | Yes | When the invoice line was last modified. | - |
LeaseRentalContracts
Column | Required | Description | Relates to |
LeastRentalContractId | Yes | - | - |
ItemId | Yes | The item this record is for. | Items |
VendorId | No | - | - |
Vendor | No | - | - |
VendorUnitId | No | - | - |
Contract | No | - | - |
Type | No | - | - |
Requisition | No | - | - |
StartDate | No | - | - |
EndDate | No | - | - |
ReminderDate | No | - | - |
LeaseTerm | No | - | - |
LeaseAmount | No | - | - |
ResidualValue | No | - | - |
RentalRate | No | - | - |
RateUnit | No | - | - |
Comments | No | - | - |
RateSheetHeaders
Contains a single record for every RateSheet. Individual rate lines are stored in the RateSheetLines view.
Column | Required | Description | Relates to |
RateSheetHeaderId | Yes | - | - |
Number | Yes | A shorthand string used to refer to this rate sheet | - |
Description | Yes | A brief phrase describing this rate sheet | - |
HoursInDay | Yes | Migrate to the daily rate after this many hours | - |
HoursInWeek | Yes | Migrate to the weekly rate after this many hours | - |
HoursInMonth | Yes | Migrate to the monthly rate after this many hours | - |
MigrateToDailyAt | Yes | - | - |
MigrateToWeeklyAt | Yes | - | - |
MigrateToMonthlyAt | Yes | - | - |
Type | Yes | Which type of assets this rate sheet is for, either 'Tools & Equipment' or 'Materials & Consumables' | - |
IsDeleted | Yes | Indicates if this rate sheet has been deleted | - |
CreatedOn | Yes | When the record was created. | - |
CreatedById | Yes | The user that created the record. | Entities |
ModifiedOn | Yes | When the record was last modified. | - |
ModifiedById | Yes | The user that last modified the record. | Entities |
RateSheetLines
Contains a record for each rate line specified on a Rate Sheet. Note that the rate sheet shows all models, but this table only contains records for models that the user had specified a rate for.
Column | Required | Description | Relates to |
RateSheetLineId | Yes | The unique ID of this rate sheet line | - |
RateSheetHeaderId | Yes | The Rate Sheet this line belongs to | RateSheetHeaders |
ModelId | Yes | The model this rate is for | Models |
Type | Yes | 'Job Cost' or 'Billing' | - |
IsRelatedRow | Yes | True if this row is a related charge row | - |
RelatedDescription | Yes | Free-text description of the related charge, e.g. 'cleaning' or 'delivery'. Only present if IsRelatedRow. | - |
BaseCost | Yes | The essential value of the model, used as the starting point for all rate calculations. Initially populated with the base value from the Model detail. | - |
CalculatonMethod | Yes | 'Calendar (Minutes)', 'Assignment', 'Hourly', 'Calendar (Hourly)', or 'Calendar (Date)' | - |
InitialCharge | No | The flat charge generated each time the asset is assigned | - |
ReturnCharge | No | The flat credit generated each time the asset is returned from an assignment or moved to another assignment | - |
HourlyRate | No | The hourly charge for an asset, before it's migrated to the daily rate | - |
DailyRate | No | The hourly charge once an asset has migrated to the daily rate | - |
WeeklyRate | No | The hourly charge once an asset has migrated to the weekly rate | - |
MonthlyRate | No | The hourly charge once an asset has migrated to the monthly rate | - |
MaintenanceRate | No | The amount to charge once an item has a positive ROI | - |
MaintenanceRatePromoteAt | No | The threshold at which the maintenance rate will become effective | - |
IdleRate | No | The amount to charge for non-working hours | - |
MinimumChargeHours | No | The minimum number of hours to charge, for calendar-based rates | - |
MinCharge | No | The minimum dollar amount to charge | - |
MaxCharge | No | The maximum amount to charge. See IsMaxChargeEnabledAtJob. | - |
LossCharge | No | The amount to charge if the asset is retired | - |
ChargeAccountNo | No | Free-text string to be stamped with calculated billing entries | - |
CreditAccountNo | No | Free-text string to be stamped with calculated billing entries | - |
CostCodeId | No | Default cost code to associate with this model | CostCodes |
IsMaintenanceRateEnabled | Yes | Indicates if a maintenance charge should be calculated | - |
IsIdleRateEnabled | Yes | Indicates if an idle charge should be calculated | - |
IsMinChargeEnabled | Yes | Indicates if a min charge should be calculated | - |
IsMaxChargeEnabled | Yes | Indicates if a max charge should be calculated | - |
IsLossChargeEnabled | Yes | Indicates if a loss charges should be calculated | - |
IsLossChargeDeductedFromTotal | Yes | If set, the loss charge will be reduced by the amount already charged. | - |
IsMaintenanceRateEnabledAtJob | Yes | Indicates if an asset will generate a charge during non-working hours | - |
IsMaxChargeEnabledAtJob | Yes | If enabled, the max charge is accrued at the assignment-level. Otherwise max charge is accrued to the asset itself. | - |
IsDoNotBill | Yes | If set, assets of this model will not generate any charge. This overrides all the other options. | - |
IsDeleted | No | Indicates if this record has been deleted. | - |
ModifiedOn | Yes | When the record was last modified. | - |
ModifiedById | Yes | The user that last modified the record. | Entities |