Please visit our new TrackAbout Knowledge Base at https://supportkb.trackabout.com for the most-up-to-date documentation on TrackAbout and TrackAbout Mobile.
Difference between revisions of "OpenData"
Lsilverman (talk | contribs) |
Lsilverman (talk | contribs) |
||
| Line 73: | Line 73: | ||
| 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. | | 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. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.Records== | ==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. | 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. | ||
| Line 165: | Line 165: | ||
| 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. | | 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. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.RecordDetails== | ==opendata.RecordDetails== | ||
The nature of the RecordDetails is explained in the prior section about Records. | The nature of the RecordDetails is explained in the prior section about Records. | ||
| Line 216: | Line 216: | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.CustomAssetInfo== | ==opendata.CustomAssetInfo== | ||
CustomAssetInfo contains current asset properties, not historical changes. The most recently set values are stored in the CustomAssetInfo table. | CustomAssetInfo contains current asset properties, not historical changes. The most recently set values are stored in the CustomAssetInfo table. | ||
| Line 265: | Line 265: | ||
|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. | |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. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.Geolocation== | ==opendata.Geolocation== | ||
See [[TA Smartphone - Geolocation]]. | See [[TA Smartphone - Geolocation]]. | ||
| Line 297: | Line 297: | ||
|Timestamp when the reading was taken. | |Timestamp when the reading was taken. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.RecAssets== | ==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. | 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. | ||
| Line 346: | Line 346: | ||
|Reference to a row in the Geolocations table, or <code>NULL</code> | |Reference to a row in the Geolocations table, or <code>NULL</code> | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.RecAssetsSetCustomAssetInfo== | ==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. | 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. | ||
| Line 391: | Line 391: | ||
|Same definitions as CustomAssetInfo table, refer to it. | |Same definitions as CustomAssetInfo table, refer to it. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.RecordFormData== | ==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. | 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. | ||
| Line 420: | Line 420: | ||
|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. | |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. | ||
|} | |} | ||
| − | + | <br/><br/> | |
==opendata.RecordGeolocation== | ==opendata.RecordGeolocation== | ||
In addition to geolocation coordinates collected for each asset scan (found in <code>RecAssets</code> linking to the <code>Geolocations</code> 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 <code>Geolocations</code>. | In addition to geolocation coordinates collected for each asset scan (found in <code>RecAssets</code> linking to the <code>Geolocations</code> 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 <code>Geolocations</code>. | ||
Revision as of 11:04, 7 June 2017
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 | 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 in DeviceDetails. Smartphone/tablet devices are not tracked in the same way. |
opendata.RecordDetails
The nature of the 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. |
| 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.
|
opedata.UserDetails
| opendata.RecordFormData | |
|---|---|
| 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. |
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.