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
1:18OverviewEdit Business Central Tables Live in a Spreadsheet
2:08Part 1Edit Business Central Data in an Excel-Style Sheet
3:08Part 2Business Central Bulk Update Without Export and Import
2:50Part 3Business Central Budget in a Spreadsheet, Written Back Live
2:35Part 4Business Central Budget Entry by Department
2:56Part 5Business Central Consolidation: Companies and Account Mapping