Introduction: Whenever the a single field in NAV contains Name, Address and other values seperated by delimiter such as ‘,’. But this field cannot formated in SSRS to look appropriate as follows. For Example in NAV: John Peter, Long Island, New York,US. Formatting in SSRS: John Peter, Long Island, New York, US. Pre-requisites: Microsoft Dynamics NAV. Solution: Getting a combined value field in this we are concating mutliple values seperated by ‘|’. In this case I’m using the comments on Sales Invoice Lines. This can be a single field or combination of mulitple fields Code to combine all comments in single variable Writing a Query in SSRS report query to seperate the values by delimiter Field and its expression: Output: In this output screen the comments are seperated properly in SSRS report. Conclusion: This is one of the easiest way of combining and seperating and formatting the report by the use of  delimeter.

Introduction: When coding an app that uses Webservices to connect to the integration solutions, it is generally recommended that there is a Setup, which we can configure, where the Webservices are created as well as deleted. Pre-requisites: Business Central VS Code with AL Language Extension Demonstration: Create a Setup Page having a boolean variable say ‘SetupExtension‘ and create a field having value as ‘SetupExtension Variable’.Output: On the OnOpenPage trigger add a code to Check if Web Services are already created and accordingly set the ‘SetupExtension‘ boolean field.Thus, whenever the Setup Page is Opened, the ‘SetupExtension‘ field is set as per the records in Web Services. On the OnQueryClosePage trigger write a code which will create the Web Services when ‘SetupExtension‘ boolean is set as ‘TRUE‘ and delete the Web Services if ‘SetupExtension‘ boolean is set as ‘FALSE‘.Output:i. When ‘SetupExtension‘ is set to ‘TRUE‘New Webservices are created.ii. When ‘SetupExtension‘ is set to ‘FALSE‘Web Services are deleted.  Conclusion: Thus this way we can keep the existing app installed and enable/disable Web Services as needed. As compared to creating WebServices.xml file which directly creates the Web Services on installation of the app, I’ll be demonstrating it in my next blog.

Introduction: Dealing with Electronic Bank Payment Setup can be tedious and time consuming. In this blog, I’ve a unusual requirement where the payment date should be the next day of the check issue date. Pre-requisite: 1. Microsoft Dynamics Business Central 2. VS Code and AL Language Extension Demonstration: 1. In standard Data Exchange Definition, I didn’t find any rule to increment the date by 1 day. So using the Integration Event and Event Subscriber, I’ve written AL Code to use the custom transformation rule and perform the +1D operation. 2. I’ve created a custom Transformation Rule in the Feild Mapping on Data Exchange Definition in this case ‘ADD1D’ and set in the field mapping. 3. In the code I’m looking for the transformation rule with the name ‘ADD1D’ and then use the code to actually add 1 day to the date. Code: 4. Output: Conclusion: This type of customization not only can be done with Data Exchange Definitions. This was an example of how the Integration-Event Subscriber can be used to modify existing functionality in Business Central

Introduction: There is a client requirement where the Positive Pay Export has a void-check indicator which needs to be 3 Text character. Although there is 1 Text character field for Void Check Indicator in standard positive pay, I couldn’t get it to convert to the desired format by using Transformation Rules or Text Padding. Thus I have made few customizations and is demonstrated below. Pre-requisites: Microsoft Dynamics 365 Business Central. Working understanding of Positive Pay Export. Refer(https://www.cloudfronts.com/setup-positive-pay-export/) Demonstration: As per requirements for Santander Bank, Void Check is indicated by *26 Print Check is indicated by *10 Whereas in standard we get, Void Check is indicated by ‘V’ Print Check is indicated by ‘<blank>’ Using transformation rules such as replace V with *26 and <Blank> with *10, the transformation rules only converts the first character which is V to * and  <Blank> to *. 1. There is an existing codeunit(1704) which is used as Pre-Mapping Codeunit in field Mapping in Positive Pay Data Exchange Definition. 2. Create a Table Extension for Table  1241( Positive Pay Detail)and added a text field name ‘VoidStatusIndicator’ of size 50 3. Thus I exported the codeunit to text and converted to AL using Text-To-AL and added the lines as follows in PrePosPayDetail function. 4. Map the newly added field VoidStatusIndicator in the field mapping and chnage the Pre-Mapping codeunit value to the ID of newly created codeunit. Output: *2623137269100000000008935713384000000500710241800000010205 *1023137269100000000008935713384000000500910241800000010201 Conclusion: This is how we can do Positive Pay customization can be done. This method is not limited to Positive Pay but with all the Data Exchange methods. In my next blog, I’ll be writing about the customization for ACH.

Problem Definition: When working with ‘Edit In Excel’ functionality in Business Central, there was an error( https://github.com/Microsoft/AL/issues/4060 ) when the page fields were modified using extensions. Introduction: Excel Buffer is one of the feature that is widely used to Import & Export data to and from Excel Sheets. With NAV evolving to Business Central, few of the existing functions are deprecated and cannot be used in AL Extensions. Pre-requisites: Microsoft Dynamics 365 Business Central Demonstration: 1. Excel Buffer Import: In this code, total rows and columns to be imported is found, then each field is stored as ‘CellValue’ in new records in Excel Buffer. Thus, I’ve used GetValueAtIndex(Row,Column) function to get the exact ‘CellValue’. 2. Excel Buffer Export: Conclusion: In this blog, I’ve demonstrated how to use Excel Buffer for Import/Excel of Excel from a table using AL Extensions. NOTE: Code is located( https://github.com/olisterr/ExcelBufferImportExportforBC )

Introduction: In Business Central, the existing reports cannot be modified. Thus, for modifications in the report, the existing report is to converted to text using Text-To-Al and then modifications are to performed. But in case of Check Report(10401) it doesn’t work giving error ‘Index out of bounds!’. Pre-requisite: Microsoft Dynamics Business Central (Online) Solution: When modifying the Check Report in Business Central, the only approach available is converting existing Check Report 10401 to Text and then using Text-To-Al.Which gives the error ‘Index-Out-of-Bounds!’. Thus for a workaround I used the code from https://community.dynamics.com/nav/b/moxie4nav/archive/2014/12/08/numbers-to-words Although this algorithm works perfectly on NAV and Business Central On Premise, it returns the same error on Business Central Online. As assumed, it was because of the recursion used. So, here is the non-recursive algorithm that works perfectly fine up to 8 digits characteristics and 2 digits of mantissa. List of variables: Units[10], Tens[20], Expos[30] are arrays with respective dimensions. Initialization of Variables: Code: Kindly find the text object on GitHub: https://github.com/olisterrcf/CheckReportAmountInWords Conclusion: While the updates are still getting generated on this issue, it is far from being conclusive to reach out to the expected results. As per our cordial discussions with Microsoft, the existing  report objects on On-Premise versions such as NAV and Business Central On Premise are not 100% compatible with Business Central Online  which are converted using Text-To-Al tool. In the next blog, I’ll write about update for the same.

Introduction: In this blog I’ll be demonstrating few features of Business Central October Release[RC3]. Pre-requisite: Microsoft Dynamics 365 Business Central October Release[RC3]. Demonstration: 1.Refreshed User Experience Improved Card Page Layout. Improved List Page Categorized Actions. Changed position of New,Edit, Delete as well as Previous andNext buttons. 2. Improved Productivity Advanced filtering with multiple column filtering: Limit Totals: Row-based column copy past: Actual Data: Data in Excel Keyboard shortcuts: The October release, also consists the shortcut F8 that copies the value of the cell above. Thus, you can fill the new rows by moving across cells and selecting F8 on the cells where you want to copy the values from cell above. List of Keyboard Shortcuts : https://docs.microsoft.com/en-us/dynamics365/business-central/keyboard-shortcuts 3. Improved Search The search option has also been enhanced and categorized where previous search results were only related to pages and reports. In the upcoming release with the improvement in logic, the search results will also include the actions on the current page and the documentation along with the reports and pages. From the search itself you can check and perform the tasks that you want (for example: Create a contact for customer) Enter the contact details for the customer and click on OK 4. Permission Sets are Editable In Business Central October Release, the Permission Sets are Editable. New Permission Sets can be created as well as Permission Sets can be modified and also indented as well. Click on More Options to get the details. Changing the Permisison Sets in Business Central: Added an execute permission: Conclusion: These are few features of the Business Central. In the next blog I’ll demonstrate about the AL Language extension improvements.

Introduction: In Customer Applied Entries there are Payments applied to List of Invoices .Both Payment and Invoices are from Customer Ledger Entry Table .The Payment type entries can be obtained by filtering Document Type = ‘Payment’. Inorder to find the Invoices Applied there are no specific filters that can be applied during compile time and entry list is stored in temporary record variable. And Temporary Record variables cannot be used as DataItem, the list of applied Invoices cannot be displayed in SSRS Report. Pre-requisite: Microsoft Dynamics NAV 2017 Solution: 1. Using the Codeunit 10202 Entry Mgmt, gives the list of applied  Invoices to a specific Payment. 2. List of Variables Note: AppliedCLE is a temporary record variable 3. Creating DataItem as Integer and adding temporary record(Applied CLE) fields in the Integer DataItem  4. Linking between Integer DataItem and AppliedCLE temporary record variable Thus the count of AppliedCLE records is obtained and used to loop the Integer DataItem. 5. Verifying the output i. Using Applied Customer Entries ii. Using the created report Conclusion: DataItems can be virtual tables. These virtual tables can be used to deal with many runtime table filtering scenarios.

Introduction: In NAV when building a custom report that deals with  totalling the amount by multiple G/L accounts by week then the report becomes tough to read as well as develop  in standard SSRS Report. A possible and easy workaround is dumping all the entries in the Excel and performing the pivot table operation on the entries to give a precise results. Pre-requisite: Microsoft Dynamics NAV 2017 Demonstration: 1. List of Variables: 2. UpdateRange  function to automatically update the sheet range of the current row: 3. Creation of Excel Entries Sheet: Creation of Excel Sheet: Creation of Column Titles: Insertion of Data Entries: 4. Pivoting the Data from the sheet containing entries: Pivoting operation in Excel takes place with the help of Pivot Cache. This Cache is modified using the Automation Variable. Conclusion: The method of using Automation Variable is allowed in On-Prem version of NAV, as it uses Excel libraries that already installed with Microsoft Office. Although this method works perfectly fine Windows Client, it does not work on WebClient. Thanks for reading!!

Introduction: What is Positive Pay Export and Why it’s used? Positive Pay Export is a file that is given to the bank to verify the Cheques that have been printed. Whenever the company wants to issue any payment to Customer or a Vendor, it is done with the help of Payment Journals. After creation of Payment Journal lines, the check is printed through a Report.  After the creation of the Check in the Bank Account there is an action called as PositivePayExport. This file is a text file and contains the information related to the Cheques printed and it is given to the bank. Every bank has their format which is used to automatically verify the Cheques that are being issued. Pre-requisites: Microsoft Dynamics NAV 2017 Demonstration: 1. Creating a Bank Account: To issue the cheque setting up Bank Account is mandatory. 2. Setting up the Data Exchange Definitions: Data Exchange Definition is used to set up the format for Positive Pay Export. The format for Export: Create a new Data Exchange Definition: Creating Column Definitions: Column Definition is the sequence in which the data is to be generated as per the format. Creating a Line Definitions: Line Definitions are used to map the System Fields to the fields in the column definitions. In the Line Definition, there are transformation rules that are used to modify the data that is exported into the format. Create a Line Definition and Select Field Mapping. There are some predefined Transformation Rules as well as new transformation Rules can be created. I’ve created the following transformation rules. I’ve shown the details of REPLACE transformation rule 3. Connecting the Data Exchange Definition with the Bank Account: To link the Positive Pay Export and the Bank Account, go to Bank Accounts and select the Positive Pay Export Code. 4. Creating Payment Journal Entries, Printing Cheques and Exporting the Positive Pay file. Creating Payment Journal Entry Printing the Check Report Positive Pay Export in Bank Account KEY4679 After Exporting Positive Pay Export the values generated in Positive Pay Export file are as follows. 000003296812746790000000012201805150000010200 000003296812746790000000013201805150000010512