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)
• What client tools you intend to use to query the database. Is it SQL Server Management Studio? Tableau? Custom code? This will dictate the level of login security we can use.

Static IP Address Required

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.
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 RecordDetails which indicates which user caused the reading to be taken.

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