Please visit our new TrackAbout Knowledge Base at https://supportkb.trackabout.com for the most-up-to-date documentation on TrackAbout and TrackAbout Mobile.
OpenData
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
- 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.
- 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.
- 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:
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. |
| 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. |
| 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.
|
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.
|
| 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. |
| LocationStr | The 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.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.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 |
| ProductCodeName | Product code (aka part number, SKU, etc) |
| ProductCodeDescription | Longer description of the product code. |
| GeolocationId | Reference to a row in the Geolocations table, or 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.
|
| 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 |
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. |
| 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. |
| 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.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 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. |
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.