Small and medium business | Business Central, N...

Joining 2 tables in Business Central cloud AL Code

(0) Share

Report

Posted on by DavidGibbons

Hi I want to be able to join 2 tables together in AL code for Business Central for the cloud.

Similar to the way you would in SQL with a "UNION ALL" statement.

As the cloud version of business central does not let you get directly to the tables and create views, I need to do this in AL code.

I know I can create a new table and load the data from the 2 table into that table and then use that table on the page extension, but as the table grow, this would get slower and slower.

The 2 table I want to join together first is the following.

"Sales Invoice line"; //table 113

"Sales Cr.Memo line"; //table 115

All responses (13)

Answers (0)

Suggested answer

blindzero 5 on at

Like (1)

Report

RE: Joining 2 tables in Business Central cloud AL Code

Technically UNIONS are not supported in AL queries.

A UNION would be dataItemLink objects on the same level.
Taiwan 20 on at

Like (1)

Report

RE: Joining 2 tables in Business Central cloud AL Code

Hi~David:

I think the question you want to ask is:

?Union Two tables are in the same List?

In this way, the data of the two tables can be sorted or filtered.

Because i have the same problem,

I created a schematic diagram as follows:
YUN ZHU 82,804 Super User 2025 Season 1 on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

I think you can try to use query.

Hope this can help.

https://yzhums.com/3206/
Community Member on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

[quote user="DavidGibbons"]
Hi I want to be able to join 2 tables together in AL code for Business Central for the cloud.

Similar to the way you would in SQL with a "UNION ALL" statement.

As the cloud version of business central does not let you get directly to the tables and create views, I need to do this in AL code.

I know I can create a new table and load the data from the 2 table into that table and then use that table on the page extension, but as the table grow, this would get slower and slower.

The 2 table I want to join together first is the following.

"Sales Invoice line"; //table 113

"Sales Cr.Memo line"; //table 115
[/quote]

Hi David

I have a similar need (union from Pord Order routing lines and PO lines)

Did you ever figure this out?

Thanks

Mark
Suggested answer

THE Italian on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

No.

From what I see you could use one of the following

1. Create a query that join the 2 table with the field that you need then bound the page to an integer data item and process numbers until there are no record in the query. Drawback: limited filtering experience

2. Bound the page to e.g. sales invoice and then in the on after get record add a cr. memo global variable and for the rest of the page use crmemo.field to bound the page filter. Drawback: limited filtering experience
DavidGibbons 25 on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

Can the dictionary then be used on a page in a similar way a table is. As i am trying to display the 2 tables on the same page, with sorting and filtering ETC.
Suggested answer

THE Italian on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

Use a dictionary

www.hougaard.com/.../
DavidGibbons 25 on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

Ok there is no need to be rude about this. I was just asking for some advice.

With regard to your second option. I had already mentioned that in my original question.

I was trying to see if any know of a simpler way of doing this.

Putting the data from 2 tables into a temp table could have performance issue as the tables increase in size.
keoma 32,727 on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

you did not write, that you want to filter over the data of both tables at the same time and export the result. so describe at the beginning what you need and not step by step, because that is a waste of time for the persons who spend their freetime at the end! this is no support hotline!

additional i gave you a second option, which you obviously did not understand!
DavidGibbons 25 on at

Like (0)

Report

RE: Joining 2 tables in Business Central cloud AL Code

This still give you 2 lists. You cannot sort the 2 list when you have subpages or have filters, plus if you export them to excel, you would still need to join the 2 lists together.