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:

  1. From your Admin Area, go to Inventory > Import/Export.
  2. Click the Saved Exports tab.
  3. 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';
Did this answer your question?