Open Forum

Like what you see? Discover the benefits of the NAVUG Community. Learn More

Jet Express - Link Tables

  • 1.  Jet Express - Link Tables

    Posted 02-14-2018 05:15 PM
    Hello.  I am looking for what seems would be very basic Jet Express help.  We are running NAV 2009RTC.  For the sake of this example, I simply want to retrieve sales order information, some of which is in the sales header and some are in the lines.  It has been a while since I looked at Jet, but when I pull up table builder and add Sales Header and Sales Line tables and choose to "Link" the two by their related fields, Jet is combining the results instead of listing them individually?  So if a sales order has 5 items and each item has a quantity of 10, all I see is the sales order number and a total of 50 when instead I want one row for each line.  Under links, the option to "include sales header rows 'Always'" is grayed out so I can't change that.  See screenshot.  Thanks in advance for any assistance.

    Jet Table Builder Example

    ------------------------------
    Trey Morris
    IT Systems Supervisor
    Sunshine MIlls
    Red Bay AL
    ------------------------------


  • 2.  RE: Jet Express - Link Tables

    SILVER CONTRIBUTOR
    Posted 02-15-2018 10:29 AM
    Hi! Disclaimer...I don't use the Table Builder much as I prefer to build the reports from scratch, so there may be an easier way to do this.  The only way I could get it to show one line for each sales line is to link Sales Line to Sales Header. I used Document No. from the Sales Line rather than No. from the Sales Header, then added External Document No. just to make sure I could get a value from Sales Header. Also, it looks like your Quantity field is a sum, so that could be why it's giving a total for all lines.



    ------------------------------
    Leanne Paul
    Systems Analyst
    Home Market Foods, Inc.
    Norwood MA
    ------------------------------



  • 3.  RE: Jet Express - Link Tables

    TOP CONTRIBUTOR
    Posted 02-15-2018 10:32 AM
    Hi Trey, that is what Table builder does. Joins all your tables together into a single large table. That's all it can do.

    You might need Jet Professional if you're trying to list headers and their respective lines underneath.

    But, if I understand correctly, from your description you should be OK by reversing the tables. Start with the Sales Line table, and then add the Sales Header table, if you indeed need fields from there.

    That checkmark you mention is only for linked tables, and there's always going to be a sales header for a sales line, although the opposite isn't true. In other words, you could check it in your current report and any SO without lines wouldn't show up. But if you reverse the tables as I'm suggesting, that checkbox won't do anything.

    But, if the sales lines table has all the info you need, don't link the header.

    ------------------------------
    Val Gameiro
    Senior NAV Analyst
    BPL Plasma Inc.
    Austin, Texas
    NAVUG Austin Chapter Leader
    ------------------------------



  • 4.  RE: Jet Express - Link Tables

    NAVUG ALL STAR
    Posted 02-20-2018 10:43 AM
    Start with the lines and then add header  as the second table.

    This way it pulls in each line and attaches the header information to each line, instead of the other way around.

    ------------------------------
    Holly Kutil ~ NAVUG All-Star
    American Ring/CIO
    Solon, OH 44139
    **Great Lakes Chapter**
    ♥♥ Women In Dynamics ♥♥
    ------------------------------



  • 5.  RE: Jet Express - Link Tables

    Posted 02-21-2018 10:18 AM
    Holly is absolutely correct: start with detail, then add header info as necessary. If you need to rearrange the ordering of the columns afterward, that is possible, but my experience has been that the top-level report (formatted, summarized, and appropriate analysis) is easier to manage than the data table that Jet generates.

    ------------------------------
    M. Scott Beaton
    End user, Consultant
    Sanford FL
    ------------------------------