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.

Getting Set Up

Pricing

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

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

Requesting Access

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 connection details, which include a network server address, database name, username and password.

Your database is hosted in Microsoft Azure SQL Database. Microsoft's documentation around Azure SQL Database is excellent, so we will not reproduce instructions on how to connect here.

There are many tools that can connect to Azure SQL Database. We highly recommend Microsoft's SQL Server Management Studio (SSMS). SSMS became a standalone, free product in January 2017. We also recommend using the latest version available.

See the article Azure SQL Database: Use SQL Server Management Studio to connect and query data.

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.

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

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.

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

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

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

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