Union 2 tables in 1 form

Business Central/NAV Developers Forum
1

hai all…

I want tu union 2 tables in 1 form

like this sql syntax

→

SELECT *
FROM [Table1]
UNION
SELECT *
FROM [Table2]

I want to union tabel 17 (GL Entry) and table 81 (general journal line)

how could i do that?

thanks

2

I mean,it’s not just for 1 form

but in 1 table box , it contains data from 2 tables (with the same columns)

3

THe UNION query only works if both tables have the same structure! Thus, a "SELECT *" with T17 UNION T81 cannot work. So maybe you could just select some of the fields …

PLease refer to the “Books Online” for further details about UNION.

4

Stan what are you trying to do?

5

in chart of account form there is field net change that if we click the drill down, it showed from table 17

so I want to showed every transaction from table 17 (Posted) and table 81 (not posted) for every same document no

and then from the 2 tables, i want to SUM the amount

is that possible to do that?

6

Actually i want to show those 2 tables like this

SELECT ‘17’ As [Table No],[G_L Account No_],[Document No_],Amount
FROM [Company$G_L Entry]
UNION
SELECT ‘81’ As [Table No],[Account No_],[Document No_],[Amount (LCY)]
FROM [Company$Gen_ Journal Line]

7

Hi Stan,

I am still not really sure what you want to accomplish, but here is an example of how you might achieve what you want. The basis of it is that you create a view on SQL Server and that in NAV you create a table that is based on and linked to this view. The example uses an INNER JOIN for the Customer and the Detailed Cust. Ledg. Entry tables.

So …

  1. Create the following view on SQL Server in your NAV database:
    SELECT dbo.[CRONUS International Ltd_$Customer].Name, dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Posting Date],
    dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].Amount
    FROM dbo.[CRONUS International Ltd_$Customer] INNER JOIN
    dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry] ON
    dbo.[CRONUS International Ltd_$Customer].No_ = dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
  2. Save it as: View_Detailed_Cust_Ledg_Entry
  3. In NAV create a new table with fields
    Field Name Data Type Length
    Name Text 30
    Posting Date Date
    Amount Decimal
  4. Set the following table propeties:
    DataPerCompany = No
    LinkedObject = Yes
  5. Save the table as View_Detailed_Cust_Ledg_Entry (take whatever id that suites)
    Note: you really have to make sure that the name of the NAV table is equal to the name of the view you want to link it to!

Ready!

Now if you run this table you will see it already ‘contains’ data. Well, actuallu not as it is showing you the data through the SQl Server view.

Hope this can help you.

8

BTW1: just did a search on SQL+View: it shows (a.o.) some valuable postings on this topic

BTW2: you even can edit the data thorugh this table, so you might ask yourself if that is wanted …