Coding, Day to Day

Business Central – Dude, Where’s the Warranty Date?

There is always something new to learn everyday in Business Central. I was doing some setup to test item tracking involving serial numbers and was fiddling with the warranty date required setting (See screenshot below). Somehow, the warranty column did not show up in the item tracking line page for both Purchase and Sales lines.

Item Tracking Card

After consulting one of the senior functional consultants, I learnt that the warranty column had to be manually added in via the personalization feature. The end result is as shown below.

Item Tracking Lines

Wonder why they couldn’t tie the visibility of the warranty date column to the setup. Am I missing anything?

Coding, Day to Day

Snakes and Camels?

I was intrigued by an email from a vendor that mentioned snakes and camel case for the field names from an API call. “What snakes and camels? Since when do animals come partying in our project?”, I wondered. These terms are quite rarely heard these days. Typically people deal with title case, uppercase, lowercase etc., so I decided to do some research to find out what is it about.

Camel case and snake case are different forms of variable naming convention style. These two are the more popular ones that programmers use. Camel case and snake case stand at opposite ends of the naming convention spectrum and we shall see how and what they are like in this article. You might even be using them without knowing what they are called, just like me.

Snake case uses underscore between words to create separation. As variable names cannot contain spaces, this reduces readability when multiple words are combined to describe its purpose. Snake case tries to minimize this shortcoming.

a_snake_case_name_looks_like_this

It is immediately apparent why it is called a snake case. It do looks like a snake wiggling amongst the codes. One study has found that readers can recognize snake case variable names more quickly than camel case.

Camel case uses capitalization of the first character of each word to denote the start of a new word. It is also known as Pascal case or upper camel case. When it is not, it is often referred to as lower camel case.

ACamelCaseNameLooksLikeThis

It is obvious why it is called a camel case. It does looks like it has a lot of humps doesn’t it. Do note that it is different from title case where it retains space as word separators. Camel case has been criticized as not been as readable compared to other methods due to the removal if spaces (and/or alternative replacements) and uppercasing of every word.

From the above examples, it seems pretty obvious that snake case provides better readability, but that is just my opinion. Some people find camel case easier to read. To me readability is especially important in coding. If your team member or others are unable to understand it, it is useless. The coding language and platform that you are using do affect your choice of naming convention. It is better to follow the standards (if there are any).

Coding, Day to Day

Business Central – CLEAR vs INIT function

What is the difference between CLEAR and INIT functions? Clear(Record) vs Record.Init()

CLEAR function

When used on a variable, i.e. Clear(SomeVariable), it simply clears the value that is stored to its default state. For example if it is a number variable, it will be set to 0 (zero), a date variable to undefined date (0D) and so on. Clear can be used on a Guid data type, and in this case sets the GUID to all zeroes.

When used on a record, i.e. Clear(Rec), or an array, all elements are cleared. It resets all the filters, resets the primary key and all fields will be initialized based on the InitValue property. If ChangeCompany is used, it will reset back to the original company.

INIT function

Used for initializing a new record in a table for insertion. It will assign default values to all the fields in the record, including the SystemId field.

Both of these functions might seem to be doing the same thing, setting fields to their default state, but they are used under different circumstances for different purposes.

Coding, Day to Day

Business Central – How to change primary key of a record

Typically when we want to update the contents of a record in BC, we would just use the modify function. For most cases, that would suffice, but what happens if the field you want to update is part of a composite primary key? Or when updating a record in which the content to be changed includes part of a primary key? In these scenarios, using the modify function will return an error. The solution? Read on!

Sample Record Structure

Here is a sample record structure for reference. In this case, we have a composite key consisting of “Entry No.” and “Line No.” as well as other data fields.

table 55047 "Sample Line Data"
{
    fields
    {
        field(1; "Entry No."; Integer)
        {
            Caption = 'Entry No.';
        }

        field(2; "Line No."; Integer)
        {
            Caption = 'Line No.';
        }
       
        field(3; "Item Code"; Code[10])
        {
            Caption = 'Item Code';
        }

        field(4; "Item Description"; Text[50])
        {
            Caption = 'Item Description';
        }
    }

    keys
    {
        key(PK; "Entry No.", "Line No.")
        {
            Clustered = true;
        }
    }
}

So what should we do if we need to change the following data: “Line No.” and “Item Description”?

We use the Rename function to update the “Line No.” which is part of the primary key, then re-retrieve the record and update the “Item Description” using the modify function.

The syntax for Rename function as show below.

[Ok := ]  Record.Rename(Value1: Any [, Value2: Any,…])

Code example as show below.

// #1 change part of primary key, line no from 1000 to 2000
if MyRecord.Get(1,1000) then
    MyRecord.Rename(MyRecord."Entry No.", 2000); 

// #2 get record again with new key
if MyRecord.Get(1, 2000) then
    begin 
        // #3 update item description (non key field)
        MyRecord."Item Description" := 'Changed Description';
        MyRecord.Modify();
    end;

Remember, do not change the sequence of the primary key!

Coding, Day to Day

Business Central – Code vs Text data type

When I first started doing NAV/BC development, I would get a bit confused over the purpose of Code and Text data type from time to time. After all, they are quite similar in function and role. Both of them store a string of text. It is also quite novel to me after working with Java, C++, VB.NET to see this type of data structure.

In the beginning, I assumed that Code data type’s role is for primary key and text is for everything else. While this is a fair assumption, I realized this is not the case as I gained experience over the years. There are use cases where you might want to use a code over text and in others text over code data types.

I have seen people using Code data type for storing record descriptions when a Text data type would be a better option. I have also made similar mistake when I started out, using a Code data type to store password when I should be using a Text data type.

Text data type denotes a text string, they are general purposes and comes with a lot of text manipulation functions. The Text data type is a value type, such that every time you use a method on it, you are creating a new text string object in memory. This means that there will be an allocation of space for each instance you are using it, which equates to overheads. It should not be a deal breaker unless you are dealing with and/or need to manipulate massive amount of textual data.

Code data type denotes a special type of string. The values are always converted and stored in uppercase and all leading and trailing spaces are removed. A Code variable cannot be null. That is what makes it so suitable for use as a primary/unique/referential key data type.

Coding, Day to Day

NAV2016 – How to Import Excel Data into NAV

Note: This is only for Microsoft Dynamics NAV, NOT Business Central. While the principle behind it is the same, Business Central uses different objects to achieve the intended result. What we are focused on in this post is how to import data from an excel spreadsheet to NAV (example, transfer data from excel to cash receipt journal records). This will be a brief guide on the import aspect.

Variables Required

  • ExcelBuffer – Record “ExcelBuffer” – need to declare as a global variable
  • FileManagement – Codeunit “File Management”
  • ServerFileName – Text
  • SheetName – Text
  • TotalRows – Integer
  • RowNo – Integer
  • And any other variables to store the excel cell data

Supporting Function

// Get Value At Cell Function 
LOCAL GetValueAtCell(RowNo : Integer;ColNo : Integer) : Text
IF ExcelBuffer.GET(RowNo,ColNo) THEN
  EXIT(ExcelBuffer."Cell Value as Text")
ELSE
  EXIT('');

The above function is required to get the value from a specific cell in the excel spreadsheet based on the Row and Column of the cell and return the result as a string. You will have to do the necessary conversion to other data type if you want a numeric value and so on.

Import Function

First we will need to create a function (i.e. in a codeunit) that can be called from a page to trigger this import function. In this function we will trigger the dialog for the user to select the excel spreadsheet to import and also to select which sheet to import (if there are multiple excel sheets). The code snippet for this is as shown below

// Sample Import Call Function that takes in some parameters
ImportItemJournalExcel(JournalBatchName : Code[10];JournalTemplateName : Code[10])

// Get Import File
ServerFileName := FileManagement.UploadFile('Please select import File','File');
IF ServerFileName ='' THEN
  EXIT;

// Get Sheet
SheetName := ExcelBuffer.SelectSheetsName(ServerFileName);
IF SheetName = '' THEN
  EXIT;

Additional Information: It might be possible to import a zip file of excel spreadsheets for processing and/or also to process multiple sheets in an excel file. But we will not be covering this here.

Next step is to open the excel document and get the total number of rows of data.

ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFileName,SheetName);
ExcelBuffer.ReadSheet;
ExcelBuffer.RESET;
ExcelBuffer.SETCURRENTKEY("Row No.");
IF ExcelBuffer.FINDLAST THEN
  TotalRows := ExcelBuffer."Row No.";

Once we know the total number of rows, we need to identify which row to start taking data from and start looping the spreadsheet to extract the data. And in this loop, you can add the codes to insert the data to a record (example Gen. Journal Lines)

FOR RowNo:=3 TO TotalRows DO BEGIN    // Start from Row 3

  // Get Cell Data
  SampleLocationData := GetValueAtCell(RowNo,2); // Column Number 2 : Location Code
  SamplePONo := GetValueAtCell(RowNo,8); // Column Number 8 : PO Number
  SampleProdCode := GetValueAtCell(RowNo,13); // Column Number 13 : Product Code
  SampleProdDescr := GetValueAtCell(RowNo,16); // Column Number 16 : Product Description  

  //  Do any logic here (i.e. insertion)

  CustomRecord.INIT;
  CustomRecord.LocationCode := SampleLocationData;
  CustomRecord.PONo := samplePONo;
  // continue adding data as needed
  CustomRecord.INSERT();


END;

Finally to end it off.

ExcelBuffer.CloseBook;

MESSAGE('Excel Import Completed');

That’s all that there is to it.

Coding, Day to Day

Business Central – How to use PromotedActionCategories

Our intern was asking me for help on using PromotedActionCategories. She tried and she was not about to make it work. She said that no matter what captions she had placed, it just showed up as “Category 9” instead of “Request Approval”. This was how she had done it.

page 50210 "Vendor Contract"
{
    Caption = 'Vendor Contract';
    PageType = Card;
    PromotedActionCategories = 'Release, Request Approval';

...

                action(CancelApprovalRequest)
                {
                    ApplicationArea = Suite;
                    Caption = 'Cancel Approval Re&quest';
                    Image = CancelApprovalRequest;
                    Promoted = true;
                    PromotedCategory = Category9;
                    ToolTip = 'Cancel the approval request.';

...

Then this was how it was resolved.

page 50210 "Vendor Contract"
{
    Caption = 'Vendor Contract';
    PageType = Card;
    PromotedActionCategories = 'New,Process,Report,Release,Request Approval';

...
                action(CancelApprovalRequest)
                {
                    ApplicationArea = Suite;
                    Caption = 'Cancel Approval Re&quest';
                    Image = CancelApprovalRequest;
                    Promoted = true;
                    PromotedCategory = Category5;
                    ToolTip = 'Cancel the approval request.';
...

As you can see from the above example, “Category4” caption has been renamed as “Release” and “Category5” caption has been renamed as “Request Approval”.

For a clearer idea on how this was resolved, you may refer to the microsoft documentation here.

Coding, Day to Day

Business Central – Action buttons not showing up in custom page

Intern was asking around for help on an issue that she was not able to solve, and I gave her some help, since I was the only one who bothered to reply. The action buttons on her custom page were not showing up, even though she had done whatever she was supposed to. Her original codes as shown below with some portions removed for brevity.

page 50210 "Vendor Contract"
{
    Caption = 'Vendor Contract';
    PageType = Document;
    PromotedActionCategories = 'Release';
    RefreshOnActivate = true;
    SourceTable = "Vendor Contract";

    layout
    {
        area(Content)
        {
            group(GroupName)
            {
                field("Contract No."; Rec."Contract No.")
                {
                    ApplicationArea = Suite;
                }
                field("Contract Name"; Rec."Contract Name")
                {
                    ApplicationArea = Suite;
                }
                field(Status; Rec.Status)
                {
                    ApplicationArea = Suite;
                }
            }
        }
    }

    actions
    {
        area(Processing)
        {
            group(Action1)
            {

                Caption = 'Release';
                Image = ReleaseDoc;
                action(Release)
                {
                    ApplicationArea = Suite;
                    Caption = 'Re&lease';
                    Image = ReleaseDoc;
                    Promoted = true;
                }
                action(Reopen)
                {
                    ApplicationArea = Suite;
                    Caption = 'Re&open';
                    Enabled = Rec.Status <> Rec.Status::Open;
                    Image = ReOpen;
                    Promoted = true;
                }
            }
        }
    }

}
Ok, no action buttons on this one

The fix is relatively simple and quite unexpected. Somehow Business Central/NAV detects that if there are no actions tied to the button, it assumes that it is not necessary to display on the page. So, you just have to add some action triggers and it will be resolved.

And we have the buttons back online!
Coding, Day to Day

Business Central – How to allow insertion of record but not modification in a list page

An intern had asked the above question and I thought to post the answer here to help out anyone who have the same. Instead of overly complicated methods/solution as her mentors suggested, the answer is very simple. Just two lines of codes.

Code example

There is no need to set Editable flag, just assign the appropriate flags to allow for insert and disallow for modify. That is all you need to do.

InsertAllowed = true;
ModifyAllowed = false;

The screenshots below shows the result of this setting, you are able to insert new records and delete but the edit option is hidden.

Hope that this is useful!

Coding, Day to Day

Business Central – Masking password or any data fields on a page

The consultants in the company were asking around if it was possible to mask a data field on a page, similar to the password field in a login page. They were thinking it was not possible as they had never seen any implementation in their existing NAV/BC projects. And that was the cue for me to show off.

I have seen it done before in my research some time ago and it was unsurprisingly simple. It was just one line of code.

ExtendedDatatype = Masked;

How it is being used is as shown below.

        field(50; "Password"; Text[50])
        {
            Caption = 'Password';
            ExtendedDatatype = Masked;
        }

The ExtendedDatatype property allows you to control the behaviour of controls on a page. For example, to display a field as a dots (for passwords), or as an email or URL or phone number. You can refer to this microsoft documentation for more information.

The end result is as shown below.