SQL Fields
SQL Field Name |
Required? |
Description |
RecordID |
Optional |
Order number, Contact ID, Account ID or any identifier. Not used by Descartes ShipRush, but crucial for passing into the INSERT/UPDATE statement when writing history. |
CompanyName |
See Description |
Ship-To Company Name (optional if ContactName is supplied) |
ContactName |
See Description |
Ship-To Contact Name (optional if CompanyName is supplied) |
ContactType |
Optional |
Contact type, currently not used |
Address1-Address3 |
See Description |
Ship-To Address info. Only pull data into Address1 and Address2 (pulling into Address3 causes the data to be fed into 1 and 2). At least Address1 must be returned |
City |
Required |
Ship To City |
State |
Required (when applicable) |
Ship To State |
ZIP |
Required |
Ship To Postal Code |
Country |
Optional |
Ship To Country |
Phone |
Required |
Ship To Contact Phone |
Fax |
Optional |
Ship To Fax Number |
Optional |
Ship To Contact Email |
|
Notes |
Optional |
Order Notes |
CarrierType |
Optional |
Shipping Carrier Type, as a string. If not specified then default carrier used. |
Reference |
Optional |
Package reference for Package1 |
Reference2 |
Optional |
Populates the PO Number field for the first package. |
Reference3 |
Optional |
Populates the Invoice field for the first package. |
Reference4 |
Optional |
|
PackageWeight |
Optional |
Box 1 Weight, using default weight type / lbs. For partial weights (oz with USPS for example) use decimels. Ex. .5 = 8oz. 0.0625 = 1oz. |
Service |
Optional |
Shipping Service |
DimLength |
Optional |
Package Length |
DimWidth |
Optional |
Package Width |
DimHeight |
Optional |
Package Height |
PaymentType |
Optional |
(Does not apply to postal shipping) Values are simple numbers, 0, 1, 2, 3: 0 = default (Prepaid aka Sender pays) 2 = Recipient Pays 3 = Third Party billing -- Requires that ShipperAcct, below, also be set. |
ShipperAcct |
Optional |
Used to select between multiple shipping accounts |
ReceiverAcct |
Optional |
Used when billing is set to recipient / 3rd party billing |
CODType |
Optional |
Set COD option |
CODAmount |
Optional |
Set COD amount |
Write Fields (aka History)
SQL Field Name |
Description |
RecordID |
RecordID from incoming query. Empty if incoming query did not exist. |
FromCity |
Ship-From Conact/Address info |
FromCompanyName |
|
FromCountry |
|
FromName |
|
FromStateOrProvince |
|
FromStreet1 |
|
FromZip |
|
ToCountry |
Ship To Contact / Address Info |
ToName |
|
ToStateOrProvince |
|
ToStreet1 |
|
ToZip |
|
Reference1 |
Package 1, Reference 1 |
Reference2 |
Package 2, Reference 2 |
Reference3 |
Package 3, Reference 3 |
ShippingCarrier |
Shipping Carrier that was used. “USP”, “FEDEX”, “USPS”, etc. |
ShippingService |
Shipping service that was used “Next Day”, “2 Day”, etc. |
TrackingNumber |
Carrier Tracking Number |
ShippingCharges |
Total Shipping Charges |
ShippingWeight |
Total Shipment Weight |
HistoryNotes |
Full shipment details in human-readable format. Multiline CRLF separated. |
SmallHistoryNotes |
Short shipment details in human-readable format. Multiline CRLF separated |
HistoryReference |
Very short shipment details. Single line. |
Merge Codes
Code |
Description |
%GUID% |
Causes Descartes ShipRush to generate a new GUID. For example, if you are creating a database record and need a new GUID, use this to make Descartes ShipRush create a new GUID on the fly. |
%RecordID% |
Available only when writing history |
%EnvVar(<varname>)% |
Reads environment variables from the local PC. For example, if the user's login name, or PC name needs to be merged into a SQL statement, this merge code can be used. Example: %EnvVar(ComputerName)% |
%Today(format)% |
Current date formatted as specified. Example: %Today(mmddyy)% See below for available format options. |
%SEARCHPARAM% |
The element searched for or scanned with a bar code scanner |
Date Formatting
Code |
Description |
c |
Displays the short date, followed by the long time. |
d |
Displays the day as a number without a leading zero (1-31). |
dd |
Displays the day as a number with a leading zero (01-31). |
ddd |
Displays the day as an abbreviation (Sun-Sat). |
dddd |
Displays the day as a full name (Sunday-Saturday). |
m |
Displays the month as a number without a leading zero (1-12). |
mm |
Displays the month as a number with a leading zero (01-12). |
mmm |
Displays the month as an abbreviation (Jan-Dec). |
mmmm |
Displays the month as a full name (January-December). |
yy |
Displays the year as a two-digit number (00-99). |
yyyy |
Displays the year as a four-digit number (0000-9999). |
h |
Displays the hour without a leading zero (0-23). |
hh |
Displays the hour with a leading zero (00-23). |
n |
Displays the minute without a leading zero (0-59). |
nn |
Displays the minute with a leading zero (00-59). |
s |
Displays the second without a leading zero (0-59). |
ss |
Displays the second with a leading zero (00-59). |
z |
Displays the millisecond without a leading zero (0-999). |
zzz |
Displays the millisecond with a leading zero (000-999). |
t |
Displays the time using short format. |
tt |
Displays the time using long format. |
am/pm |
Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. |
a/p |
Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. |
ampm |
Uses the 12-hour clock for the preceding h or hh specifier. |
/ |
Displays the date separator. |
: |
Displays the time separator |
'xx'/"xx" |
Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting. |
Next: SQL Webstore - Example Scripts