If you need to perform an export for Stamps.com, there are only a few simple steps you need to take. To add a new query:
- From your Admin Area, go to Inventory > Import/Export.
- Click the Saved Exports tab.
- Click Add New Query and complete the required fields, as shown below.
Once you've added the export, it will be available in your Saved Exports when you need it - export the desired information out to an export file and use as needed.
ID: 0 (this grants access to this query to any administrator within your store)
QB Table: DB_Export
QB Title: Stamps.com
QB Query Type: Select
QB Export File Type: CSV
QB Query:
SELECT
o.OrderID AS 'Order ID (required)',
CONVERT(date, o.OrderDate) AS 'Order Date',
o.PaymentAmount AS 'Order Value',
sm.ShippingMethodName AS 'Requested Service',
o.ShipFirstName + ' ' + o.ShipLastName AS 'Ship To - Name',
o.ShipCompanyName AS 'Ship To - Company',
o.ShipAddress1 AS 'Ship To - Address 1',
o.ShipAddress2 AS 'Ship To - Address 2',
'' AS 'Ship To - Address 3',
o.ShipState AS 'Ship To - State/Province',
o.shipCity AS 'Ship To - City',
o.shipPostalCode AS 'Ship To - Postal Code',
ct.iso AS 'Ship To - Country',
o.ShipPhoneNumber AS 'Ship To - Phone',
c.EmailAddress AS 'Ship To - Email',
CONVERT(Decimal(10,1), (SUM(p.ProductWeight) * 16)) AS 'Total Weight in Oz',
CASE
WHEN p.Ships_By_Itself = 'Y' THEN p.Length
ELSE NULL END
AS 'Dimensions - Length',
CASE
WHEN p.Ships_By_Itself = 'Y' THEN p.Width
ELSE NULL END
AS 'Dimensions - Width',
CASE
WHEN p.Ships_By_Itself = 'Y' THEN p.Height
ELSE NULL END
AS 'Dimensions - Height',
CAST(o.Order_Comments AS Varchar) AS 'Notes - From Customer',
CAST(o.OrderNotes AS Varchar) AS 'Notes - Internal',
CASE
WHEN gw.GiftWrap = 'Y' THEN 'TRUE'
ELSE 'FALSE' END
 AS 'Gift Wrap?',
 CASE
 WHEN gw.GiftWrap = 'Y' THEN gw.GiftWrapNote
 ELSE NULL END
AS 'Gift Message'
FROM
Orders AS o WITH (NOLOCK)
LEFT JOIN
ShippingMethods AS sm WITH (NOLOCK) ON o.ShippingMethodID = sm.ShippingMethodID LEFT JOIN
Customers AS c WITH (NOLOCK) ON o.CustomerID = c.CustomerID LEFT JOIN
(SELECT TOP (1)
OrderID,
GiftWrap,
GiftWrapNote
FROM
OrderDetails WITH (NOLOCK)
WHERE
GiftWrap = 'Y'
ORDER BY
Len(GiftWrapNote) DESC
) AS gw ON o.OrderID = gw.OrderID
JOIN
OrderDetails AS od WITH (NOLOCK) ON o.OrderID = od.OrderID JOIN
Products_Joined AS p WITH (NOLOCK) ON od.ProductCode = p.ProductCode JOIN
Countries AS ct WITH (NOLOCK) ON o.ShipCountry = ct.Name
WHERE
o.OrderStatus = 'Ready to Ship'
GROUP BY
o.OrderID,
o.OrderDate,
o.PaymentAmount,
sm.ShippingMethodName,
o.ShipFirstName,
o.ShipLastName,
o.ShipCompanyName,
o.ShipAddress1,
o.ShipAddress2,
o.ShipState,
o.shipCity,
o.shipPostalCode,
ct.iso,
o.ShipPhoneNumber,
c.EmailAddress,
p.Ships_By_Itself,
p.Length,
p.Width,
p.Height,
CAST(o.Order_Comments AS Varchar),
CAST(o.OrderNotes AS Varchar),
gw.GiftWrap,
gw.GiftWrapNote
ORDER BY
o.OrderID DESC;
UPDATE
Orders
SET
Orders.OrderStatus = 'Pending Shipment',
 Orders.Locked = 'Y'
WHERE
Orders.OrderStatus = 'Ready to Ship';