OpenData

About

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

Request Access to OpenData

Pricing

TrackAbout charges an 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.

Please provide:

• Your company network's static IP address from where SQL queries will originate (see next section)

To get access to your OpenData database, you must first provide TrackAbout with a static IP address.

Your SQL queries must originate from this static IP address. If you are on a computer on your corporate network, then likely your queries will appear to TrackAbout to be coming from your static IP address.

The static IP address you give to TrackAbout will be added to your database's IP firewall. This will allow only your company to access the database.

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

If you know 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: 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: 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.

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

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:

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 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 or slow down users who abuse their databases.

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:

• AT TIME ZONE
• SWITCHOFFSET

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.

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.
LocationStr	If an asset is currently in-house (at an internal location), this is the Location ID of that location.
LocationName	If an asset is currently in-house (at an internal location), this is the name of that location.
TrackNum	The tracking number, also known as a tag, barcode or RFID tag unique ID.
CrossRef	The serial number of the asset. Usually this exists on the asset as an indelible stamp or etched into a name plate. See Serial Number for more information on how serial numbers might be scrubbed or transformed on the way into the database.
Status	Statuses are Active, Lost, or Retired.
AssetCatName	Asset Category Name as shown in TrackAbout.
AssetGroupName	Asset Group Name as shown in TrackAbout.
AssetTypeName	Asset Type Name as shown in TrackAbout.
ProductCodeName	Product code, part number, catalog number.
OwnershipName	Designated owner of the asset, full name.
OwnershipStr	If set in TrackAbout, this is the unique ID which is associated with the OwnershipName. May be blank.
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.

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.
DeviceId	Lookup ID for the DeviceDetails table. Only rugged TAMobile 6 devices are tracked. Smartphone/tablet devices are not tracked in the same way.

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.
UserId	Foreign key to the UserDetails table.
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.

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

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

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.

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.

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

opendata.Addresses

Addresses store billing and shipping addresses for orders. Orders.BillToAddressId and Orders.ShipToAddressId 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

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.
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
PurchaseOrder	The Purchase Order number against which the Asset Agreement was created.
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.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.

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.

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

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