Function in Jet Reports to Get List into a String

Jet ReportsWhile working on a Jet Report for a client recently, working with data from Microsoft Dynamics 365 Business Central, I had need to return the output of an NL Filter into another function which required a string rather than a list, which meant that I couldn’t use the NL Filter directly.

Fortunately, there is a What in the NP Jet function which allows you to exactly this.

My NL Filter was in cell D4 which I can reference in the Array field of the NP function; the third field to set is the Join String which is the character which will separate the joined items from the list; you need to place the double quotes around the join string yourself:

Jet Function Wizard with a What of Join, Array of $D4 and Join String of "|"

Continue reading “Function in Jet Reports to Get List into a String”

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Find a Matching Record in a Power Automate Flow

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 5th of the new functionality in the Adapt faster with Power Platform section is find a matching record in a Power Automate flow.

Using Power Automate to build flows is already relatively easy, but from time to time, makers are stumbling into this common element of a given process: querying Business Central for a single record that matches certain criteria.

Enabled for: Users, automatically
Public Preview: Apr 2024
General Availability: Apr 2024

Feature Details

Currently, the Find records action always returns a collection or list of records, even if there’s only one record that matches specific criteria, such as the unique record ID. The current action also requires that Power Automate makers understand the syntax of the OData query.

The new Find one record action not only lets makers find a single record that matches their criteria, but also gives them a more friendly user interface to provide parameters and define conditions.

Makers can select several conditions for various fields in the requested API, add sorting conditions so that the proper record is found, and the Power Automate connector transforms these settings into an OData query.

Finding a single record also ensures that all subsequent actions in the flow can operate on it, without requiring the Apply to each loop.

note Note

This feature is only available in Business Central online, but it’s in the Power Platform connector so it’s backward compatible with all supported versions of Business Central.

A new Find one record action in Power Automate connector

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Find a Matching Record in a Power Automate Flow”

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Add Table and Field Mappings for Existing Integration Tables

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 4th of the new functionality in the Adapt faster with Power Platform section is add table and field mappings for existing integration tables.

Business needs change and so do business processes. Changes of business processes require data being exchanged through integrations with Business Central to be implemented in a fast and cost-effective way.

Enabled for: Users, automatically
Public Preview: Apr 2024
General Availability: –

Feature Details

You can add new table mappings by choosing the New Table Mapping action on the Integration Table Mappings page.

Shows New Table Mapping action in Integration Table Mappings page

The action starts the New Integration Mappings assisted setup guide, which will help you set up new integration table and field mappings.

Shows New Integration Mappings guide page with a placeholder to enter new mapping name

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Add Table and Field Mappings for Existing Integration Tables”

Create a Calculated Field in the Jet Reports Table Builder

Jet ReportsI’ve recently been creating some reports for a client using Jet Reports and data pulled from Microsoft Dynamics 365 Business Central. One of the requirements was to have a report which pulled out Intrastat related information in a specific format. As well as pulling out the standard information, they also wanted a calculation of the net weight for a sales order line, which mean linking from the Purch. Inv. Line (123) to the Item (27) table and multiplying the quantity by the net weight.

I initially tried to do this using a calculated field in the Excel PivotTable which was added on top of the data returned by Jet Reports, but quickly realised that the result wasn’t correct. This [prompted me to go back to the source data and see if I could perform the calculation using Jet Reports as it got the data rather than afterwards.

The report was being built using the Table Builder tool of Jet Reports; to add a calculated field in Table Builder, click the Add Formula button in the bottom left:

Table Builder with the Add Formula button ringed

Continue reading “Create a Calculated Field in the Jet Reports Table Builder”

Add Calculated Field to PivotTable in Microsoft Excel

Microsoft OfficeClients often think that I know more Excel than I do, because I know how to use some functions such as IF, CONCAT and more, but my knowledge of Excel is quite limited when it comes to charts, graphs and PivotTables. I was recently writing a report for a client using Jet Reports to pull Intrastat information from Microsoft Dynamics 365 Business Central (both Jet and Business Central are irrelevant as far as the detail fo this post are concerned).

One of the things I wanted to do was create a summary of the data pulled from Business Central, which meant using a standard Excel PivotTable, something with which I have almost no experience. I managed to get most of the PivotTable configured without issue, but I needed to perform a calculation within the PivotTable and it took me a while to work out.

This is done by adding a calculated field through the PivotTable Analyze tab on the action bar; click the Fields, Items & Sets » Calculated Field button:

Excel showing the Calculated Field button on the PivotTable Analyze tab

Continue reading “Add Calculated Field to PivotTable in Microsoft Excel”

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Share Error Details to Get Help from Another User

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 3rd of the new functionality in the User experiences section is share error details to get help from another user.

By facilitating collaboration and knowledge sharing, Microsoft are enabling teams to work together more effectively and efficiently. You can leverage the expertise of colleagues or subject matter experts to overcome obstacles and minimize downtime. The ability to share error details in Business Central enhances collaboration and problem-solving capabilities.

Enabled for: Users, automatically
Public Preview: Mar 2024
General Availability: Apr 2024

Feature Details

When you’re blocked by an error, you can quickly and easily share the error details with colleagues, subject matter experts, or other users to get assistance. The details include the exact error message, error code, and other information that’s helpful when troubleshooting an error. By sharing the error details, you can effectively communicate the specific issue you’re facing, which helps your colleagues provide targeted support and guidance.

Shows new share details menu on inline validation error message

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Share Error Details to Get Help from Another User”

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Use Actions to Navigate and Highlight or Fix Platform-Generated Errors

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 2nd of the new functionality in the User experiences section is use actions to navigate and highlight or fix platform-generated errors.

Microsoft are making it easier for you to help yourself when something goes wrong in Business Central. Error messages provide actions that take you to the page, or even a specific field on a page, so you can quickly resolve the issue. The actions provide value to organizations by enhancing productivity and ensuring smooth workflows.

Enabled for: Users, automatically
Public Preview: Mar 2024
General Availability: Apr 2024

Feature Details

Microsoft have enhanced the user experience by making it easier to understand, go to, and resolve errors that come from the platform. Previously, when an error occurred, you had to manually go to the relevant page or area to address the issue.

The error messages that the Business Central platform generates are now easier to read and understand because they no longer contain field names. However, messages still contain the full technical details, including field names, in the Detailed error message section. Select the Copy details icon on inline validation errors or in an error message to access the technical information.

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Use Actions to Navigate and Highlight or Fix Platform-Generated Errors”

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Use Drag and Drop to Attach Multiple Files

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 1st of the new functionality in the User experiences section is use drag and drop to attach multiple files.

Drag and drop functionality to attach multiple files to Business Central records makes it easier to manage files. This feature benefits users who work with multiple files, and adds value to organizations by enhancing data organization and accessibility.

Enabled for: Users, automatically
Public Preview: Mar 2024
General Availability: Apr 2024

Feature Details

You can conveniently drag multiple files at the same time to attach them to records in Business Central. Select multiple files, and then just drag them to the drop targets.

This improvement is particularly beneficial if you often work with multiple files and need to associate them with a specific record. The ability to attach several files at the same time eliminates the need to manually upload files one-by-one, which provides a more efficient and user-friendly experience.

Drag and drop file capability on prompt dialog

My Opinion

despite knowing it doesn’t work, I keep on trying to drag and drop multiple files, so it will be good to see this finally work.

Click to show/hide the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 Series Index

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1
Use Virtual Tables to Expose More Business Central Data in Dataverse
Improve Approval Processes via Power Automate
Add Table and Field Mappings for Existing Integration Tables
Find a Matching Record in a Power Automate Flow
Archive Projects
Usability Improves for Service and Projects
Use Deferral Codes in Purchase and Sales Journals
Define a Service Invoice Posting Policy for Various Users
Define Default Location for Project or Project Phase
Block Item, Item Variant or Service Item from Use in Service Management transactions
Connect Business Central With Shopify B2B
Set Up Financial Consolidation Runs More Efficiently
Simpler Shopify Connection
Use General Ledger Account Revaluation for More Accurate Financial Statements
Get More Productive While Entering Time Sheets
Create Projects That You Can Invoice to Multiple Customers
Assemble to Project
Invoice a Customer for Multiple Projects
Use New Excel Layouts for 35 Selected Reports
Use Standard Terminology for Project Management
Use Currencies When Posting Employee Transactions
Manage Work Orders in Field Service, Invoice Them in Business Central
Manage Document Attachments in Service Items and Documents
Export and Import Multiline Text Using Configuration Packages
Correct Errors in Service Invoices
Prevent Editing Number Series on Service Documents and Cards
Be More Productive When Approving Time Sheets
Inventory Package Numbers Work Like Item Tracking Dimensions
Assign Salesperson to Customer Ship-to Addresses
View Incoming Documents in Archived Quotes and Orders
Specify Dimensions on Posted Entries Applications With Realized Gains or Losses
Allow Lines with Amounts of Zero on Recurring General Journals
Automate the Reminders Process in Collections
See Improved Usability for Warehouse and Inventory
Ensure Accurate Inventory Valuation by Controlling Cost Adjustment
Use a New Role Center for Collection Management
Manage Subscription Billing and Revenue and Expense Recognition
Enable Sustainability Features for Integrations with APIs
Install Field Service Integration with Business Central from AppSource
Introduce Power Automate Copilot Integration with Business Central
Map e-Documents to Purchase Order Lines with Copilot
Create Sales Lines Easily with Copilot
Extend Copilot Using Richer Development Tools
Get to Insights Faster with Copilot-Generated Analysis Tabs
Complete Bank Account Reconciliation Faster With Copilot
Create Product Information Faster with Copilot
Learn More About Fields with Copilot
Chat with Copilot
Simplify collections with Copilot for Finance and Business Central
Automate Tests for your Copilot Extensions with the Business Central Test Toolkit
Automate IRS 1099 Form Submission in US
Enable Additional Countries and Regions
Install UK Localization as an App
Process Czech Bank Statements, Payment Orders More Productively
Improvement of Czech Localization Reports
Report Explorer has Additional Outputs in the Czech Version
Improved Updates of ARES Data in the Czech Version
Allow More Extendability of Advance Payments in the Czech Version
Print QR Codes on Sales Invoices in the Czech Version
Use Excel Layouts for Czech Reports
Handle Multiple File Uploads and File Drop Zones
Debug the System Application
Remove Friction When Working with External App Dependencies
Use In-client Page Scripting Tool for Acceptance Testing
Use AL-controlled Barcode Scanning For All Scenarios
Write flexible and performant code with NumberSequences
Use the OData V4 IN Operator in Web Service Queries
Define Extension Objects in Same App as Their Base Object
Use Cookies in HTTP Calls From Business Central
Define Tooltips on Table Fields
Use Linked Environment in Dataverse Integration
Audit Business Central Operations in Microsoft Purview
Encrypt Data At-Rest with Customer-Managed Encryption Key
Allow Customers to Consent to Microsoft Support Accessing Their Data
Link Business Central Environments to Power Platform Environments
Copy Companies with Dataverse Integration Safely
View and Filter Users With Additional User Details
Control partner access per environment
Analyze Telemetry Events Across Browser and Server for a Session or User
Use Premium License to Sign In to Environment with Essentials Licenses
Experience the New Embedded AppSource Marketplace Page
Migrate Record Links and Notes with Cloud Migration Tooling
See VAT Date in Posting Previews
Automate Country of Origin Listed for Item Tracking Code in Intrastat Reporting
Achieve Sustainable Compliance with Business Central
Discover Report and Data Analysis Content Easily
Use Drag and Drop to Attach Multiple Files
Use Actions to Navigate and Highlight or Fix Platform-Generated Errors
Share Error Details to Get Help from Another User
Access Worksheet Pages from Mobile Phones
Boost Productivity With the Modern Action Bar
Enhancements to the User Experience

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Discover Report and Data Analysis Content Easily

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 1st of the new functionality in the Reporting and data analysis section is discover report and data analysis content easily.

End users can now discover report and data analysis content, including queries, reports, and Power BI pages, by using navigational search (Tell Me) or the new report explorer page. This feature minimizes the effort it takes to find report and data analysis content, making it more accessible and user-friendly.

Enabled for: Users by admins, makers, or analysts
Public Preview: Apr 2024
General Availability: Apr 2024

Feature Details

Business Central comes with many options for reporting, business intelligence (BI) dashboards, and data analysis. This release makes this content much easier to discover through search and a new report explorer page.

In the search experience, Microsoft include the title and description from teaching tips (if defined by the developer), so that users can better decide whether the report, page, or query is what they’re looking for to solve their analytics need.

The role explorer has explanations on all entries, so that when you hover over a report, page, or query, you’ll find:

  • The title and description from the teaching tip (if defined).
  • You can open the report, page, or query in a new browser tab without closing the explorer view.
  • An icon that helps you decide whether this is a report that delivers Excel or PDF files, whether the page is a Power BI report, or whether the query supports data analysis.

Click to show/hide the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 Series Index

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1
Use Virtual Tables to Expose More Business Central Data in Dataverse
Improve Approval Processes via Power Automate
Add Table and Field Mappings for Existing Integration Tables
Find a Matching Record in a Power Automate Flow
Archive Projects
Usability Improves for Service and Projects
Use Deferral Codes in Purchase and Sales Journals
Define a Service Invoice Posting Policy for Various Users
Define Default Location for Project or Project Phase
Block Item, Item Variant or Service Item from Use in Service Management transactions
Connect Business Central With Shopify B2B
Set Up Financial Consolidation Runs More Efficiently
Simpler Shopify Connection
Use General Ledger Account Revaluation for More Accurate Financial Statements
Get More Productive While Entering Time Sheets
Create Projects That You Can Invoice to Multiple Customers
Assemble to Project
Invoice a Customer for Multiple Projects
Use New Excel Layouts for 35 Selected Reports
Use Standard Terminology for Project Management
Use Currencies When Posting Employee Transactions
Manage Work Orders in Field Service, Invoice Them in Business Central
Manage Document Attachments in Service Items and Documents
Export and Import Multiline Text Using Configuration Packages
Correct Errors in Service Invoices
Prevent Editing Number Series on Service Documents and Cards
Be More Productive When Approving Time Sheets
Inventory Package Numbers Work Like Item Tracking Dimensions
Assign Salesperson to Customer Ship-to Addresses
View Incoming Documents in Archived Quotes and Orders
Specify Dimensions on Posted Entries Applications With Realized Gains or Losses
Allow Lines with Amounts of Zero on Recurring General Journals
Automate the Reminders Process in Collections
See Improved Usability for Warehouse and Inventory
Ensure Accurate Inventory Valuation by Controlling Cost Adjustment
Use a New Role Center for Collection Management
Manage Subscription Billing and Revenue and Expense Recognition
Enable Sustainability Features for Integrations with APIs
Install Field Service Integration with Business Central from AppSource
Introduce Power Automate Copilot Integration with Business Central
Map e-Documents to Purchase Order Lines with Copilot
Create Sales Lines Easily with Copilot
Extend Copilot Using Richer Development Tools
Get to Insights Faster with Copilot-Generated Analysis Tabs
Complete Bank Account Reconciliation Faster With Copilot
Create Product Information Faster with Copilot
Learn More About Fields with Copilot
Chat with Copilot
Simplify collections with Copilot for Finance and Business Central
Automate Tests for your Copilot Extensions with the Business Central Test Toolkit
Automate IRS 1099 Form Submission in US
Enable Additional Countries and Regions
Install UK Localization as an App
Process Czech Bank Statements, Payment Orders More Productively
Improvement of Czech Localization Reports
Report Explorer has Additional Outputs in the Czech Version
Improved Updates of ARES Data in the Czech Version
Allow More Extendability of Advance Payments in the Czech Version
Print QR Codes on Sales Invoices in the Czech Version
Use Excel Layouts for Czech Reports
Handle Multiple File Uploads and File Drop Zones
Debug the System Application
Remove Friction When Working with External App Dependencies
Use In-client Page Scripting Tool for Acceptance Testing
Use AL-controlled Barcode Scanning For All Scenarios
Write flexible and performant code with NumberSequences
Use the OData V4 IN Operator in Web Service Queries
Define Extension Objects in Same App as Their Base Object
Use Cookies in HTTP Calls From Business Central
Define Tooltips on Table Fields
Use Linked Environment in Dataverse Integration
Audit Business Central Operations in Microsoft Purview
Encrypt Data At-Rest with Customer-Managed Encryption Key
Allow Customers to Consent to Microsoft Support Accessing Their Data
Link Business Central Environments to Power Platform Environments
Copy Companies with Dataverse Integration Safely
View and Filter Users With Additional User Details
Control partner access per environment
Analyze Telemetry Events Across Browser and Server for a Session or User
Use Premium License to Sign In to Environment with Essentials Licenses
Experience the New Embedded AppSource Marketplace Page
Migrate Record Links and Notes with Cloud Migration Tooling
See VAT Date in Posting Previews
Automate Country of Origin Listed for Item Tracking Code in Intrastat Reporting
Achieve Sustainable Compliance with Business Central
Discover Report and Data Analysis Content Easily
Use Drag and Drop to Attach Multiple Files
Use Actions to Navigate and Highlight or Fix Platform-Generated Errors
Share Error Details to Get Help from Another User
Access Worksheet Pages from Mobile Phones
Boost Productivity With the Modern Action Bar
Enhancements to the User Experience

New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Achieve Sustainable Compliance with Business Central

Microsoft Dynamics 365 Business CentralThis post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2024 Wave 1.

The 3rd of the new functionality in the Legislation section is achieve sustainable compliance with Business Central.

This feature enables you to collect and report on your sustainability activities. The solution is the foundation that will be used for compliance with the European Union’s Corporate Sustainability Reporting Directive (CSRD) and other ESG standards..

Enabled for: Users, automatically
Public Preview: Apr 2024
General Availability: May 2024

Feature Details

Microsoft are introducing a groundbreaking feature focused on sustainability reporting. This new, easily extendable functionality lets you record and report on a wide spectrum of sustainability information, encompassing both qualitative and quantitative aspects, and forward-looking and retrospective data. You can also leverage the feature to actively reduce emissions, making it a valuable tool for small and midsized organizations seeking to comprehensively report on sustainability initiatives.

This feature ensures compliance with the European Union’s CSRD, which requires companies to report on the sustainability of their activities. The CSRD introduces a heightened requirement for reporting, impacting approximately 50,000 companies in the European Union—more than four times the number covered by the previous Non-Financial Reporting Directive (NFRD), which the CSRD supersedes. However, the solution will be built to ensure that ESG compliance aligns with other standards.

Continue reading “New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1: Achieve Sustainable Compliance with Business Central”