Export for Stamps.com

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';