Warning
WARNING: The TrackAbout MetaWiki has been deprecated and is no longer being updated. Please visit our new TrackAbout Knowledge Base at https://supportkb.trackabout.com for the most-up-to-date documentation on TrackAbout and TrackAbout Mobile.

OpenData

From TrackAbout MetaWiki
Revision as of 17:28, 10 May 2023 by Lsilverman (talk | contribs) (→‎Query Performance and Volume)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Opendata.png

About

OpenData is a paid feature that enables TrackAbout customers to access a simplified, read-only view of their TrackAbout SQL database.

Request Access to OpenData

Pricing

TrackAbout charges for the extra management, maintenance, security and hosting of this feature.

  • For TrackAbout clients with more than or equal to 50,000 assets the cost is $200 per month.
  • For TrackAbout clients with fewer than 50,000 assets the cost is $100 per month.

If you're interested in being able to write your own SQL queries to retrieve TrackAbout data, please contact support@trackabout.com to sign up.

Providing a static IPv4 Address

To get access to your OpenData database, you must first provide TrackAbout with a static IPv4 address. IPv6 addresses are not accepted.

Please read the following article to learn the difference between a static and a dynamic IP address: Static vs. dynamic IP address

Static means "unchanging". In order to provide access to OpenData, you must give TrackAbout your static IPv4 address so that we may add a rule to our firewall to allow you to connect.

Warning
WARNING: If the IP address you provide TrackAbout is not static, or changes for any reason, you will lose the ability to connect to OpenData.


Your SQL queries must always originate from this static IP address.

If your are unsure whether your company has a static IP address, you will need to ask your IT department or your company's Internet Service Provider (ISP). Most business-class Internet accounts include at least one static IP.

If you are certain your company has a static IP address, but you're not sure what it is, try the following:

  • Be on a computer or device on your corporate network (wired or WiFi).
  • Open a browser and Google What's my IP (or click that link, which will Google it for you).

Warnings

  1. If you provide us with an IP address, but it's not static, and it changes in the future, you will no longer be able to connect to OpenData.
  2. If your company changes Internet service providers in the future, or your static IP address changes for any reason, you will no longer be able to connect to OpenData. You must let us know what the new static IP address is.
  3. Most users of OpenData intend to automate data retrieval into some other local database. Make sure the computer that is doing the retrieval exits onto the Internet using the same static IP address.

Connecting to your Database

Once you are registered for OpenData, TrackAbout will communicate the following important connection details by email:

  • Azure SQL Server network address
  • Database name to connect to
  • SQL username
  • SQL password
Warning
WARNING: You are responsible for securely storing these credentials. If you lose the password, TrackAbout will have to reset and securely send you the new password. This may be disruptive to services you have created which are actively querying the database.

Your database is hosted in Microsoft Azure SQL Database.

There are many tools that can connect to Azure SQL Database. We recommend Microsoft's SQL Server Management Studio (SSMS). SSMS is a standalone, free product from Microsoft. Also see the article Azure SQL Database: Use SQL Server Management Studio to connect and query data.

How to Connect Using SQL Server Management Studio

Warning
WARNING: PLEASE READ THIS: You MUST explicitly specify the database name in SSMS on the Connection Properties tab of the connection dialog. Failing to follow this instruction is the number one reason customers call with problems connecting to OpenData. Having to enter the database name explicitly is different from how highly privileged users who have access to the master database are used to connecting. You will not have access to the master database in our environment, and therefore you MUST explicitly specify your database name.

Open the Connect to Server dialog.

Choose SQL Server Authentication from the Authentication dropdown.

Enter the provided username and password.

Ssms-connect-dialog.png

MUST DO: Click the Options button, then the Connection Properties tab and put your database name in the Connect to database field.

Ssms-connection-properties.png

The connection will fail if you do not specify the database explicitly.

Click Connect

Expand the tree in Object Explorer. You should see the following:

Ssms-object-explorer.png

Troubleshooting Connectivity

If you're having trouble connecting, follow these steps to eliminate the most common causes.

If you run these tests but are unsure about the results, please screenshot your tests and share them with support@trackabout.com

Testing DNS Resolution

Every address on the internet like our database's address of ta-proddb-ceus.database.windows.net has to be turned into an IP Address by your computer. In some environments, this so-called "DNS resolution" might not be working due to a network misconfiguration or even local network security policy blocking access.

Test DNS using nslookup on Windows

Open a PowerShell command prompt (powershell.exe) and execute the following command:

nslookup ta-proddb-ceus.database.windows.net

You should receive a response like:

C:\Users\username> nslookup ta-proddb-ceus.database.windows.net
Server:  some.server.name
Address:  192.168.10.1

Non-authoritative answer:
Name:    cr5.centralus1-a.control.database.windows.net
Address:  104.208.21.1
Aliases:  ta-proddb-ceus.database.windows.net
          ta-proddb-ceus.privatelink.database.windows.net
          dataslice7.centralus.database.windows.net
          dataslice7centralus.trafficmanager.net

We're looking for a valid IP address under "Non-authoritative answer:".

Test DNS using dig on Linux or Mac

If you're on Linux or a Mac, you will need to use the "dig" command. If it's not installed, check your package manager for bind-utils and install it.

dig ta-proddb-ceus.database.windows.net
; <<>> DiG 9.18.1-1ubuntu1.3-Ubuntu <<>> ta-proddb-ceus.database.windows.net
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 2427
;; flags: qr rd ad; QUERY: 1, ANSWER: 5, AUTHORITY: 0, ADDITIONAL: 0
;; WARNING: recursion requested but not available

;; QUESTION SECTION:
;ta-proddb-ceus.database.windows.net. IN        A

;; ANSWER SECTION:
ta-proddb-ceus.database.windows.net. 0 IN CNAME ta-proddb-ceus.privatelink.database.windows.net.
ta-proddb-ceus.privatelink.database.windows.net. 0 IN CNAME dataslice7.centralus.database.windows.net.
dataslice7.centralus.database.windows.net. 0 IN CNAME dataslice7centralus.trafficmanager.net.
dataslice7centralus.trafficmanager.net. 0 IN CNAME cr8.centralus1-a.control.database.windows.net.
cr8.centralus1-a.control.database.windows.net. 0 IN A 13.89.169.20

;; Query time: 180 msec
;; SERVER: 172.25.208.1#53(172.25.208.1) (UDP)
;; WHEN: Wed Apr 12 13:05:03 CDT 2023
;; MSG SIZE  rcvd: 502

Above, the line cr8.centralus1-a.control.database.windows.net. 0 IN A 13.89.169.20 contains the final lookup which resulted in a valid IP address (13.89.169.20). This is a positive (good) test result.

Testing TCP/IP Connectivity

Once we've verified name resolution is working, we move on to testing end-to-end connectivity.

Here we'll test whether your computer can find a route on the network to our database server.

On Windows

In a Powershell command prompt, run this command:

Test-NetConnection -ComputerName ta-proddb-ceus.database.windows.net -Port 1433
ComputerName     : ta-proddb-ceus.database.windows.net
RemoteAddress    : 13.89.169.20
RemotePort       : 1433
InterfaceAlias   : TP-Link Card
SourceAddress    : 192.168.10.15
TcpTestSucceeded : True

We want to see TcpTestSucceeded : True.

Anything else indicates a failure to reach the remote server. You should contact your local IT network support for help.

On Linux or Mac

Here we'll use the netcat (nc) command line utility.

nc -z -v ta-proddb-ceus.database.windows.net 1433
Connection to ta-proddb-ceus.database.windows.net (13.89.169.20) 1433 port [tcp/ms-sql-s] succeeded!

Anything else indicates a failure to reach the remote server. You should contact your local IT network support for help.

Try a Query

Right-click on your database and select New Query

Try this query:

SELECT TOP 10 * FROM opendata.assets

You should get 10 rows back.

Limitations and Expectations

Allowed Connections

It is TrackAbout's expectation that customers will be connecting to their database only from within their company network from their known static IP address, and not from several different geographic locations having different IP addresses. We can work with you if you have several static IP addresses from where you wish to query, but we cannot support dynamic (changing over time) IP addresses, such as those provided to consumer-class or home Internet services.

Read-Only

The data views are read-only by design. You cannot write to this data. Writing is not a planned feature.

Query Performance and Volume

It is our expectation that you will not abuse the database by re-running very large or expensive queries frequently. The database platform has finite resources, and abuse will impact other users. TrackAbout will be monitoring usage and will throttle, slow down or even block users who abuse the system.

Here are some performance tips:

  • Try not to use SELECT * (unless you're just exploring, initially). Fetch only the columns you require.
  • Please be conscious of how frequently you schedule executions.
  • Try to use precise WHERE clauses to return just the data you require, and not pull back the entire history of transactions. Over time, as your database grows with use, you may find queries getting slower and slower with the accumulation of TrackAbout data.

We're always happy to help tune up a query that's not performing well. Just let us know if you feel you have a query you can't get to execute quickly. We have a lot of experience in query tuning.

Most queries, if well written, should be able to execute in under a minute or two. There are always exceptions, and we understand. If you have a query that's running longer than a couple minutes, please let us help.

Local data caching (copying data to a local database or temporary table) is recommended if you need to re-query the same data repeatedly.

Acceptable Use

OpenData is intended to be used by a small handful of data professionals within your organization, generally to create reports, visualizations or other business intelligence value.

OpenData is NOT intended to be called from multiple devices, multiple geographic locations or apps, in general.

If you wish to use the data made available in OpenData more broadly, for example in a context like a mobile app, we recommend you extract the needed data on a periodic basis (and please be mindful of the load you may be creating), transform and load the data into another data system that is more appropriate for your use case.

Must Know - Dates are UTC

All dates in TrackAbout are stored in the Universal Coordinated Time (UTC) time zone. You likely will want to convert these times to the time zone of your business headquarters where you do your billing or reporting.

Microsoft added two very useful new commands to SQL that make time zone conversions easy. We highly recommend reading up on:

The Data Views (Tables)

OpenData presents a highly simplified view of the actual underlying TrackAbout database. 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.

We felt these views represented a reasonable starting point to offer to our customers, and we know we'll need to add to it over time.

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
opendata.Assets
Column Description
AssetId Database-generated unique ID for each asset.
DBEnterDate The date this asset was first introduced to the system.
HolderId If an asset is currently delivered to a customer, this is the internal system ID of that customer. Join to Holders for customer details.
HolderStr If an asset is currently delivered to a customer, this is the Customer ID of that customer.
LocationId 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.
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.
LocationTypeId 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.
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.
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.
IsAContainer Indicates if this asset is a container asset.
IsPartOfAContainer Indicates if this asset is inside a container.
IsAPack 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.
IsPartOfAPack Indicates if this asset is inside a Pack
IsABundle 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.
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.AuditEntries

An "Audit Entry" in TrackAbout documents a change made to a record after it has been initially saved. Audit Entries document the details of the change and provide a historical record for accountability.

opendata.AuditEntries
Column Description
AuditId System-generated unique ID for the AuditEntries.
RecordId The id of the record on which the Audit was made.
AuditTypeId The type of edit the user made.
AuditTypeName The name of the type of edit operation that was performed that caused the audit entry to be recorded.
AuditDesc A standard description of the type of audit.
AuditDate The date in UTC the audit entry was made.
UserId The id of the user who made the edit that created the audit.
AuditNote The user-given reason for the audit.
AuditDetails The system-generated full text details of what was changed in the record.
RawData Stores the changes due to the Audit in JSON format

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.
opendata.Records
Column Description
RecordId System-generated unique ID for the record.
RecDetailId 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.
RecDate The denormalized timestamp from RecordDetails. This will be the timestamp of the earliest action that created the record.
ActionId 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.
ActionName The type of action being performed (Add New, Deliver, etc).
IsLocSetting 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.
HolderId This is internal system ID of the customer. Not every record will have a customer. Join to Holders for customer details.
HolderName A "holder" is your customer. This is the full name of the customer. Not every record will have a customer.
HolderStr This is a string containing your Customer ID. This comes from your back-end systems. Not every record will have a customer.
IsDeleted If the record was deleted, contains a 1. Otherwise, 0.
Invoice For deliveries, the provided invoice/order/delivery number will be stored here.
PurchaseOrder If provided during delivery, the purchase order number.
SignerName If entered during delivery, the name entered into the Signer field.
LocationId The internal integer TrackAbout ID of the internal location or truck.
LocationStr Your company's ID of the internal location or truck.
LocationName The full name of the internal location or truck.
LocationTypeId The type of the Location: Branch, Reseller, Sales, Supplier, Truck
OriginLocationStr The ID of the origin location or truck.
OriginLocationName The full name of the origin location or truck.
OriginLocationTypeId The type of the origin location: Branch, Reseller, Sales, Supplier, Truck
HowEntered One of: Mobile Unit, Web Site, Bulk Load, Auto Enter, [Invoice] Comparison, Wireless Unit, Legacy Import, Linked Exchange, Web Service, External Import, TrackAbout API, Smartphone/Tablet.
The [Invoice] word is normally replaced in TrackAbout's app servers with a customer-preferred value like "Order", "Delivery" or something else.
UserId Lookup ID for the UserDetails table.
FirstName First name of the user.
LastName Last name of the user.
AccountingUserId The ID of the user in the accounting system.
DeviceId Lookup ID for the DeviceDetails table. Only rugged TAMobile 6 devices are tracked. Smartphone/tablet devices are not tracked in the same way.
IsAHandoff Specifies whether the record delivers or returns assets from a customer.
CreatedNewCustomer Indicates whether this record resulted in the creation of a new customer that did not previously exist in TrackAbout.
VerifyUserId The internal system ID of the user who verified the record.
VerifyUserFirstName The first name of the user who verified the record.
VerifyUserLastName The last name of the user who verified the record.
VerifyAccountingUserId The ID in the accounting system of the user who verified the record.
VerifyDate The date the record was verified. This is in the UTC time zone.
HowVerified 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

HowVerifiedDescription Indicates whether the record is verified, and if so, how it was done. See the HowVerified column for possible values.
InvestigationStatus Indicates whether or not this record is marked for Investigation by a client administrator. Possible values are:

0 : NotUnderInvestigation
1 : Investigate

InvestigationStatusDescription Indicates whether or not this record is marked for Investigation by a client administrator. See the InvestigationStatus column for possible values.
AutoModifiedPostVerified 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.

opendata.RecordDetails
Column Description
RecordId System-generated unique ID for the record. Foreign key to the Records table.
RecDetailId System-generated unique ID for this RecordDetails row. This is the primary key of this table.
StartDate 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.
RecDate The timestamp when the user saved the record to the device.
DBEnterDate The timestamp when this row was written to the database.
HowEntered Indicates the source of the data, whether it's from a mobile device, the Web UI, an API, file imports, etc.
HowEnteredAccountingCode Indicates the value in the accounting system for the source of the data.
UserId Foreign key to the UserDetails table.
AccountingUserId The Id of the user in the accounting system.
FirstName User's first name.
LastName User's last name.
DeviceId Foreign key to the DeviceDetails table, if TAMobile6 (not smartphone) was used to save the record.
PurchaseOrder Purchase order number entered by the user.
SignerName Name typed by the signer of a delivery.
IsHolderStrKeyedIn Indicates whether or not the user manually keyed in the Customer ID.
IsControlStrKeyedIn Indicates whether or not the user manually keyed in the Invoice Number for the record.
IsPurchaseOrderKeyedIn Indicates whether or not the user manually keyed in the Purchase Order number.

opendata.Geolocation

See TA Smartphone - Geolocation.

Geolocation data is collected on the smartphone platform 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.

opendata.Geolocation
Column Description
Id Database-generated unique id for this particular geolocation reading.
Latitude Self-explanatory.
Longitude Self-explanatory.
Accuracy How accurate the device believes the reading to be.
Altitude Self-explanatory.
MeasurementDate 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.

opendata.RecAssets
Column Description
RecordId System-generated ID of the Record, foreign key to the Records table.
AssetId System-generated ID of the Asset, foreign key to the Assets table.
RecDetailId Foreign key to RecordDetails table, which contains the details of who performed the action and when.
RecDate The denormalized timestamp from Records. This will be the timestamp of the earliest action that created the record.
IsLocSetting This column is Denormalized from Records. This column contains a 1 if the record changed the physical location, and 0 if not.
IsDeliver 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.
EnterDate The precise time this row was written to the database.
TrackNum Tag, barcode or other unique serialized tracking number.
CrossRef Serial number of the asset (not a tracking number).
IsTrackNumKeyedIn Indicates whether the user manually entered the barcode rather than scanning it.
FoundBy 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

FoundByDescription Indicates what method was used to search for and find this asset in order to attach it. See FoundBy column for possible values.
AssetTypeId The internal system ID of the asset type for this asset. Join to AssetTypes for details.
AssetCatName Asset category
AssetGroupName Asset group
AssetTypeName Asset type
ProductCodeId Internal ID and foreign key to the opendata.ProductCodes view.
ProductCodeName Product code (aka part number, SKU, etc), denormalized from opendata.ProductCodes for convenience.
ProductCodeDescription Longer description of the product code, denormalized from opendata.ProductCodes for convenience.
GeolocationId Reference to a row in the Geolocations table, or NULL
LotNumber 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.
Volume The volume of the asset as specified in this record, if applicable.
ContainerId The records assets container id. Foreign key to Containers.
ContainerTrackNum The bar code of the container.
ContainerCrossRef The serial number of the container.

opendata.RecContainers

This table associates containers with records.

opendata.RecContainers
Column Description
RecordId System-generated unique ID for the record. Foreign key to Records.
ContainerId System-generated unique ID for the container. Foreign key to Containers.
RecDetailId System-generated unique ID for the record detail. Foreign key to RecordDetails.
AssetId System-generated unique ID for the asset. Foreign key to Asset.
TrackNum Bar code of the asset.
CrossRef Serial number of the asset.
AssetTypeId System-generated unique ID for the asset type. Foreign key to AssetTypes.
AssetCatName The asset category name of the asset type.
AssetGroupName The asset group name of the asset type.
AssetTypeName The name of the asset type.
ProductCodeId System-generated unique ID for the product code. Foreign key to ProductCodes.
ProductCodeName The name of the product code.
ProductCodeDescription The description of the product code.

opendata.RecHardGoods

Records the quantity and type of Hard Goods or Consumables assets attached to a given record.

(This table will be available with the release of version AWS0331 in March 2022.)

opendata.RecHardGoods
Column Description
Id System-generated unique ID for the RecHardGoods.
RecordId The record this hard good is in. Foreign Key to Records.
HardGoodProductCodeId The hard good product code. Foreign Key to HardGoodProductCodes.
Quantity The quantity for this line.
OrderItemOwnerFlagId The OrderItemOwnerFlag for the Hard Good. Foreign Key to OrderItemOwnerFlags.
GeolocationId Geolocation ID associated with the hard good. Foreign Key to GeoLocation.
LotNumber Optional lot number associated with the hard goods on a record

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".

opendata.RecAssetsSetCustomAssetInfo
Column Description
RecordId System-generated ID of the Record, foreign key to the Records table.
AssetId System-generated ID of the Asset, foreign key to the Assets table.
Name Same definitions as CustomAssetInfo table, refer to it.
DataType Same definitions as CustomAssetInfo table, refer to it.
StringValue Same definitions as CustomAssetInfo table, refer to it.
IntegerValue Same definitions as CustomAssetInfo table, refer to it.
DateValue Same definitions as CustomAssetInfo table, refer to it.
DecimalValue Same definitions as CustomAssetInfo table, refer to it.
BooleanValue Same definitions as CustomAssetInfo table, refer to it.
OptionDisplayName Same definitions as CustomAssetInfo table, refer to it.
OptionValue 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.

opendata.RecAssetsSetUseState
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
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.


opendata.RecAssetsSetVolume
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.

(This table will be available with the release of version AWS0331 in March 2022.)

opendata.RecordDifferenceReasonCodes
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.

opendata.RecordFormData
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.

opendata.RecordGeolocation
Column Description
Id 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 RecordDetails which indicates which user caused the reading to be taken.

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.

opendata.RecordPostFillAnalytes
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.

opendata.RecordPostFillDetails
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.

opendata.RecordSubActions
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.

opendata.RecAssetsNS
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 assoicated 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.

opendata.RecAssetsFailedRNS
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).

opendata.RecNonHolderNSAssets
Column Description
Id System-generated unique ID for the RecNonHolderNSAssets.
RecordId The record for these not-scanned assets.
ProductCodeId The product code for these not-scanned assets.
Quantity The count of not-scanned assets.
GeolocationId Geolocation ID associated with the not-scanned assets.
LotNumber 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.

opendata.Addresses
Column Description
AddressId TrackAbout's internal ID for the address. This will be referenced by other tables, such as Orders
Address1 Address line 1
Address2 Address line 2
Address3 Address line 3
Address4 Address line 4
Address5 Address line 5
Address6 Address line 6
City City
Region Region / State
SubRegion Subregion, if applicable
PostalCode Postal Code or Zip Code
Country Country
Footer1 Address footer 1
Footer2 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.

opendata.AssetFamilyCollections
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.

opendata.AssetFamilies
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.

opendata.AssetFamilyMembers
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

This view will be available with release AWS0329

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.

opendata.CollisionAssets
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.

opendata.Containers
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.

opendata.Holders
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 Follow-On customer admin or you.
FOTHolderStr ID for the customer, provided by the Follow-On customer admin or you.
FollowOnType

0 = This customer is not enabled for Follow-On Tracking user.
1 = This customer is enabled only for Follow-On Viewing, not Tracking
2 = This customer is fully enabled for Follow-On 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.

opendata.DeviceDetails
Column Description
HolderId The customer having the balance. Join to Holders for customer details.
DNSBalance The balance of Delivered Not-Scanned assets.
RNSBalance The balance of Returned Not-Scanned assets.
AssetTypeId The Asset Type of the not-scanned asset. Join to AssetTypes.
ProductCodeId 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.

opendata.HolderServicingLocations
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.

Devices

opendata.DeviceDetails

These are TAMobile 5 and TAMobile 6 devices. TrackAbout does not model or track smartphone/tablet devices in the same way.

opendata.DeviceDetails
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 Follow-On Trackers 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.

opendata.UserDeviceDetails

Contains details of devices (TAMobile 6 and TAMobile 7) that were operated by users. Only data from the last 90 days is available in this view.

opendata.UserDeviceDetails
Column Description
UserId The user that operated the device. Foreign key to the UserDetails table.
FirstName First Name of the user that operated the device.
LastName Last Name of the user that operated the device.
AccessDate The date on which the device was operated.
ApplicationType The application that was operated by the user. Possible values are TAMobile6 and TAMobile7.
ApplicationVersion The version of the application.
DeviceInformation Information about the device model. For TAMobile7 devices, the operating system version is also available in this column.
ApplicationInstanceId For each physical device running TAMobile 7, this column will contain a GUID. The GUID is created at the time of application installation. If the application is reinstalled, the GUID will change. This is intended to try to help uniquely identify iOS and Android devices running TAMobile 7. Due to Apple and Google restrictions, developers are not permitted to read unique device identifiers, so we have to make up our own.


For TAMobile 6 devices, this column is empty. You can use opendata.DeviceDetails to count unique TAMobile 6 devices.

Entity Metadata and Classification

opendata.AssetTypes

This tables contains the Asset Types used for classifying assets.

opendata.AssetTypes
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.

opendata.CustomAssetInfo
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 The will contain 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.

opendata.CustomAssetInfoTypes
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.

opendata.CustomAssetInfoOptions
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.

opendata.CustomInfoTypeCategories
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.

opendata.CustomInfoTypes
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
Category Id Category Name Foreign Key Table
1 Holder Info Holders
2 Product Code Info ProductCodes
3 Asset Type Info AssetTypes
6 Location Info Locations
7 User Info UserDetails
11 Branch Location Info Locations
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.

opendata.CustomInfoOptions
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.

opendata.CustomInfo
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.

(This table will be available with the release of version AWS0331 in March 2022.)

opendata.DifferenceReasonCodes
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.

(This table will be available with the release of version AWS0331 in March 2022.)

opendata.HardGoodProductCodes
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.

opendata.ProductCodees
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.

opendata.UseStates
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.

opendata.IntegrationMessageQueue
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.

opendata.Locations
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 locationt 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 cancelled orders.

opendata.AllOrders
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 Cancelled.
CancelledByUserId If the order was cancelled, the internal system ID of the user who cancelled the order. Join to UserDetails for details.
CancelledDate If the order was cancelled, the date the cancellation was done.
CancellationReason If the order was cancelled, the reason code for the cancellation.

opendata.Orders

Orders contains all non-deleted orders that have been created in TrackAbout.

opendata.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.

opendata.OrderItems

OrdersItems contain the order lines for non-hard good items

opendata.OrderItems
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.

opendata.OrderAssets
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. (This field will be available with the release of AWS0317 in Feb 2020.)

opendata.OrderHardGoodItems

OrdersHardGoodItems contain the order lines for non-hard good items

opendata.OrderHardGoodItems
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.

opendata.AssetAgreements
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.

opendata.AssetAgreementLineItems
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.

opendata.AssetAgreementAssets
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the invoices associated with an Asset Agreement.

opendata.AssetAgreementInvoices
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.

opendata.RentalClassGroups
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.

opendata.RentalClasses
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

(This table will be available with the release of AWS0321 in June 2020.)

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.

opendata.RentalRateTables
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 value 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 [ Add Column Description ] 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

(This table will be available with the release of AWS0321 in June 2020.)

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.

opendata.RentalRates
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

(This table will be available with the release of AWS0321 in June 2020.)

The audit table for the RentalRates table. Tracks the who/what/when of changes to rental rates.

opendata.RentalRatesAudit
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.

opendata.RentalFlatFees
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 and 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 and 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.

opendata.RentalFlatFeeHolders
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.

opendata.RentalFlatFeeOverrides
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.

opendata.RentalFlatFeeAssetClasses
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores calculated Rental Bills.

opendata.RentalBills
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 a equipment rental bills 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

(This table will be available with the release of AWS0321 in June 2020.)

Models the addresses that are used in rental bills.

opendata.RentalBillAddresses
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the information that goes into calculating the line item rental for a single Rental Class or Asset Type.

opendata.RentalBillLineItems
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the Rate Details for a Rental Bill Line Item.

opendata.RentalBillRateDetails
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

(This table will be available with the release of AWS0321 in June 2020.)

Models a single balance-changing line item on a rental bill.

opendata.RentalBillBalanceChanges
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 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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the details of asset agreement renewals that are being charged in a rental bill.

opendata.RentalBillAssetAgreements
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

(This table will be available with the release of AWS0321 in June 2020.)

Contains the details of a single flat fee as stored on a single rental bill.

opendata.RentalBillFlatFeesApplied
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the surcharges applied to a bill.

opendata.RentalBillSurcharges
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 and 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

(This table will be available with the release of AWS0321 in June 2020.)

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.

opendata.TelemetryDevices
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

(This table will be available with the release of AWS0321 in June 2020.)

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.

opendata.UsePoints
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

(This table will be available with the release of AWS0321 in June 2020.)

Models the classification of an use point.

opendata.UsePointClassifications
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

(This table will be available with the release of AWS0321 in June 2020.)

Stores the readings recorded for use points.

opendata.UsePointReadings
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.

opendata.UserDetails
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 TAMobile 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 TAMobile6.
IsSmartphoneUser User can use Smartphone.
IsSimulatorUser User can use TAMobile6 Desktop.
IsAwsUser User can access the Application Web Site (AWS).
Status 1 = Active
2 = Disabled
3 = Deleted
HolderId If user is a Follow-On Tracking 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.


Sample Queries

To see some example OpenData sql queries, please see the OpenData Base Queries page.

Frequently Asked Questions

The data I need isn't in the OpenData tables. Can you add it?
Not every TrackAbout customer needs the same data. We can work with you to add data to your tables.
There's data in the tables I don't care about / I don't like the name of a table/column. Can you change it?
To offer a service that can scale with customer growth, TrackAbout needs the OpenData tables to remain identical across all customers. Maintaining dozens of different database schemas would require raising the price significantly to cover the cost. This means you may have tables or columns you don't want or care about. With SQL, you can always query only the tables and columns you do care about, and ignore the rest. You can also rename columns in your result set.
Why can't I log into my database from home?
Please re-read the section of this page regarding static IP addresses. TrackAbout can only enable access to static (unchanging) IP addresses. Most home Internet accounts have dynamic IP addresses that change periodically. We cannot support dynamic IP addresses. You might try connecting to your work's VPN (if you have one). This might allow you to run queries from home.

Changelog

Febrary 15, 2023

Added view opendata.AuditEntries

January 20, 2023

Added view opendata.UserDeviceDetails.

November 2nd, 2021

Created logical groupings of the various views under major headings.

Added the following views, which will become generally available after AWS0330 goes to Production.

  • RecAssetsNS
  • RecAssetsFailedRNS
  • RecNonHolderNSAssets
October 4th, 2021

Adding CollisionAssets, which will become generally available after AWS0329 goes to Production.

January 8th, 2021

Added BillToAddress and ShipToAddress to the Holders view.

August 12th, 2020

Added the following views:

  • Containers
  • RecContainers

Added new columns to the following views:

  • Assets
    • ContainerId
  • RecAssets
    • ContainerId
    • ContainerTrackNum
    • ContainerCrossRef
August 11th, 2020

Added HolderServicingLocations view.

July 1st, 2020

Added the IntegrationMessageQueue View.

June 4th, 2020

Added the following views:

  • AssetAgreementInvoices
  • AssetFamilies
  • AssetFamilyCollections
  • AssetFamilyMembers
  • RentalBillAddresses
  • RentalBillAssetAgreements
  • RentalBillBalanceChanges
  • RentalBillFlatFeesApplied
  • RentalBillLineItems
  • RentalBillRateDetails
  • RentalBills
  • RentalBillSurcharges
  • RentalClasses
  • RentalClassGroups
  • RentalRates
  • RentalRateAudit
  • RentalRateTables
  • TelemetryDevices
  • UsePointClassifications
  • UsePointReadings
  • UsePoints

Added new columns to the following views:

  • AssetAgreements
    • OriginalStartDateUtc
    • RenewalTermInMonths
    • PaymentTerms,
    • CustomerAgreesToAutoRenew,
    • LegacyAgreementNumber,
    • AccountingInvoiceNumber,
    • NotifyUserOfExpiration,
    • RentalBillId,
  • Assets
    • LocationId
    • LocationTypeId
  • AssetTypes
    • RentalClassId
    • RentalClassName
  • CustomAssetInfo
    • OptionId
  • RecAssets
    • RecDate
    • IsLocSetting
    • IsTrackNumKeyedIn
    • FoundBy
    • FoundByDescription
    • AssetTypeId
    • Volume
  • RecordDetails
    • HowEnteredAccountingCode
    • AccountingUserId
    • IsHolderStrKeyedIn
    • IsControlStrKeyedIn
    • IsPurchaseOrderKeyedIn
  • Records
    • AccountingUsrId
    • IsAHandoff
    • CreatedNewCustomer
    • VerifyUserId
    • VerifyUserFirstName
    • VerifyUserLastName
    • VerifyAccountingUserId
    • VerifyDate
    • HowVerified
    • HowVerifiedDescription
    • InvestigationStatus
    • InvestigationStatusDescription
February 6th, 2020
Added the HolderId column to the Records and Assets tables.
January 28th, 2020
Added the following columns to the Orders table: DeliveryTimeWindow, OrderCommentsToPrint, OrderCommentsToDisplay, OrderType
Added opendata.OrderAssets.
January 21st, 2020
Added AssetAgreements, AssetAgreementLineItems, AssetAgreementAssets, RentalFlatFees, RentalFlatFeeHolders, RentalFlatFeeOverrides and RentalFlatFeeAssetClasses views.
August 27, 2019
Added RecordPostFillDetails and RecordPostFillAnalytes views.
May 15, 2019
Added RecAssetsSetVolume and HolderNSBalance tables.
April 29, 2019
Added RecAssetsSetUseState and UseStates tables.
November 16, 2018
Extended RecAssets view by adding the LotNumber column.
November 1, 2018
Added RecordSubActions view
September 26, 2018
Added AssetCatId and AssetCatName columns to AssetTypes view
August 29, 2018
Added views CustomInfo, Orders, OrderHardGoodItems