Open Forum

Expand all | Collapse all

"Joins" in Object Designer

  • 1.  "Joins" in Object Designer

    TOP CONTRIBUTOR
    Posted Jan 07, 2020 04:59 PM
    Edited by Mark Anderson Jan 07, 2020 05:12 PM
    Hi All

    I need to add two related tables to a sales invoice lines

    In SQL the join is as follows and returns the correct result

    SELECT
    *
    FROM
    [dbo].[test$Sales Invoice Header] [ih]
    LEFT JOIN [dbo].[test$Sales Invoice Line] [il] ON [il].[Document No_] = [ih].[No_]
    LEFT JOIN [dbo].[test$Item] [i] ON [il].[No_] = [i].[No_]



    In Object designer, Sales Invoice Line is indentation level 1
    I made item indentation level 2 and my DataIteLink is No = No

    As soon as I do this, my data set contains 2 records for each item

    Anyone any ideas what I'm doing wrong?

    I arbitrarily selected keys for DataItemTableView's for these two new data items so that I didn't get fasttabs in the request dialog

    Thanks

    Mark


    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: "Joins" in Object Designer

    Posted Jan 18, 2020 07:27 AM
    I am assuming You are looking at the dataset in a report?
    If You have a screen-dump of the layout, my guess is that you have fields for the 'Sales Invoice Line'. They will come out before the Item element. To avoind that You need to place all of those fields on the Item level.

    What version are You on? You may be able to use a query instead?

    ------------------------------
    Henrik Helgesen
    California Cryobank, LLC
    Los Angeles CA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: "Joins" in Object Designer

    TOP CONTRIBUTOR
    Posted Jan 18, 2020 08:23 AM
    Thanks for reply Henrik.

    We're on 13 on prem. from what I understand you're saying the item and table need to be same indentation level? This is equivalent to left join right? I thought indenting one level was equivalent to a join? Any idea how same level and level + 1 indentation would relate in SQL terms?

    Still very new to Nav, so not sure how I'd hook a report to a query

    Would really like to do as a proper AL extension in near future. Dabbled in AL a bit and understand basics of table relations and made a custom qeury and report there. Still not enough experience of entire AL SDLC to move that way yet (even though I know I'm building technical dbt right now :-))

    Thanks Again

    Mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: "Joins" in Object Designer

    TOP CONTRIBUTOR
    Posted Feb 03, 2020 04:40 PM
    Hi Henrik

    It tried that, but got a message saying that data link items must be at a different indent level (or something along those lines)

    Regards

    Mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role