Introduction: In this article I will be giving the procedure required to hide whitespace if the textbox value is empty and hence not displayed in a report of Dynamics NAV. Pre-Requisite: Microsoft Dynamics NAV Procedure: 1. I have created a simple report with four field values from Customer table for demonstration. 2. I want to display these four fields in a report. So I have created a textbox in the report with these four field values. Instead of creating four different textboxes, I prefer only adding one textbox and giving a line break between each field. 3. The value of the expression of the textbox is as follows: =First(Fields!Name_Customer.Value, “DataSet_Result”) & “<br/>” & First(Fields!Address_Customer.Value, “DataSet_Result”) & IIf(First(Fields!Address2_Customer.Value, “DataSet_Result”)<>””,”<br/>”,””) & First(Fields!Address2_Customer.Value, “DataSet_Result”) & “<br/>” & First(Fields!City_Customer.Value, “DataSet_Result”)  Expression A I’ll explain the Expression A below in detail:  a. “<br/>” is added to insert a line break between each field value. But this expression is used in HTML. So we need to set the textbox to Interpret HTML tags as styles. Hence, you need to double click on your textbox expression. On double clicking the expression, a window opens up which is the Placeholder Properties window. In the Placeholder properties window, set the ‘Markup type’ to ‘HTML-Interpret HTML tags as styles’. b. Now, there is a possibility that ‘Address 2’ field value of the Customer can be empty at times. So a whitespace will be displayed in the report. So to remove the whitespace, I have put the below expression in Point no.: 3 IIf(First(Fields!Address2_Customer.Value, “DataSet_Result”)<>””,”<br/>”,””)  This means that if the Address 2 of the Customer has a value there will be a line break and if no value is present nothing will be displayed and there will be no line break too. Hence whitespace will not be visible in the report. This can be done for any field value which has a possibility to be empty. The output of the report looks like below: I executed the report for Customer No: 01445544-Progressive Home Furnishings If the Address 2 field value is empty:  Report preview is as below:  If the Address 2 has a value present: Report preview is as below: This is the simplest way to hide whitespace in a report if the textbox value is empty.

Introduction: In this article, I will be explaining the procedure to display amount in words in a report of Microsoft Dynamics NAV. Pre-Requisite: Microsoft Dynamics NAV 2016 Procedure: Step 1: Declare the following variables in C/AL Globals: ‘RepCheck’ is a variable of DataType ‘Report’ and Subtype ‘Check’ which is Report 1401. ‘NoText’ is a variable of DataType ‘Text’ with Dimensions Value as 2 which is set by navigating to the properties of the ‘NoText’ variable. ‘AmountInWords’ is a variable of DataType ‘Text’ which will store the final result of the amount in words. Step 2: Now in my custom purchase order report, I want to display the Amount to Vendor in Words. So in the Purchase Header – OnAfterGetRecord() trigger, I have put the following code: I have declared a variable ‘AmountVendor’ of DataType ‘Decimal’ in which I will be storing the ‘Amount to Vendor’. I have rounded off the Amount to 2 decimal places. AmountVendor:= ROUND(“Purchase Header”.”Amount to Vendor”,0.01); Now add the following code to display the Amount in Words: RepCheck.InitTextVariable; RepCheck.FormatNoText(NoText,AmountVendor,”Purchase Header”.”Currency Code”); AmountInWords:=NoText[1]; So the complete code looks like below: Step 3: Now get the variable ‘AmountInWords’ under the DataSource ‘Purchase Header’ in the report and display it in the design layout of the report. After saving and running the report the Amount to Vendor will be displayed in Words as shown below: Conclusion: Thus using the above procedure and the Report 1401 i.e. ‘Check’ report, the amount in words can be easily displayed in any report of Microsoft Dynamics NAV 2016.  

Introduction: In reports, we always a requirement to display header rows of the table on every page. In this article, I will be explaining the steps to do the same in Dynamics NAV. Pre-Requisite: 1. Microsoft Dynamics NAV 2. Visual Studio Report design Environment Procedure: 1. Under layout of your report, click on the arrow under Column groups and then click on Advanced Mode. 2. Now click on (Static) under Row Groups section. 3. Navigate to the properties window of the (Static) under row groups which is displayed on the right. If it is not present, you can open it by navigating to View->Properties Windows or using ‘F4’ button. The properties window will then appear on the right. 4. Now, you need to modify the following properties. Set ‘FixedDate’ property to ‘True’. Set ‘KeepWithGroup’ property to ‘After’. Set ‘RepeatOnNewPage’ property to ‘True’. Using the above steps, your table header rows will be displayed on every page of your report. In the above screenshot, my table header row which includes the captions for the column i.e. row containing ‘No’ and ‘Name’ will be displayed on every page of the report.  

Introduction: With the help of XML port, it is possible to import and export data in Dynamics NAV. While exporting data to an Excel sheet, we always have a requirement to export data along with the column captions to an excel sheet. In this blog article, I will be explaining the procedure to achieve the same. Pre-requisite: Microsoft Dynamics NAV 2017 Procedure: Enter a node name. I have entered my node name as ‘Root’ with Node Type as ‘Element’ and Source Type as ‘Text’. For the caption names, first create a node with Node Type as ‘Element’ and Source Type as ‘Table’. Enter the Data Source as ‘Integer’ which is a virtual table in Dynamics NAV. Now enter a node name for your first caption name. In this example, I have entered ‘VINTitle’ as my first column caption name with Node Type as ‘Element’ and Source Type as ‘Text’. Go to C/AL code of ‘VINTitle’ which is my first node name and in ‘OnBeforePassVariable()’ write the code for the caption name that should appear in the Excel sheet when data is exported.This will display the name of the field which is entered in the table or you can also manually assign the name of the caption that should appear in the Excel sheet after exporting data as shown below. After the caption name is mentioned, now you have to enter the value which should be fetched from the table and appear in the Excel sheet. Enter the node name for your table with ‘Node Type’ as ‘Element’ and Source Type as ‘Table’. Enter your Data source. In this I have taken ‘Item’ as my Data source and the field ‘No.’ of Item table. Conclusion: After exporting data to an Excel sheet, the column captions appear in it along with the values of the column.  

Introduction: In my previous blog, I had mentioned the steps required to setup ACH payments to vendors in NAV. For more details of my previous blog, click here. After all the setups are done, the next step will be to Export and Transmit the ACH payment file. In this blog, I will be giving the procedure for the same. Pre-requisite: Microsoft Dynamics NAV 2017 (North America NA version). Procedure: Step 1: Navigate to Payment Journal. I have created a new payment general journal batch named ‘ACH’. Step 2: Select ‘Suggest Vendor Payments’ which processes open vendor ledger entries and creates a payment suggestion as lines in the Payment Journal window. Enter the Last Payment Date and the Posting Date. Enable Find Payment Discounts, Summarize per Vendor to create one line per vendor for each currency in which the vendor has ledger entries. Select the Bal. Account Type as ‘Bank Account’ and select the bank from which ACH payment has to be made in Bal. Account No. Select the Bank payment type as ‘Electronic Payment’. Select a filter as payment method code is ‘ACH’ so that open vendor ledger entries with vendor payment method code as ACH are displayed. Step 3: Export payments on journal lines to a file prior to transmitting the file to the bank. Select the ‘Export’ action. Output method is basically for the Remittance advice. Select the output method to save it. Step 4: The export file will be created automatically and saved in the Export folder. Now logon to the bank, upload the file and once verification is given from the bank that everything is perfect then it can be transmitted. Step 5: To transmit the payment, click on transmit. After transmitting the payment, NAV will remove the notepad file from export folder and transfer it in the transmit folder. Step 6: Last step is to post the payment. Note: To edit the format of the export file as per the bank requirement, you need to edit format of the Codeunit 10090 – Export Payments (ACH). Using the above procedure, it is possible to do ACH payments to vendors in Microsoft Dynamics NAV.  

Introduction: In Microsoft Dynamics NAV, Lookup Form on a field corresponding to a particular selected option of another field can be displayed in the Request page of a report using C/AL code. In this article we are going to see the procedure with the code required to achieve the same. Pre-requisites: Microsoft Dynamics NAV 2017 Procedure: Step 1: Consider the below two fields which are created in the request page of a report i.e. ‘Bal. Account Type’ and ‘Bal. Account No’. Step 2: BalAccountType is a global C/AL variable of type option which has two options in OptionString property i.e. G/L Account and Bank Account. Condition: If G/L Account option is selected in the ‘Bal. Account Type’ field, then G/L Account List i.e. Page 18 should be displayed as a lookup form in the ‘Bal. Account No’ field. If Bank Account option is selected in the ‘Bal. Account Type’ field, then Bank Account List i.e. Page 371 should be displayed as a lookup form in the ‘Bal. Account No’ field. Step 3: Firstly in ‘SelectionFilterManagement’ i.e. Codeunit 46, create a function GetSelectionFilterForBankAccount(). In the above C/AL code, BankAccount is a global C/AL variable of type record ‘Bank Account’ i.e. Record 270. The local parameter of the function is: Return value of the function is: The variable of the function is: Step 4: Now, create a function ‘GetSelectionFilter()’ in ‘Bank Account List’ page. In the above C/AL code, BankAccountTable is a global C/AL variable of type record ‘Bank Account’ i.e. Record 270. The properties of the above function are as follows: The return value of the function is: Step 5: Create a function ‘GetSelectionFilter’ in ‘G/L Account List’ i.e. Page 18 In the above code, ‘GLAcc’ is a global C/AL variable of type record i.e. Record 15. The return value of the function is: The variables of the function are: Step 6: To display the lookup form based on the condition mentioned in step 2, C/AL code needs to be added in the OnLookup trigger of ‘Bal. Account No’ field. In the above code, ‘BankAccountNoList’ is a global C/AL variable of type Page ‘Bank Account List’ i.e. Page 371 and GLAccountPage is a global C/AL variable of type Page ‘G/L Account List’ i.e. Page 18. Following screenshots show the lookup form being displayed based on a option selected in another field: If G/L Account is selected in Bal. Account Type, the following lookup form i.e. ‘G/L Account List’ is displayed. If ‘Bank Account’ is selected in Bal. Account Type, the following lookup form i.e. ‘Bank Account List’ is displayed. Thus after completing the above mentioned steps, Lookup Form on a field corresponding to a particular condition of another field can be displayed in the Request page of the report.