OpenData Data Views (Tables)
OpenData presents a highly simplified view of the actual underlying TrackAbout database.
NOTE
A typical TrackAbout customer database contains over 400 tables. It's highly normalized, which means it's designed to reduce redundant data. This also makes it very complicated to understand and query without intricate knowledge of the design.
We've designed the OpenData views to greatly simplify the effort required to query the database. Not everything in the database can be found in OpenData. If there is data you need that's not found here, please contact us and we can look into adding the data you need.
opendata.RecAssetsSetBulkDelivery
opendata.RecAssetsSetCustomAssetInfo
opendata.RecordDifferenceReasonCodes
opendata.RecordPostFillAnalytes
Devices (TrackAbout Mobile 6 only)
Entity Metadata and Classification
opendata.AssetAgreementLineItems
opendata.AssetAgreementInvoices
opendata.RentalFlatFeeOverrides
opendata.RentalFlatFeeAssetClasses
opendata.RentalBillRateDetails
opendata.RentalBillBalanceChanges
opendata.RentalBillAssetAgreements
Records and Assets
opendata.Assets
This table contains a row for each unique, serialized (tagged) asset. The Assets table contains current (not historical) details about assets.
Common uses of this table include:
-
Getting a count of all active or in-use assets
-
Getting a count of all assets in a particular use state in a particular location
-
Reporting on serialized assets currently at a particular customer or in a particular location
-
Finding the current status of a single asset given a tag number (barcode, RFID, etc.)
-
Determining assets expiring soon
| Column | Description |
|---|---|
|
|
Database-generated unique ID for each asset. |
|
|
The date this asset was first introduced to the system. |
|
|
If an asset is currently delivered to a customer, this is the internal system ID of that customer. Join to Holders for customer details. |
|
|
If an asset is currently delivered to a customer, this is the Customer ID of that customer. |
|
|
If an asset is currently in-house (at an internal location), this is the internal system ID of that location. Join to Locations for details. |
|
|
If an asset is currently in-house (at an internal location), this is the Location ID of that location. |
|
|
If an asset is currently in-house (at an internal location), this is the name of that location. |
|
|
If an asset is currently in-house (at an internal location), this is the type of that location. Possible values are: Branch, Truck, Reseller, Supplier, Sales. |
|
|
The tracking number, also known as a tag, barcode or RFID tag unique ID. |
|
|
The serial number of the asset. Usually this exists on the asset as an indelible stamp or etched into a name plate. See Serial Number for more information on how serial numbers might be scrubbed or transformed on the way into the database. |
|
|
Statuses are Active, Lost, or Retired. |
|
|
Asset Category Name as shown in TrackAbout. |
|
|
Asset Group Name as shown in TrackAbout. |
|
|
Asset Type Name as shown in TrackAbout. |
|
|
Product code, part number, catalog number. |
|
|
Designated owner of the asset, full name. |
|
|
If set in TrackAbout, this is the unique ID which is associated with the OwnershipName. May be blank. |
|
|
As assets move through their various work flows, different Use States may be applied. The names vary by TrackAbout customer. |
|
|
There are a few ways to store Expiration Date in TrackAbout. The first way we stored it (and still do for some customers) was as a value associated with an asset on a record stored in a table called RecAssetsSetExpirationDate. Values stored in that table are denormalized into the Assets table and stored in this field. Other customers may store Expiration Date in CustomAssetInfo, in which case the values will be found in a different table. |
|
|
Foreign key to the AssetTypes table. |
|
|
Foreign key to the ProductCodes table. |
|
|
Indicates if this asset is a container asset. |
|
|
Indicates if this asset is inside a container. |
|
|
Indicates if this asset is a Pack. A Pack is a group of uniquely registered assets that have been manifolded together. The system has information on BOTH the frame and the assets inside. |
|
|
Indicates if this asset is inside a Pack |
|
|
Indicates if this asset is a Bundle. A Bundle refers to a group of assets manifolded together in which ONLY the frame and product information are gathered, but there is no information pertaining to the individual assets. A bundle can be converted to a Pack when information is entered for the individual assets in the bundle. |
|
|
Returns the current lot number for an asset. This may be a Ramp Lot (when lot numbers are generated through TrackAbout) or the lot number entered into TrackAbout when lot numbers are not generated or are collected through a vendor filling the asset. |
|
|
The id of the asset's container. Foreign key to Containers. |
|
|
If an asset ownership is marked as 'Customer-owned', this is the internal system ID of that customer. Join to Holders for the ownership customer details. |
|
|
If an asset ownership is marked as 'Customer-owned', this is the Customer ID of that customer. Join to Holders for the ownership customer details. |
opendata.AssetNotes
Lets you view the notes associated with an asset.
| Column | Description |
|---|---|
AssetId
|
The asset to which this note applies, foreign key to the Assets table. |
EnterDate
|
|
UserId
|
|
UserName
|
|
Note
|
opendata.ContainerAssets
| Column | Description |
|---|---|
AssetId
|
Database-generated unique ID for each asset. |
DBEnterDate
|
The timestamp when this row was written to the database |
HolderId
|
If an asset is currently delivered to a customer, this is the internal system ID of that customer. Join to Holders for asset details. |
HolderStr
|
ID for the customer, provided to TrackAbout by you. |
HolderName
|
Name for the customer, provided to TrackAbout by you. |
LocationStr
|
If an asset is currently in-house (at an internal location), this is the Location ID of that location. |
LocationName
|
If an asset is currently in-house (at an internal location), this is the name of that location. |
TrackNum
|
The tracking number, also known as a tag, barcode or RFID tag unique ID. |
CrossRef
|
The serial number of the asset. Usually this exists on the asset as an indelible stamp or etched into a name plate. See Serial Number for more information on how serial numbers might be scrubbed or transformed on the way into the database. |
Status
|
Statuses are Active, Lost, or Retired. |
AssetCatName
|
Asset Category Name as shown in TrackAbout. |
AssetGroupName
|
Asset Group Name as shown in TrackAbout. |
AssetTypeName
|
Asset Type Name as shown in TrackAbout. |
ProductCodeName
|
Product code, part number, catalog number. |
OwnershipName
|
Designated owner of the asset, full name. |
OwnershipStr
|
If set in TrackAbout, this is the unique ID which is associated with the OwnershipName. May be blank. |
OwnerHolderStr
|
If an asset ownership is marked as 'Customer-owned', this is the internal system ID of that customer. Join to Holders for the ownership customer details. |
OwnerHolderName
|
If an asset ownership is marked as 'Customer-owned', this is the Customer ID of that customer. Join to Holders for the ownership customer details. |
UseState
|
As assets move through their various work flows, different Use States may be applied. The names vary by TrackAbout customer. |
ExpirationDate_v1
|
There are a few ways to store Expiration Date in TrackAbout. The first way we stored it (and still do for some customers) was as a value associated with an asset on a record stored in a table called RecAssetsSetExpirationDate. Values stored in that table are denormalized into the Assets table and stored in this field. Other customers may store Expiration Date in CustomAssetInfo, in which case the values will be found in a different table. |
AssetTypeId
|
Foreign key to the AssetTypes table. |
ProductCodeId
|
Foreign key to the ProductCodes table. |
LotNumber
|
Returns the current lot number for an asset. This may be a Ramp Lot (when lot numbers are generated through TrackAbout) or the lot number entered into TrackAbout when lot numbers are not generated or are collected through a vendor filling the asset. |
ContainerId
|
The id of the asset's container. Foreign key to Containers. |
opendata.Records
Every transaction committed by an end user that affects assets creates a row in the Records and RecordDetails table. There is a many-to-one relationship between RecordDetails and Records. This is necessary because multiple users can act on a single record. The Record contains those facts about a record that do not change when multiple users act upon it. RecordDetails contains the facts that do change, like which user acted on the record and when.
Many users can append assets to a record after its initial creation. It's necessary to choose a single timestamp to represent when the record "happened". TrackAbout chooses the timestamp of the earliest action as the record date, or "RecDate".
About Records and Locations
In the early days of TrackAbout, we had just one Location associated with a record. This always represented the Internal Location (distribution center, plant, factory, etc.) associated with a record. A different Records column held the Customer ID, if a customer was involved.
As TrackAbout evolved, we introduced the notion of Trucks. Trucks deliver assets to customers, and return assets back to internal locations.
We chose to model a Truck as another kind of Location. Branches, resellers, sales depots, suppliers and trucks are all different kinds of Locations stored in the Locations table.
Following this change, two locations could now be involved in a single transaction. Loading a truck moves assets from an internal location (distribution center, plant, factory) to a truck. Delivering assets moves assets from a Truck to a Customer (which is not a Location), but we still want to capture the originating location (plant, DC, etc.) with the record.
A new table was added to capture the "OriginLocationId". For our OpenData offering, we are bringing both locations into the Records table. They will be called Location and OriginLocation.
-
For a delivery, the OriginLocation will be the plant, DC, etc. The Location associated with the record will be the truck.
-
For a Truck Load, the OriginLocation will be the plant, DC, etc. The Location on the record will be where the asset ended up — on the truck.
-
For a Truck Unload, the OriginLocation will be the truck. The Location on the record will be where the asset ended up — the plant, DC, etc.
|
Column |
Description |
|---|---|
|
|
System-generated unique ID for the record. |
|
|
Denormalized from RecordDetails, this is the ID of the row in RecordDetails that was the earliest actor on the record. We say this actor created the record. |
|
|
The denormalized timestamp from RecordDetails. This will be the timestamp of the earliest action that created the record. |
|
|
The internal TrackAbout ID for the action. Can be useful for more accurately filtering on particular action types and not relying on string names for actions. |
|
|
The type of action being performed (Add New, Deliver, etc.). |
|
|
Is this action location-setting? Some action types change the physical location of an asset, and some do not. This column contains a 1 if the record changed the physical location, and 0 if not. |
|
|
This is internal system ID of the customer. Not every record will have a customer. Join to Holders for customer details. |
|
|
A "holder" is your customer. This is the full name of the customer. Not every record will have a customer. |
|
|
This is a string containing your Customer ID. This comes from your back-end systems. Not every record will have a customer. |
|
|
If the record was deleted, contains a 1. Otherwise, 0. |
|
|
For deliveries, the provided invoice/order/delivery number will be stored here. |
|
|
If provided during delivery, the purchase order number. |
|
|
If entered during delivery, the name entered into the Signer field. |
|
|
The internal integer TrackAbout ID of the internal location or truck. |
|
|
Your company's ID of the internal location or truck. |
|
|
The full name of the internal location or truck. |
|
|
The type of the Location: Branch, Reseller, Sales, Supplier, Truck |
|
|
The ID of the origin location or truck. |
|
|
The full name of the origin location or truck. |
|
|
The type of the origin location: Branch, Reseller, Sales, Supplier, Truck |
|
|
One of: Mobile Unit, Web Site, Bulk Load, Auto Enter, [Invoice] Comparison, Wireless Unit, Legacy Import, Linked Exchange, Web Service, External Import, TrackAbout API, TrackAbout Mobile 7 (iOS and Android). The [Invoice] word is normally replaced in TrackAbout's app servers with a customer-preferred value like "Order", "Delivery" or something else. |
|
|
Lookup ID for the UserDetails table. |
|
|
First name of the user. |
|
|
Last name of the user. |
|
|
The ID of the user in the accounting system. |
|
|
Lookup ID for the DeviceDetails table. Only rugged TrackAbout Mobile 6 devices are tracked. Smartphone/tablet devices are not tracked in the same way. |
|
|
Specifies whether the record delivers or returns assets from a customer. |
|
|
Indicates whether this record resulted in the creation of a new customer that did not previously exist in TrackAbout. |
|
|
The internal system ID of the user who verified the record. |
|
|
The first name of the user who verified the record. |
|
|
The last name of the user who verified the record. |
|
|
The ID in the accounting system of the user who verified the record. |
|
|
The date the record was verified. This is in the UTC time zone. |
|
|
Indicates whether the record is verified, and if so, how it was done. Possible values are: 0 : NotVerified 1 : VerifiedByUser 2 : VerifiedByInvoiceComparison 3 : VerifiedByBalanceComparison 4 : VerifiedOnRecordSaveComparingToInvoices 5 : AutoVerifiedByWebsite 6 : AutoVerified |
|
|
Indicates whether the record is verified, and if so, how it was done. See the HowVerified column for possible values. |
|
|
Indicates whether or not this record is marked for Investigation by a client administrator. Possible values are: 0 : NotUnderInvestigation 1 : Investigate |
|
|
Indicates whether or not this record is marked for Investigation by a client administrator. See the InvestigationStatus column for possible values. |
|
|
If True, signifies that the assets on this record have been altered since the record was verified. |
opendata.RecordDetails
The nature of RecordDetails is explained in the prior section about Records.
There is a many-to-one relationship from RecordDetails to Records. There can be many RecordDetails rows for a single Record.
In the TrackAbout web UI, RecordDetails rows are represented as "Addendums" to Records.
| Column |
Description |
|---|---|
|
|
System-generated unique ID for the record. Foreign key to the Records table. |
|
|
System-generated unique ID for this RecordDetails row. This is the primary key of this table. |
|
|
A timestamp is captured when the user begins an action in the mobile software. This is that timestamp. If the action/record is created by the web site, this timestamp will be equal to RecDate as we do not time actions submitted via the web site user interface. |
|
|
The timestamp when the user saved the record to the device. |
|
|
The timestamp when this row was written to the database. |
|
|
Indicates the source of the data, whether it's from a mobile device, the Web UI, an API, file imports, etc. |
|
|
Indicates the value in the accounting system for the source of the data. |
|
|
Foreign key to the UserDetails table. |
|
|
The Id of the user in the accounting system. |
|
|
User's first name. |
|
|
User's last name. |
|
|
Foreign key to the DeviceDetails table, if TrackAbout Mobile 6 (not TrackAbout Mobile 7 for iOS and Android) was used to save the record. |
|
|
Purchase order number entered by the user. |
|
|
Name typed by the signer of a delivery. |
|
|
Indicates whether or not the user manually keyed in the Customer ID. |
|
|
Indicates whether or not the user manually keyed in the Invoice Number for the record. |
|
|
Indicates whether or not the user manually keyed in the Purchase Order number. |
opendata.Geolocation
See TA Smartphone - Geolocation.
Geolocation data is collected on TrackAbout Mobile 7 (iOS and Android) only.
The mobile app takes a geolocation reading for every asset scanned. Those readings are in this table and are referenced from the RecAssets table.
| Column | Description |
|---|---|
|
|
Database-generated unique id for this particular geolocation reading. |
|
|
Self-explanatory. |
|
|
Self-explanatory. |
|
|
How accurate the device believes the reading to be. |
|
|
Self-explanatory. |
|
|
Timestamp when the reading was taken. |
opendata.RecAssets
The two primary entities in TrackAbout are Records and Assets. RecAssets is the linkage between Records and Assets. A Record tells you who did something, when and where. RecAssets links to which assets were acted upon.
| Column |
Description |
|---|---|
|
|
System-generated ID of the Record, foreign key to the Records table. |
|
|
System-generated ID of the Asset, foreign key to the Assets table. |
|
|
Foreign key to RecordDetails table, which contains the details of who performed the action and when. |
|
|
The denormalized timestamp from Records. This will be the timestamp of the earliest action that created the record. |
|
|
This column is Denormalized from Records. This column contains a 1 if the record changed the physical location, and 0 if not. |
|
|
If 1, indicates the asset is being delivered to a Holder (aka Customer). Value will be 0 in all other cases. 0 on a Delivery record means the asset is being returned from a Customer to a truck or in-house location. In all other cases except delivery to customer, this will be 0. |
|
|
The precise time this row was written to the database. |
|
|
Tag, barcode or other unique serialized tracking number. |
|
|
Serial number of the asset (not a tracking number). |
|
|
Indicates whether the user manually entered the barcode rather than scanning it. |
|
|
Indicates what method was used to search for and find this asset in order to attach it. Possible values are 0 : NotSet 1 : ByTrackNum 2 : ByCrossRef 3 : ByBoth 4 : ByAssetId |
|
|
Indicates what method was used to search for and find this asset in order to attach it. See FoundBy column for possible values. |
|
|
The internal system ID of the asset type for this asset. Join to AssetTypes for details. |
|
|
Asset category |
|
|
Asset group |
|
|
Asset type |
|
|
Internal ID and foreign key to the opendata.ProductCodes view. |
|
|
Product code (aka part number, SKU, etc), denormalized from opendata.ProductCodes for convenience. |
|
|
Longer description of the product code, denormalized from opendata.ProductCodes for convenience. |
|
|
Reference to a row in the Geolocations table, or NULL |
|
|
If the record relating to this RecAssets row collected a Lot Number, then this column shall contain the Lot Number assigned to this asset on that specific record. If the record did not collect lot information, then this column shall contain NULL. |
|
|
The volume of the asset as specified in this record, if applicable. |
|
|
The records assets container id. Foreign key to Containers. |
|
|
The bar code of the container. |
|
|
The serial number of the container. |
opendata.RecContainers
This table associates containers with records.
| Column |
Description |
|---|---|
|
|
System-generated unique ID for the record. Foreign key to Records. |
|
|
System-generated unique ID for the container. Foreign key to Containers. |
|
|
System-generated unique ID for the record detail. Foreign key to RecordDetails. |
|
|
System-generated unique ID for the asset. Foreign key to Asset. |
|
|
Bar code of the asset. |
|
|
Serial number of the asset. |
|
|
System-generated unique ID for the asset type. Foreign key to AssetTypes. |
|
|
The asset category name of the asset type. |
|
|
The asset group name of the asset type. |
|
|
The name of the asset type. |
|
|
System-generated unique ID for the product code. Foreign key to ProductCodes. |
|
|
The name of the product code. |
|
|
The description of the product code. |
opendata.RecHardGoods
Records the quantity and type of Hard Goods or Consumables assets attached to a given record.
| Column | Description |
|---|---|
|
|
System-generated unique ID for the RecHardGoods. |
|
|
The record this hard good is in. Foreign Key to Records. |
|
|
The hard good product code. Foreign Key to HardGoodProductCodes. |
|
|
The quantity for this line. |
|
|
The OrderItemOwnerFlag for the Hard Good. Foreign Key to OrderItemOwnerFlags. |
|
|
Geolocation ID associated with the hard good. Foreign Key to GeoLocation. |
|
|
Optional lot number associated with the hard goods on a record |
opendata.RecAssetsSetBulkDelivery
| Column |
Description |
|---|---|
|
|
The Record Id. |
|
|
The Asset Id. |
DeliveredQuantity
|
The quantity delivered. |
DeliveredItemNumber
|
The Item Number of the Product that was delivered. |
IgnoredInPlanning
|
Whether this should be ignored in planning of future deliveries. |
RecDate
|
Denormalized from Records |
UnitOfMeasureName
|
The long name of the unit of measure specified during delivery. |
UnitOfMeasureAbbreviation
|
The standard abbreviation of the unit of measure. |
opendata.RecAssetsSetCustomAssetInfo
On any given record, custom asset info may be set by the user. This table contains those values that were set on a specific record at a specific point in time. This table therefore contains the historical record of all custom properties that have ever been set on assets.
You could derive the most recent or current asset properties from this table by looking at the most recent values set on each asset, but we have denormalized that data into CustomAssetInfo already. Use CustomAssetInfo if you want the asset properties as of "right now".
| Column |
Description |
|---|---|
|
|
System-generated ID of the Record, foreign key to the Records table. |
|
|
System-generated ID of the Asset, foreign key to the Assets table. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
|
|
Same definitions as CustomAssetInfo table, refer to it. |
opendata.RecAssetsSetUseState
See the page Use States for a description of the feature.
When a use state is set on a particular asset on a particular record, this view will contain the value that was set. This view contains all use states for all assets for all time.
To find the historical value of a use state of an asset at a specific point in time in the past, consult this view.
To find the current use state of an asset as of right now, see the opendata.Assets view.
To look up the name of the use state, see the opendata.UseStates view.
| Column |
Description |
|---|---|
RecordId
|
Composite primary key. The record on which the value is set. |
AssetId
|
Composite primary key. The asset on which the value is set. |
UseStateId
|
The Use State value. Look up in opendata.UseStates view. |
RecDate
|
Denormalized date the record occurred, from Records |
opendata.RecAssetsSetVolume
When volume data is collected for an asset on a particular record, this view will contain the value that was set, along with the units of measure specified.
This view contains all volume values set for all time.
This table should be joined against opendata.RecAssets using both RecordId and AssetId. By doing so, you'll be able to retrieve the volume set on that specific asset on that specific record.
NOTE
TrackAbout collects and stores volume in a few different ways, depending on which features are being used. Some volumes are stored in this view, and some volumes may be stored in CustomAssetInfo. If you do not see the volume data you require here or in CustomAssetInfo, please make a request through Support to investigate adding it.
| Column | Description |
|---|---|
RecordId
|
Composite primary key. The record on which the value is set. |
AssetId
|
Composite primary key. The asset on which the value is set. |
Volume
|
The volume recorded for the asset on this record. |
RecDate
|
Denormalized date the record occurred, from Records |
UnitOfMeasureName
|
The long name of the unit of measure specified when collecting the volume. |
UnitOfMeasureAbbreviation
|
The standard abbreviation of the unit of measure. |
opendata.RecordDifferenceReasonCodes
Stores the recorded reasons when there were discrepancies in the record compared to the expected quantities.
| Column |
Description |
|---|---|
Id
|
System-generated unique ID for the RecordDifferenceReasonCodes. |
RecordId
|
The id for the record. Foreign Key to Records. |
ReasonCodeId
|
The id for the reason code. Foreign Key to RecordDifferenceReasonCodes. |
ProductCodeId
|
The id of the product code, when this is associated with a product code, otherwise NULL. Foreign Key to ProductCodes. |
HardGoodProductCodeId
|
The id of the hard good product code, when this is associated with a hard good product code, otherwise NULL. Foreign Key to HardGoodProductCodes. |
OrderItemOwnerFlagId
|
The id of the ownership flag. Foreign Key to OrderItemOwnerFlags. |
opendata.RecordFormData
TrackAbout's Dynamic Form module allows for custom workflows to be designed and deployed to mobile software. The fields on those custom forms may represent properties of a Record which acts ono assets, or they may be values that need to be propagated to the assets attached to the record.
If the data collected on a dynamic form is to be associated with the attached assets, that data will be replicated into the RecAssetsSetCustomAssetInfo table.
Record-only fields (fields not propagated to assets) will only be found here in this table.
| Column |
Description |
|---|---|
RecordId
|
System-generated ID of the Record, foreign key to the Records table. |
FormFieldId
|
A foreign key to a table that is not currently exposed in OpenData. Only useful for TrackAbout personnel. |
FieldName
|
The label of the field as displayed on the dynamic form. |
FieldValue
|
The value collected in this field by the user. |
FieldSuffix
|
May contain a value indicating a unit of measure. |
CustomAssetInfoTypeId
|
Foreign key to the CustomAssetInfoTypes table which contains definitions of all custom asset info types configured in your TrackAbout account. This column is provided so that you may exclude rows containing a value in the column in order to restrict your view to select only Record-level (not asset-level) fields. |
opendata.RecordGeolocation
In addition to geolocation coordinates collected for each asset scan (found in RecAssets linking to the Geolocations tables), we collect one geolocation at the start of a record, and one at the end when the record is saved by the user. This table contains the IDs of those two readings to be found in Geolocations.
| Column | Description |
|---|---|
|
|
System-generated unique ID of this reading. |
RecordId
|
The record on which the reading was taken. Foreign key to Records. |
GeolocationId
|
Foreign key to Geolocations table which contains the details of the reading. |
RecDetailId
|
Foreign key to |
opendata.RecordPostFillAnalytes
In certain gas and chemical production, a chemical analysis is performed on the contents of a filled container. This view contains the details of the specific analytes under test. An analyte is a substance whose chemical constituents are being identified and measured. You may expect zero-to-many rows in this table for each Post-Fill test conducted.
| Column |
Description |
|---|---|
Id
|
System-generated primary key id for this row. |
RecordId
|
Foreign key to the Records table. Analysis information is recorded at the Record-level and not the asset level, even though specific assets will likely be attached to this record. |
Analyte
|
The analyte tested. |
Value
|
The measurement collected for this analyte by the user. |
UnitOfMeasure
|
The unit of measure entered by the user. |
Passed
|
A bit field indicating whether or not the test passed or failed. 0 means failed, 1 means passed. |
UncertaintyValue
|
Indicates the uncertainty of the analysis, if recorded by the user. |
UncertaintyUnitOfMeasure
|
Indicates the unit of measure for the uncertainty of the analysis. |
opendata.RecordPostFillDetails
In certain gas and chemical production, a chemical analysis is performed on the contents of a filled container. This view contains the details of the analysis. You should expect a single row in this table for each Record in which a post-fill analysis was performed.
| Column |
Description |
|---|---|
RecordId
|
Foreign key to the Records table. The row in the Records table will specify an Action type consistent with a post-fill analysis. |
LotNumber
|
The Lot Number entered during Post-Fill Analysis. |
TestMethod
|
The testing method used (single or batch). |
TrackNumTested
|
The unique tracking number of the container or asset being tested. |
TestTiming
|
When the test occurred - 0 = PostFill (default), 1 = PreFill |
AssetTypeId
|
The Asset Type for the Analysis record. |
ProductCodeId
|
The Product Code for the Analysis record. |
opendata.RecordSubActions
Every record has an action name, like "Locate", "Deliver", "Load Truck" or "Maintenance".
A record may also have one or more subactions. A subaction is commonly used in maintenance scenarios, where a single maintenance action performs several activities on one or more assets. Each activity is tracked as a subaction.
Because of the one-to-many relationship between a Record and its actions, a separate table is necessary to list the subactions that may exist on a single record.
| Column | Description |
|---|---|
RecordId
|
Foreign key to Records table. Links the record to this subaction. |
SubActionName
|
The name of the subaction performed. |
Not-Scanned Assets on Records
opendata.RecAssetsNS
Records the quantity and type of Not-Scanned assets attached to a given record, always involving a Holder (Customer), which impacts that holder's balance.
| Column | Description |
|---|---|
RecordId
|
System-generated unique ID for the RecAssetsNS. |
AssetTypeId
|
The Asset Type of the NS attached. |
RecDetailId
|
The RecordDetails entry that attached the NS assets. |
AssetCount
|
Quantity of NS attached. |
IsDeliver
|
Indicates whether the NS assets attached are delivers or returns. |
Id
|
Primary key |
OrderItemOwnerFlagId
|
The Order Item Owner Flag for the Not Scanned Assets, if applicable. |
ProductCodeId
|
Optional product code for tracking not scanneds down to the product code level. |
ReturnCode
|
An optional value that indicates why the NS assets are being returned |
ReturnCodeOptionId
|
Represents an option ID for one of the CustomAssetInfoOptions corresponding to the Return Code CustomAssetInfoType. |
GeolocationId
|
Geolocation ID associated with the NS assets. |
IsFullReturn
|
Used for full complaints to set asset as full |
LotNumber
|
Optional lot number associated with the not-scanned assets on a record |
opendata.RecAssetsFailedRNS
Records the quantity of Returned Not-Scanned assets that could not be attached at the time of record save due to insufficient balance at the time of the attempt. These can sometimes be retried later when conditions change.
| Column | Description |
|---|---|
RecordId
|
System-generated unique ID for the RecAssetsFailedRNS. |
RecDetailId
|
Composite primary key. The record detail entry that attempted to attach the RNS. |
AssetTypeId
|
Composite primary key. The type of the RNS that failed to attach. |
Quantity
|
The quantity of RNS assets that could not be attached. |
HolderId
|
Denormalized from Records |
RecDate
|
Denormalized from Records |
NonRetriableQuantity
|
The quantity of RNS that failed to attach and which cannot be retried. |
OrderItemOwnerFlagId
|
The Order Item Owner Flag, if applicable. |
Id
|
The primary Key. |
ProductCodeId
|
The Product Code, if applicable. |
IsFullReturn
|
Used for full complaints to set asset as full |
opendata.RecNonHolderNSAssets
For tracking quantities and types of not-scanned assets attached to records that do not involve a Holder (Customer).
| Column | Description |
|---|---|
|
|
System-generated unique ID for the RecNonHolderNSAssets. |
|
|
The record for these not-scanned assets. |
|
|
The product code for these not-scanned assets. |
|
|
The count of not-scanned assets. |
|
|
Geolocation ID associated with the not-scanned assets. |
|
|
The lot number for the not-scanned assets. |
Addresses
opendata.Addresses
Addresses stores billing and shipping addresses. Holders, Orders, and AssetAgreementInvoices contain columns that reference this table.
| Column |
Description |
|---|---|
|
|
TrackAbout's internal ID for the address. This will be referenced by other tables, such as Orders |
|
|
Address line 1 |
|
|
Address line 2 |
|
|
Address line 3 |
|
|
Address line 4 |
|
|
Address line 5 |
|
|
Address line 6 |
|
|
City |
|
|
Region / State |
|
|
Subregion, if applicable |
|
|
Postal Code or Zip Code |
|
|
Country |
|
|
Address footer 1 |
|
|
Address footer 2 |
Asset Grouping
opendata.AssetFamilyCollections
Models the grouping for asset families. An asset family collection is a grouping of compatible asset families that have been grouped together to form a collective unit.
| Column | Description |
|---|---|
AssetFamilyCollectionId
|
System-generated unique ID for the asset family collection. |
AssetFamilyCollectionName
|
The collection name. |
opendata.AssetFamilies
Models asset families, which provide a structure for grouping similar products.
| Column | Description |
|---|---|
AssetFamilyId
|
System-generated unique ID for the asset family. |
AssetFamilyName
|
The name of the asset family. |
Description
|
The description of the asset family. |
AssetFamilyCollectionId
|
The internal system ID of the asset family collection that this asset family belongs to. Join to AssetFamilyCollections for details. |
IsActive
|
Indicates whether this asset family is currently active. |
opendata.AssetFamilyMembers
Models the asset classifications that belong to an asset family.
| Column |
Description |
|---|---|
AssetFamilyMemberId
|
System-generated unique ID for the asset family member. |
AssetFamilyId
|
The internal system ID of the asset family that this classification belongs to. Join to AssetFamilies for details. |
AssetCatId
|
The internal system ID of the asset category for this asset family member. |
AssetGroupId
|
The internal system ID of the asset group for this asset family member. A value of 0 indicates all asset groups. |
AssetTypeId
|
The internal system ID of the asset type for this asset family member. A value of 0 indicates all asset types. |
ProductCodeId
|
The internal system ID of the product code for this asset family member. A value of 0 indicates all product codes. |
opendata.CollisionAssets
When a unique match cannot be found during the attach of an asset, a Collision might occur. Users must resolve collisions before the asset can be attached. Full details of each collision are stored on disk in serialized XML.
| Column | Description |
|---|---|
CollisionId
|
System-generated unique ID for the CollisionAssets. |
RecDetailId
|
Points to the RecDetail entry on which this CollisionAsset was created. |
IsResolved
|
Indicates whether or not the collision has been resolved. |
LastAutoResolveAttemptDateUtc
|
The last time an auto-resolve was attempted for this collision. |
LastAutoResolveAttemptStatus
|
The status of the last auto-resolve attempt for this collision. |
IsHidden
|
Hides collisions from reporting views. This field is set manually in the database to exclude certain older collisions from reporting views by customer request. |
CollisionData
|
Xml data containing details of the collision. |
opendata.Containers
This table contains a row for each unique container.
| Column |
Description |
|---|---|
Id
|
System-generated unique ID. Primary key of the container. |
AssetId
|
System-generated ID of the asset. Foreign key to Assets. |
UseStateId
|
System-generated ID of the use state. Foreign key to opendata.UseStates. |
UseState
|
The Client-assigned name of this use state. |
CrossRef
|
Serial number of the container. |
TrackNum
|
The bar code of the container. |
Customers
opendata.Holders
These are your Customers.
| Column | Description |
|---|---|
HolderId
|
System-generated unique ID for this Holder. Primary key. |
ParentId
|
Reference back to this table to indicate parentage in a hierarchical parent-child relationship. Will be 0 for top-level (parent) holders. |
HolderStr
|
ID for the customer, provided to TrackAbout by you. |
HolderName
|
Name for the customer, provided to TrackAbout by you. |
FOTHolderName
|
Name for the customer, provided by the Customer Tracking Portal admin or you. |
FOTHolderStr
|
ID for the customer, provided by the Customer Tracking Portal customer admin or you. |
FollowOnType
|
0 = This customer is not enabled for Customer Tracking Portal user. 1 = This customer is enabled only for Customer Tracking Portal Viewing, not Tracking 2 = This customer is fully enabled for Customer Tracking Portal Tracking and will have associated users in the UserDetails table. |
TimeZoneId
|
Self-explanatory. |
IsActive
|
1 if this Holder is enabled, 0 if not. |
RentalBillEmailTo
|
The email address to which Rental Bills are sent. |
BillToAddressId
|
A reference to Addresses. Indicates the billing address on file for the customer. |
ShipToAddressId
|
A reference to Addresses. Indicates the shipping address on file for the customer. |
opendata.HolderNSBalance
Contains the current balance for Delivered Not-Scanned and Returned Not-Scanned assets for each customer (Holder), asset type and product code.
| Column | Description |
|---|---|
|
|
The customer having the balance. Join to Holders for customer details. |
|
|
The balance of Delivered Not-Scanned assets. |
|
|
The balance of Returned Not-Scanned assets. |
|
|
The Asset Type of the not-scanned asset. Join to AssetTypes. |
|
|
If tracking Not-Scanned assets down to the Product Code level, this contains the product code of the not-scanned asset. Join to Product Codes. |
opendata.HolderServicingLocations
Each Holder can have a number of servicing locations. These are internal locations that may deliver goods to that holder. There can be a single primary servicing location specified in this view.
| Column | Description |
|---|---|
Id
|
System-generated unique ID for the HolderServicingLocations. |
HolderId
|
The HolderId for which the Servicing Location is being specified. |
ServicingLocationId
|
The Servicing Location associated with the Holder. |
IsPrimary
|
Indicates if this Servicing Location is treated as the primary Servicing Location for the Holder. |
opendata.CustomerAuditUserEnteredAuditNames
View to see the user-entered customer audit names in ungrouped audit records.
| Column | Description |
|---|---|
RecordId
|
System-generated unique ID for the record. |
UserEnteredCustAuditName
|
When a user saves a Customer Audit record on the mobile device, they enter a free text name. The admin uses these names to group records into a single Customer Audit. |
Devices (TrackAbout Mobile 6 only)
opendata.DeviceDetails
These are TrackAbout Mobile 6 devices. TrackAbout does not model or track smartphone/tablet devices in the same way.
|
Column |
Description |
|---|---|
DeviceId
|
System-generated unique ID for this device. Primary key. |
LastSyncDate
|
Timestamp when this device last synced. |
DeviceOS
|
Operating system of this device, to the best of our ability to gather. Not all devices report this. |
DeviceName
|
Name that you assigned this device. |
TAMobileVersion
|
Current installed version as of the last sync. It's possible that a sync resulted in an update being sent down to the device, in which case this might not be accurate if the install succeeded. |
LocationId
|
The location to which this device is assigned. Foreign key to Locations. |
DeviceDesc
|
User-given description of the device. |
ShowOnDashboard
|
Indicates whether this device's details will be displayed on the Dashboard. |
HolderId
|
For Customer Tracking Portal users who have devices, this field indicates the customer/holder to which this device is assigned. |
SerialNumber
|
User-entered serial number of the device. This is usually on a sticker on the device placed there by the manufacturer. |
CribNumber
|
The Crib Number that this device collects information about. Applicable to handhelds used by Holders. |
InventoryLocationId
|
The Inventory of this Truck or Branch will be sent down to the device if the Point of Delivery module is being used. |
DeliveryRouteId
|
A device may be mapped to a Delivery Route. The Delivery Route controls which orders are sent to the device during sync. This is for the Point of Delivery feature. |
Entity Metadata and Classification
opendata.AssetTypes
This table contains the Asset Types used for classifying assets.
| Column | Description |
|---|---|
AssetTypeId
|
System-generated unique ID for this Asset Type. Primary key. |
AssetTypeName
|
The name of the AssetType. |
AssetGroupId
|
Unique identifier of the Asset Group. |
AssetGroupName
|
The name of the parent AssetGroup. |
AssetCatId
|
Unique identifier of the Asset Category |
AssetCatName
|
The name of the parent AssetCategory. |
Status
|
The status of this AssetType. 0 = Offline 1 = Active 2 = Retired |
IsInAccountingSystem
|
Indicates whether or not this AssetType exists in the client's accounting system. Some Asset Types exist only in TrackAbout. |
Description
|
A description of this asset type, typically used for display on rental bills. |
ReplacementPrice
|
If this asset is lost, this value represents the price the client will charge the customer to replace the lost asset. These values are displayed on some rental bills so the customer knows how much value they are carrying. |
IsExchangeType
|
Indicates whether this is an Exchange Asset Type. Special logic and rules apply to Exchange Asset Asset Types. |
IsBulkTank
|
Indicates whether this asset is a Bulk Tank. Special logic and rules apply to Bulk Tanks. |
RentalClassId
|
The internal system ID of the Rental Class to which this AssetType is assigned. Join to RentalClasses for details. |
RentalClassName
|
The name of the Rental Class to which this AssetType is assigned. |
opendata.CustomAssetInfo
CustomAssetInfo contains current asset properties, not historical changes. The most recently set values are stored in the CustomAssetInfo table.
There can be many rows in CustomAssetInfo for a single asset, because one asset can have many different properties.
Custom Asset Info (CAI) is a TrackAbout feature that allows customers (you) to define new fields for assets. Any number of custom fields can be created. Dynamic Forms are designed that show on the mobile software and collect this custom asset info from users.
Which Value column is populated will depend on the DataType of the custom asset info. The value column can be NULL if the user did not enter data into an optional field.
| Column |
Description |
|---|---|
AssetId
|
Primary key into the Assets table. |
Name
|
The given name of the Custom Asset Info type. |
TypeId
|
Unique system-generated ID key for the specific type of Custom Asset Info. |
DataType
|
One of String, Integer, Date, DateOnly (ignore any time component), Decimal, Boolean, OptionList. |
StringValue
|
If DataType is 'String', there may be a value here. |
IntegerValue
|
If DataType is 'Integer', there may be a value here. |
DateValue
|
If DataType is 'Date' or 'DateOnly', there may be a value here. If DataType is 'Date', the date is in the UTC time zone. If DataType is 'DateOnly', no time zone conversion is done on the date before storing it in the table. The time component should be ignored if DataType is 'DateOnly' |
DecimalValue
|
If DataType is 'Decimal', there may be a value here. |
BooleanValue
|
If DataType is 'Boolean', there may be a value here. |
OptionId
|
If DataType is 'OptionList', there may be a value here. This is the internal system ID of the option. |
OptionDisplayName
|
If DataType is 'OptionList', there may be a value here. This is the friendly display name of the option field. |
OptionValue
|
If DataType is 'OptionList', there may be a value here. This is the value chosen by the user. If the CAI type was a multi-select list, there can be multiple values having the same TypeId here. |
Value
|
Contains the appropriate StringValue, IntegerValue, DateValue, DecimalValue, BooleanValue or OptionDisplayName based on the DataType. |
opendata.CustomAssetInfoTypes
This table contains the definitions for all custom fields. If a field's DataType is OptionList, the options for the list are stored in CustomAssetInfoOptions.
| Column |
Description |
|---|---|
TypeId
|
System-generated unique ID. Primary key |
Name
|
The non-localized name of this type. |
DataType
|
Indicates the underlying data type of the CustomAssetInfoType value. |
HasOptions
|
Defines whether or not this CustomAssetInfoType has multiple options that would be contained in CustomAssetInfoOptions. |
AllowMultiselect
|
Whether or not an option-based CustomAssetInfoType allows for multiple selections from the list of options. |
FormatString
|
Usually used for date formatting, allows for the explicit specification of how the field value should be inputted by the user |
BooleanOptionNames
|
Set to two concatenated strings separated by a # character. The two strings will be used to indicate True and False in the display. If not set, True and False will be shown. |
ForceDayOption
|
When the type is Date, we can force all inputted dates to be changed to the first day of the month or the last day of the month. |
IsVersioned
|
Designates whether or not this CustomAssetInfoType is versioned, meaning that the value, when entered, is stored in a table relative to the Records table named RecordsSetCustomAssetInfo, thereby creating a versioned history. |
opendata.CustomAssetInfoOptions
For custom fields in the CustomAssetInfo table with DataType "OptionList", this table contains the list of options.
| Column |
Description |
|---|---|
OptionId
|
System-generated unique ID. Primary key |
DisplayName
|
Non-localized display name for this option. |
ValueToStore
|
The value assigned to this option item. |
TypeId
|
Indicates which CustomAssetInfoType this option is for. |
IsDisabled
|
Indicates whether this Custom Asset Info is disabled. Disabled custom asset info options are not displayed in the dropdowns in the site or mobile software. |
SortOrder
|
Sort ordering in the UI. |
opendata.CustomInfoTypeCategories
This table specifies which type of entity a custom property defined in the CustomInfoTypes table points to.
| Column | Description |
|---|---|
Id
|
System-generated unique ID. Primary key |
CategoryName
|
The name of the category. |
opendata.CustomInfoTypes
This table contains the definitions for custom fields for different entities like Asset Types, Product Codes, Locations, Holders, etc. If a field's DataType is OptionList, the options for the list are stored in CustomInfoOptions.
| Column |
Description |
|||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
TypeId
|
System-generated unique ID. Primary key |
|||||||||||||||||||||
CategoryId
|
Foreign Key to the CustomInfoTypeCategories table. This value determines which table would become the foreign key to the ItemId column in the CustomInfo table. The value of CategoryId and the corresponding Foreign Key table is as follows:
|
|||||||||||||||||||||
Name
|
The non-localized name of this type. |
|||||||||||||||||||||
DataType
|
Indicates the underlying data type of the CustomInfoType value. |
|||||||||||||||||||||
HasOptions
|
Defines whether or not this CustomInfoType has multiple options that would be contained in CustomInfoOptions. |
|||||||||||||||||||||
AllowMultiselect
|
Whether or not an option-based CustomInfoType allows for multiple selections from the list of options. |
|||||||||||||||||||||
FormatString
|
Usually used for date formatting, allows for the explicit specification of how the field value should be inputted by the user |
|||||||||||||||||||||
BooleanOptionNames
|
Set to two concatenated strings separated by a # character. The two strings will be used to indicate True and False in the display. If not set, True and False will be shown. |
|||||||||||||||||||||
ForceDayOption
|
When the type is Date, we can force all inputted dates to be changed to the first day of the month or the last day of the month. |
opendata.CustomInfoOptions
For custom fields in the CustomInfo table with DataType "OptionList", this table contains the list of options.
| Column | Description |
|---|---|
OptionId
|
System-generated unique ID. Primary key |
DisplayName
|
Non-localized display name for this option. |
ValueToStore
|
The value assigned to this option item. |
TypeId
|
Indicates which CustomInfoType this option is for. |
IsDisabled
|
Indicates whether this Custom Asset Info is disabled. Disabled custom asset info options are not displayed in the dropdowns in the site or mobile software. |
opendata.CustomInfo
CustomInfo contains current properties for different entities like Asset Types, Product Codes, Locations, etc. Historical changes are not stored for these properties.
There can be many rows in CustomInfo for a single entity, because one entity can have many different properties.
Custom Info (CI) is a TrackAbout feature that allows customers (you) to define new fields for different entities. Any number of custom fields can be created.
Which Value column is populated will depend on the DataType of the custom asset info. The value column can be NULL if the user did not enter data into an optional field.
| Column | Description |
|---|---|
ItemId
|
Primary key into the table representing the entity. The type of the entity is defined by the corresponding CategoryTypeId in the CustomInfoTypes table. Please refer to CustomInfoTypes for information about the different tables that this column may point to. |
Name
|
The given name of the Custom Info type. |
TypeId
|
Unique system-generated ID key for the specific type of Custom Info. |
DataType
|
One of String, Integer, Date, DateOnly (ignore any time component), Decimal, Boolean, OptionList. |
StringValue
|
If DataType is 'String', there may be a value here. |
IntegerValue
|
If DataType is 'Integer', there may be a value here. |
DateValue
|
If DataType is 'Date' or 'DateOnly', there may be a value here. If DataType is 'DateOnly' then ignore or truncate the time component. |
DecimalValue
|
If DataType is 'Decimal', there may be a value here. |
BooleanValue
|
If DataType is 'Boolean', there may be a value here. |
OptionDisplayName
|
If DataType is 'OptionList', there may be a value here. This is the friendly display name of the option field. |
OptionValue
|
If DataType is 'OptionList', there may be a value here. This is the value chosen by the user. If the CAI type was a multi-select list, there can be multiple values having the same TypeId here. |
opendata.DifferenceReasonCodes
Stores the list of reasons that can be used for recording discrepancies in the record compared to the expected quantities.
| Column |
Description |
|---|---|
Id
|
System-generated unique ID for the DifferenceReasonCodes. |
Code
|
The code used by third party systems for this reason. |
Description
|
The description for this code. |
ReasonType
|
The type of records this reason applies to. |
IsDeactivated
|
Flag to deactivate a code. |
opendata.HardGoodProductCodes
This tables contains the Hard Goods or Consumables defined in the system.
| Column |
Description |
|---|---|
ProductCodeId
|
System-generated unique ID for the HardGoodProductCodes. |
ProductCodeName
|
The name for the hard goods product code |
ProductCodeDescription
|
The description for the hard goods product code |
IsDeactivated
|
If this value is 1, then the product code is no longer used. |
IsLotNumberRequired
|
Indicates whether or not the lot number is required when delivering a hard good with this product code. |
IsLotNumberRequiredDuringPick
|
Indicates whether or not lot numbers are to be collected for this hard good during pick. |
IsLotNumberRequiredDuringLoad
|
Indicates whether or not lot numbers are to be collected for this hard good during load. |
IsLotNumberRequiredDuringUnload
|
Indicates whether or not lot numbers are to be collected for this hard good during unload. |
opendata.ProductCodes
This tables contains the Product codes / part numbers /catalog numbers defined in the system.
| Column | Description |
|---|---|
ProductCodeId
|
System-generated unique ID for this Product Code. Primary key. |
ProductCodeName
|
The name of the product code. This is the value used in the accounting system. |
ProductCodeDescription
|
A description of the product code. |
ShortDescription
|
A short description, mainly used for display in mobile applicaions. |
AssetTypeId
|
The AssetType to which the product code is mapped. Foreign key to the AssetTypes table. |
UNNumber
|
The United Nations Hazardous Materials Classification. |
UNHazardIdentifier
|
The United Nations Hazard Class Identifier. |
IsVolumeRequiredDuringDelivery
|
Indicates whether gas volume needs to be collected during delivery of assets of this type. |
FixedDefaultVolume
|
This column contains a fixed volume amount for this product code. The volume delivered when an asset of this product code is delivered is fixed. The handheld will assign this default value, and the user will not have to manually enter it. |
IsEmpty
|
Specifies whether this ProductCode is used for empty assets. |
EmptyProductCodeId
|
For Product Codes not designated as empty (through the IsEmpty column), specifies the corresponding empty product code for this product code. Foreign key to itself. |
ImportSetting
|
Indicates how this product code is imported. Possible values are 0 = Import And Compare 1 = Do Not Import 2 = Import And Compare And If Accounting Is Lower Then Add NS |
Status
|
0 = Active 1 = Offline 2 = Obsolete |
UnitOfMeasureName
|
The unit of measure used for measuring volume or weight for this Product Code. |
opendata.UseStates
See the page Use States for a description of the feature.
This is a lookup view in which you can find the string names of the various Use States.
Changes to Use States will be recorded in opendata.RecAssetsSetUseState.
The present value of an asset's Use State is denormalized in opendata.Assets.
| Column | Description |
|---|---|
UseStateId
|
Primary key |
TAGivenName
|
The TrackAbout-assigned name of this Use State. |
ClientGivenName
|
The Client-assigned name of this Use State. |
IsEmpty
|
If true, indicates that this use state indicates that the asset is empty. |
Integration
opendata.IntegrationMessageQueue
Stores integration messages that have been sent to, or are queued for sending to external accounting systems.
For messages that are not associated with a record, only the last 90 days of data are available in this table. The MessageXml column is purged for successfully transmitted messages that are older than 90 days.
| Column | Description |
|---|---|
MessageId
|
System-generated unique ID for the IntegrationMessageQueue. |
DateAdded
|
Indicates when the message was added. |
DateLastAttempted
|
Indicates when the last attempt to transmit the message was made. |
MessageStatus
|
The current status of the message. |
MessageStatusDescription
|
The description of the current status of the message. |
MessageType
|
The type of message being queued. |
MessageTypeDescription
|
The description of the message type being queued. |
MessageSubType
|
Tracks the subtype of the message. Not all message types have subtypes. A value of 0 indicates that the message has no subtype |
MessageSubTypeDescription
|
The description of the subtype of the message being queued. |
RecordId
|
The record this message is associated with, if applicable. |
MessageXml
|
The serialized message content. This field will be empty for messages older than 90 days that have already been sent. |
NumberOfTries
|
The number of times this message has been attempted to be transmitted. |
Locations
opendata.Locations
These are internal locations like branches, resellers, sales depots, suppliers and trucks. Trucks are treated as a Location.
| Column |
Description |
|---|---|
LocationId
|
System-generated unique ID of this Location. Primary key. |
LocationStr
|
ID of this location provided by you, the TrackAbout customer. |
LocationName
|
Name of this location provided by you, the TrackAbout customer. |
IsDisabled
|
If this location has been taken offline, IsDisable=1. Otherwise, 0. |
LocationTypeId
|
Indicates the type of location (e.g. Branch, Reseller, Sales, Supplier, Truck) |
TimeZoneId
|
Self-explanatory |
PhoneNumber
|
Phone Number of this location provided by you, the TrackAbout customer. |
Orders
opendata.AllOrders
AllOrders contains all orders that have been created in TrackAbout, including deleted and canceled orders.
| Column | Description |
|---|---|
OrderId
|
The primary key used by TrackAbout to identify the order. This will be the id to use in OrderItems and OrderHardGoodItems |
OrderNumber
|
Order Number for the order. This is also referred to by Document Number, Invoice or Delivery Number |
DocumentType
|
This field has two values: Delivery for items delivered to a customer, and Transfer for items moved from one location to another |
OriginatingLocationStr
|
The location ID for the location where the order originates. |
OriginatingLocationName
|
The location name for the location where the order originates. |
HolderStr
|
When the DocumentType is Delivery, this will be the ID of the Customer receiving the order. For Transfers, this will be null. |
HolderName
|
When the DocumentType is Delivery, this will be the name of the Customer receiving the order. For Transfers, this will be null. |
DestinationLocationStr
|
When the DocumentType is Transfer, this will be the ID of the location the order will be transferred to. For Delivery, this will be null. |
DestinationLocationName
|
When the DocumentType is Transfer, this will be the name of the location the order will be transferred to. For Delivery, this will be null. |
PlannedDeliveryDate
|
The date the delivery is planned to be made. |
PurchaseOrder
|
The purchase order number for the order. |
OrderedDate
|
The date the order was placed. |
DeliveredDate
|
If the order has been delivered, this will be the date of the delivery, otherwise it will be null. |
DeliveredRecordId
|
If the order has been delivered, this will be the record ID of the delivery, otherwise it will be null. |
TripNumber
|
The trip number this order is scheduled to be delivered with. |
DeliverySequence
|
The sequence that this delivery will happen on the trip. |
SalesOrderNumber
|
For systems with multiple deliveries per order, the order number will be the delivery number and this will hold the overall sales order number. |
BillToAddressId
|
A reference to Addresses to indicate which address this order should be billed to. |
ShipToAddressId
|
A reference to Addresses to indicate which address this order should be shipped to. |
DeliveryTimeWindow
|
The allowed time window for delivery to the customer. |
OrderCommentsToPrint
|
Comments on the order that will be printed on the order receipt. |
OrderCommentsToDisplay
|
Comments on the order that will be displayed to the device user during delivery. |
OrderType
|
0 = Pending 1 = Invoiced 2 = FillWorkOrder |
DeliveryRouteName
|
The name given to the delivery route to which this order is assigned. |
Status
|
The status of the order. Possible values are Active, Deleted and Canceled. |
CancelledByUserId
|
If the order was canceled, the internal system ID of the user who canceled the order. Join to UserDetails for details. |
CancelledDate
|
If the order was canceled, the date the cancellation was done. |
CancellationReason
|
If the order was canceled, the reason code for the cancellation. |
opendata.Orders
Orders contains all non-deleted orders that have been created in TrackAbout.
| Column | Description |
|---|---|
OrderId
|
The primary key used by TrackAbout to identify the order. This will be the id to use in OrderItems and OrderHardGoodItems
|
OrderNumber
|
Order Number for the order. This is also referred to by Document Number, Invoice or Delivery Number |
DocumentType
|
This field has two values: Delivery for items delivered to a customer, and Transfer for items moved from one location to another |
OriginatingLocationStr
|
The location ID for the location where the order originates. |
OriginatingLocationName
|
The location name for the location where the order originates. |
HolderStr
|
When the DocumentType is Delivery, this will be the ID of the Customer receiving the order. For Transfers, this will be null. |
HolderName
|
When the DocumentType is Delivery, this will be the name of the Customer receiving the order. For Transfers, this will be null. |
DestinationLocationStr
|
When the DocumentType is Transfer, this will be the ID of the location the order will be transferred to. For Delivery, this will be null. |
DestinationLocationName
|
When the DocumentType is Transfer, this will be the name of the location the order will be transferred to. For Delivery, this will be null. |
PlannedDeliveryDate
|
The date the delivery is planned to be made. |
PurchaseOrder
|
The purchase order number for the order. |
OrderedDate
|
The date the order was placed. |
DeliveredDate
|
If the order has been delivered, this will be the date of the delivery, otherwise it will be null. |
DeliveredRecordId
|
If the order has been delivered, this will be the record ID of the delivery, otherwise it will be null. |
TripNumber
|
The trip number this order is scheduled to be delivered with. |
DeliverySequence
|
The sequence that this delivery will happen on the trip. |
SalesOrderNumber
|
For systems with multiple deliveries per order, the order number will be the delivery number and this will hold the overall sales order number. |
BillToAddressId
|
A reference to Addresses to indicate which address this order should be billed to. |
ShipToAddressId
|
A reference to Addresses to indicate which address this order should be shipped to. |
DeliveryTimeWindow
|
The allowed time window for delivery to the customer. |
OrderCommentsToPrint
|
Comments on the order that will be printed on the order receipt. |
OrderCommentsToDisplay
|
Comments on the order that will be displayed to the device user during delivery. |
OrderType
|
0 = Pending 1 = Invoiced 2 = FillWorkOrder |
DeliveryRouteName
|
The name given to the delivery route to which this order is assigned. |
opendata.OrderItems
OrdersItems contain the order lines for non-hard good items
| Column |
Description |
|---|---|
OrderId
|
The Id of the order. A reference to Orders |
OrderNumber
|
The Order Number from Orders |
LineNumber
|
The line number for this line. |
ProductCodeId
|
The TrackAbout id of the product code for this line. |
ProductCodeName
|
The name of the product code. |
BillingCode
|
The accounting or billing code for this line |
OrderQuantity
|
The number of items requested to be delivered. |
ReturnQuantity
|
The number of items requested to be picked up. |
Comments
|
Comments specific to this line. |
opendata.OrderAssets
OrderAssets contains specific asset line items assigned to the order.
| Column |
Description |
|---|---|
Id
|
A unique id for the line item. |
HolderId
|
The Holder (customer) assigned to the order. |
CreateDate
|
The date and time the record was created in UTC. |
LocationId
|
The location to which the order is being delivered. This applies only to internal branch transfers as opposed to normal customer delivery orders. |
AssetId
|
The id of the specific asset that is part of the order. |
AccountingLineNumber
|
The line number the asset entry is associated with. |
FilledByRecordId
|
The RecordId that Filled this asset for this Order. |
ReservedByRecordId
|
If using asset reservations, the RecordId that reserved the assets for this order. |
opendata.OrderHardGoodItems
OrdersHardGoodItems contain the order lines for non-hard good items
|
Column |
Description |
|---|---|
OrderId
|
The Id of the order. A reference to Orders |
OrderNumber
|
The Order Number from Orders |
LineNumber
|
The line number for this line. |
ProductCodeId
|
The TrackAbout id of the hard good product for this line. |
ProductCodeName
|
The name of the hard good product. |
BillingCode
|
The accounting or billing code for this line. |
OrderQuantity
|
The number of items requested to be delivered. |
ReturnQuantity
|
The number of items requested to be picked up. Note that this will be 0 until work is completed to support returning hard goods. No date for this feature is currently set. |
Comments
|
Comments specific to this line. |
Rental
opendata.AssetAgreements
This view stores the Rental Asset Agreements belonging to different customers. Asset agreement is an agreement between you and your customer to not charge a customer rent for a specific type and quantity of assets for a specified amount of time. This has the effect of reducing a customer’s monthly rental by the quantity of assets on the Asset Agreement.
Each Asset Agreement can have one or more line items, which are stored in the AssetAgreementLineItems view. Each line item can be associated with one or more Rental Classes or Asset Types. They are stored in the AssetAgreementAssets table.
| Column | Description |
|---|---|
AssetAgreementId
|
System-generated unique ID for this Asset Agreement. Primary key. |
AgreementNumber
|
The agreement number assigned to this Asset Agreement. |
AgreementType
|
The type of the Asset Agreement. |
HolderId
|
The internal system ID of the customer assigned to this Asset Agreement. Join to Holders for customer details. |
HolderStr
|
The ID of the customer assigned to this Asset Agreement. |
HolderName
|
The name of the customer assigned to this Asset Agreement. |
OriginalStartDateUtc
|
The date when the Agreement first went into effect. |
CurrentStartDateUtc
|
The starting date from which the Asset Agreement is in effect. |
CurrentEndDateUtc
|
The ending date up to which the Asset Agreement is in effect. |
TermInMonths
|
The number of months for which the Asset Agreement is in effect. This is also the number of months between the CurrentStartDateUtc and CurrentEndDateUtc |
RenewalTermInMonths
|
Indicates the new duration of the agreement in months when the agreement is renewed. |
PurchaseOrder
|
The Purchase Order number against which the Asset Agreement was created. |
PaymentTerms
|
The payment terms that is applicable to this asset agreement. |
CustomerAgreesToAutoRenew
|
Specifies whether the agreement can be automatically renewed on expiration, without additional confirmation from the customer. |
LegacyAgreementNumber
|
The earlier agreement number of this agreement in the legacy system before it was imported into TrackAbout. |
AccountingInvoiceNumber
|
The Invoice Number used in the client's accounting system. |
NotifyUserOfExpiration
|
Indicates whether the Agreement will appear in the Expiring Agreements page. |
RentalBillId
|
The internal system ID of the rental bill that the renewal for this asset agreement was billed on. A NULL value means the renewal has not been billed yet. Join to RentalBills for details. |
Note
|
Additional notes associated with this Asset Agreement. |
opendata.AssetAgreementLineItems
This view stores the line items corresponding to each Asset Agreement. Each line item can be associated with one or more Rental Classes or Asset Types. They are stored in the AssetAgreementAssets table.
|
Column |
Description |
|---|---|
AssetAgreementLineItemId
|
System-generated unique ID for this Asset Agreement Line Item. Primary key. |
AssetAgreementId
|
The internal system ID of the Asset Agreement that this line item is associated with. Join to AssetAgreements for details of the Asset Agreement. |
LineItemIndex
|
A numeric index specifying the position of the line item. |
Quantity
|
The quantity of assets covered by this line item. |
UnitCost
|
The line item unit cost that is charged when billing the Asset Agreement. Multiply by Quantity to obtain the total cost for the line item. |
opendata.AssetAgreementAssets
This view stores the Asset Classes that are covered by each Asset Agreement line item. The items covered can be either at the Rental Class or Asset Type level.
| Column |
Description |
|---|---|
AssetAgreementAssetId
|
System-generated unique ID for this Asset Agreement Asset. Primary key. |
AssetAgreementLineItemId
|
The internal system ID of the Asset Agreement line item that this row is associated with. Join to AssetAgreementLineItems for details of the line item. |
Type
|
Indicates whether this row specifies a Rental Class or Asset Type that is covered. Possible values are RentalClass and AssetType. |
RentalClassId
|
The internal system ID of the Rental Class that is covered. Will have a non-null value when the Type is RentalClass. |
RentalClassName
|
The name of the Rental Class that is covered. Will have a non-null value when the Type is RentalClass. |
AssetTypeId
|
The internal system ID of the Asset Type that is covered. Will have a non-null value when the Type is AssetType. Join to AssetTypes for details. |
AssetCatName
|
The category of the Asset Type that is covered. Will have a non-null value when the Type is AssetType. |
AssetGroupName
|
The group of the Asset Type that is covered. Will have a non-null value when the Type is AssetType. |
AssetTypeName
|
The name of the Asset Type that is covered. Will have a non-null value when the Type is AssetType. |
opendata.AssetAgreementInvoices
Stores the invoices associated with an Asset Agreement.
| Column |
Description |
|---|---|
AssetAgreementInvoiceId
|
System-generated unique ID for the asset agreement invoice. |
AssetAgreementId
|
The Asset Agreement to which this invoice belongs. Join to AssetAgreements for details. |
InvoiceNumber
|
The invoice number. |
InvoiceDateUtc
|
The UTC date this invoice was generated. |
InvoiceDateLocal
|
The local date this invoice was generated. This is the local time based on the Time Zone of the Headquarter. |
InvoiceType
|
The type of the invoice. Possible values are: 0: Invoice was printed 1: Invoice sent through billing system |
InvoiceTypeDescription
|
The type of the invoice. Possible values are: Printed and SentToBilling. |
UserId
|
The internal system ID of the user generating the invoice. Join to UserDetails for details. |
UserFirstName
|
The first name of the user generating the invoice. |
UserLastName
|
The last name of the user generating the invoice. |
AgreementStartDateUtc
|
The UTC start date of the agreement period for which this invoice was generated. |
AgreementStartDateLocal
|
The Local start date of the agreement period for which this invoice was generated. This is the local time based on the Time Zone of the Headquarter. |
AgreementEndDateUtc
|
The UTC end date of the agreement period for which this invoice was generated. |
AgreementEndDateLocal
|
The UTC end date of the agreement period for which this invoice was generated. This is the local time based on the Time Zone of the Headquarter. |
CustomerAgreesToAutoRenew
|
Whether the Agreement gets renewed on expiration. |
PaymentTerm
|
The payment term for this agreement. |
BillHolderId
|
The internal system ID of the customer being billed in this invoice. Join to Holders for details. |
BillHolderStr
|
The ID of the customer being billed in this invoice. Join to Holders for details. |
BillHolderName
|
The name of the customer being billed in this invoice. Join to Holders for details. |
BillToAddressId
|
The internal system ID of the bill-to address of the customer being billed. Join to Addresses for details. |
ShipToAddressId
|
The internal system ID of the ship-to address of the customer being billed. Join to Addresses for details. |
RemitToAddressId
|
The remittance address for this invoice. Join to Addresses for details. |
Total
|
The total of the Invoice, before any applicable tax. |
TotalTax
|
The tax applicable on this invoice. |
GrandTotal
|
The grand total including tax. |
TaxableAmount
|
The total taxable amount of the Invoice. |
PurchaseOrder
|
The purchase order number for the associated asset agreement. |
CityTax
|
The city tax applied to this invoice. |
StateTax
|
The state tax applied to this invoice. |
CountyTax
|
The county tax applied to this invoice. |
BetweenStatesTax
|
The inter-state applied to this invoice. |
CityTaxRate
|
The applicable city tax rate for this invoice. |
StateTaxRate
|
The applicable state tax rate for this Invoice. |
CountyTaxRate
|
The applicable county tax rate for this invoice. |
TaxRateBetweenStates
|
The applicable inter-state tax rate for this invoice. |
opendata.RentalClassGroups
Models the grouping for Rental Classes. Rental Classes are grouped within Rental Class Groups.
| Column | Description |
|---|---|
RentalClassGroupId
|
System-generated unique ID for the rental class group. |
Name
|
User-given name of this rental class group. |
IsEditable
|
Indicates whether this is a permanent Group, or whether a user is allowed to edit/delete it. |
SortOrder
|
Controls sort ordering for lists. Larger numbers sink to the bottom. |
opendata.RentalClasses
A Rental Class is a grouping of Asset Types for which rental is calculated.
| Column | Description |
|---|---|
RentalClassId
|
System-generated unique ID for the rental class. |
Name
|
User-given name of this rental class. |
GroupId
|
A reference to the Rental Class Group to which this rental class is assigned. Join to RentalClassGroups for details. |
GroupName
|
The name of the Rental Class Group to which this rental class is assigned. |
IsEditable
|
Indicates whether this is a permanent rental class, or whether a user is allowed to edit/delete it. |
RentalBillingPeriodTypeName
|
The name of the Rental Billing Period Type that will include assets belonging to this rental class. |
TaxCategoryName
|
The name of the Tax Category that this rental class is assigned to. A Tax Category may be marked as tax exempt, which will make the rental class tax exempt. |
TaxCategoryAccountingCode
|
The accounting code of the Tax Category that this rental class is assigned to. A Tax Category may be marked as tax exempt, which will make the rental class tax exempt. |
AccountingCode
|
The Accounting Code for this rental class. |
CostPercentage
|
The cost incurred for renting out an asset, as a percentage of the rental charge. |
HoldEquipmentRentalBillIfNotInspected
|
Indicates whether Equipment Rental Bills will be automatically put on Hold if an asset has not been inspected after return from a customer. |
opendata.RentalRateTables
There can be several different tables used for rental calculation. There's always one Standard rate table at Id = 0. There may be additional Bracketed tables to which many customers are assigned, and specific customer tables.
| Column | Description |
|---|---|
RentalRateTableId
|
System-generated unique ID for the rental rate table. |
Name
|
The user-given name of this rental rate table. |
Notes
|
User-specified notes or description of this rental rate table. |
Type
|
The type of table. Possible values are: Standard, Bracketed, and Customer. |
HolderId
|
If the type of the table is Customer, indicates the internal system ID of the customer the table is assigned to. Join to Holders for details |
HolderName
|
If the type of the table is Customer, indicates the name of the customer the table is assigned to. |
HolderStr
|
If the type of the table is Customer, indicates the ID of the customer the table is assigned to. |
opendata.RentalRates
Contains either RentalClass or AssetType rental rates used during bill calculation. These rates may belong to any of a number of Rental Tables such as the Standard Rate Table or a specific Bracketed or Holder table.
| Column | Description |
|---|---|
RentalRateId
|
System-generated unique ID for the rental rate. |
RentalRateTableId
|
The ID of the rental rate table to which this rental rate belongs. Join to RentalRateTables for details. |
RentalRateTableName
|
The name of the rental rate table to which this rental rate belongs. |
Type
|
Indicates whether this rate applies to rental classes (RC) or asset types (AT). |
RentalClassId
|
The ID of the rental class, if type is RC, otherwise null. Join to RentalClasses for details. |
RentalClassName
|
The name of the rental class, if type is RC, otherwise null. |
AssetTypeId
|
The ID of the asset type if type is AT, otherwise null. Join to AssetTypes for details. |
AssetCatName
|
The asset category name of the asset type if type is AT, otherwise null. |
AssetGroupName
|
The asset group name of the asset type if type is AT, otherwise null. |
AssetTypeName
|
The name of the asset type if type is AT, otherwise null. |
RentalMethodId
|
The ID of the rental method to be used for this rental rate. |
RentalMethodName
|
The name of the rental method to be used for this rental rate. |
DailyRate
|
The daily rental rate. |
WeeklyRate
|
The weekly rental rate. |
MonthlyRate
|
The monthly rental rate. |
LastChangedUtc
|
This is the date the rate was last changed. |
TierMinimumBalance
|
The minimum balance for which this rate applies. |
TierMaximumBalance
|
The maximum balance for which this rate applies. A value of NULL indicates there is no maximum. |
opendata.RentalRatesAudit
The audit table for the RentalRates table. Tracks the who/what/when of changes to rental rates.
| Column | Description |
|---|---|
RentalRatesAuditId
|
System-generated unique ID for the RentalRatesAudit. |
UserId
|
The internal system ID of the user making the rate change. Join to UserDetails for details. |
UserName
|
The name of the User making the Rate change. |
ChangeDateUtc
|
The date the change was made. |
RentalRateChangeAction
|
The type of the change. Possible values are: Created, Updated, and Deleted. |
RentalRateTableId
|
The internal system ID of the rental rate table to which this row belongs. Join to RentalRateTables for details. |
RentalRateTableName
|
The name of the rental rate table to which this row belongs. |
RentalClassId
|
The internal system ID of the corresponding rental class if this is for a rental class based rate, otherwise null. Join to RentalClasses for details. |
RentalClassName
|
The rental class name if this is for a rental class based rate, otherwise null. |
AssetTypeId
|
The internal system ID of the corresponding asset type if this is for an asset type based rate, otherwise null. Join to AssetTypes for details. |
AssetTypeName
|
The asset type name if this is a for an asset type based rate, otherwise null. |
OldRate
|
Details of the rental rate prior to the change. When rental rates are created, this value is null. The details are stored in XML format. |
NewRate
|
Details of the updated or new rental rate. When rental rates are deleted, this value is null. The details are stored in XML format. |
opendata.RentalFlatFees
This view stores the different Rental Flat Fees that can be added to rental bills. TrackAbout allows for adding a flat fee for each customer in addition to, or in place of, regular rent. There are several ways to apply flat fees, such as using flat fees for hazmat or delivery charges, or for flat negotiated rental rates.
| Column | Description |
|---|---|
FlatFeeId
|
System-generated unique ID for this Flat Fee. Primary key. |
FlatFeeName
|
The name of the Flat Fee that is displayed in the TrackAbout site. |
DisplayName
|
The name that is used in rental bills. |
FlatFeeTypeId
|
A value that specifies the type of the Flat Fee. Possible values are: 1 - Single Surcharge 2 - Replaces All Rental 3 - Per Each Asset of Type 4 - For Asset Type 5 - Equipment Recurring Charge 6 - Equipment Delivery Charge 7 - Equipment Return Charge, 8 - Per Asset Rent Day of Type |
FlatFeeTypeName
|
The name of the type of the Flat Fee. Please see FlatFeeTypeId for possible values. |
AppliesToBillType
|
Specifies how the Flat Fee is applied. Values are: EntireBill - The amount is added to the entire bill. AssetType - The amount is applied based on Asset Types that are being billed. Asset - The amount is applied based on the Assets that are being billed |
AppliesToHolderType
|
Specifies whether the Flat Fee will be applied to bills for all customers, or only to specific customers. Possible values are: SpecificHoldersOnlyWithCharges AllHoldersWithCharges SpecificHoldersRegardlessOfCharges AllHoldersRegardlessOfCharges. When the type is SpecificHoldersOnlyWithCharges or SpecificHoldersRegardlessOfCharges, the RentalFlatFeeHolders table will contain the list of customers that will be charged this Flat Fee. |
QuantityCalculationOption
|
For Flat Fees that are based on quantity, specifies how the quantity will be calculated. Possible values are: BasedOnEndingBalance BasedOnDeliveredAssets |
Amount
|
The amount that will be charged for this Flat Fee. For Flat Fees that are based on quantity, the total amount can be obtained by multiplying the amount and quantity |
MinAmountPerBill
|
The minimum amount that will be charged for this Flat Fee. A value of 0 specifies that there is no minimum. |
MaxAmountPerBill
|
The maximum amount that will be charged for this Flat Fee. A value of 0 specifies that there is no maximum. |
opendata.RentalFlatFeeHolders
This view stores the list of customers that will be charged each Flat Fee, for Flat Fees of type SpecificHoldersOnlyWithCharges or SpecificHoldersRegardlessOfCharges in the FlatFees.AppliesToHolderType column. For Flat Fees of type AllHoldersWithCharges or AllHoldersRegardlessOfCharges that are charged to all customers by default, there will be no entry in this table.
| Column | Description |
|---|---|
RentalFlatFeeHolderId
|
System-generated unique ID for this view. Primary key. |
FlatFeeId
|
The internal system ID of the Flat Fee that the customer will be charged. Join to FlatFees for details. |
FlatFeeName
|
The name of the Flat Fee that the customer will be charged. |
HolderId
|
The internal system ID of the customer that will be charged the Flat Fee. Join to Holders for details. |
HolderStr
|
The ID of the customer that will be charged the Flat Fee. |
HolderName
|
The name of the customer that will be charged the Flat Fee. |
opendata.RentalFlatFeeOverrides
It is possible to override properties of Flat Fees on a per customer basis. This view stores the values of those overrides.
| Column | Description |
|---|---|
RentalFlatFeeOverrideId
|
System-generated unique ID for this view. Primary key. |
FlatFeeId
|
The internal system ID of the Flat Fee that is being overridden for a customer. Join to FlatFees for details. |
FlatFeeName
|
The name of the Flat Fee that is being overridden for a customer. |
HolderId
|
The internal system ID of the customer for whom the Flat Fee is being overridden. Join to Holders for details. |
HolderStr
|
The ID of the customer for whom the Flat Fee is being overridden. |
HolderName
|
The name of the customer for whom the Flat Fee is being overridden. |
IsFlatFeeIgnoredForHolder
|
This applies to Flat Fees of type AllHoldersWithCharges or AllHoldersRegardlessOfCharges (in the FlatFees.AppliesToHolderType column) that are charged to all customers by default. A value of 0 indicates that the customer will NOT be charged this Flat Fee. A value of 1, or a missing row for Flat Fees of these types indicate that the Flat Fee will be charged. |
OverriddenAmount
|
The overridden amount of the Flat Fee that will be charged for this customer. A value of 0 indicates that the amount is not overridden. |
OverriddenMinAmountPerBill
|
The overridden minimum amount of the Flat Fee that will be charged for this customer. A value of 0 indicates that the minimum amount is not overridden. |
OverriddenMaxAmountPerBill
|
The overridden maximum amount of the Flat Fee that will be charged for this customer. A value of 0 indicates that the maximum amount is not overridden. |
opendata.RentalFlatFeeAssetClasses
For Flat Fees of type 3 - Per Each Asset of Type, 4 - For Asset Type (in the RentalFlatFees.FlatFeeTypeId and RentalFlatFees.FlatFeeTypeName) column, this view stores the Rental Classes and Asset Types that will be charged against those Flat Fees.
| Column | Description |
|---|---|
RentalFlatFeeAssetClassId
|
System-generated unique ID for this view. Primary key. |
FlatFeeId
|
The internal system ID of the Flat Fee for which the Rental Classes and Asset Types are being specified. Join to FlatFees for details. |
AssetClassType
|
Indicates whether this row specifies a Rental Class or Asset Type that will be charged. Possible values are RentalClass and AssetType. |
RentalClassId
|
The internal system ID of the Rental Class that will be charged. Will have a non-null value when the Type is RentalClass. |
RentalClassName
|
The name of the Rental Class that will be charged. Will have a non-null value when the Type is RentalClass. |
AssetTypeId
|
The internal system ID of the Asset Type that will be charged. Will have a non-null value when the Type is AssetType. Join to AssetTypes for details. |
AssetCatName
|
The category of the Asset Type that will be charged. Will have a non-null value when the Type is AssetType. |
AssetGroupName
|
The group of the Asset Type that will be charged. Will have a non-null value when the Type is AssetType. |
AssetTypeName
|
The name of the Asset Type that will be charged. Will have a non-null value when the Type is AssetType. |
opendata.RentalBills
Stores calculated Rental Bills.
| Column | Description |
|---|---|
RentalBillId
|
System-generated unique ID for the rental bill. |
InvoiceNumber
|
The Invoice Number assigned to this bill. |
RentalBillingPeriodId
|
The internal system ID of the rental billing period that this bill was generated in. |
RentalBillingPeriodName
|
The name of the rental billing period that this bill was generated in. |
StartDateUtc
|
The start of the date range over which rental is being calculated. |
EndDateUtc
|
The end of the date range over which rental is being calculated. |
StartDateLocal
|
The start of the date range over which rental is being calculated. This is the local time based on the Time Zone of the Headquarter. |
EndDateLocal
|
The end of the date range over which rental is being calculated. This is the local time based on the Time Zone of the Headquarter. |
WhenCalculated
|
Returns the date the bill was calculated in the Time Zone of the Headquarter. |
PrimaryHolderId
|
The internal system ID of the customer being billed. |
PrimaryHolderStr
|
The ID of the customer being billed. |
PrimaryHolderName
|
The name of the customer being billed. |
BillToHolderId
|
The internal system ID of the customer that receives the bill. |
BillToHolderStr
|
The ID of the customer that receives the bill. |
BillToHolderName
|
The name of the customer that receives the bill. |
BillToAddressId
|
The billing address of the customers. Join to RentalBillAddresses for details. |
ShipToAddressId
|
The ship-to address of the customers. Join to RentalBillAddresses for details. |
RemitToAddressId
|
The remittance address of the bill. Join to RentalBillAddresses for details. |
HeaderAddressId
|
The address displayed in the header of the bill. Join to RentalBillAddresses for details. |
GrandTotal
|
The Grand Total of the Bill. |
PaymentTerms
|
The payment terms for the bill. |
Territory
|
The Billing Territory. |
ReplacementCharge
|
Replacement price of all assets on balance as of the billing end date. |
PurchaseOrder
|
The Purchase Order. |
RentalBillState
|
Indicates the current state of the rental bill and if the bill should be rolled forward, not sent, or sent normally. Possible values are: 0 (AboveMinimum) 1 (BelowMinimumButRoundedUp) 2 (BelowMinimumNotBilled) 3 (BelowMinimumRolledForward) 4 (NoRent) |
RentalBillStateDescription
|
Description of the current state of the rental bill. Refer to the RentalBillState column for possible values. |
HoldStatus
|
Indicates the Hold Status of the Bill. |
HoldNotes
|
Notes related to the Hold Status of the Bill. |
IsRolledUpByRentalClass
|
Indicates whether the Bill is rolled up by Rental Class. |
FlatFeeCharges
|
This is the total amount of Flat Fee Charges applied to the bill. |
DueDateLocal
|
The localized due date for the rental bill. |
DueDateUtc
|
The UTC due date for the rental bill. |
InvoiceDateOverrideUtc
|
Optional. If specified, TrackAbout uses this value instead of calculating an invoice date for the rental bill. |
IsLocked
|
Indicates whether the Bill is locked. Bills are locked after they have been printed. Locked bills cannot be deleted or modified by users. |
IsPrinted
|
Indicates whether the Bill has been printed. |
IsSentToAccounting
|
Indicates whether the bill has been sent to the accounting system. |
BillDeliveryMethod
|
Flag indicating how the bill will be sent to the customer. Possible values are: 0 (PostalMail), 1 (EmailAndOrFax), 2 (Email), 3 (Fax) |
BillDeliveryMethodDescription
|
Specifies how the bill will be sent to the customer. Refer to the BillDeliveryMethod column for details. |
DaysInMonth
|
The number of days in month used in calculation of the Rental Bill. Used only for Equipment Rental calculation. |
IsGeneratedThroughReturnRecord
|
Indicates whether an equipment rental bill was generated for a specific return record. |
TaxRate
|
The tax rate to use in calculating the bill. |
PreTaxTotal
|
The pretax total amount of the bill. |
PreTaxTotalBeforeMinimumBillableAmountComparison
|
The actual PreTax total before taking into account the Minimum Billable amount. |
TotalTax
|
The total tax applicable to the bill. |
TaxAccountingCode
|
The Accounting Code for the Tax. |
TaxableAmount
|
The total taxable amount of the Bill. This excludes the taxable amount that may have been carried forward from other bills. |
TaxRegionCode
|
The Tax Region Code for the Tax applied. |
TaxStatusId
|
The tax status id of the customer on the Bill. |
TaxStatusCode
|
The tax status code of the customer on the Bill. |
IsCityTaxCharged
|
Whether city tax is charged for the customer on the bill. |
IsCountyTaxCharged
|
Whether county tax is charged for the customer on the bill. |
IsStateTaxCharged
|
Whether state tax is charged for the customer on the bill. |
IsTaxBetweenStatesCharged
|
Whether inter-state tax is charged for the customer on this bill. |
opendata.RentalBillAddresses
Models the addresses that are used in rental bills.
| Column | Description |
|---|---|
RentalBillAddressId
|
System-generated unique ID for the rental bill address. |
Address1
|
Street address line 1 |
Address2
|
Street address line 2 |
Address3
|
Street address line 3 |
Address4
|
Additional address field |
Address5
|
Additional address field |
Address6
|
Additional address field |
City
|
City |
Region
|
State, Province or Region |
SubRegion
|
Sub Region for international addresses |
PostalCode
|
Zip Code or equivalent, if applicable |
Country
|
Country |
Phone
|
The phone number for this address. |
Fax
|
The fax number for this address. |
Email
|
The email address. |
Footer1
|
The Address Footer. |
Footer2
|
The second line for the address footer. |
opendata.RentalBillLineItems
Stores the information that goes into calculating the line item rental for a single Rental Class or Asset Type.
| Column | Description |
|---|---|
RentalBillLineItemId
|
System-generated unique ID for the rental bill line item. |
RentalBillId
|
Foreign Key to RentalBills. |
LineItemType
|
The type of the Line Item. Possible values are: RC = Rental Class AT = Asset Type AS = Asset (Used for Equipment Rental) |
RolledUpLineItemId
|
The Rental Bill line item to which an Asset Type line item has been rolled up to. Will be null for Rental Class line items, and for Asset Type line items that have not been rolled up. |
RateDetailId
|
Points to the Rate Details corresponding to this line item. Join to RentalBillRateDetails for details. |
StartingBalance
|
The starting balance prior to counting up the rental days in the rental period. |
TotalAssetDaysBeforeDebits
|
Total number of assets times days before subtracting any days for adjustments like leases and customer-owned offsets. |
RentalClassCustomerOwnedOffsetAssetDays
|
Number of Asset Days to offset due to RentalClass-based Customer-Owned Offsets. |
RentalClassAssetAgreementOffsetAssetDays
|
Number of Asset Days to offset due to RentalClass-based Asset Agreements. |
AssetTypeCustomerOwnedOffsetAssetDays
|
Number of Asset Days to offset due to AssetType-based Customer-Owned Offsets. |
AssetTypeAssetAgreementOffsetAssetDays
|
Number of Asset Days to offset due to AssetType-based Asset Agreements. Will be null for Rental Class line items. |
RentalCharge
|
The total charge for this line item. |
StateTax
|
The computed tax for the line item using the state tax rate. |
CityTax
|
The computed tax for the line item using the city tax rate. |
CountyTax
|
The computed tax for the line item using the county tax rate. |
BetweenStatesTax
|
The computed tax for the line item using the inter-state tax rate. |
RentalMethodIdUsedForLowestDyWkMoRate
|
Applicable when the Rental Method for the Line Item is Lowest of daily/Weekly/Monthly. Indicates the Rental Method type that was the lowest and was used for the calculation. |
RentalMethodDescriptionUsedForLowestDyWkMoRate
|
Applicable when the Rental Method for the Line Item is Lowest of daily/Weekly/Monthly. Indicates the Rental Method type that was the lowest and was used for the calculation. |
DaysForLowestDyWkMoRate
|
Stores the number of days of rental for cases where the Daily Method has been used while calculating the lowest of Daily/Weekly/Monthly Rate. |
WeeksForLowestDyWkMoRate
|
Stores the number of weeks of rental for cases where the Weekly Method has been used while calculating the lowest of Daily/Weekly/Monthly Rate. |
MonthsForLowestDyWkMoRate
|
Stores the number of months of rental for cases where the Monthly Method has been used while calculating the lowest of Daily/Weekly/Monthly Rate. |
AssetAgreementDaysBillableAsCustomerOwnedOffset
|
The number of Asset Agreement days that should be displayed as Customer Owned Offset in the Bill. |
opendata.RentalBillRateDetails
Stores the Rate Details for a Rental Bill Line Item.
| Column | Description |
|---|---|
RentalBillRateDetailId
|
System-generated unique ID for the rental bill rate detail. |
RateType
|
Indicates whether this is a rental class or asset type rate, indicated by the values RC and AT respectively. |
RentalClassId
|
The internal system ID of the rental class that this line item rate is for. Join to RentalClasses for details. |
RentalClassName
|
The name of the rental class that this line item rate is for. |
RentalClassGroupName
|
The name of the rental class group that this line item rate is for. |
AssetTypeId
|
The internal system ID of the asset type that this line item rate is for. Join to AssetTypes for details. |
AssetCatName
|
The name of the asset category that this line item rate is for. Join to AssetTypes for details. |
AssetGroupName
|
The name of the asset group that this line item rate is for. Join to AssetTypes for details. |
AssetTypeName
|
The name of the asset type that this line item rate is for. Join to AssetTypes for details. |
AssetTypeDescription
|
The description of the asset type that this line item rate is for. Join to AssetTypes for details. |
IsExchangeType
|
Indicates whether this asset type is of exchange type. |
RentalRateId
|
The internal system ID of the rental rate. Join to RentalRates for details. |
RentalMethodName
|
Contains the name of the rental method (i.e. Daily, Weekly, Monthly) |
RentalMethodId
|
The ID of the rental method used. |
DailyRate
|
The daily rate. |
WeeklyRate
|
The weekly rate. |
MonthlyRate
|
The monthly rate. |
ReplacementPrice
|
The total Replacement price for all assets in the line item that this rate belongs to. |
IsRentalClassTaxExemptForRegion
|
Indicates whether the rental class is tax exempt for the tax region applicable to the bill. |
IsRentalMethodTaxExemptForRegion
|
Indicates whether the rental method is tax exempt for the tax region applicable to the bill. |
IsRentalClassTaxExemptForHolder
|
Indicates whether the rental class is tax exempt for this bill customer. |
IsRentalMethodTaxExemptForHolder
|
Indicates whether the rental method is tax exempt for this bill customer. |
RateTableName
|
The name of the rate table that this rate belongs to. |
RateTableType
|
The type of the rate table that this rate belongs to. Possible values are Standard, Bracketed, Customer. |
RentalClassAccountingCode
|
The accounting code for the rental class for this rate. |
IsUsingOverriddenRates
|
Indicates whether a overridden rate was used instead of the rates in the standard, bracketed or customer rate tables. Rates can be overridden only for equipment rental bills. |
RentalBillCalculationMethod
|
The rental calculation method that was applied. Possible values are: 0 (NotSet) 1 (StartOfDay) 2 (EndOfDay) 3 (MaxOfStartAndEndOfDay) 4 (AssetsTiedUp) |
RentalBillCalculationMethodDescription
|
The description of the rental calculation method that was applied. Possible values are NotSet, StartOfDay, EndOfDay, MaxOfStartAndEndOfDay, AssetsTiedUp. |
RentalBillDescription
|
The description of the line item printed on the rental bill. |
AssetId
|
The internal system ID of the asset being billed. Join to Assets for details. This only applies to equipment rental bills. |
ProductCodeId
|
The internal system ID of the product code of the asset being billed. Join to ProductCodes for details. This only applies to equipment rental bills. |
ProductCodeName
|
The name of the product code of the asset being billed. This only applies to equipment rental bills. |
ProductCodeDescription
|
The description of the product code of the asset being billed. This only applies to equipment rental bills. |
AssetRentalDescription
|
The description of the asset being billed. This only applies to equipment rental bills. |
TrackNum
|
The barcode of the asset being billed. This only applies to equipment rental bills. |
CrossRef
|
The serial number of the asset being billed. This only applies to equipment rental bills. |
opendata.RentalBillBalanceChanges
Models a single balance-changing line item on a rental bill.
| Column | Description |
|---|---|
RentalBillBalanceChangeId
|
System-generated unique ID for the rental bill balance change. |
RentalBillLineItemId
|
The line item that this balance change belongs to. Join to RentalBillLineItems for details. |
BalanceChangeType
|
The Type. 0: BalanceChange 1: DateRangeWithNoActivity |
BalanceChangeTypeDescription
|
The description of the balance change type. Possible values are: BalanceChange and DateRangeWithNoActivity. |
StartDateUtc
|
The start date of the balance change in the UTC time zone. |
StartDateLocal
|
The start date of the balance change. This is the local time based on the Time Zone of the Headquarter. |
EndDateUtc
|
Only has meaning if the BalanceChangeType is DateRangeWithNoActivity, in which case this is the end of the date range during which there was no change in balance. |
EndDateLocal
|
Only has meaning if the BalanceChangeType is DateRangeWithNoActivity, in which case this is the end of the date range during which there was no change in balance. This is the local time based on the Time Zone of the Headquarter. |
Invoice
|
When the line item corresponds to a balance change due to a record, this field will contain the invoice of the record causing the balance change. |
DeliverQuantity
|
The quantity of assets delivered in the current balance change. |
ReturnQuantity
|
The quantity of assets returned in the current balance change. |
AssetCountEndOfday
|
The count of assets at the end of the balance change. |
RecordId
|
This value only has meaning when the line item represents a single balance changing record. Join to Records for details. |
DeliverPoolQuantity
|
The quantity of pool assets delivered by the current balance change. |
ReturnPoolQuantity
|
The quantity of pool assets returned by the current balance change. |
StartingNonPoolBalance
|
The starting balance of non-pool assets for this balance change. |
StartingPoolBalance
|
The starting balance of pool assets for this balance change. |
CurrentPoolBalance
|
The current running pool balance at the time of balance change calculation, not including the current balance changes transactions. |
PurchaseOrderNumber
|
When the line item corresponds to a balance change due to a record, this field will contain the purchase order number of the record causing the balance change *OR* the purchase number of the corresponding order (if available). If there is a conflict between the record and the order, this will contain the most recent of the two. |
opendata.RentalBillAssetAgreements
Stores the details of asset agreement renewals that are being charged in a rental bill.
| Column | Description |
|---|---|
RentalBillAssetAgreementId
|
System-generated unique ID for the rental bill asset agreement. |
AgreementNumber
|
The agreement number for the agreement being charged for. |
Duration
|
The total duration of the asset agreement, in days. |
StartDateUtc
|
The date the asset agreement becomes active |
RentalBillId
|
The rental bill this asset agreement was charged on. Join to RentalBills for details. |
PreTaxTotal
|
The total being charged, before any applicable tax. |
GrandTotal
|
The grand total including tax. |
AssetAgreementId
|
The internal system ID of the asset agreement being billed. Join to AssetAgreements for details. |
AssetAgreementTypeId
|
The internal system ID of the type of the AssetAgreement |
AssetAgreementTypeName
|
The asset agreement type name. |
AssetAgreementTypeAccountingCode
|
The accounting code for this agreement type. |
StateTax
|
The state tax applicable on this asset agreement renewal. |
CountyTax
|
The county tax applicable on this asset agreement renewal. |
CityTax
|
The city tax applicable on this asset agreement renewal. |
BetweenStatesTax
|
The inter-state tax applicable on this asset agreement renewal. |
opendata.RentalBillFlatFeesApplied
Contains the details of a single flat fee as stored on a single rental bill.
| Column | Description |
|---|---|
RentalBillFlatFeeAppliedId
|
System-generated unique ID for the rental bill flat fees applied. |
RentalBillId
|
Foreign Key to RentalBills. |
FlatFeeId
|
The internal system ID of the flat fee applied. Join to RentalFlatFees for details. |
Name
|
Client-given Name of the flat fee applied. |
DisplayName
|
Name of the Flat Fee as displayed on the rental bill. |
FlatFeeTypeName
|
The type of Flat Fee. |
FlatFeeTypeId
|
The internal system ID of the flat fee type. |
AccountingCode
|
Accounting code according to the accounting system for this flat fee. |
Amount
|
The amount for a single quantity of this Flat Fee. |
Quantity
|
The number of times this flat fee should be multiplied. |
MinAmountPerBill
|
The minimum amount of flat fee Applied. This minimum amount only applies when the Flat Fee calculated is greater than 0. |
MaxAmountPerBill
|
The max amount, per bill, that can be applied to a rental bill for this flat fee. 0 means no maximum. |
IsFlatFeeTaxExemptForRegion
|
Indicates whether the flat fee is tax exempt for the tax region applicable to the Bill. |
IsFlatFeeTaxExemptForHolder
|
Indicates whether the flat fee is tax exempt for this billed customer. |
StateTax
|
The total state tax added to the rental bill for this flat fee. |
CountyTax
|
The total county tax added to the rental bill for this flat fee. |
CityTax
|
The total city tax added to the rental bill for this flat fee. |
BetweenStatesTax
|
The inter-state tax added to the rental bill for this flat fee. |
QuantityCalculationOption
|
Indicates how the flat fee quantity is calculated. 0 = Based on Ending Balance 1 = Based on Delivered Assets |
QuantityCalculationOptionDescription
|
Description of how the flat fee quantity is calculated. Possible values are: BasedOnEndingBalance, BasedOnDeliveredAssets. |
opendata.RentalBillSurcharges
Stores the surcharges applied to a bill.
| Column | Description |
|---|---|
RentalBillSurchargeId
|
System-generated unique ID for the rental bill surcharge. |
RentalBillId
|
The rental bill this surcharge belongs to. Join to RentalBills for details. |
RentalSurchargeType
|
The type of the Surcharge. Possible values are: 0 (Minimum Billable Amount) 1 (Previously Unbilled Amount) |
RentalSurchargeTypeDescription
|
Description of the type of the surcharge. Possible values are: MinimumBillableAmount PreviouslyUnbilledAmount |
Name
|
The name of the surcharge. This is printed on the final bill. |
Amount
|
The surcharge amount. |
AccountingCode
|
The accounting code for this surcharge. Will be empty if no accounting code is applicable. |
RolledForwardRentalBillId
|
For surcharges of type PreviouslyUnbilledAmount, contains the internal system ID of the Rental Bill that got rolled forward in this surcharge. |
StateTax
|
The state tax amount applicable to the rental surcharge. |
CityTax
|
The city tax amount applicable to the rental surcharge. |
CountyTax
|
The county tax amount applicable to the rental surcharge. |
BetweenStatesTax
|
The tax between states applicable to the rental surcharge. |
Telemetry and Use-Points
opendata.TelemetryDevices
Models the physical telemetry device which is deployed in the field collecting live readings. The readings are sent to us over the internet and recorded as UsePointReadings.
| Column | Description |
|---|---|
TelemetryDeviceId
|
System-generated unique ID for the Telemetry Device. |
TelemetryDeviceName
|
User-provided name of this Telemetry Device. |
SerialNumber
|
Serial Number of the TelemetryDevice. |
MacAddress
|
Hardware ethernet MAC address of the Telemetry Device. |
NumberOfSensors
|
The number of physical sensors on the Telemetry Device. |
HolderId
|
The internal system ID of the customer assigned to this Telemetry Device. Join to Holders for customer details. |
HolderStr
|
The ID of the customer assigned to this Telemetry Device. |
HolderName
|
The name of the customer assigned to this Telemetry Device. |
IsEnabled
|
True if this device is enabled and is accepting updates. |
AlertWhenNotUpdatedForSeconds
|
Number of seconds after not receiving an update that an alert notification should be sent. |
OnErrorEmailTo
|
Semi-colon delimited list of e-mails to which to send errors. |
OnErrorCustomerEmailTo
|
Semi-colon delimited list of Customer e-mails to which to send errors. |
LastReadingUtc
|
The date/time of the last time this device received a reading. |
opendata.UsePoints
Models a pressure, temperature or volume-monitored system. There can be standalone use points where readings are taken manually, or telemetry devices which have multiple sensors (each a use point) that record automatically.
| Column | Description |
|---|---|
UsePointId
|
System-generated unique ID for the use point. |
UsePointDescription
|
User-defined description of this use point. |
UsePointClassificationId
|
The internal system ID of the classification of this use point. Join to UsePointClassifications for details. |
HolderId
|
The internal system ID of the customer where this use point is located. Join to Holders for details. |
HolderStr
|
The ID of the customer where this use point is located. |
HolderName
|
The name of the customer where this use point is located. |
TelemetryDeviceId
|
The internal system ID of the telemetry device assigned to this Use Point, or 0 if the Use Point is managed manually. Join to TelemetryDevices for details. |
TelemetryDeviceName
|
The name of the telemetry device assigned to this Use Point. |
TelemetryDeviceSensorNumber
|
The number of the sensor of the telemetry device attached to this use point. |
TrackNum
|
Barcode of the use point if this UsePoint is managed manually. |
AccountingSystemFixedAssetNumber
|
Fixed Asset Number of this use point as stored in the Accounting System. |
UnitOfMeasure
|
The unit of measure that is recorded at this use point. |
MaximumValue
|
The maximum recordable value at this Use Point. |
ReorderThresholdValue
|
The threshold value beyond which e-mail notification for reordering takes place. |
ReorderThresholdEmailTo
|
Semicolon-delimited list of e-mails to which to send reorder notifications. |
CriticalThresholdValue
|
The threshold value beyond which a critical notification e-mail is sent. |
CriticalThresholdEmailTo
|
Semicolon-delimited list of e-mails to which to send critical notifications. |
ReorderThresholdCustomerEmailTo
|
Semicolon-delimited list of customer e-mails to which to send reorder notifications. |
CriticalThresholdCustomerEmailTo
|
Semicolon-delimited list of Customer e-mails to which to send critical notifications. |
ReorderAlertLastFiredUtc
|
The timestamp at which the Reorder e-mail alert was last fired. |
CriticalAlertLastFiredUtc
|
The timestamp at which the Critical e-mail alert was last fired. |
ChangeoutDropPercentage
|
A percentage drop applied to CriticalValueThreshold that indicates if a changeout event is occurring. |
ChangeoutPauseMinutes
|
The number of minutes that it takes to reset a sensor. Reading will not be taken until ChangeoutPauseMinutes elapses after a changeout event occurs. |
IgnoreReadingsUntil
|
No readings will be taken until the UTC date is greater than this date. A value of DateTime.MinValue indicates that readings are not ignored. |
opendata.UsePointClassifications
Models the classification of an use point.
| Column | Description |
|---|---|
UsePointClassificationId
|
System-generated unique ID for the use point classification. |
Name
|
User-provided name of this use point classification. |
Description
|
User-provided description of this use point classification. |
opendata.UsePointReadings
Stores the readings recorded for use points.
| Column | Description |
|---|---|
ReadingId
|
System-generated unique ID for the use point reading. |
UsePointId
|
The use point that registered this reading. |
ReadingDateUtc
|
The timestamp of the reading. |
UserId
|
The internal system ID of the user that entered manual data. A value of 0 indicates telemetry-collected data. Join to UserDetails for details. |
AccountingUserId
|
The ID in the accounting system of the user that entered manual data. |
ReadingValue
|
The value of the reading. |
Users
opendata.UserDetails
These are your employees who use TrackAbout.
| Column | Description |
|---|---|
UserId
|
System-generated unique ID for this user. Primary key. |
LoginName
|
Login name user can use to authenticate (optional). User must have either a LoginName or Email.Website users in TrackAbout can be created using an email address, a login name, or both. A NULL value for LoginName represents users created using only email address. Users with access only to TrackAbout Mobile 6 or earlier may be created without specifying any login name or email address. These users log into the mobile software by selecting their full name from a dropdown list. These users will have a NULL value for both the LoginName and Email columns. Deleted users will have a GUID value in this column. These users will also have a value of 3 in the Status column. The status values are: Active = 1, Disabled = 2, Deleted = 3 |
Email
|
Email user can use to authenticate (optional). User must have either a LoginName or Email. |
AccountingUserId
|
Some TrackAbout customers provide user IDs from other systems, such as ERP. That value can be set in this field and can be used to ease integrations back into ERP or other systems. |
FirstName
|
Self-explanatory. |
LastName
|
Self-explanatory. |
IsMobileUser
|
User can use TrackAbout Mobile 6. |
IsSmartphoneUser
|
User can use TrackAbout Mobile 7 (iOS and Android). |
IsSimulatorUser
|
User can use TrackAbout Mobile 6 Desktop. |
IsAwsUser
|
User can access the Application Web Site (AWS). |
Status
|
1 = Active 2 = Disabled 3 = Deleted |
HolderId
|
If user is a Customer Tracking Portal user, this column contains a foreign key reference into the Holders table indicating which customer the user belongs to. |
LocationId
|
Foreign key reference to Locations. Indicates which primary internal location this user is associated with. |
LastLoginDate
|
Timestamp of the last time the user authenticated to TrackAbout. |
