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 served up 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	Database-assigned primary key.
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 in DeviceDetails. Smartphone/tablet devices are not tracked in the same way.

opendata.CustomAssetInfo

opendata.CustomAssetInfo
Column	Description
AssetId		Name		DataType		StringValue		IntegerValue		DateValue		DecimalValue		BooleanValue		OptionDisplayName		OptionValue

opendata.Geolocation

opendata.Geolocation
Column	Description
Id		Latitude		Longitude		Accuracy		Altitude		MeasurementDate

opendata.RecAssets

opendata.RecAssets
Column	Description
RecordId		AssetId		RecDetailId		IsDeliver		EnterDate		TrackNum		CrossRef		AssetCatName		AssetGroupName		AssetTypeName		ProductCodeName		ProductCodeDescription		GeolocationId

opendata.RecAssetsSetCustomAssetInfo

opendata.RecAssetsSetCustomAssetInfo
Column	Description
RecordId		AssetId		Name		DataType		StringValue		IntegerValue		DateValue		DecimalValue		BooleanValue		OptionDisplayName		OptionValue

opendata.RecordDetails

opendata.RecordDetails
Column	Description
RecordId		RecDetailId		StardDate		RecDate		DBEnterDate		HowEntered		UserId		FirstName		LastName		DeviceId		PurchaseOrder		SignerName

opendata.RecordFormData

opendata.RecordFormData
Column	Description
RecordId		FieldName		FieldValue		FieldSuffix

opendata.RecordGeolocation

opendata.RecordGeolocation
Column	Description
Id		RecordId		GeolocationId

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.