Workbooks
Connected tables and SQL
Join related Business Central tables in a sheet without SQL, run SELECT queries with cell parameters, and put SQL and AL formulas in a single cell.
updated: applies-to: 1.0.0.0
Join related tables without SQL, write a SELECT statement when you need one, or put SQL right in a cell.
Connected tables
- On the Insert tab, choose Connected tables. It starts like any data table: pick the main table, for example Item, and the columns you need. Search the columns by name and tick them.
- Choose + Add related table. It lists the tables the main table points to, for example Vendor, through Vendor No. You can add up to 5 related tables, 3 steps deep.
- A table that points back, such as Item Vendor, shows the first matching entry, and the list says so. To see every entry, start from that table.
- Pick the columns of the related table, for example the vendor's name. They're read-only in the sheet.
- Under Filters, narrow the rows, for example item numbers ending in S.
- To let the main table's columns write back, tick Allow changes to be written back to Business Central (after a review). It's off for a new connected table.
- Choose Insert. Every item comes in with its supplier.
The block title says Writes back. Changes to the item columns go through Review changes, like any data table; the vendor columns never write back. See Live table editing.
See the SQL behind it
The gear on the block's title bar reopens the settings. Write SQL turns the table into an SQL query: it shows the query behind the table, with a join for each related table. As an SQL query you can change it freely, but it's read-only: changes in the sheet are no longer written back to Business Central. Cancel keeps the connected table.
SQL queries
- On the Insert tab, choose SQL query. Give it a name and type a SELECT statement.
- The hint sets the rules: SELECT only, with your own permissions. Names go in square brackets, such as
[Unit Price], and dates as 'yyyy-mm-dd'. - A ? is a parameter. Give it a value, or point it at a cell, such as
[C2]. The cell button takes the selected cell, and the dialog shows the value it reads now. - Test runs the query with the parameters above and shows the first rows. Nothing is saved.
- Set the table style, and a totals row if you want one, then choose Insert.
Change the cell, and the query runs again by itself. A code such as a vendor number is text: here C2 holds ="30000".
SQL query results are values, not records: nothing in them writes back, and there's no record to open.
SQL and AL in a cell
=SQL(statement, parameters)puts a query in one cell and spills the rows below it, with a header row. LIKE and GROUP BY work, for example to count each vendor's items.=SQL.DATA(statement, parameters)returns the same rows without the header.=AL(expression, values)runs a read-only AL expression, for example=AL("Round(A1 * 1.1, 0.01)", D6)for a price plus ten percent.
AL formulas only read data: they can't write, run objects or call the internet. They need Allow AL Formulas in Studio Setup. See Setup, permissions and the role center.
When to use it
Use it for supplier lists, price checks and quick counts, straight from Business Central.