Navision 4.0 and UPS worldship integrations

themave

It doesn't appear as if the Landham e-ship program handles transfer orders or purchase returns with Navision 4.

I am looking for ways of integrating ups worldship with Navision 4.0

and searching the forum is not the solutions as that reveals only a few prior post asking about it, but no actual answers on how to do it.

Here is what I am thinking as a process of what happens, but not sure how to do each step

1.Navision
..a.Process as part of daily activity
.....i.Sales orders
.....ii.Transfer orders
.....iii.Purchase returns
..b.Manual or Automatic, run Navision end of day routine which:
.....i.Exports all three shipment types to Access and mark Navision records as exported. Would I use a dataport to export and a non-printing report to mark Navison records, and I am not sure how to get all three table data types, into one access file.

2.UPS Worldship
a.Run Keyed Import, and bring in the access file to Worldship
b.Enter weight information
c.Print Shipping Labels
d.Run end of day Worldship routine
i.Automatically export shipment info to MS Access

3.Navision
a.Nightly routine to pull posted shipment info from MS Access back into Navision documents


4.Microsoft Access
a.Need tables for:
i.Pending Shipments
ii.Posted Shipment
iii.Addresses

And ideas how to actually inplement this?

Thanks in advance

Savatage

We use E-ship without problems..Maybe you can ask them directly if e-ship can do what you need it to do.

Note: E-ship replaces Worldship for us. We can see all orders packages and track right from Navision. it's really been a life saver. At the end of the day navision creates a text file where we upload it into a UPS web site and it's done.

But we don't do Transfer orders N Such. I guess they would know.

http://www.lanhamassoc.com/contact.htm

themave

I did ask directly, they referred me to my solution center, I am currently looking for a new solution center as my current one, has decided I do not bring enough custom programing money to them, and have stated they no longer wish to support us as soon as our upgrade from 2.0 to 4.0 is complete. I am do understand at least partially, because I do most everything myself, as Navision is flexible, and I am not afraid to change an enternal practice to match Navision. So, I don't request a lot of changes. Only bug fixes, which of coarse doesn't provide a lot of revenue to them.

Savatage

We are the same way.
Our center set up all the major stuff and after that we took over.
Our center seemed to "let us go" because we were doing so much ourselves. I don't even think we'll get an XMAS card
& before you know it you'll have 800 posts too \:D/

emulsified

I did some searching after finding out how much $$$ it would cost to simply make UPS and navision talk to each other.

I don't like to re-invent the wheel or buy an expensive one for something simple.

I found this link and was wondering if anyone has ever accomplished this.

http://heydasch.net/code/worldship/Navision_and_UPS_Worldship_Integration.pdf

I saw it a few months ago but didn't try it. It seems I can't download it any longer but Google has a cache of it in HTML.

The problem is that there is code units, forms, data ports, and other code this guy wrote that seems like it would work out. But I am not able to download them. It seems the site is down.

Anyone know where or how I might find those files on the site?

Has anyone tried this?

My company simply creates a Sales Invoice (SIO) and then adds the FREIGHT charges manually before putting the invoice in the box. So we are doing extra steps that I would like to eliminate.

The following would be great:
1. Order gets entered as a Sales Invoice (SIO) but not posted yet.

2. Order is being pulled and packed.

3. Shipper goes to UPS workstation and enters SIO# in WorldShip, it then brings in the Ship To, etc.. fields.

4. Shipping rate shows up, they print a label.

5. Worldship sends out the email with the tracking number, etc...

6. Worldship writes the Weight, Freight $$, Tracking Number back to the originating Navision order somehow.

7. User posts the Sales Invoice, prints it, puts in the box, and out the door.

Savatage

Wow that post is from 2005 - anyway did you find out how much Lanhams E-ship is? It sound exactly what you need.
http://www.lanhamassoc.com/e-ship.htm

Whatever it is it will pay for itself.
It used to be: (you choose what you need) contact a reseller.
E-Ship pack line scanning - Approx 2,700
Scale interface - Approx 800
UPS Shipping - Approx 2,300
Freight Inv Reconciliation - Approx 850

It's all done In navision. You can make packages & track packages, etc.
Right now we create sales orders - The warehouse packs them. They scan the barcode on top of the pick ticket. They enter the # of boxes, the scale does the rest, out pops a label(s). freight charges can be manully added or have e-ship do it.

Ups will still provide the labels & label printer for free (ask nice) 8)

For mail (USPS) we're using Endicia's GalaxyShip. Export Sales Header info (address, name. city) to excel file from NAV. Endicia uses the Excel file as it's datafile & writes back a tracking number. Then I use a dataport to bring in the tracking numbers. Since E-ship has e-mail options built in. on posting of the orders - emails are sent out to customers with order details & tracking numbers.

Savatage

also

6. Worldship writes the Weight, Freight $$, Tracking Number back to the originating Navision order somehow.

Since we use eship - i'm not too familiar but I'm sure that worldship can post back data to some file. and if it can you can create a dataport to import that data into a new table "UPS Packages" or something that you create. Then have a lookup from the posted sales header to that table to get tracking number on the fly.

ara3n

Hello I worked on a project that required integration with Worldship.

The description you are describing is exactly as you described. This was 3 years ago.


Last year I had to integrate with Clipper-ship. Same senario.

A dataport to export the data and mark the shipment header recods, and a process to import the data back from clippership/worldship.

This is a much easier solution then implement E-ship. Also cheaper.

I would spend the time on gathering the requirement. E.g. doing the field mapping on how the file looks like and what fields need to be done.

Once you have all the details written up, development shouldn't take that long. Less than a day.

Savatage

True, and you don't have to worry about not having the most upto date rates. Worldship has that covered. Now they make intergration pretty easy now with Worldship 9.0
http://www.ups.com/content/us/en/bussol ... /inte.html

emulsified

I thought I was going in the right direction.

I've setup C/ODBC and the Navision client on the UPS Worldship 9.0 (I have their integration documents and read them) machine and when I go to create the IMPORT MAP it connects to Navision Database Server (native) and it just sits there forever.

I verified that the 'worldship' ID shows up in the users logged in so I know it's connecting fine.

I set the following permissions under my 'C/ODBC' role as follows:

Object Type Object ID Object Name Read Permission Insert Permission Modify Permission Delete Permission Execute Permission Security Filter
Table Data 0 Yes Yes Yes Yes Yes
Table Data 18 Customer Yes Indirect Yes
Table Data 36 Sales Header Yes Yes
Table Data 37 Sales Line Yes Yes
Table Data 112 Sales Invoice Header Yes Yes
Table Data 113 Sales Invoice Line Yes Yes
Table Data 2000000006 Company Yes Yes
Table 0 Yes Yes
Form 0 Yes Yes
Report 0 Yes Yes
Dataport 0 Yes Yes
Codeunit 0 Yes Yes
XMLport 0 Yes Yes
MenuSuite 0 Yes Yes
System 0 Yes Yes Yes Yes Yes
System 9130 C/ODBC Yes Yes Yes Yes Yes

Now the strange thing is I can use Microsoft Word and Excel to import data via Microsoft Query without any problems?

Any suggestions?

emulsified

Also, Lanham's E-Ship is ballpark $12,000 to $17,000 for the software not including integration.

I respect what they have done but at some point someone must realize that a company of less than 35 to 50 employees isn't likely to plunk down that much money especially when the whole Navision solution cost around $30,000 to begin with.

UPS is actually trying to help us with this but their Crossware team is a bit too slow and we want to do this before 2008.

ara3n

C/ODBC isn't fully imlpemented and is buggy. I'm guessing they've only tested with MS office products.

I suggest to extract the data to excel to ODBC and then import that to worldship.

Or at least put in here the fields that you want from what table, and I'll give you a dataport.

emulsified

You are probably right.

The data I want from Navision is as follows:

Table: Sales Header

Fields:
No.
Your Reference
External Document No.
Ship-to Name
Ship-to Name 2
Ship-to Address
Ship-to Address 2
Ship-to City
Ship-to Contact
Ship-to Post Code
Ship-to County
Ship-to Country Code
Shipment Method Code
Shipping Agent Code

What I want to write back into Navision for the chosen "No." is as follows:

Field: Package Tracking No. into Table: Sales Header
Freight Amount from WorldShip as line for "No." into Table: Sales Line

Marije_Brummel

I have integrated with UPS using webservices from and to Navision. It's only a few objects.

Send me an email if you are interested and we'll work it out from there.

Savatage

emulsified wrote:

Freight Amount from WorldShip as line for "No." into Table: Sales Line

Your gonna have to specify a Line No. = how about 1000

You've got me interested in trying this myself.
Really would help with international order that need all UPS forms.
Hopefully I'll have some time next week

ara3n

Here is a dataport that will import/export comma separate file based columns above.

Also for import it expects the following fields

Sales Order No., Package no.,Freight cost

OBJECT Dataport 50000 WorldShip
{
  OBJECT-PROPERTIES
  {
    Date=12/15/07;
    Time=[ 5:35:16 PM];
    Modified=Yes;
    Version List=MOD01;
  }
  PROPERTIES
  {
    FieldStartDelimiter=<None>;
    FieldEndDelimiter=<None>;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        AutoSave=No;
        AutoUpdate=No;
        AutoReplace=No;
        DataItemTableView=SORTING(Number);
        OnPreDataItem=BEGIN
                        SalesSetup.GET;
                        IF NOT CurrDataport.IMPORT THEN BEGIN
                          SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Order);
                          //Add additional filter for the field that marks them as exported
                          SETRANGE(Number,1,SalesHeader.COUNT);

                        END;
                      END;

        OnBeforeExportRecord=BEGIN
                               IF Number = 1 THEN
                                 SalesHeader.FINDSET
                               ELSE
                                SalesHeader.NEXT;

                               Column[1] := SalesHeader."No.";
                               Column[2] := SalesHeader."Your Reference";
                               Column[3] := SalesHeader."External Document No.";
                               Column[4] := SalesHeader."Ship-to Name";
                               Column[5] := SalesHeader."Ship-to Name 2";
                               Column[6] := SalesHeader."Ship-to Address";
                               Column[7] := SalesHeader."Ship-to Address 2";
                               Column[8] := SalesHeader."Ship-to City";
                               Column[9] := SalesHeader."Ship-to Contact";
                               Column[10] := SalesHeader."Ship-to Post Code";
                               Column[11] := SalesHeader."Ship-to County";
                               Column[12] := SalesHeader."Ship-to Country/Region Code";
                               Column[13] := SalesHeader."Shipment Method Code";
                               Column[14] := SalesHeader."Shipping Agent Code";


                               FOR I := 1 TO ARRAYLEN(Column) DO BEGIN
                                Column[I] := DELCHR(Column[I],'=',',');
                               END;
                             END;

        OnAfterExportRecord=BEGIN
                              CLEAR(Column);
                            END;

        OnBeforeImportRecord=BEGIN
                               CLEAR(Column);
                             END;

        OnAfterImportRecord=BEGIN
                              FOR I := 1 TO ARRAYLEN(Column) DO BEGIN
                               Column[I] := DELCHR(Column[I],'<>',' ');
                              END;

                              CLEAR(SalesHeader);
                              CLEAR(SalesLine);
                              CLEAR(ReleaseCU);

                              IF SalesHeader.GET(SalesHeader."Document Type"::Order,Column[1]) THEN BEGIN
                                SalesHeader."Package Tracking No." := Column[2];
                                SalesHeader.MODIFY(TRUE);
                                ReleaseOrder := SalesHeader.Status = SalesHeader.Status::Released;
                                IF ReleaseOrder THEN
                                  ReleaseCU.Reopen(SalesHeader);
                                SalesSetup.TESTFIELD("G/L Freight Account No.");
                                SalesLine.SETRANGE("Document Type",SalesHeader."Document Type");
                                SalesLine.SETRANGE("Document No.",SalesHeader."No.");
                                IF SalesLine.FINDLAST THEN;
                                LineNo := SalesLine."Line No." + 10000;
                                CLEAR(SalesLine);
                                SalesLine."Document Type" := SalesHeader."Document Type";
                                SalesLine."Document No." := SalesHeader."No.";
                                SalesLine."Line No." := LineNo;
                                SalesLine.SetHideValidationDialog(TRUE);
                                SalesLine.INSERT(TRUE);
                                SalesLine.VALIDATE(Type,SalesLine.Type::"G/L Account");
                                SalesLine.VALIDATE("No.",SalesSetup."G/L Freight Account No.");
                                SalesLine.VALIDATE(Quantity,1);
                                EVALUATE(SalesLine."Unit Price",Column[3]);
                                SalesLine.VALIDATE("Unit Price");
                                SalesLine.MODIFY(TRUE);
                                CLEAR(ReleaseCU);
                                IF ReleaseOrder THEN
                                  ReleaseCU.RUN(SalesHeader);

                              END;
                            END;

        OnPostDataItem=BEGIN
                         COMMIT;
                       END;

      }
      FIELDS
      {
        {      ;     ;Column[1]            }
        {      ;     ;Column[2]            }
        {      ;     ;Column[3]            }
        {      ;     ;Column[4]            }
        {      ;     ;Column[5]            }
        {      ;     ;Column[6]            }
        {      ;     ;Column[7]            }
        {      ;     ;Column[8]            }
        {      ;     ;Column[9]            }
        {      ;     ;Column[10]           }
        {      ;     ;Column[11]           }
        {      ;     ;Column[12]           }
        {      ;     ;Column[13]           }
        {      ;     ;Column[14]           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      Column@1000000000 : ARRAY [50] OF Text[100];
      I@1000000001 : Integer;
      SalesHeader@1000000002 : Record 36;
      SalesLine@1000000003 : Record 37;
      LineNo@1000000007 : Integer;
      SalesSetup@1000000004 : Record 311;
      ReleaseCU@1000000005 : Codeunit 414;
      ReleaseOrder@1000000006 : Boolean;

    BEGIN
    END.
  }
}

emulsified

I actually decided to use a MySQL database using ADO from within Navision since the Navision C/ODBC driver is terribly slow.

So now I have Navision writing to a MySQL table via ADO. I've created the keyed import on UPS Worldship 9.0 to the MySQL db and it's lightening fast.

I'll be happy to post the code I came up with shortly as I am still working on the next step which is having the UPS shipment data written back into Navision. I'm not sure which method I'm going to use yet. I know that I will definitely have Navision get the data back via ADO from the MySQL db because I am planning to have some other systems use the MySQL db, possibly PHP or other.

After I clear this up I am moving on to FedEx then DHL (Airborne).

I will post the code shortly when I have a chance to extract and format it into a post. If anyone is interested in the code sooner, just drop me a PM and I'll send what I have.

Savatage

emulsified wrote:

I'll be happy to post the code I came up with shortly

I knows it's been 2 years but did you ever get this to work smoothly? 8)

emulsified

Yes. It works wonderfully. Also have it working with FedEx.

I found it was easier to just write C/AL code into the INVOICE and ORDER forms that would connect to a MySQL database and write the appropriate data to the MySQL database tables as well as code to read the matching Sales Invoice or Sales Order data from the MySQL database tables all using ADO controls with the MySQL 3.51 ODBC driver on each client machine.

UPS, FedEx, and many others have no problems working with either MySQL/SQL.

Here is what I've done:

Sales Order Form/Sales Invoice Form - Added a button to the form named "Update ShipInfo" with code to write data to MySQL, added the same code into the posting button under the "Test Report" item just in case they forgot the push the button.

MySQL Database - created a table called 'shipments' with three tables: 'manifest', 'processed', 'shipinfo'
manifest - holds all the Sales Lines for each SO or SI document
processed - holds all records created by UPS/FedEx shipping/tracking information written to by UPS/FedEx stations
shipinfo - holds all SO and SI header information just like 'Sales Header' table in Navision does

And when it comes time to get the shipment information back into Navision, the user clicks a button "Retrieve Shipping Information", and then I just read from the MySQL database tables using the same logic with some code that we required which creates a new SALES LINE for the shipping charges and also populates the Tracking No. field for that SO or SI, the user then posts or does whatever they need to do.