Use FlowFields directly inside your Spreadsheet inside Business Central (Advanced Spreadsheets)
Using FlowFields directly in Business Central Advanced Spreadsheets: building a FlowField region for item sales, adding date range regions, referencing cells in filters, and connecting slicers. Demonstrated with item sales calculated per period.
- Introduction to FlowFields in Spreadsheets
- Creating a FlowField Region for Item Sales
- Adding Date Ranges to Filter Data
- Connecting Date Filters with Cell References
- Spreadsheet Behavior with Relative References
- Using Slicers to Filter FlowField Results
- Benefits and Conclusion
Overview
Erik Hougaard shows how to take a specific FlowField from anywhere in Business Central and use it inside a spreadsheet with the advanced spreadsheet and reporting app. The demo builds a FlowField region on the item table for item sales, then adds a date range region so the same calculation runs across 12 periods.
He then connects the date filters to spreadsheet cells with cell references, explains how relative references behave as they move down rows, and adds slicers to filter the FlowField results interactively. The approach lets you get large calculated numbers out of Business Central without querying them in the usual way.
Key points
- A FlowField from any table, such as the item table, can be used in a spreadsheet region in the advanced spreadsheet and reporting app.
- Date range regions take a start date or formula, an interval and a count. A formula based on the current year (minus CY, optionally minus one year) can generate the periods.
- In the demo the item sales FlowField region initially shows 12 identical totals; linking each row to a date range cell makes them calculate per period.
- Cell references go into filter formulas in square brackets and behave like normal spreadsheet references, so relative references update as they move down rows.
- When referencing cells inside regions, the reference is always from the first row of the region.
- Slicers are defined with a question mark prefix and can filter by date, dimension, field list or range.
- A slicer must be connected to the FlowField region using absolute (dollar sign) references.
Features
Every feature below: status not stated, demoed.
| Feature | At |
|---|---|
| FlowFields in Spreadsheets | 0:11 |
| Date Range Regions | 1:36 |
| Cell References in Filters | 2:49 |
| Slicers in Spreadsheets | 3:58 |
AL objects mentioned
As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.
- table 27 “Item” at 0:45
- table “item table” at 4:09
Not found in BC28-30: table “item table”.
Quotes
- 0:11 “in this video I’m going to show you how you can actually take a specific flow field from anywhere in the system and use”
- 1:13 “now you see I have 12 um flow fields here. They’re all calculating the same and basically they’re just calculating how many items I”
- 1:48 “You specify a start date or formula. Uh and then an interval and how many. So, in this case it says minus CY means”
- 3:11 “whenever you’re working with references in in regions, it’s always from from the first one. So, I’ll I’ll select that. So, we have now”
- 4:09 “are defined by question mark first. We can have a date, we can have a dimension, we can have a just a field”
Presenters (as heard): Eric.