Day to Day, Coding

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.

Day to Day

Warship Legend – Main Interface at a Glance (2)

This is Part 2 of interface guide for Warship Legend. If you have missed out Part 1, click here for a quick recap! We hope that you had enjoyed the earlier guide!

#18 Training Center

The training center comprises of the following:

  • Daily Instance
  • Cruise
  • Joint Expedition
  • War Game

In Daily Instance, you get to clock a limited amount of free resources daily. This will increase to a maximum limit as you progress up the levels. In Cruise, you assign a combination of ships based on the mission requirement to collect resources after a specific duration. The number of mission increases based on your VIP level (which you need to pay money to level up). Joint Expedition and War Game and quite similar in concept where by you fight with AI ships to advance the level in that area of game (sort of a mini game/side quest) to gain resources.

#19 Club

The club is where you expend your chips in roulette style. There are three types of chips – Normal, Gold and Mysterious chips – that can be collected in game. Mysterious chips are very hard to get unless you buy them with real money. The normal roulette will bag you common ships and resources. The gold/special roulette will give you the chance to get some rare ships shards. Mysterious roulette will give you the chance to get some legendary/titanium warship shards. There is also a mini game – Top Gun – in the club which does not offer anything for the gameplay itself. A minor distraction I would say.

#20 War Schedule

This place gives you a brief overview/timeline of game events in Warship Legends so that you can plan and accumulate your resources accordingly.

#21 Legion

This area is sort of a clan management and activity area. You organize/manage your team members and sign up for legion events such as Waterway Conflict and Landing Operations for Team v Team battles. There is also a Legion Instance event where your legion can work together to fight against AI ships to advance up the level. These events offer some resources that you can use to purchase items from the market.

#22 Archives Bureau

Archives Bureau stores the storyline and also provides information on different classes and types of ships.

#23 Control Tower

Control Tower is the area where you have Championship, Master League and Tactical Tournament leader board competition. The higher you can advance, the better the rewards you can get!

That’s all for the interface guide! Thank you!

Day to Day, Thoughts

What do you want?

Whenever someone complains incessantly, especially if you are the one doing it, ask them or yourself this simple question – “What do you want?“. In most cases, it should shut the other party up (no guarantees), but if you are serving this question to yourself, it is important that you answer it honestly. No beating about the bush, no avoiding it.

Complaining can be viewed as a form of stress relief, a bit and ineffective method. It serves no constructive function and waste energy for everyone. It doesn’t help with the situation because it is all in the mind and there are no actions being taken to resolve it. Asking this question, “What do you want?“, puts a pause on the runaway mental train and kickstarts the mind to view the issue from another angle (hopefully).

If there are no answers, then it is advisable to put it to rest since likely there are no solutions to it. Brooding and overthinking it would just further waste more energy and create more pain. It’s not worth it. On the other hand, if there are answers that came out of this question, then the next question to ask would be “What are you going to do about it?“.

What are you going to do about it?“, prepares the mind to translate this thought to something actionable. Something that one can do to resolve this conflict that is inside the head. Make sure that the actionable items are positive and constructive.

I confess to being a serial complainer and i will do the following once I am aware of my mistakes.

  1. Write down on a piece of paper what i am complaining about
  2. Answer the question “What do you want?’ on the paper
  3. If there are answers for (2), then answer “What are you going to do about it?” on the paper
  4. Translate (3) into actionable items (i.e. adding to calendar/task)
  5. Make sure they are positive and constructive before acting upon it

I prefer to write my thoughts on paper instead of processing it in the mind because it slows down the racing mind. From there, one can calmly view and comprehend what is going on and make a more realistic and practical judgement. At the same time, it keeps the focus on what matters at that moment instead of allowing the “monkey” mind to start going off tangent and overthinking again.

Hope that this article is of help to anyone out there. How do/would you handle this issue? What is your tried and tested method? If you have any better ideas, let me know too.

Thoughts

What Matters

Let us be honest about it and face the brutal truth. You are not going to live forever. We are not going to live forever. Our lifespan is finite, unlike money which is pretty much infinite – or at least until you run out of materials to represent one. Not just that, life is full of uncertainties. We do not know how much time we have. Time is a cruel master. Hence, It is understandable why we as humans, tend to not think about the above as realistic but label them as pessimistic thoughts.

What matters? This could be the most important question that one have to ask everyday. What really matters now? This moment, now. While we are at this topic, let us not get confused over what is urgent and what is important. These are two different matters. What is urgent might not be important, but what is important will have to be addressed at some point in your life.

What matters? Is this action that you will be undertaking matter in the next 10, 15, 20 years? Will it affect you for the entire lifetime? Will what matters change as time grinds down whatever finite amount of time that you have? All these are questions are harsh and best answered with honesty.

Remember to keep reviewing and reflecting on what matters. Choose wisely and make the best out of it.

Day to Day

Ideas for Repurposing Old Android Devices

Got a few mobile devices like mobile phones or tablets lying around and not making themselves useful? Sounds like a normal occurrence in our modern society. I do happened to have a few of these devices myself consisting of a LG G3 with a fickle display and an Asus ZenPad 8 with a busted SIM card slot. Here’s what i had done to milk for all their worth, and hopefully provide you with some ideas on what you could do for yours.

Crunching Numbers for Humanity

Help the world to rid diseases (i.e. COVID-19) or make new scientific discovery by donating your device’s computing cycles to distributed computing projects via BOINC. I’m currently running World Community Grid project, but there are a wide range of projects to choose from/support. At this time of posting, the latest version of the app can be found on their official site and only supports Android devices. Google Play store has an older version but in my opinion is still usable for older devices. My personal configuration will be to set only one CPU for compute so that it doesn’t get overheated and still allow for other usage concurrently.

A brief mention – you can support the fight against COVID-19 by donating your computer processing cycles to the following:

TV/Video Streaming

You can also use them to watch YouTube/Netflix videos, for yourself or for the younger ones. If there are TV streaming apps available in your country/region, you can watch Live TV on the go instead of needing the TV. A use case would be to catch up on news or drama in the kitchen while you are whipping up a feast. I have been using mine to watch Live TV (MeWatch) and YouTube on the tablet.

Internet Radio/Jukebox

If your device has front firing speakers, they might make a decent internet radio/jukebox. You can either load up on the MP3 files if there are no working internet connectivity or install Spotify, YouTube Music, TuneIn or any live streaming radio apps for music and podcasts. In my case, it will be mainly Spotify.

eReader/Reading

Google Books should come pre-installed on your device. It’s the default app i go to for reading my eBooks. Alternatives could be Amazon Kindle, Kobo Books, just to name a few. Even the lowest powered device should suffice for this function as long as you have a working display. You could even link up to your local library using Overdrive. A good idea to keep up the habit of reading, so no excuses.

White Noise Generator

Need a white noise generator for bedtime or to focus at work? I used Baby Sleep on the LG G3 for sleep. It works for me, but i cannot vouch the same for you.

Alarm Clock

Enough said. I think this is self explanatory.

Donate/Recycle/E-Waste Disposal

Should your devices not be able to perform the above tasks, perhaps it is time to donate or send to a recycling/e-waste disposal center for processing. Your device had done what it could and it is time for it to rest.

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!
Day to Day, Reviews

Strange Weather by Joe Hill


A collection of four chilling novels, ingeniously wrought gems of terror from the brilliantly imaginative, #1 New York Times bestselling author of The Fireman, Joe Hill

I bought the eBook version from Google Books last year and finally finished it this year. Compared to his previous works, I really like this novel. It has a refreshing sense and it resembles the storytelling style of his father, Stephen King. I am a huge fan of Stephen King and I probably would start to be a fan of Joe Hill too at this rate. It also reminded me of my first Stephen King book, “Four Past Midnight”.

Book Details

TitleStrange Weather
AuthorJoe Hill
GenreHorror, Speculative Fiction
PublisherWilliam Morrow
Publication DateOctober 2017
Pages448

The book starts off with its first story, “Snapshot”. It’s a disturbing story of a adolescent who finds himself threatened by a mysterious man called “The Phoenician” who possess a Polaroid Instant Camera that can erase memories, one picture at a time. It’s this first story that got me hooked all the way to the last and it is both chilling and touching at the same time.

In “Loaded” , a mall security guard courageously stops a mass shooting and becomes a hero. Or is there more to the story? As his story begins to unravel under the glare of the spotlights, will the truth be out and does it take his sanity with it too?

“Aloft” starts off with a young man taking his first parachute jump and winds up getting stuck on an impossibly solid cloud with things that animate to life by thoughts of his own. Will he manage to free himself from this cloud “prison” or will he be stuck forever? What will he or has he discovered there? That is for you to find out.

“Rain”, an apocalyptic chapter which is really about Strange Weather, a downpour of deadly silver and gold pieces that destroys and shreds everything in its path, causing global chaos. It’s a nice story with a sad ending.

If you are looking for a book to read to pass time or if you like Stephen King’s book, I would highly recommend this to you. This is not a normal novel of short stories, and each of these stories have their own unique twist.

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!