I’m trying to create a query that finds how much was paid in shipping costs to various carriers over specified time frames like last month or quarter. However, this information does not appear to be in Epicor but in QuickShip. How does one normally create queries to access these tables? When I look at the available tables in The BAQ Designer screen it doesn’t list them.
In QuickShip I found a screen “Query Manager” but to use this I apparently have to know the QuickShip schema which I don’t - I can see it from MS SSMS on the database server but it’s not very helpful at letting me browse it, and a quick glance at a promising looking table “Shipment” is confusing because it only contains a handful of records. I also don’t seem to find any documentation on the tables what would help me figure out what tables and fields I need to be looking at. Pasting and running a SQL query directly from a form in QuickShip whenever the info is needed doesn’t seem best practice to me either.
Do people just not write queries against Quickship data? Is the data expiring quickly which is why I’m only see a few days worth of Shipment data?
Take a look at the Shipment Summary Inquiry. You can run it by date, carrier, workstation, etc. It will return shipment data that you can export to Excel. Gives you a lot of information on each shipment including the estimated and actual freight cost. Can even save your grid layout.
We’re only integrated for freight costs with FedEx currently but for those shipments the data is in Epicor in the Shipment Tracker on the Manifest Response tab. Data is on the ShipHead table; MFTransNum, MFFreightAmt, MFDiscFreight etc fields.
Out of the box you can’t run BAQs against the Quick Ship data. If you’re on-prem you can connect the QS database as an external data source and query it that way. But it’s a separate database so the tables won’t be in the BAQ designer.
Well that is odd. I looked in Shipment Summary Inquiry and see only 7 shipments total for the last few years. Which is also the same number of rows in the Shipment table.
No we aren’t shipping everything directly from QS, but how much we actually do ship from QS I’m trying to find out now.
From what I’m seeing I think we may ship mostly from Epicor (and I guess it goes through QS but doesn’t store data there? We must have installed for some reason… ) but I think we have shipped directly from QS before this month so it’s a bit puzzling there aren’t any shipments recorded before that.
Do you use the freight service with a workstation or just are marking shipped and entering the tracking numbers? If you don’t use a workstation and use the freight option it doesn’t actually freight in QS.
We recently upgraded QuickShip and I think the existing data did not get carried along. There appear to be two different QuickShip databases in SSMS. So it would appear there is shipping cost data scattered across three separate database…
The installer out of the box creates a new dB for each QS version. And then you have to restore your data back over the top. Or, you start over each time.
) but I think we have shipped directly from QS before this month so it’s a bit puzzling there aren’t any shipments recorded before that.
