Microsoft Dynamics GP SOP has the ability to store the tracking numbers for shipments. Open the SOP invoice, and open the user-defined fields to enter or obtain the tracking information. If the custom links are set up correctly for your shippers, you can drill into the tracking information on the Internet from the SOP Invoice.
Tip
Yes, the trick is getting the shippers set up correctly. Each shipper has a different link that needs to be used, as they all store tracking numbers differently. Review this Microsoft Support KB article for more information: http://bit.ly/GPsopTrack.
This distribution company sells product through some websites that require that shipping to occur within a specified time period. In an attempt to retrieve tracking numbers with the invoice information, tracking numbers are stored in the GP invoice user-defined window.
When a customer calls to find out the status of their order, it takes a while to get to the tracking information. The company wanted an Excel document that shows all orders shipped within the last 30 days with links to the shippers' tracking web pages, in order to shorten the time it takes to review the status of the shipment.
We will be using an Excel table for this report as well. This will allow for quick refresh, and can be extended to non-GP users.
There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.
Create the following view in SQL Server:
We've warned you about SQL; don't mess it up.
CREATE VIEW view_BI_SOP_TrackingNumber AS SELECT 'Posted' AS Status, CASE SH.soptype WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 5 THEN 'Backorder' WHEN 6 THEN 'Fulfillment_Order' ELSE 'Other' END AS Doc_Type, SH.SOPNUMBE AS Doc_Number, SH.DOCDATE AS Doc_Date, SH.ReqShipDate AS Requested_Ship, SH.ACTLSHIP AS Actual_Ship, SH.CUSTNMBR AS Customer_ID, SH.CUSTNAME AS Customer, SH.CSTPONBR AS Customer_PO, SH.MSTRNUMB AS Master_Num, SH.PRSTADCD AS Ship_To_ID, SH.ShipToName AS Ship_To, SH.ADDRESS1 AS Address_1, SH.ADDRESS2 AS Address_2, SH.ADDRESS3 AS Address_3, SH.CITY AS City, SH.STATE AS STATE, SH.ZIPCODE AS Zip, SH.CNTCPRSN AS Contact, SH.PHNUMBR1 AS Phone, ST.Tracking_Number, SH.SHIPMTHD AS Shipping_Method, CL.CUSTOMLINKLBL AS GP_Link, RTRIM(REPLACE(CL.CUSTOMLINKINETADDR, '%1', RTRIM(ST.Tracking_Number))) AS URL FROM dbo.SOP30200 AS SH INNER JOIN dbo.SOP10107 AS ST ON SH.SOPNUMBE = ST.SOPNUMBE AND SH.SOPTYPE = ST.SOPTYPE INNER JOIN dbo.SY01201 AS CL ON SH.SHIPMTHD = CL.CUSTOMLINKFIELDVAL WHERE (CL.CUSTOMLINKPRMPT = 9) AND (SH.ACTLSHIP >= DATEADD(DAY, - 30, GETDATE())) Union SELECT 'Unposted' AS Status, CASE SH.soptype WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 5 THEN 'Backorder' WHEN 6 THEN 'Fulfillment_Order' ELSE 'Other' END AS Doc_Type, SH.SOPNUMBE AS Doc_Number, SH.DOCDATE AS Doc_Date, SH.ReqShipDate AS Requested_Ship, SH.ACTLSHIP AS Actual_Ship, SH.CUSTNMBR AS Customer_ID, SH.CUSTNAME AS Customer, SH.CSTPONBR AS Customer_PO, SH.MSTRNUMB AS Master_Num, SH.PRSTADCD AS Ship_To_ID, SH.ShipToName AS Ship_To, SH.ADDRESS1 AS Address_1, SH.ADDRESS2 AS Address_2, SH.ADDRESS3 AS Address_3, SH.CITY AS City, SH.STATE AS STATE, SH.ZIPCODE AS Zip, SH.CNTCPRSN AS Contact, SH.PHNUMBR1 AS Phone, ST.Tracking_Number, SH.SHIPMTHD AS Shipping_Method, CL.CUSTOMLINKLBL AS GP_Link, RTRIM(REPLACE(CL.CUSTOMLINKINETADDR, '%1', RTRIM(ST.Tracking_Number))) AS URL FROM dbo.SOP10100 AS SH INNER JOIN dbo.SOP10107 AS ST ON SH.SOPNUMBE = ST.SOPNUMBE AND SH.SOPTYPE = ST.SOPTYPE INNER JOIN dbo.SY01201 AS CL ON SH.SHIPMTHD = CL.CUSTOMLINKFIELDVAL WHERE (CL.CUSTOMLINKPRMPT = 9) AND (SH.ACTLSHIP >= DATEADD(DAY, - 30, GETDATE())) GO GRANT SELECT ON view_BI_SOP_TrackingNumber TO DYNGRP
Let's build a report!
Know what you want. Yeah, we're kind of a know-it-all, but we know what we want:
Tip
Note that this report will only show SOP documents that have tracking numbers assigned to them.
If a shipment is missing from this list, the probable cause is that the tracking number is missing, the custom link that contains the URL has not been set up, or the shipping method does not match the custom link that contains the URL. It can also be a combination of any or all of these issues.
Let's make a connection to the GP data:
- Open a blank Microsoft Excel workbook. Click on DATA from the menu, and in the Get External Data area of the ribbon, select From Other Data Sources. On the drop-down list, select From SQL Server:
- In the Data Connection Wizard window, enter your Server name and log in using your network or AD login and password. Then, click on Next:
- Select your database, and then scroll down and select the view_BI_SOP_TrackingNumber view. Then, click on Finish:
- On the Import Data window, make sure the Table default is selected and that the Where do you want to put the data? option is set to Existing Worksheet in the =$A$1 cell. Then, click on OK:
- Let's insert a blank line above the table and insert a report name:
- To allow all the information to appear on a single view without scrolling (just for the purposes of this book), we are going to hide columns K through T (this includes the ship to address information).
- Insert a column after Document_Number.
- Click on the first blank row on the new column. This is row 3, column D, for us. We are going to add a formula in this column. The column will display the document number, but contain the URL link to the tracking information.
- From the menu, select Formulas, and then in the Function Library area of the ribbon, select Lookup & Reference. On the drop-down list, select HYPERLINK:
- Click in the Link_location field, and then click on column URL, making sure to select the same row as this formula. You can also enter [@URL] to reference the column.
- Click in the Friendly_name field, and then click on column Doc_Number, making sure to select the same row as this formula. You can also enter [@[Doc_Number]] to reference the column:
- Click on OK.
- Let's hide columns C, Doc_Number and Y, URL.
- Save the report; it is complete:
The report displays all orders with tracking numbers and actual shipping dates in the last 30 days for both posted and unposted documents. Clicking on this hyperlink we created will open the tracking information window for the appropriate shipper on the shipper's website, providing you with the tracking information for this order.
You can also filter this report by customer, customer PO number, master number, dates, and other fields. All of this information is available directly from Excel, eliminating the need to log into GP.
By using this report, the company's shipping department can handle phone calls for all shipped orders (they enter a tracking number for every order) from this spreadsheet. If the order does not display, the call is forwarded to the sales department for research. This company saves time and user licenses by using this Microsoft Excel Table.