DynamicsCentral - A Business Central D365 Blog for Developer and Consultant

How to Fix gitignore not working in .gitignore file?

2022-03-30T13:51:00.002-04:00

A gitignore file may not be working for multiple reasons,

committed code to the repository before creating gitignore file
file type of gitignore file
naming of gitignore file

How to Fix gitignore not working in .gitignore file?

Below are some solutions to the gitignore now working issue in a .gitignore file:

If the files/folder are already in the repository then you have to remove them.
If your .gitignore file is saved in any other encoding then UTF-8 like UTF-16, change it to UTF-8. (windows)
Make sure your file name is .gitignore. There are some cases where we just name file as gitignore or gitignore.al or gitignore.txt.

Fix 1: Add Files/Folder to Repository again:

git rm -r --cached .

git add .
git commit -m "fixed untracked files"

Fix 2: Fix encoding of gitignore file

Fix 3: Fix naming and file format of gitignore file

References:

How to fix "gitignore not working" issue · GitHub

git - Gitignore not working - Stack Overflow

How to Fix Gitignore Not Working? – Here Are Solutions for You (partitionwizard.com)

How to Change File Encoding in VS Code - Kindacode

How to convert Date to DateTime in Business Central [AL- VsCode]?

2022-03-28T12:03:00.001-04:00

I have to assign a "Date" Value to a "DateTime" variable. How do I convert the "Date" to a "DateTime"?

There are two ways to achieve this,

Using CREATEDATETIME procedure
Using EVALUATE procedure

Using CREATDATETIME Procedure:

Use procedure CREATEDATETIME where your date is first parameter and 0T is second parameter.

YourDateTime := CREATEDATETIME(YourDate,0T);

I would suggest to create a procedure where parameter is your Date to convert and return value is your datetime variable.

You can copy paste following:

    
    /// <summary>
    /// ConvertDateToDateTime using CREATEDATETIME
    /// </summary>
    /// <param name="YourDate">Date.</param>
    /// <returns>Return variable YourDateTime of type DateTime.</returns>
    procedure ConvertDateToDateTime(YourDate: Date) YourDateTime: DateTime
    begin
        //using CREATEDATETIME
        YourDateTime := CreateDateTime(YourDate, 0T);
        
    end;

Using EVALUATE procedure:

Use procedure EVALUATE where your datetime variable is first parameter and yourdate inside Format is second parameter.

Evaluate(YourDateTime, Format(YourDate));

I would suggest to create a procedure where parameter is your Date to convert and return value is your datetime variable.

If you want to copy the code:

   
    /// <summary>
    /// ConvertDateToDateTime using EVALUATE
    /// </summary>
    /// <param name="YourDate">Date.</param>
    /// <returns>Return variable YourDateTime of type DateTime.</returns>
    procedure ConvertDateToDateTime(YourDate: Date) YourDateTime: DateTime
    begin
        //USING EVALUATE
        Evaluate(YourDateTime, Format(YourDate));
    end;

Conclusion:

These are two ways I know to convert date to DateTime in business central and NAV. I hope you found this useful, thank you for reading.

How to discard all Local Changes/Commit and Pull Latest Data from Repo - GIT

2022-03-18T12:03:00.005-04:00

You will learn how to discard all local changes in your git repository in this post. Local changes could be change in file, added directory or commit.

Step 1: Remove all untracked directories and files

$ git clean -fd

The -f flag forces the clean, while the -d flag applies it to untracked directories, as well.

Step 2: Reset all changes in your project files

$ git reset --hard origin/<branch name>

This will reset the branch. <branch name> is your local branch name.

My scenario:

I made changes in my local git repository and did two commits in main branch. I only realize my mistake later when I was trying to do the sync.

I switched to my working local branch (Branch-A)and made same changes and sync my code to remote repo. I created pull request and my changes in my branch(Branch-A)got pull to main branch in remote. During pull request I deleted my working branch in remote too.

Again later I got another task for same project so I created another branch (Branch-B)from main branch in remote git repository.

Now I want to update all my local git repositories. How can I do this?

I opened my Terminal/git-bash in vs code

$ git clean -fd

$ git reset --hard origin/main

Hope this help you. :)

How to Publish Business Central Apps in AppSource? [Step by Step Guide]

2022-03-08T12:26:00.001-05:00

How to create an app for Business Central? or If you created an app for business central and you want to publish the business central Apps in AppSource then this is what we will be discussing in this post.

Some useful links:

https://aka.ms/BCYammer

https://aka.ms/CheckBeforeYouSubmit

https://aka.ms/TechValidationFAQ

https://aka.ms/AppSourceGo

https://aka.ms/MarketingValidationFAQ

https://aka.ms/readyfornextmajor?appid=<yourappid>

References:

1. Microsoft Event: DKWC045 Business Central apps in AppSource

VS Code for AL Development [Tips, Setup and Extensions]

2022-03-01T15:22:00.000-05:00

In this post I want to share some of the shortcut I am using in vs code during development.

To download VS Code: https://code.visualstudio.com/download

Resources:

1. Waldo DynamicsCon Video:

'al/gotodefinition' failed with error: 'Index was outside the bounds of the array.' - Business Central

2021-05-06T13:08:00.000-04:00

While moving mouse over the code in vs code we get the error:

Please report this issue to https://github.com/microsoft/al/issues including information on how to reproduce it, if possible.

Processing of message 'al/gotodefinition' failed with error: 'Index was outside the bounds of the array.'

I don't know what is the reason behind this error but it is causing because something is broken in extension.

I believe this will be fixed once this extension is updated. Currently I am using v0.2.4.

Note: I read some where about removing git extension will fix this, but for me it didn't fixed.

Solution/Workaround:

But for now, instead of opening base object files form the sidebar using AL Object Designer button, Open it from the package folder.

Don't open from here:

Open using: Open it from the package folder.

STEP 1: click on the package you want to see the object. For example to see the customer select Microsoft Base Application.

STEP 2: It will open list of objects in the package. Right click on the object you want to see (In Our example Customer). Then press Go to Definition. It will open the object (code).

Object will have the extension of dal (.dal)

Hopefully this fixed your issue.

Reference:

Github

RDLC Report Designer Tutorial and Basic Setup [using RDLC Report Builder] for Business Central

2020-07-05T16:52:00.004-04:00

In this rdlc report designer tutorial, I will discuss the basic setup for the rdlc report in Report Builder. Microsoft Report Builder is a tool to build paginated reports used by the Microsoft reporting system. RDLC Stands for Report Definition Language Client-Side. The .rdlc is the format of a report file used by the Microsoft reporting system.

Let's start this tutorial with Basic Setup.

Report properties setup:

When you open the report designer right-click on blank space and select report properties.

First of all, let's see Page Setup properties:

Page Units: Inches
Paper Orientation: Portrait (As per required, but start with portrait if not sure)
Paper Size: Letter
Margins:

Left: 0.5 in
Right: 0.5 in
Top: 0.5 in
Bottom: 0.5 in

Other report properties like Code, References, and variables are not needed to change for the basic setup. If you need to use get and set data we need to write code on the Code properties section.

Report Body Setup:

Click on the report body and see the properties window on the right side of the screen. If the properties window is not shown then go to the view tab and checkmark Properties.

In properties window set the size of report page:

Size Setup:

Width: 7.4 in
Height: 2 in (could increase/decrease as needed)

Note: we set the width as 7.4 in because we subtract 0.5 * 2 (page margins from page setup) from 8.5 and just keep 0.1 inches as precision/safety. You can keep 7.5 in but sometimes the last column of the table might print on the next page during the report preview. So to avoid this try to fit the report in 7.4 inches.

If you need to change the background color of the page or border style of body section then you can change the setting in the properties section.

Font setup:

To activate font setup you need to select a text box or any report item. By default Font is Arial and size is 10. But for a better view of the report I always setup my font as following:

Font Family: Segoe UI
Font size: 8 pt (10 pt - For Title)
font color: Black
Font Style: Normal
Font weight: Default (Bold - For Title)
Text Decoration: Default
Text Align: Right

For font setup I suggest changing the font after creating most of the report items like text box and tables. Also for tables select individual cells or select all cells, but selecting the table doesn't work for font properties. During the finalization of the report please make sure all fonts meet the standard of the report.

Text Box Setting:

Just setting up font is not sufficient for displaying reports elegantly, we need to setup textbox and table too. The following are some of them for Textbox.

Padding: 2 pt, 2 pt, 2 pt, 2 pt
CanGrow: False
CanShrink: False
Border Style: None
Size:

width: 1 in
Height: 0.5 cm (Height in centimetre)

Table Setting:

Table is one of the most use report item in the rdlc report. I usually do following setup when working in tables:

First I will setup the data source and name of the table. To do so:

First, go to tablix properties by right-clicking the table top-left box.
In general tab, go to the dataset name and select the dataset. Generally, the name of the dataset is 'Dataset_Result'
To change name of table from Tablix1, just go to name section in general tab. In box of name section write name of table without space and CamelCase.

After this I change the font from Arial to Segoe UI 8 pt. Do this by selecting all cells individually.
After font is setup we need to fix the size of row.

Width: 1 in
Height: 0.5 cm (0.19685 in)

Also make growing and shrinking property false so even the field is long or short table design remains constant.

CanGrow: False
CanShrink: False

Remove border from header and body of table. To do so select all cell of table and change the border property.

BorderStyle: None
BorderColor: Black (Delete the color it will take it to default)
BorderWidth: 1 pt

On the header I always prefer to keep border at bottom section. So for that select all the cell of only header of table and change the border style.

BorderStyle: Solid
BorderColor: Black (Delete the color it will take it to default)
BorderWidth: 1 pt

Advance Table setup :

To repeat table header on each page.
Add grouping

Adding Built-in fields in the report:

In beginning days of report development, we used to get page number, date, and user from a dataset but now these fields are available as a built-in variable in the report itself.

We will be adding these built-in fields in the header section of the report. Let's add a header to the report by right-clicking outside of the report body and selecting Report Header.

Execution Date and Time:

We use a built-in field called ExecutionTime to get execution date and time of the report. This global variable gives the date and time that the report begins to run.

=Globals!ExecutionTime

If you want to just display date only or time only then you need to format the field.

Page Number:

We use a built-in field called PageNumber for the current page number and to see total page number we use TotalPages.

The current page number, which can be reset through the use of page breaks. Can be used only in page header or footer.

=Globals!PageNumber

The total number of pages in the current continuous page sequence. The number can be reset by using page breaks. Can be used only in a page header and footer.

=Globals!TotalPages

For more setup of page number, I will write it in another blog post.

User ID:

We use a built-in field called USERID to get the ID of the user running the report.

=User!UserID

Conclusion:

I will be updating this post as I find some more tips on the report but for basic this setup is enough. If you have any other setup in a report please let me know in a comment below, I will update the setup in this blog.

Resources:

To download the latest version of Microsoft Report Builder click here.

To know rdlc report basic setup in visual studio click here.

For the report-builder tutorial click here.

PageNo is Being deprecated in versions: 1.0 or greater - [Business Central Report Tips]

2020-07-02T14:50:00.003-04:00

PageNo Method is used to get or set current page number of report. But when you convert report code from C\AL to AL then in vs code you will see warning saying 'PageNo' is being deprecated in versions: 1.0 or above. This warning will become an error in future release.

So what is the solution for the issue of PageNo?

The solution is simple delete this line of code. This code is used in classic report designer to show the page number or to get or set the page number of the report. But in RDLC report page number is built-in variable and you can use that instead of CurrReport.PageNo variable.

In all of the standard report built-in variable called Globals!PageNumber is used when we need page number in report.

Conclusion:

Just remove the line of code it doesn't do anything useful when used in C/AL or AL code. Also if it is used in datasets remove from there too.

Reference:

PageNo Method

Reports How to Re-implement Page Number

Preparing for Business Central Upgrade

2020-06-25T17:12:00.006-04:00

How to upgrade business central from different version of NAV? If this is your question then this blog post might be first step for you. In this blog post I will share some important point to consider for preparing for Business Central Upgrade on the basis of great webinar I watched recently.

I watched webinar of Jon Long, Saurav Dhyani and Luc van Vugt on upgrading to BC. There are many consideration need to be taken during upgrade and some of very best consideration are discussed in this webinar.

Preparing your BC upgrade – A panel discussion

Following are some of the takeaway from the webinar:

Follow the following path to upgrade to Business Central.
Business Central Life-cycle Policy says there will be 18 months of involvement in each release. First 6 month there will be new features and next 12 month will be support from Microsoft.
Microsoft will bring N-2 support policy starting from BC17 (2020 Wave 2 ) i.e. 10/01/2020
License and Deployment options. How and why Microsoft is taking deployment to SaaS.
Before starting the upgrade you should follow some of the steps or best practices so you would make your workflow easier. Following are Preparation steps for AL Upgrade

Prefix or Suffix : There need to be Company Initial in each objects, fields and method. To know why need to watch the video.
Application area tagging: Why you should have ApplicationArea = All or Basic or Suite on your objects
Usage Category : UsageCategory = Report&Analysis etc. need to be in object so object be searchable in Business Central
Permission sets & Permission, tenant permission
Profiles
User Personalization

Summary:

Before starting upgrade of your system there are many important thing needs to be consider. In this blog post I have given some from the panel discussion. To know it in detail please watch the video.

Reference:

YouTube - Preparing your BC upgrade – A panel discussion

PS. Picture are taken from presentaion.

You do not have following permission on TableDate

2020-03-25T16:15:00.002-04:00

You do not have the following permissions on TableData {TableName}: {Read/Insert/Modify/Delete}

Solution:

Use developer License and run the object.
Use developer License and in object Properties window change permission properties to rimd

How to run Report from Page Trigger in NAV - [with code Example]

2020-03-24T15:09:00.003-04:00

Report in NAV could be run by different ways.

If code is written then it exceed propertes.

By specifying report on properties

Go to Page where you want to place report and go to Action Page (CTRL + V + O)

In report section write the Name of report and select properties
Fill the boxes as shown you must fill the RunObject Properties with the name of report to execute with starting Report. ie. Report Report Name

By Code
Following are some way of writing code:

To write code go to page action and select action and write code in action trigger

In this example CustReportVariable is Report Local variable which needs to be execute.


Rec.SETRECFILTER;
CLEAR(CustReportVariable);
CustReportVariable.SETTABLEVIEW(Rec);
CustReportVariable.RUNMODAL;

Reference: SETRECFILTER in NAV [Microsoft Official Document]

In this example CustRecord is Customer record Local Variable and CustReportVariable is Report Local variable which needs to be execute.


CustRecord.RESET;
CustRecord := Rec;
CustRecord.SETRANGE("No.","No.");
CustRecord.SETRANGE(...);
CustRecord.SETRECFILTER;

CLEAR(CustReportVariable);
CustReportVariable.SETTABLEVIEW(CustRecord);
CustReportVariable.RUNMODAL;

In this example CustRecord is Customer record Local Variable.


CustRecord.RESET;
CustRecord.SETRANGE("No.","No.");
CustRecord.SETRANGE(...);
REPORT.RUNMODAL(REPORT::"Report Name",TRUE,FALSE,CustRecord);

how to update git in windows

2020-03-11T10:21:00.000-04:00

To update git from terminal or git bash use following command:


            git update-git-for-windows

Earlier we can update git using git-update command but this command is been deprecated.

Git Update in windows

Not Equal to Filter in NAV

2020-01-30T15:53:00.001-05:00

Filter in NAV code

Not equal filter in nav

"Order Line".SETFILTER("No.",'<>%1',ServiceNo);

Event and Subscription in NAV - Business Central

2019-06-06T08:46:00.000-04:00

I love the function.
Events and Subscription are modern functions.

Before talking about event and subscription let me start from my inauguration day when I was learning C or C++, I am devoted to function. I adore seeing how main() function is the entry point of any C program. I tried to learn function as much as I could, I explored it more. I reached from functions with argument and without argument, functions with return value and without return value, functions which do pass by value and which do pass by reference. That was my fantastic learning experience. When I start working on Navision ( Dynamics NAV 5.00) I always tried to play with my favorite 'function'. Also, in later versions, whenever I have to do any modification I used to use function. I love how it make code clear, separate from rest and tie code together with one purpose, remove repeating of code, make it readable and manageable.

In NAV 2016, Microsoft introduces events and subscription. In this modern concept of programming, by using modern function they tried to decouple base code from customized code. They wrote many new events in existing code which was the huge blow in the NAV. As I remember most of us don't give consideration to this new feature and we continued working in our old style - happy style. Some of us tried to use it but we used to forget we were using event and start modification in the code itself or by creating the function and calling from the code. Microsoft, at that time tried to show us the new direction where NAV was heading. Now, we are in business central (BC) and we can see how heavily events and subscription is being used in the extensions.

To know about event and subscription from Microsoft visit:

Events and Subscription - Microsoft

Event and Subscription - Saurav Blog

Events and Subscription - Peik on Dynamics 365 User Group

I cannot explain how important are events and subscription but I can say they are essential. So, if you have not tried it yet, please take some time to explore it and use it in your code.

Designing table in NAV - Most important tips

2019-06-02T22:16:00.001-04:00

Recently I wrote a change order for a client. Their requirement was to track revision with different vendors and revision include multiple fields. Also, the client provided a list of fields they want to track. The guy who gave requirements was a technical guy and he gave me list including primary key. When writing the change order, I decided to make the table as supplement table and put the list of all field as he gave me in that list. Later, one senior developer also checked the change order before submitting it to the client and he suggested me golden advice.

Always take one thing in mind, don't trust completely on your client. The technical guy who gives you requirement is very good at SQL, he has good experience of the designing table but always considers he is not a NAV guy and he doesn't know how tables are structured in NAV, he doesn't know how the things flow and how the things should be designed for NAV. You are NAV guy and you are NAV consultant. So trust yourself and design tables as it should be. The primary key of the supplement table should always be "code".

Then I remembered what I studied about best practices of table development during my inception days of NAV. Following was the thing I used to practice during those old days:

Master Table:
For Master table like Customer, Vendor primary key is always "No" with Data Type "CODE" and field length 20.
Supplement Table:
For Supplement table like Currency, Payment Terms, Revision primary key is always "Code" with Data Type "CODE" and field length is 10 or sometimes 20.

Another thing that always follows during linking of supplement table with other table was a naming convention. Always named field as "Table name Code". For example, "Agreement Code", "Currency Code" etc.

This is a revision of what I used to do and should do when designing a table. This makes code standard and we also follow the design pattern with best practice. Most importantly it make code understandable along with the purpose of the table for an experienced developer.

Join tables in Excel | Merge table in Excel

2019-05-28T09:06:00.000-04:00

When we work on multiple tables in excel, sometimes we need to join two tables based on some values common to both tables. There are tables with Header and Line relationships like sales header and sales line, invoice header and invoice line, we need or want to join these table so we can view data from line and header in the same table. To get holistic data from one table we merge two tables and start analyzing data. In this post, we are going to discuss joining of two tables in excel by using Microsoft Query.

Let me start by first why not relationship?

A relationship is a way to link two tables using some value common on both tables like joining two tables using a primary key. I personally prefer this approach when working with multiple tables. But sometimes you want to be sure you are whether you are using correct data or not, and unless you see all your data in one table you can't rely on yourself. In that case, you should do the table merge.

How to merge two tables?

STEP 1 : Go to Data Tab
STEP 2 : Select Get Data
STEP 3 : Select From Other Sources
STEP 4 : Select From Microsoft Query

STEP 4 : In Choose Data Source window select Excel Files

STEP 5 : Select OK to confirm the sources

STEP 7 : In select workbook window first select the directory where your excel file is located
STEP 8 : Select the excel file
STEP 9 : Click OK to continue

STEP 10 : Sometime no data is shown in available sheet window, to fix this select options
STEP 11 : Select System Tables
STEP 12 : Click OK to continue

STEP 13 : Select the sheet you want to merge then press forward arrow to move all fields from the sheet to column in your query window.
STEP 14 : Click NEXT to continue.

STEP 15 : Select OK to continue during warning.

STEP 16 : Select field you want to join in one table then drag it to the field in other table. This will create a line.

STEP 17 : We don't need to save it. Best approach is to select Return Data to Microsoft Excel.

STEP 18 : Windows will change to excel, here select table as first option and in second option you can put your data on new worksheet or Existing worksheet(select if you start working in new worksheet in beginning).

This is final view of joined table.

This is the way I use for joining tables in excel. If you have some other way of doing it easily or more efficiently please let us know on comment below.

Journey To: Business Central

2019-05-14T18:49:00.001-04:00

How much I am struggling to install BC on my machine to start development is one good story and could be follow up guide for a newbie. I am still doing my development work on Microsoft Dynamics NAV Development Environment using C-SIDE or C\AL Programming language. On recent April release from Microsoft, they announced that they are going to replace C\AL by AL programming language in October Release. So I decided to take this new path towards BC.

How I started?

I searched for "development in Business Central" or "Starting AL programming". After going through some link I found some useful link to start the development.

I found that we have to use a sandbox environment for development. I choose my Dynamics 365 Business Central Development Sandbox Environment. I planned to use the container-based image hosted locally. There are other options available such as:

run a sandbox environment deployed as a Dynamics 365 Business Central service
run a container-based image hosted on Azure Virtual Machine
run a container-based image hosted locally

Follow this link to know detail about available sandbox options

PS. sandbox meaning: a virtual space in which new or untested software can be run securely. To know more what is sandbox follow this link(what is sandbox?)

Next Step on journey to Business Central

After I decided to install business central locally, I must install Docker to run the image. When I follow the link I got a message in first as "If not already done, install Docker".
Setup Local-hosted container sandbox for Business Central

So I have to install Docker. At that point, I was using Windows 10 Home edition and I came to know that I could not install docker, I need to install docker toolbox. I found a link to install docker toolbox as well as to enable virtualization in windows 10 home. So first enable virtualization and then install docker toolbox.

At this point, everything seems working with lots of research and crawling on my knees. 😅😎😁

Then I opened business central (https://businesscentral.dynamics.com/ ) and searched for sandbox and downloaded it locally.

Documented in these images:

1. search sandbox on BC and select container sandbox environment

2. select host locally, then it promote for download accept and it will be downloaded in name as CreateBCSandbox.ps1

3. BCSandbox script opened on power-shell, we should have to change values here and execute it

When I followed upto step 9 from link (Setup sandbox environment locally) , I got an error. I realized it was may be because of Windows 10 home. So I decided to use windows 10 Pro.

I just change the key to upgrade my windows 10 Home to Windows 10 Pro. To upgrade you need to get windows 10 pro key and to be in safe side, take backup. Follow the steps explain in the video:
Upgrade windows 10 home to windows 10 Pro

Upgrade hardly took 10 minutes, after the upgrade was completed I uninstalled Docker toolbox immediately and I removed Oracle virtual machine which was installed during installation of Docker toolbox.

Then I enable Hyper-v manager.

Press windows key and search "Turn Windows features on or off"
It open windows feature page, check all hyper-v on the page

Install Docker for Windows:

Then I downloaded docker from official docker site. For me, the best option is to register in docker hub (https://hub.docker.com/) and from docker hub I could download the Docker. Alternatively, you can download using the following link:

After the download is completed, click on the Docker Desktop Installer.exe file and install it. Follow the wizard to complete the installation. Please make sure to give all the permission. You can make it "windows container" now or later. For development you need "windows container".

Go to search and click the Docker Desktop, then the docker will start. It will take some time to start docker completely. It will give you notification once it is done. At this moment, you have an option to go to Hyper-V manager to see the list of Virtual Machine and in the list you will see MobyLinuxVM.

View of Docker on Hyper-V manager

After docker was started, I logged in Docker hub. Then went to command prompt and typed the command docker and again I ran into an error saying
"could not read CA certificate "C:\\Users\\...\\.docker\\machine\\machines\\default\\ca.pem": open C:\Users\...\.docker\machine\machines\default\ca.pem: The system cannot find the path specified."

This error was surely caused because of Docker toolbox which we installed for windows 10 home. Then I followed the link https://stackoverflow.com/questions/49679818/docker-could-not-read-ca-certificate and resolved the issue.

At this point, I just installed docker in windows machine. Next will be the installation of Business Central (BC) on it.

--------- update July 2020

I recently found a video by Saurav really helpful for installing docker.

Date Format in RDLC report | RDLC report in NAV

2019-02-07T15:36:00.000-05:00

How to convert a date into a specific date format in RDLC report?

In RDLC report when we set a date field in a report it displays either full DateTime or it shows in a different style than we desire. We could solve this kind of situation in different ways. In my case date is displayed as 30 January 2019 but my client wants date look numeric like 01/30/2019. I normally take the following approach to format the date value:

Approach 1:

1. Select the field whose format we want to change then Right-click on the field in the RDLC report
2. Choose "Text box properties" from the list

3. Then choose the "Number" Option from Text Box Properties windows
4. Then choose one of the multiple "Date" options. There is also an option for the regional setting.

5. If you don't find your desired option in Date then specify a Custom formatting option by going to "Custom" Option. We can use a complex form as "dd.MM.yyyy HH:mm:ss". Sample step is shown in the image as below. (Date Formatting in RDLC report)

Approach 2:

If there is an expression or complex expression Approach 1 doesn't work as expected. In this condition, we need to write code. Some probable codes are as follows (Suppose "IssuingDate" is our field)
=CDate(Fields!IssuingDate.Value).ToString("MM/dd/yyyy")
=Format(Fields!IssuingDate.Value,"MM/dd/yyyy")

To write the code:
1. Select the field whose format we want to change then Right-click on the field in the RDLC report
2. Choose "Expression" from the list

3. Write the code in Expression window.
4. Close the window by clicking "OK".

Complex Case:

In the code given below, the Format function doesn't work properly so I used CDate function.

       

Earlier Code:

=Cstr(Choose(2,Split(Cstr(ReportItems!HeaderInfo.Value), Chr(177))))

Updated Code:

=CDate(Choose(2,Split(Cstr(ReportItems!HeaderInfo.Value), Chr(177)))).ToString("MM/dd/yyyy")

How To solve NAV not showing code in it's Development Environment

2019-02-01T17:40:00.001-05:00

I was coding on NAV development environment last night and experienced a unique situation where NAV development environment was not showing the code. I just completed a development task and was testing it without creating fob of the object. When I tried to open codeunit by clicking design codeunit button on the development environment, codeunit object popup so differently. It shrank to the left side and was not showing the code, It was wired. I was worried then get out of that codeunit and try to open other objects and another object but all seems similar. Finally, I tried the following way to resolve the issue and make my NAV code visible and make my NAV development environment workable. Ready? Let's dive in.

Steps to solve code not showing issue in NAV:

STEP 1: Close NAV Development Environment.

STEP 2: Open NAV Development Environment.

These solve the issue of code not showing in NAV Development Environment. After solving this I remembered I promised to write a blog each week. So now I am back to my promise of writiting at least a blog each week on these interesting topics of Dynamics NAV, Business Central, and PowerBI.

How to convert .fob file into .txt file in NAV

2018-11-01T22:30:00.001-04:00

How to convert .fob file into .txt file in Microsoft Dynamics NAV?

How to change '.fob' backup file to '.txt' file or how can I view contents of a .fob file is a common question for new developer of Microsoft Dynamics NAV.

When we export objects from NAV Development Environment, we got two options either export objects as .fob file or export object as .txt object.By the way, fob stands for Financials OBject. Both .fob file format and .txt file format have their own importance and usages.

.fob Format:

Generally, when objects are ready to promote to production we export objects as .fob file and import them, even Microsoft releases its cumulative update as fob file. When we import .fob file in the Development Environment, it promotes an option to compile the imported object.

.txt Format:

.txt file is the text version of the object with some formatting, we use the .txt file to compare the same object from the different system or different version of the file usually using tools like winmerge or beyond compare. With this comparison, we can determine the difference and make appropriate changes as required. We need NAV developer License to import .txt file in Object Designer, but we don't get the option of compiling .txt file. So it overwrites old object and object must be compiled to make it workable.

We cannot convert .fob file into .txt file.

It is not possible to convert .fob file into .txt file but there are two alternative solution for converting .fob file into .txt file.

Export same object as the .txt file from the original source.
In a separate blank database, import those .fob objects and then compile those there and export as text.

Find Duplicate Row in Excel - Part 2

2018-10-26T18:11:00.002-04:00

How to find duplicate value in excel?

In first part of this tutorial we see how to find duplicate record in one column. Sometime we need to find duplicate record in combination of two rows. One of the most use case is for table with composite key or say multiple primary key. Excel is very helpful to find duplicate record in multiple rows but we need to do some extra steps to make it easy. Lets see how we can we deal with this type of condition.

CASE 2: Find duplicate in a group of columns (Find duplicate country and capital)

If we follow the steps as CASE 1 we will get misleading result, so we need to follow different approach.
With Approach of CASE 1:

1. select both columns
2. Go to Conditional Formatting
3. Go to Highlight Cells Rules
4. Click Duplicate Values...
5. Click OK

Result we get seems correct but if we see carefully we will find error. You will see result for Brazil is correct because in both row country is Brazil and Capital is Brasilia but for India it doesn't hold true. Because in one row India has New Delhi as capital where in other row India has Delhi as capital, both rows are different. But our approach shows incorrect result.

To solve this problem, we need to take different approach as follows:

Step 1: Make a new column C by Merging column A and B by using & as =A2&B2. Now we will use this column to test duplicate row using CASE 1 approach.

Step 2: Select Column C.
Step 3: Select Conditional Formatting from Home tab in ribbon.
Step 4: Select Highlight Cells Rules.
Step 5: Select Duplicate Values...

Step 6: Click OK.

To view duplicate record use filter by color by doing following steps:

Step 1: select duplicate record and right click on that cell.
Step 2: select Filter from list.
Step 3: Select Filter by Selected Cell's Color

We will only get duplicate record which are duplicate in both column A and Column B.

Note: To clear formatting rules from entire sheet:

Step 1: Select conditional formatting in Home ribbon.
Step 2: Select Clear Rules.

Step 3: Clear rules from entire Sheet

Convert Column into Row | Tips & Tricks

2018-10-24T01:18:00.000-04:00

Converting Column into Row or Cell

Converting column into row is useful many times while working with Database, CSV file or in Microsoft Dynamics NAV. We have columns of data in Excel sheet and we just want to convert a specific column into cell.

In case of Microsoft Dynamics NAV we copy rows of object from certain filtering condition and we paste those rows into excel. After that we need to make filter on specified objects, for this purpose we need only objects number separated with bar (|).

While working for CSV file some time we need to take a row from a column and convert them into comma-separated list in a cell so user can copy that cell and use it.

To solve this problem, I found this three solutions very helpful. For me using Microsoft Word was the best option but now I love to use formula in Office 365 Excel.
Listed are different solutions

Solve Using Word
Solve Using Excel

Suppose column to convert is Column A in excel.

Solve Using Word

Copy the column in Excel (Copy Column A)
Open Word and "Paste special" as Text Only (T)
Select the data in Word (the one that you need to convert to text separated with ,), press Ctrl-H (Find & replace)
In "Find what" box type ^p and in "Replace with" box type |
Select "Replace all"

Solve Using Excel

MANUAL
Our data is in Column A, now put the following formula in column B:
B1: =A1
B2: =B1&"|"&A2
After this paste column B2 down the whole column. The last cell in column B should now be a comma separated list of column A.
(Note: This method is best fro limited number of rows, be careful)

USING FORMULA
Use TEXTJOIN formula as
=TEXTJOIN("|",TRUE,A:A)
This formula only works on Office 365 Excel.

Converting column of data from excel into single cell is quite handy.

Hope you like this tips & trick. If you have different way of doing this please share in comment. Thanks.

Reference:

Convert column to comma separated list

Related Article:

Find duplicate Rows in Excel

How to find duplicate rows in excel

2018-10-14T23:41:00.000-04:00

Find Duplicate rows in Excel

We use excel for different data processing and one of the major requirement is finding repeating data. We can easily remove duplicate data using remove duplicate in excel but finding unique or duplicate value is quite tricky. This list contains name of countries and their capital. In Column A India and Brazil are duplicate records. If we consider both column A and Column B only Brazil has duplicate record.

CASE 1: Find duplicate in a column (Find duplicate country)

Step 1: Click on the Column Header to highlight Column A.

Step 2: Select the Conditional Formatting button on the Home tab.

Step 3: Select Highlight Cells Rules.

Step 4: Click on Duplicate Values...

Step 5: click OK (Optionally you can change color or pattern on values with box, after completing values press OK)

All duplicate values in that column will be highlighted with specific color. To view only duplicates values, follow the steps:

Step 1: select a duplicate value cell (colored cell) and right click on that cell.
Step 2: Click on Filter
Step 3: select Filter by Selected Cell's Color.

Only cell with duplicate value will be listed. To remove filters, go to Data tab in ribbon and do clear filter.

In next tutorial we will discuss case 2 where we will find duplicate value from multiple rows.

How to color alternative row in report

2018-10-10T00:51:00.001-04:00

In report we utilize distinctive shading to alternative rows so each line could be comprehensible. We have to color elective line to recognize data clearly as well as to perceive information unmistakably. In RDLC report we can shade alternative rows with different color. To color alternative row in RDLC report follow the step below:

Open report layout in visual studio
Select row from table for which we want to show alternative color
Go to properties windows and go to "BackgroundColor" properties.
Then select drop-down assist key and select expression.
In expression window write code as follows:
```
       
            = iif(RowNumber(Nothing) mod 2, "AliceBlue", "White")
       
```
Replace "AliceBlue" and "White" with color of your choice. Also we can select color from color picker.
After completion of code click OK and save the report.

color alternative row in rdlc report

code to color alternative row in report

RDLC Report Design Basic Setup in Visual Studio

2018-03-15T16:57:00.003-04:00

Before Starting to design RDLC report in visual studio, I prefer to do some basic setup or say prerequisite setup in visual studio. In this post, I will share my basic RDLC report setup and some report designing tips.

At the end of this:
You will able to do basic setup in visual studio report designer that is required for RDLC report development

Before we start:
We will design and upgrade reports by using the Microsoft Dynamics NAV Development Environment. The software requirements for designing and upgrading reports are as follows:

Microsoft Report Viewer (if not already installed on target computer Microsoft Dynamics NAV setup will install during installation)
Microsoft Visual Studio or Microsoft SQL Server Report Builder.

Dataset in the report

Basic Setup To Create an RDLC report Layout

Open report using Microsoft Dynamics NAV Development Environment and open layout

Tools > Object Designer
Report > Design
View > layout

Visual studio will opens and now open basic toolbar

View > Toolbox (Ctrl + Alt + X)
View > Properties Windows (F4)
View > Report Data (Ctrl + Alt + D)

Report Data Toolbox

Now let's setup toolbar

Toolbar setup in report design setup in visual studio

View > Toolbars > Layout + Report + Report Borders + Report Formatting + Standard

I usually rearrange these toolbar as shown in picture below.

Toolbar arrangement for easy report design in visual studio
Report properties setup

Right click on report and select properties
On properties window select page units -> centimeter/inches
Select Orientation -> Portrait/Landscape
Paper size/Type -> A4/Letter/other as you required
At the beginning I always give margin of 0.4 inches on each side which I will adjust in future if required
After this all setup done click OK

Report Properties Page setup

Font and text-box setup : Either in font toolbox in the top or in properties windows set Font as following

FontFamily : Segoe UI
FontSize: 8pt
FontStyle: Normal
FontWeight: Default
TextDecoration: Default

Font Setting in RDLC report design in Visual studio
Also set CanGrow: False so even if text is not fit in the text box it will not grow and change design
Padding: 0pt, 0pt, 0pt, 0pt
TextAlign: Right
Text-box Size:

Width: depends
Height: 0.16654in ~ 0.2in

Text-box size and padding setting in report design in visual studio

TIPS for Report Design:

Data remine less report perform best so when designing data-set always try to get exact data you need, do not filter data in report
Make report body width less than actual report width.
we setup report width to 8.5inch and set margin to 0.4inch in left and right both side then report body width should be less than 8.5 - 0.4 - 0.4 = 7.7 inch
Try to make proper alignment of text box so report looks beautiful.
Zoom the windows to 400% and see if there is unnoticed horizontal or vertical gap between text-boxes and also you can use background color to check if the there is some thing missing in report design.

Reference:

<https://msdn.microsoft.com/en-us/library/dd301054(v=nav.71).aspx>

At the end:

I will improve this post hence it could be resource for future. If you have any ideas or tips or comment please feel free to drop here.

DynamicsCentral - A Business Central D365 Blog for Developer and Consultant

How to Fix gitignore not working in .gitignore file?

How to Fix gitignore not working in .gitignore file?

Fix 1: Add Files/Folder to Repository again:

Fix 2: Fix encoding of gitignore file

Fix 3: Fix naming and file format of gitignore file

How to convert Date to DateTime in Business Central [AL- VsCode]?

I have to assign a "Date" Value to a "DateTime" variable. How do I convert the "Date" to a "DateTime"?

Using CREATDATETIME Procedure:

Using EVALUATE procedure:

How to discard all Local Changes/Commit and Pull Latest Data from Repo - GIT

How to Publish Business Central Apps in AppSource? [Step by Step Guide]

VS Code for AL Development [Tips, Setup and Extensions]

'al/gotodefinition' failed with error: 'Index was outside the bounds of the array.' - Business Central

Solution/Workaround:

Open using: Open it from the package folder.

Reference:

RDLC Report Designer Tutorial and Basic Setup [using RDLC Report Builder] for Business Central

Report properties setup:

Report Body Setup:

Font setup:

Text Box Setting:

Table Setting:

Advance Table setup :

Adding Built-in fields in the report:

Execution Date and Time:

Page Number:

User ID:

Conclusion:

Resources:

PageNo is Being deprecated in versions: 1.0 or greater - [Business Central Report Tips]

So what is the solution for the issue of PageNo?

Conclusion:

Reference:

Preparing for Business Central Upgrade

Preparing your BC upgrade – A panel discussion

Following are some of the takeaway from the webinar:

Summary:

You do not have following permission on TableDate

How to run Report from Page Trigger in NAV - [with code Example]

how to update git in windows

Not Equal to Filter in NAV

Event and Subscription in NAV - Business Central

Designing table in NAV - Most important tips

Join tables in Excel | Merge table in Excel

Let me start by first why not relationship?

How to merge two tables?

Journey To: Business Central

How I started?

Next Step on journey to Business Central

Documented in these images:

Then I enable Hyper-v manager.

Install Docker for Windows:

Date Format in RDLC report | RDLC report in NAV

How to convert a date into a specific date format in RDLC report?

Approach 1:

Approach 2:

Complex Case:

How To solve NAV not showing code in it's Development Environment

Steps to solve code not showing issue in NAV:

STEP 1: Close NAV Development Environment.

STEP 2: Open NAV Development Environment.

How to convert .fob file into .txt file in NAV

How to convert .fob file into .txt file in Microsoft Dynamics NAV?

.fob Format:

.txt Format:

Find Duplicate Row in Excel - Part 2

How to find duplicate value in excel?

CASE 2: Find duplicate in a group of columns (Find duplicate country and capital)

Convert Column into Row | Tips & Tricks

Converting Column into Row or Cell

Solve Using Word

Solve Using Excel

MANUALOur data is in Column A, now put the following formula in column B:B1: =A1B2: =B1&"|"&A2After this paste column B2 down the whole column. The last cell in column B should now be a comma separated list of column A.(Note: This method is best fro limited number of rows, be careful)

USING FORMULA

How to find duplicate rows in excel

Find Duplicate rows in Excel

CASE 1: Find duplicate in a column (Find duplicate country)

How to color alternative row in report

RDLC Report Design Basic Setup in Visual Studio

MANUAL
Our data is in Column A, now put the following formula in column B:
B1: =A1
B2: =B1&"|"&A2
After this paste column B2 down the whole column. The last cell in column B should now be a comma separated list of column A.
(Note: This method is best fro limited number of rows, be careful)