Importing Data

Below listed are column names and data types required for the correct file structure when importing into Transtream. There are example files in Downloads. If using these file structures to onboard carriers or users, see Onboard Carriers or Import Users for related guidance.

Identify Database Rows

Database rows are identified using keys. The keys are used to target database entities and update or create rows during data import. There are several keys associated with each table, which are prioritized and used in the following hierarchy:

  • Primary Keys. The table's ID. This value can be exposed during data export.
  • Alternative Primary Keys. Other rows from the table that can be used to identify it. These keys can be utilized if the ID is not known.
  • Foreign Keys. Rows from related tables used to identify the table.
  • Alternative Foreign Keys. Additional rows from related tables used to identify the table. Where used, the field that it is an alternative to is listed.
Operating with multiple alternative keys
If including multiple alternative keys in import files, note that data rows must have a unique combination of the alternative key fields in order for a new row to be added. If a row exists containing identical alternative key field values as the row you are attempting to import, Transtream updates the existing entry instead of creating a new data row. See Process for detail on how Transtream identifies database rows.

Accounts

Supported entity column headings and data types for [dbo.Accounts].

Column Name Type Key Required Notes
ID int PK - -
AccountNumber string Alt PK Yes -
Description string Alt PK Yes -
CarrierID int FK - A valid value, or alternative keys, must be provided.
CarrierName string Alt FK - Alternative to CarrierID. A valid value, or alternative keys, must be provided.
SignatureReleaseNumber string - - -
DisplayAccountNumber string - - -
AccountTypeID int FK Yes A valid value, or alternative keys, must be provided.
PackageNumberID int - - -
SmartPostHubID int - - -
MailerID string - - -
TrackingNumberID int - - -
PostalServicePassword string - - -
PostalServiceUserID string - - -
EntryFacilityZipCode string - - -
ManifestNumberRange int - - -
BPODPassword string - - -
CurrencyCodeID int FK - -
CODCurrencyCodeID int FK - -
CarrierSettings string - - -
ShipmentIdentifierID int FK - -
DefaultPostalMeter string - - -
USPSPermitCityState string - - -
USPSPermitNumber string - - -
USPSPaymentTypeID int - - -
USPSPostOfficeZipCode string - - -
AccountTypeDescription string Alk FK - Alternative to AccountTypeID. A valid value, or alternative keys, must be provided.
ShipmentIdentifierIDDescription string Alk FK - Alternative to ShipmentIdentifierID.
TrackingNumberIDDescription string Alk FK - Alternative to TrackingNumberID.
PackageNumberIDDescription string Alk FK - Alternative to PackageNumberID.
AutoCloseUserName string Alk FK - Alternative to AutoCloseUserID.
CurrencyCode string Alk FK - Alternative to CurrencyCodeID.
CODCurrencyCode string Alk FK - Alternative to CODCurrencyCodeID.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Account Codes

Supported entity column headings and data types for [dbo.AccountCodes].

Column Name Type Key Required
ID int PK -
Code string
(50)
Alt PK Yes
Description string
(250)
- Yes
PK: Primary Key, Alt PK: Alternative Primary Key

Top

Address Book Type Permissions

Supported entity column headings and data types for [dbo.AddressBookTypePermissions].

Column Name Type Key Required Notes
UserID int FK - -
ProfileID int FK - -
AddressBookTypeID int FK - A valid value, or alternative keys, must be provided.
UserEmail string Alt FK - Alternative to UserID.
ProfileDescription string Alt FK - Alternative to ProfileID.
AddressBookType Description string Alt FK - Alternative to AddressBookTypeID. A valid value, or alternative keys, must be provided.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Bulk Lists

Supported entity column headings and data types for [dbo.BulkLists].

Column Name Type Key Required Notes
ID int PK - -
Name string Alt PK Yes -
Description string - Yes -
Color string - - -
UserID int - - -
BulkListTypeID int - - A valid value, or alternative keys, must be provided.
BulkListTypeDescription string - - A valid value, or alternative keys, must be provided.
UserEmail string Alt FK - Alternative to UserID.
PK: Primary Key, Alt PK: Alternative Primary Key, Alt FK: Alternative Foreign Key

Top

Bulk List Contacts

Supported entity column headings and data types for [dbo.BulkListContacts]. Note that updates are not supported to this entity.

Column Name Type Key Required Notes
BulkListID int PK - A valid value, or alternative keys, must be provided.
ContactID int PK - A valid value, or alternative keys, must be provided.
AttentionTo string Alt FK - Alternative to ContactID. A valid value, or alternative keys, must be provided.
Address1 string Alt FK - Alternative to ContactID. A valid value, or alternative keys, must be provided.
City string Alt FK - Alternative to ContactID. A valid value, or alternative keys, must be provided.
Country string Alt FK - Alternative to ContactID. A valid value, or alternative keys, must be provided.
AddressBookTypeID int Alt FK - Alternative to ContactID. A valid value, or alternative keys, must be provided.
BulkListName string Alt FK - Alternative to BulkListID. A valid value, or alternative keys, must be provided.
BulkListDescription string Alt FK - Alternative to BulkListID. A valid value, or alternative keys, must be provided.
BulkListTypeID int Alt FK - Alternative to BulkListID. A valid value, or alternative keys, must be provided.
PK: Primary Key, Alt FK: Alternative Foreign Key

Top

Carrier Rating

Supported entity column headings and data types for [dbo.CarrierRating].

Column Name Type Key Required Notes
ID int PK - -
CarrierID int FK - A valid value, or alternative keys, must be provided.
CarrierServiceID int FK - -
CarrierPackageTypeID int FK - -
RateTypeID int FK - A valid value, or alternative keys, must be provided.Note RateTypeIDs 1 to 9 cannot be amended as they are base data.
RateType string Alt FK - Any custom Rate Type must be manually added before import.
RateBaseTypeID string FK - A valid value, or alternative keys, must be provided.
AccountID int FK - -
RateEngineID int FK - -
DisplayRate bool - - -
UpdateRate bool - - -
LocationID int FK - -
RateAsCarrierID int FK - -
RateAsCarrierServiceID int FK - -
RateAsCarrierPackageTypeID int FK - -
Optional bool - - -
RequiredCustomRateEngineSuccess bool - - -
RateBaseType string Alt FK - Alternative to RateBaseTypeID.
CarrierName string Alt FK - Alternative to CarrierID.
CarrierServiceDescription string Alt FK - Alternative to CarrierServiceID.
CarrierPackageTypeDescription string Alt FK - Alternative to CarrierPackageTypeID.
AccountNumber string Alt FK - Alternative to AccountID.
AccountDescription string Alt FK - Alternative to AccountID.
RateEngineDescriptions string Alt FK - Alternative to RateEngineID.
LocationDescription string Alt FK - Alternative to LocationID.
Address1 string Alt FK - Alternative to LocationID.
PostalCode string Alt FK - Alternative to LocationID.
CountryCode string Alt FK - Alternative to LocationID.
RateAsCarrierName string Alt FK - Alternative to RateAsCarrierID.
RateAsCarrierServiceDescription string Alt FK - Alternative to RateAsCarrierServiceID.
RateAsCarrierPackageTypeDescription string Alt FK - Alternative to RateAsCarrierPackageTypeID.
PK: Primary Key, Alt FK: Alternative Foreign Key

Top

Carrier Rating Options

Supported entity column headings and data types for [dbo.CarrierRatingOptions].

Column Name Type Key Required Notes
ID int PK - -
CarrierRatingID int FK Yes A valid value, or alternative keys, must be provided.
RatingOptionEnabled bool - Yes -
ChargeTypeID int FK - A valid value, or alternative keys, must be provided.
RateOptionMarkupTypeID int FK - A valid value, or alternative keys, must be provided.
RateOptionApplicationTypeID int FK - A valid value, or alternative keys, must be provided.
RateOptionAmount int - Yes -
RateOptionApplyToTypeID int FK - A valid value, or alternative keys, must be provided.
RateOptionOrder int - Yes -
CreateIfMissing int - Yes -
ChargeType string Alt FK - Alternative to ChargeTypeID.
RateOptionMarkupType string Alt FK - Alternative to RateOptionMarkupTypeID.
RateOptionApplicationType string Alt FK - Alternative to RateOptionApplicationTypeID.
RateOptionApplyToType string Alt FK - Alternative to RateOptionApplyToTypeID.
PK: Primary Key, Alt FK: Alternative Foreign Key

Top

Carton Configurations

Supported entity column headings and data types for [dbo.CartonConfigurations].

Column Name Type Key Required
ID int PK -
Name string Alt PK Yes
Description string - Yes
PackageCode string - -
Width decimal - Yes
Length decimal - Yes
Height decimal - Yes
Weight decimal - Yes
PK: Primary Key, Alt PK: Alternative Primary Key

Top

Cartonization Groups

Supported entity column headings and data types for [dbo.CartonizationGroups].

Column Name Type Key Required
ID int PK -
Description string Alt PK Yes
Enabled bool - Yes
PK: Primary Key, Alt PK: Alternative Primary Key

Top

Cartonization Group Config Mapping

Supported entity column headings and data types for [dbo.CartonizationGroupConfig Mapping]. The primary key is required to make any changes to this table.

Column Name Type Key Required Notes
ID int PK - -
CartonConfigurationID int FK - A valid value, or alternative keys, must be provided.
CartonizationGroupID int FK - A valid value, or alternative keys, must be provided.
CartonConfiguration string Allt FK - Alternative to CartonConfigurationID. A valid value, or alternative keys, must be provided.
CartonizationGroup string Alt FK - Alternative to CartonizationGroupID. A valid value, or alternative keys, must be provided.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Carton Package Mapping

Supported entity column headings and data types for [dbo.CartonPackageMappings]. The primary key is required to make any changes to this table.

Column Name Type Key Required Notes
ID int PK - -
CarrierID int FK Yes A valid value, or alternative keys, must be provided.
CartonConfigurationID int FK Yes A valid value, or alternative keys, must be provided.
CarrierPackageTypeID int FK Yes A valid value, or alternative keys, must be provided.
Carrier string Alt FK - Alternative to CarrierID. A valid value, or alternative keys, must be provided.
CartonConfigurtion string Alt FK - Alternative to CartonConfigurationID. A valid value, or alternative keys, must be provided.
CarrierPackageType string Alt FK - Alternative to CartonPackageTypeID.A valid value, or alternative keys, must be provided.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Company Codes

Supported entity column headings and data types for [dbo.CompanyCodes].

Column Name Type Key Required
ID int PK -
Code string
(50)
Alt PK Yes
Description string
(250)
- Yes
PK: Primary Key, Alt PK: Alternative Primary Key

Top

Contacts

Supported entity column headings and data types for [dbo.Contacts].

Column Name Type Key Required Notes
ID int PK - -
CustomerReference string Alt PK - -
AttentionTo string Alt PK Yes -
Organization string Alt PK - -
Address1 string - Yes -
Address2 string - - -
Address3 string - - -
City string - Yes -
Region string - - -
PostalCode string - - -
Country string - Yes -
Phone string - - -
AlternatePhone string - - -
Email string - - -
Notes string - - -
Residential bool - - -
UserID int FK - -
DUNS string - - -
LocationIdentifier string - - -
OutputItemPath string - - -
AddressBookTypeID int FK - A valid value, or alternative keys, must be provided.
ExternalContactID string - - -
AddressValidated bool - - -
AddressBookTypeDescription string Alt FK - Alternative to AddressBookTypeID. A valid value, or alternative keys, must be provided.
UserEmail string Alt FK - Alternative to UserID.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Consolidated Shipping Defaults

Supported entity column headings and data types for [dbo.ConsolidatedShippingDefaults].

Column Name Type Key Required Notes
ID int PK - -
BrokerID int FK - -
BrokerUseCarrier bool - Yes -
BrokerBillingPayerTypeID int FK - -
BrokerBillingAccountNumber string - - -
ImporterOfRecordID int FK - -
ReturnToID int FK - -
WeightUOM string - - -
DimensionsUOM string - - -
Currency string - - -
TermsOfSaleTypeID int FK - -
LinehaulUseCarrier bool - Yes -
LinehaulSCAC string - - -
LinehaulBillingPayerTypeID int FK - -
LinehaulBillingAccountNumber string - - -
PortOfEntryID int FK - -
Description string - Yes -
ImporterOfRecordFacilityID int FK - -
BrokerContact string Alt FK - Alternative to BrokerID.
BrokerOrganization string Alt FK - Alternative to BrokerID.
BrokerPayerTypeDescription string Alt FK - Alternative to BrokerBillingPayerTypeID.
ImporterOfRecordContact string Alt FK - Alternative to ImporterOfRecordID.
ImporterOfRecordOrganization string Alt FK - Alternative to ImporterOfRecordID.
ReturnToContact string Alt FK - Alternative to ReturnToID.
ReturnToOrganization string Alt FK - Alternative to ReturnToID.
TermsOfSaleTypeDescription string Alt FK - Alternative to TermsOfSaleID.
LinehaulBillingTypeDescription string Alt FK - Alternative to LinehaulBillingPayerTypeID.
PortOfEntryDescription string Alt FK - Alternative to PortOfEntryID.
ImporterOfRecordFacilityCode string Alt FK - Alternative to ImporterOfRecordFacilityID.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Cost Centers

Supported entity column headings and data types for [dbo.CostCenters].

Column Name Type Key Required Notes
ID int PK - -
Name string - Yes -
Description string - - -
CostCenterCode string Alt PK Yes -
Active bool - Yes 1 or 0
AccountID int FK - -
AccountNumber string Alt FK - Alternative to Account ID. If used, must match the account number of an existing account in the database. In case of duplicate accounts, the first one is used.
AccountDescription string Alt FK - Alternative to AccountID. If used, must match the account description of any existing account description in the database. In case of duplicate accounts, the first one is used.
ParentCostCenterID int FK - -
ParentCostCenterCode string - - Alternative to ParentCostCenterID. If used, then this must be either the code of a cost center that already exists in the database OR a cost center code that forms part of the import. If part of the import, it must be imported before any referring row, so ensure that parent cost centers are at the top of your import.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Custom Code Translations

Supported entity column headings and data types for [dbo.CustomCodeTranslations].

Column NameTypeKeyRequiredNotes
IDintPK--
CarrierIDintFK-A valid value, or alternative keys, must be provided.
CarrierNamestringFK-Alternative to CarrierID. A valid value, or alternative keys, must be provided.
CustomCarrierCodestringAlt PKY-
CarrierServiceTypeIDintFK--
CarrierServiceDescriptionstringAlt FK-Alternative to CarrierServiceTypeID.
CarrierPackageTypeIDintFK--
CarrierPackageTypeDescriptionstringAlt FK-Alternative to CarrierPackageTypeID.
PayerTypeIDintFL--
PayerTypeDescriptionstringAlt FK-Alternative to PayerTypeID.
CODTypeIDintFK--
CODTypeDescriptionstringAlt FK-Alternative to CODTypeID.
SaturdayDeliverybool---
Residentialbool---
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Customers

Supported entity column headings and data types for [dbo.Customers].

Column NameTypeKeyRequiredNotes
IDintPK--
CustomerReferencestringAlt PK--
Titlestring---
FirstNamestring---
Surnamestring---
AttentionTostring---
Organizationstring---
Address1string---
Address2string---
Address3string---
Citystring---
Regionstring---
PostalCodestring---
Countrystring---
Phonestring---
AlternatePhonestring---
Emailstring---
Notesstring---
Residentialbool---
UserNamestringAlt PK--
DUNSstring---
VendorNumberstring---
LocationIdentifierstringAlt PK--
OutputItemPathstring---
CustomerTypeIDintAlt PK, FK-A valid value, or alternative keys, must be provided.
CustomerTypeDescriptionstringAlt FK-Alternative to CustomerTypeID.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Importer Of Record Facilities

Supported entity column headings and data types for [dbo.ImporterOfRecordFacilities].

Column Name Type Key Required Notes
ID int PK - -
IORCode string FK Yes -
Description string FK Yes -
CountryCode string FK Yes -
PK: Primary Key, FK: Foreign Key

Top

International Defaults

Supported entity column headings and data types for [dbo.InternationalDefaults].

Column Name Type Key Required Notes
ID int PK - -
Description string - Yes -
SEDMethodID int FK - -
SEDMethodDescription string Alt FK - Alternative to SEDMethodID.
SEDExemptNumber string - - -
ExportReasonID int FK - -
ExportReasonDescription string Alt FK - Alternative to ExportReasonID
ExportCodeID int FK - -
ExportCodeDescription string Alt FK - Alternative to ExportCodeID.
IdentificationTypeID int FK - -
IdentificationTypeDescription string Alt FK - Alternative to IdentificationTypeID.
Identifier string - - -
TermsOfSaleTypeID int FK - -
TermsOfSaleTypeDescription string Alt FK - Alternative to TermsOfSaleTypeID.
DutyPayerTypeID int FK - -
DutyPayerTypeDescription string Alt FK - Alternative to DutyPayerTypeID.
DutyPayerAccountNumber string - - -
ExportLicense string - - -
ExportLicenseStarts datetime - - -
ExportLicenseExpires datetime - - -
ImportLicense string - - -
ImportLicenseExpires datetime - - -
B13AMethodID int FK - -
B13AMethodDescription string Alt FK - Alternative to B13AMethodID.
B13AStatementData string - - -
CanadaExportPermitNumber string - - -
ExportDeclaration string - - -
ExportIdentificationTypeID int FK - -
ExportIdentificationTypeDescription string Alt FK - Alternative to ExportIdentificationTypeID.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Locations

Supported entity column headings and data types for several tables that constitute to importing Locations detail. The Notes column lists related table names.

Use the LocationsWithAddresses database view to view the list of entities at the database level.

Column Name Type Key Required Notes
ID int PK - -
Description string Alt PK Yes -
SSCCLocationIdentifier string - - -
Address1 string Alt PK Yes dbo.Addresses
Address2 string - - dbo.Addresses
Address3 string - - dbo.Addresses
City string - Yes dbo.Addresses
Region string - - dbo.Addresses
PostalCode string Alt PK Yes dbo.Addresses
CountryCode string Alt PK Yes dbo.Addresses
UseSameAddressForReturn bool - - -
ReturnAddress1 string - Yes, if UseSameAddressForReturn = False dbo.Addresses
ReturnAddress2 string - - dbo.Addresses
ReturnAddress3 string - - dbo.Addresses
ReturnCity string - Yes, if UseSameAddressForReturn = False dbo.Addresses
ReturnRegion string - Yes, if UseSameAddressForReturn = False dbo.Addresses
ReturnPostalCode string - Yes, if UseSameAddressForReturn = False dbo.Addresses
ReturnCountryCode string - Yes, if UseSameAddressForReturn = False dbo.Addresses
UseSameAddressForCOD bool - - dbo.Addresses
CODRemittanceCompany string - - dbo.Addresses
CODRemittanceContactName string - - dbo.Addresses
CODRemittancePhoneNumber string - - dbo.Addresses
CODRemittanceAddress1 string - Yes, if UseSameAddressForCOD = False dbo.Addresses
CODRemittanceAddress2 string - - dbo.Addresses
CODRemittanceAddress3 string - - dbo.Addresses
CODRemittanceCity string - Yes, if UseSameAddressForCOD = False dbo.Addresses
CODRemittanceRegion string - Yes, if UseSameAddressForCOD = False dbo.Addresses
CODRemittancePostalCode string - Yes, if UseSameAddressForCOD = False dbo.Addresses
CODRemittanceCountryCode string - Yes, if UseSameAddressForCOD = False dbo.Addresses
ReturnCompany string - - -
ReturnContactName string - - -
ReturnPhoneNumber string - - -
LocationsExceeded bool - - -
LocalizationID int FK - -
LocalizationDescription string Alt FK - Alternative to LocalizationID. dbo.Localizations
OrganizationID int FK - -
OrganizationName string Alt FK - Alternative to OrganizationID. dbo.Organizations
TimezoneID int FK - -
TimezoneName string Alt FK - Alternative TimezoneID. dbo.Timezones
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Number Ranges (Package Numbers)

Supported entity column headings and data types for [dbo.PackageNumbers]

Column Name Type Key Required Notes
ID int PK - -
Start int - Yes -
Stop int - Yes -
Present int - - -
Prefix string - - -
Description string - - -
Increment int - - -
PackageNumberTypeID int FK - A valid value, or alternative keys, must be provided.
PackageNumberType string Alt FK - Alternative to PackageNumberTypeID. A valid value, or alternative keys, must be provided.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Outputs

Supported entity column headings and data types for [dbo.Outputs]

Column Name Type Key Required Notes
ID int PK - -
UserID int FK - -
CarrierID int FK - -
CarrierName string Alt FK - Alternative to CarrierID.
OutputTypeID int FK - A valid value, or alternative keys, must be provided.
OutputBaseTypeID int FK - -
OutputBaseType int Alt FK - Alternative to OutputBaseTypeID.
OutputTypeName string Alt FK - Alternative to OutputTypeID. A valid value, or alternative keys, must be provided.
OutputProducerID int FK - A valid value, or alternative keys, must be provided.
OutputProducer string Alt FK - Alternative to OutputProducerID. A valid value, or alternative keys, must be provided.
OutputPrinterID int FK - A valid value, or alternative keys, must be provided.
OutputPrinter string Alt FK - Alternative to OutputPrinterID. A valid value, or alternative keys, must be provided.
OutputFormatID int FK - A valid value, or alternative keys, must be provided.
OutputFormat string Alt FK - Alternative to OutputFormatID. A valid value, or alternative keys, must be provided.
CustomLabelID int FK - -
CustomLabel string Alt FK - Alternative to CustomLabelID.
OutputStockID int FK - -
OutputStock string Alt FK - Alternative to OutputStockID.
PrinterID int FK - -
Printer string Alt FK - Alternative to PrinterID
PrinterMarginLeft int - - -
PrintMarginTop int - - -
PrintRotateTypeID int FK - -
PrintRotateType string Alt FK - Alternative to PrintRotateTypeID.
PrintResizePercentage int - - -
ViewRotateTypeID int FK - -
ViewRotateType string Alt FK - Alternative to ViewRotateTypeID.
ViewResizePercentage int - - -
ItemPath string - - -
Copies int - - -
SqlCommand string - - -
PrintLater bool - - -
ProfileID int FK - -
Profile string Alt FK - Alternative to ProfileID.
CustomPrintEngineID int FK - -
CustomPrintEngine string Alt FK - Alternative to CustomPrintEngineID.
AutoCrop bool - - -
AutoCropRotateTypeID int FK - -
AutoCropRotateType string Alt FK - Alternative to AutoCropRotateTypeID.
AutoCropResizeTypeID int FK - -
AutoCropResizeType string Alt FK - Alternative to AutoCropResizeTypeID.
AutoCropResizePercentage int - - -
TopPosition int - - -
LeftPosition int - - -
PrintStockID int FK - -
PrintStock int Alt FK - Alternative to PrintStockID.
Paginate bool - - -
LetBrowserResize bool - - -
PermissionType string - - -
HubCappUserOverride String - - -
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Pending

Supported entity column headings and data types for [dbo.Pending].

Column NameTypeKeyRequiredNotes
FunctionIDintFKYesA valid value, or alternative keys, must be provided.
FunctionDescriptionstringAlt FK-Alternative to FunctionID. A valid value, or alternative keys, must be provided.
CarrierIDintFK-A valid value, or alternative keys, must be provided.
CarrierNamestringAlt FK-Alternative to CarrierID. A valid value, or alternative keys, must be provided.
CarrierPackageTypeIDintFK-A valid value, or alternative keys, must be provided.
CarrierPackageTypeDescriptionstringAlt FK-Alternative to CarrierPackageTypeID. A valid value, or alternative keys, must be provided.
CarrierServiceIDintFK-A valid value, or alternative keys, must be provided.
CarrierServiceDescriptionstringAlt FK-Alternative to CarrierServiceID. A valid value, or alternative keys, must be provided.
WayBillNumberstring---
ShipperReferencestring---
AttentionTostring---
Organizationstring---
Address1string---
Address2string---
Address3string---
Citystring---
Regionstring---
PostalCodestring---
CountryCodestring---
Phonestring---
Emailstring---
Weightreal---
Lengthreal---
Widthreal---
Heightreal---
CODTypeIDintFK-A valid value, or alternative keys, must be provided.
CODTypeDescriptionstringAlt FK-Alternative to CODTypeID. A valid value, or alternative keys, must be provided.
CODValuedecimal---
Holdbool---
InsuranceValuedecimal---
DespatchDatedatetime---
ContentDescriptionstring---
UseAlternateServicesbool---
DutyValuedecimal---
AESTransactionNumberstring---
DocumentsOnlybool---
Reference1string---
Reference2string---
Reference3string---
UserNamestring---
CustomCarrierCodestring---
RequiredDatedatetime---
PackageIDint---
ReturnToNamestring---
ReturnToOrganizationstring---
ReturnToAddress1string---
ReturnToAddress2string---
ReturnToAddress3string---
ReturnToCitystring---
ReturnToRegionstring---
ReturnToPostalCodestring---
ReturnToCountryCodestring---
ReturnToPhonestring---
BillingAccountNumberstring---
BillingAddress1string---
BillingAddress2string---
BillingAddress3string---
BillingCitystring---
BillingRegionstring---
BillingPostalCodestring---
BillingCountryCodestring---
BillingContactNamestring---
BillingPhonestring---
BillingEmailstring---
BillingPayerTypeIDintFK-A valid value, or alternative keys, must be provided.
BillingPayerTypeDescriptionstringAlt FK-Alternative to BillingPayerTypeID. A valid value, or alternative keys, must be provided.
DutyAccountNumberstring---
DutyPostalCodestring---
DutyCountryCodestring---
DutyPayerTypeIDintFK-A valid value, or alternative keys, must be provided.
DutyPayerTypeDescriptionstringAlt FK-Alternative to DutyPayerTypeID. A valid value, or alternative keys, must be provided.
Processbool---
TaskIdentifierstring---
BatchIdentifierstring---
PickListNumberstring---
DeliveryConfirmationbit---
PurchaseOrderNumberstring---
SalesOrderNumberstring---
PrintConditionalCustomLabel1bool---
PrintConditionalCustomLabel2bool---
PrintConditionalCustomLabel3bool---
Residentialbool---
AdditionalHandlingbool---
NonFlatMachinablebool---
NonMachinablebool---
NonRectangularbool---
Flatbool---
ShippingNotesstring---
Reference4string---
Reference5string---
TermsOfSaleTypeIDint---
TermsOfSaleConsigneeAccountNumberstring---
B13ATransactionNumberstring---
AccountNumberstring---
DateCloseddatetimeoffset---
ShippingKeystring---
Reference6string---
SaturdayDeliverybool---
CostCenterIDintFK-A valid value, or alternative keys, must be provided.
CostCenterCodestringAlt FK-Alternative to CostCenterID. A valid value, or alternative keys, must be provided. Cost Center Codes passed in import files must match existing system cost centers otherwise the import will fail.
CostCenterModestring---
ERRbool---
OutputsXmlstring---
Certifiedbool---
CurrencyCodeIDintFK-A valid value, or alternative keys, must be provided.
CurrencyCodestringAlt FK-Alternative to CurrencyCodeID. A valid value, or alternative keys, must be provided.
MailPieceNumberstring---
CODCurrencyCodestring---
DispositionMethodint---
RRMbool---
Registeredbool---
ReturnReceiptbool---
RestrictedDeliverybool---
MeterSerialNumberstring---
ProcessedTimeLogdatetimeoffset---
ErrorDescriptionstring---
RateGroupint---
RateGroupNamestring---
FilterModeint---
SortModeint---
FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Products

Supported entity column headings and data types for [dbo.Products]

Column Name Type Key Required
ID int PK -
ProductNumber string Alt PK Yes
ProductName string - Yes
ProductReference1 string - -
ProductReference2 string - -
ProductReference3 string - -
ProductReference4 string - -
Weight real - -
WeightUnitID int FK -
ContentCode string - -
ContainerTypeID int FK -
QtyPerContainer int - -
HTSUnitOfMeasure string - -
HTSScheduleBTariffCode string - -
HTSDescription string - -
HTSWeight real - -
HTSUnitValue real - -
Hazardous bool - -
HazAccessible bool - -
HazDOTIdentifier string - -
HazDOTClass string - -
HazDOTProperShippingName string - -
HazDOTLabelType string - -
HazDOTPackingGroup string - -
HazDOTSubClass string - -
HazDOTSymbols string - -
HazDOTSpecialProvisions string - -
HazDOTPackExceptions string - -
HazDOTPackBulk string - -
HazDOTPackNonBulk string - -
HazDOTPassengerQtyLimits real - -
HazDOTPassengerQtyLimitsUnitID int FK -
HazDOTPassengerQtyLimitsForbidden bool - Yes
HazDOTPassengerQtyLimitsNoLimit bool - Yes
HazDOTCargoQtyLimits real - -
HazDOTCargoQtyLimitsUnitID int FK -
HazDOTCargoQtyLimitsForbidden bool - Yes
HazDOTCargoQtyLimitsNoLimit bool - Yes
HazDOTVesselStowageLocation bool - -
HazDOTVesselStowageOther string - -
HazDOTMarinePollutant bool - Yes
HazDOTSMP bool - Yes
HazDOTLimitedQty real - -
HazDOTLimitedQtyUnitID int FK -
HazDOTReportableQty real - -
HazDOTReportableQtyUnitID int FK -
HazDOTCategoryID int FK -
HazDOTTechnicalName string - -
HazDOTHazardousShippingMethodID int FK -
HazDOTHazardousShippingModeID int FK -
HazDOTColumnTwoExceptions string - -
HazDOTSubsiduaryRisk string - -
HazDOTPermitTypeID int FK -
HazDOTPermitNumber string - -
HazDOTInhalationHazard bool - Yes
HazDOTInhalationHazardZone string - -
HazDOTCertificationMethodID int FK -
HazDOTPoisonous bool - -
HazDOTConsumerCommodity bool - Yes
HazDOTIsLimitedQuantity bool - -
HazDOTLabelCodesMask int - -
HazDOTIs500KGExempt bool - -
HazardousPackingInstructions string - -
IsHazSpecialProvisionA1A2A51A109 bool - -
ECCN string - -
CurrencyCodeID int FK -
Unid string - -
PackingGroup string - -
ProperShippingName string - -
NameKey string - -
HazardClass int - -
LabelCodes string - -
AirLabels string - -
Labels string - -
Special Provision string - -
SubsidiaryRisk string - -
MarinePollutant bool - -
Liquid bool - -
MaritimeOnly bool - -
Molton bool - -
CargoPackagingInstructions string - -
LimitedQuantityPackagingInstructions string - -
PassengerPackagingInstructions string - -
BulkPackingInstructions string - -
NonBulkPackingInstructions string - -
ExceptionPackingInstructions string - -
InstructionSelected int - -
Material Key string - -
TechnicalName string - -
InnerPackagingCount int - -
InnerPackagingType string - -
Quantity int - -
PackageInstructionType string - -
OtherRequiredInformation string - -
ReportableQuantity bool - -
Mixture bool - -
Solution bool - -
Solid bool - -
Stabilized bool - -
Waste bool - -
Hot bool - -
ElevatedTemperature bool - -
Empty bool - -
EmptyPackageType string - -
ToxicInhalationZone string - -
AdditionalQuantityInformation string - -
IsMaterialNonHazmat bool - -
IsMaterialNotInPackage bool - -
IsInnerPackagingLiquid bool - -
MaterialExemptions string - -
NetExplosiveQty string - -
Authorization string - -
ErgNumber string - -
PartNumber string - -
PartCompanyId int - -
NetExplosiveAbbreviation bool - -
ShowMarinePollutantLabel bool - -
ShowOrientationLabel string - -
CustomerInnerPackagingType string - -
QuantityKg string - -
PackageType string - -
Instructions string - -
AdditionalHandling string - -
HazardousRequiredInformation string - -
HazDOTCategoryDescription string - -
UOM string - -
HTSTotalValue real - -
WeightUnitDescription string - -
ContainerTypeSingularDescription string - -
HazDOTPassengerQtyLimitsUnitDescription string - -
HazDOTCargoQtyLimitsUnitDescription string - -
HazDOTLimitedQtyUnitDescription string - -
HazDOTReportableQtyUnitDescription string - -
HazDOTHazardousShippingMethodDescription string - -
HazDOTHazardousShippingModeDescription string - -
HazDOTHPermitTypeDescription string - -
HazDOTCertificationMethodDescription string - -
CurrencyCode string - -
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key

Top

Profiles

Supported entity column headings and data types for [dbo.Profiles].

Column Name Type Key Required Notes
ID int PK - -
Description string Alt PK Yes -
Enabled bool - Yes -
DefaultLocationID int FK - A valid value, or alternative keys, must be provided.
TimezoneID int FK - -
LocalizationID int FK - -
Name string - - -
IsDefault int - Yes -
DefaultLocationDescription string Alt FK - Alternative to DefaultLocationID. A valid value, or alternative keys, must be provided.
LocalizationDescription string Alt FK - Alternative to LocalizationID.
TimezoneName string Alt FK - Alternative to Timezone ID.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Rate Groups

Supported entity column headings and data types for [dbo.RateGroups].

Column Name Type Key Required
ID int PK -
Description string - Yes
DisplayOrder int - Yes
DisplayDefault bool - Yes
SystemDefault bool - Yes
PK: Primary Key

Top

Rate Group Members

Supported entity column headings and data types for [dbo.RateGroupMembers].

If the primary key is not provided, the only field that can be amended is DisplayOrder.

Column Name Type Key Required Notes
ID int PK - -
RateGroupID int FK - A valid value, or alternative keys, must be provided.
CarrierID int FK - A valid value, or alternative keys, must be provided.
CarrierServiceID int FK - -
CarrierPackageTypeID int FK - -
DisplayOrder int - Yes -
RateGroupDescription string Alt FK - Alternative to RateGroupID. A valid value, or alternative keys, must be provided.
CarrierName string Alt FK - Alternative to CarrierID. A valid value, or alternative keys, must be provided.
CarrierServiceName string Alt FK - Alternative to CarrierServiceID.
PackageTypeName string Alt FK - Alternative to CarrierPackageTypeID.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Third Parties

Supported entity column headings and data types for [dbo.ThirdParties].

Column Name Type Key Required Notes
ID int PK - -
ThirdPartyTypeID int FK - A valid value, or alternative keys, must be provided.
AccountID int - Yes -
AddressID int FK - A valid value, or alternative keys, must be provided.
Contact string - - -
Organization string - - -
Phone string - - -
Email string - - -
TaxIdentifier string - - -
TaxIdentifierTypeID int FK - -
BrokerReference string - - -
ThirdPartyType string Alt FK - Alternative to ThirdPartyTypeID. A valid value, or alternative keys, must be provided.
AccountDescription string Alt FK - Alternative to AccountID.
AccountNumber string Alt FK - Alternative to AccountID.
Address1 string Alt FK - A valid value, or alternative keys, must be provided.
Address2 string - - -
Address3 string - - -
City string Alt FK - A valid value, or alternative keys, must be provided.
Region string - - -
PostalCode string Alt FK - A valid value, or alternative keys, must be provided.
CountryCode string Alt FK - A valid value, or alternative keys, must be provided.
TaxIdentifierDescription string Alt FK - Alternative to TaxIdentifierID.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Security Credentials

Supported entity column headings and data types for [dbo.SecurityCredentials].

Column Name Type Key Required Notes
ID int PK - -
Description string - Yes -
CarrierID int FK - A valid value, or alternative keys, must be provided.
CarrierName string Alt FK - Alternative to CarrierID. A valid value, or alternative keys, must be provided.
Identifier string - - -
IsDefault bool - Yes -
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Shipping Keys

Supported entity column headings and data types for [dbo.ShippingKeys].

Column Name Type Key Required Notes
ID int PK - -
AccountID int FK - A valid value, or alternative keys, must be provided.
AccountDescription string Alt FK - Alternative to AccountID. A valid value, or alternative keys, must be provided.
AccountNumber string Alt FK - Alternative to AccountID. A valid value, or alternative keys, must be provided.
Description string - Yes -
ShippingKeyValue string - Yes -
ShippingKeyTypeID int FK - A valid value, or alternative keys, must be provided.
AdditionalShippingKeyProperties string - - -
ShippingKeyType string Alt FK - Alternative to ShippingKeyTypeID. A valid value, or alternative keys, must be provided.
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Shipping Order Header

Supported entity column headings and data types for [dbo.ShippingOrderHeader].

Column NameTypeKeyRequiredNotes
IDintPK--
OrderNumberstringAlt PKYes-
ShipDatedatetime---
ShipTostring---
ShipCompanystring---
ShipAddressOnestring---
ShipAddressTwostring---
ShipAddressThreestring---
ShipCitystring---
ShipStatestring---
ShipZipstring---
ShipCountrystring---
ShipPhonestring---
ShipEmailstring---
ShipResidentialbool---
BillTostring---
BillCompanystring---
BillAddressOnestring---
BillAddressTwostring---
BillAddressThreestring---
BillCitystring---
BillStatestring---
BillZipstring---
BillCountrystring---
BillPhonestring---
BillAccountNumberstring---
ReturnTostring---
ReturnCompanystring---
ReturnAddressOnestring---
ReturnAddressTwostring---
ReturnAddressThreestring---
ReturnCitystring---
ReturnStatestring---
ReturnZipstring---
ReturnCountrystring---
ReturnPhonestring---
ReturnEmailstring---
OrderPurchaseOrderNumberstring---
OrderSalesOrderNumberstring---
OrderShipperReferencestring---
OrderReferenceOnestring---
OrderReferenceTwostring---
OrderReferenceThreestring---
OrderShipViaCodestring---
OrderValuestring---
OrderCommentsstring---
DeliverBydatetime---
CustomerNumberstring---
RecordKeyOnestring---
RecordKeyTwostring---
RecordKeyThreestring---
RecordKeyFourstring---
RecordKeyFivestring---
RecordKeySixstring---
CustomIdentifierstring---
Shippedbool---
WaybillNumberstring---
ShippingCostdecimal---
ShippingTotalCostdecimal---
ShippingAccessorialCostdecimal---
ShippingOtherCostdecimal---
RecordKeySevenstring---
RecordKeyEightstring---
RecordKeyNinestring---
RecordKeyTenstring---
RecordKeyElevenstring---
RecordKeyTwelvestring---
RecordKeyThirteenstring---
RecordKeyFourteenstring---
RecordKeyFifteenstring---
RecordKeySixteenstring---
RecordKeySeventeenstring---
RecordKeyEighteenstring---
RecordKeyNineteenstring---
RecordKeyTwentystring---
ExternalSystemTypeint---
PK: Primary Key, Alt PK: Alternative Primary Key

Top

User Defaults

Supported entity column headings and data types for [dbo.UserDefaults]. The primary key is required to make any changes to this table.

Column Name Type Key Required Notes
ID int PK - -
UserID int FK - -
CarrierID int FK - A valid value, or alternative keys, must be provided.
SecurityCredentialID int FK - -
AccountID int FK - A valid value, or alternative keys, must be provided.
ShippingKeyID int FK - -
BillingPayerTypeID int FK - A valid value, or alternative keys, must be provided.
BillingAccountNumber string - - -
FunctionID int FK - -
InternationalDefaultID int FK - -
Denied bool - Yes -
IsDefault bool - Yes -
ConsolidatedShippingDefaultID int FK - -
ProfileID int FK - -
UserEmail string Alt FK - Alternative to UserID.
CarrierName string Alt FK - Alternative to CarrierID.
AccountNumber string Alt FK - Alternative to AccountID.
AccountDescription string Alt FK - Alternative to AccountID.
ProfileDescription string Alt FK - Alternative to ProfileID.
FunctionDescription string Alt FK - Alternative to FunctionID.
BillingPayerTypeDescription string Alt FK - Alternative to BillingPayerTypeID.
SecurityCredentialDescription string Alt FK - Alternative to SecurityCredentialID.
ShippingKeyDescription string Alt FK - Alternative to ShippingKeyID.
InternationalDefaultDescription string Alt FK - Alternative to InternationalDefaultID.
ConsolidatedShippingDefaultDescription string Alt FK - Alternative to ConsolidatedShippingDefaultID.
RequestFromCarrier bool - - If set to True, used to specify additional data to be requested from the carrier's API. See Onboard Carriers for detail.
AccountUserTitle string - Yes, if RequestFromCarrier = True -
AccountCompanyName string - Yes, if RequestFromCarrier = True -
AccountUserFirstName string - Yes, if RequestFromCarrier = True -
AccountUserLastName string - Yes, if RequestFromCarrier = True -
AccountUserEmail string - Yes, if RequestFromCarrier = True -
AccountUserPhone string - Yes, if RequestFromCarrier = True -
AccountInvoiceNumber string - Yes, if RequestFromCarrier = True -
AccountInvoiceDate string - Yes, if RequestFromCarrier = True -
AccountInvoiceAmount string - Yes, if RequestFromCarrier = True -
AccountInvoiceCurrencyCode string - Yes, if RequestFromCarrier = True -
AccountInvoiceControlID string - Yes, if RequestFromCarrier = True -
ShippingLocationAddress1 string - Yes, if RequestFromCarrier = True -
ShippingLocationCity string - Yes, if RequestFromCarrier = True -
ShippingLocationRegion string - Yes, if RequestFromCarrier = True -
ShippingLocationPostalCode string - Yes, if RequestFromCarrier = True -
ShippingLocationCountryCode string - Yes, if RequestFromCarrier = True -
BillingLocationAddress1 string - Yes, if RequestFromCarrier = True -
BillingLocationCity string - Yes, if RequestFromCarrier = True -
BillingLocationRegion string - Yes, if RequestFromCarrier = True -
BillingLocationPostalCode string - Yes, if RequestFromCarrier = True -
BillingLocationCountryCode string - Yes, if RequestFromCarrier = True -
ContactLocationAddress1 string - Yes, if RequestFromCarrier = True -
ContactLocationCity string - Yes, if RequestFromCarrier = True -
ContactLocationRegion string - Yes, if RequestFromCarrier = True -
ContactLocationPostalCode string - Yes, if RequestFromCarrier = True -
ContactLocationCountryCode string - Yes, if RequestFromCarrier = True -
PK: Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Users

Supported entity column headings and data types for [dbo.Users].

When importing users an additional option presents in the Data Management wizard, Enable User Import Batching?, determining the number of records imported at one time.

Column Name Type Key Required Notes
ID int PK - -
UserName string - Yes This field cannot be edited.
FullName string - Yes -
Phone string - Yes -
IsGroup bool - - -
Enabled bool - Yes -
Email string Alt PK Yes -
JobTitle string - - -
DepartmentName string - - -
Password string - - Field not included in exports. Additionally, import does not apply Transtream password policy rules.
ProfileID int FK - -
HazardousEnabled bool - - -
LocationID int FK - -
CostCenterID int FK - -
Role bool - Yes 1 = User, 2 = Admin
BulkUser bool - - -
ExternalUserID string - - -
ExternalRecieverID string - - -
AccountCodeID int FK - -
CompanyCodeID int FK - -
ExternalDepartmentCode string - - -
ExternalSiteID string - - -
TimezoneID int FK - -
LocalizationID int FK - -
HazardousShowNotification bool - - -
DefaultAppID int - - -
TaxIdentifier string - - -
TaxIdentifierTypeID int FK - -
ProfileDescription string Alt FK - Alternative to ProfileID.
LocationDescription string Alt FK - Alternative to LocationID.
Address1 string Alt FK - Alternative to LocationID. To be used with LocationDescription.
PostalCode string Alt FK - Alternative to LocationID. To be used with LocationDescription.
CountryCode string Alt FK - Alternative to LocationID. To be used with LocationDescription.
CostCenterCode string Alt FK - Alternative to CostCenterID.
AccountCodeCode string Alt FK - Alternative to AccountCodeID.
CompanyCodeCode string - - Alternative to CompanyCodeID.
LocalizationDescription string Alt FK - Alternative to LocalizationID.
TimezoneName string Alt FK - Alternative to TimezoneID.
IdentificationTypeDescription string Alt FK - Alternative to TaxIdentifierTypeID.
PK: Primary Key, Alt PK: Alternative Primary Key, FK: Foreign Key, Alt FK: Alternative Foreign Key

Top

Article last edited 6 May 2021