Skip to content
Bitta Apps

Bitta Sheets · Part 22 · 2:37 · YouTube

Business Central SQL Query and Joined Tables in a Sheet

What this video covers

Join related Business Central tables in a sheet without SQL, run SELECT queries with parameters, and put SQL in a single cell.

In this tutorial you join Item and Vendor so every item shows its supplier, see the SQL behind the table, insert an SQL query driven by a vendor code in a cell, and use the SQL, SQL.DATA and AL functions.

Transcript

Purchasing wants every chair with its supplier's name. Join it in, right in the sheet. Here's how to join related tables and run SQL queries in a Business Central sheet. Join related tables without SQL, write a SELECT when you need one,

or put SQL right in a cell. On the Insert tab, Connected tables starts like any data table: pick Item and the columns you need. Search the columns by name and tick the ones you need.

Add related table lists the tables that Item points to, up to five, three steps deep. A table that points back shows its first match, and the list says so.

Vendor brings in the vendor's name, one row per item, and its columns are read-only in the sheet. Filter the items to numbers ending in S, and allow write-back. Insert, and every item has its supplier.

Item columns write back after a review; the vendor columns don't. The table's settings open the same dialog, and Write SQL shows the query behind it. As a query you can change it freely, but it no longer writes back.

Insert, then SQL query, takes a SELECT statement. The hint sets the rules: SELECT only, with your own permissions. A question mark is a parameter. Point it at a cell, here the vendor in C2.

Test runs it, and nothing is saved. Insert, and the result fills the sheet. Change the vendor, and the query runs again. SQL query results are values, not records,

so nothing in them writes back. The SQL function puts a query in one cell. This one counts each vendor's items, with a header row. SQL DATA returns the same rows without the header.

And AL runs a read-only expression, like a price plus ten percent. AL formulas only read data. Use it for supplier lists, price checks and quick counts, straight from Business Central.

Next, queries, report data and date series. The steps are in the Bitta Sheets guide.

More Bitta Sheets videos

All videos

Next step

See Bitta Sheets on your own data