Open Forum

Expand all | Collapse all

Jet Basics Report - Linking LOT # to Open Sales Orders NAV Dyanmics

  • 1.  Jet Basics Report - Linking LOT # to Open Sales Orders NAV Dyanmics

    Posted Jan 09, 2019 01:51 PM
    Good day!
    Quite new to the Jet Reports scene.
    Using NAV Dynamics 2017 and Jet Basics

    I want to build a Jet Basics report using Table Builder to show:

    -Open Sales Orders
    -Include Sales Header information such as customer name and ship to address
    -Include Line Level information such as Item and Quantity
    -Include Item Tracking down to the lot level (tracking specification table)

    I can build the report to show the Sales Header and Line level information.  Where it gets sticky is the item and lot number.

    I'm currently linking the Sales Line to Item via  No. equals no.

    Then item to Item tracking specification via. No. equals Item No.

    Perhaps I just can't do this in Jet Basics with Table Builder.

    Suggestions?
    #NAV 2017#Technical

    ------------------------------
    Ruth Entwistle
    Heron Point Seafood
    Newmarket NH
    ------------------------------


  • 2.  RE: Jet Basics Report - Linking LOT # to Open Sales Orders NAV Dyanmics

    TOP CONTRIBUTOR
    Posted Jan 09, 2019 04:52 PM
    Hi Ruth,

    That could be tricky.  I have not tried it, but I am looking at table 336 - Tracking Specification now​.  I think you'll need to link the Sales Order No. to the Source ID field in the tracking specification and the Sales Order Line No. to the Source Ref. No. field.

    I'm not sure if you'll need it, but it could be helpful to know that table 336 also has a Source Type field with a value of "37" and a Source Subtype field with a value of "1" for Sales Orders.

    Good Luck!

    ------------------------------
    Lewis Rosenberg
    IT Manager
    Mars Fishcare
    Chalfont PA
    -----------------------------------------------
    BCUG/NAVUG All-Star
    BCUG/NAVUG Board of Advisors, Chairperson
    BCUG/NAVUG Programming Committee

    Twitter: @RosenbergL
    LinkedIn: https://www.linkedin.com/in/rosenbergl/
    ------------------------------



  • 3.  RE: Jet Basics Report - Linking LOT # to Open Sales Orders NAV Dyanmics

    Posted Jan 10, 2019 09:35 AM
    Thank you for your response!

    I am not finding the exact fields you mentioned.  (Linking Sales Line to Tracking Specification)

    In the Sales Line I have:
    Document No.
    No.
    but no Sales Order no.

    I can find Line No. and Source Ref No and have linked those.

    When I link using "No." or "Document No." to Source ID I return a list of lot numbers and no sales information.

    Any further thoughts? 


    ------------------------------
    Ruth Entwistle
    Customs Broker/Vendor Relations
    Heron Point Seafood
    Newmarket NH
    ------------------------------