OpenData

About

TrackAbout OpenData is a beta feature. OpenData enables customers to access a restricted view of their SQL data stored by TrackAbout. If you're interested in being able to write your own SQL queries to retrieve TrackAbout data, please contact support@trackabout.com.

Accessing OpenData

OpenData is hosted with and served from Microsoft Azure SQL Database. To access OpenData, customers will need to be able to log into an Azure SQL Database. Connecting to Azure SQL Database is nearly the same as to connecting to a Microsoft SQL Server database.

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

If you have signed up for the OpenData offering, TrackAbout will share connection string and credential data through support channels.

The Tables

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.

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.