A lot of people using Microsoft Dynamics 365 Business Central want to learn how to develop and publish an extension on their Business Central tenant.
What drives this is that in the old Dynamics NAV, the users are able to easily add fields onto a page even if they’re not developers. Now that they’ve upgraded to Business Central, it’s not as straightforward. For people that are not developers, we want to give the ability to add fields on to a page back in their hands.
While browsing through Youtube, I couldn’t find the video that’s short enough that I felt comfortable sharing.
So I decided to make my own.
What You Need
Install VS studio code and AL code on your PC. Microsoft has a pretty good step by step instructions on how to install Visual Studio Code and the AL Extension.
Relax folks, Microsoft didn’t remove the Export to Excel function in Dynamics 365 Business Central starting from v19. It’s just been moved.
Old Version (or versions prior to Business Central v19):
Starting from Business Central v19:
Why The Change?
The direction from Microsoft is to integrate more and more Microsoft 365 application into Business Central. Having a organized place where you can export the data into other Microsoft 365 application is one small step in that direction.
Yes, we’re getting a ton of “The export to Excel function is gone. PLEASE HELP!” questions.
Permission setting in Dynamics 365 Business Central is a serious, but not serious topic.
When a company coming from a legacy system or no system, they will always want to restrict everything. The older systems are easier to restrict because there’s not a lot of functions you need to worry about. A considerable amount of business processes had to be complimented with manual processes.
In another words, they couldn’t do anything in the legacy system anyway. This is one of the reasons why they’re migrating to Dynamics 365 Business Central.
In my experience, after the company goes live, permission will more likely become an non-issue rather than first priority. The reason is the users can now do a lot more than what their old system can do. They require less time depending on other people for troubleshooting because they have access to troubleshoot the problem on their own.
When operation is smooth, things are smooth.
Nonetheless, when the discussion comes up about setting permissions, there are a few areas and key points to consider.
Ways to Restrict
There are basically 2 fundamental ways you can set security in Dynamics 365 Business Central.
The combination of setting the permission sets and configuring the personalizations for user roles should net you the exact permissions for your organization.
In addition, there are tools like Recording Permission Sets that allows you to set the permission based on what the user actually does in the system so you don’t need to rely on the standard permission sets for the user.
To restrict what the users can see on a particular page, after you’ve customized their profile roles, you can remove the personalization options so they can’t mess around with showing fields they’re not supposed to see.
Having it Just Right
Administrators will make the mistake of setting too much permission where the user can’t do anything. Every little problem they encounter, they will need to reach out to other departments or the supervisors. Give too little they will access the areas they’re not supposed to access.
The guideline to which I always recommend to clients on setting security and access is: “why are you setting this permission”?
Are they using the access to the information for malice?
Are you restricting the access because they might make a mistake?
If giving them access to prevent them from making mistake, I will always advocate training instead of restricting.
In the modern workplace, I will always advocate empowering the users with more, not less, access to information so they can troubleshoot their own problems and make better decisions on their own without having to bug someone else. Everyone is busy enough already.
If they’ll use the information for malice, then we for sure want to restrict them access and remove the functionality from the menu and permission sets. Having said that, I suspect when companies really get down what data users access for malicious reasons, there’s really not much areas that needs restricting.
Permission setting will take a lot of time. People will underestimate the amount of time required to get it just right.
I always recommend that the end user setup security for their own organization. The partner can do the initial framework, but to get everything exactly the way you want it, you have to get your hands dirty. Otherwise, prepare for large consulting bill and still not have it exactly the way you want.
One other caveat is that the more specific you get down to making your permissions “just right”, the more maintenance and overhaul you’ll need to do when your business requirement changes or new functionalities are introduced.
I can’t tell you how much time we have to “undo” restrictive permission sets in order to help move the company forward and expand their utilization of the system they purchased.
I guess the summary of this whole article about setting security is: Give the benefit of the doubt that the users will use the information for good, not evil.
On Dynamics 365 Business Central release 18, Microsoft made significant changes to the bank reconciliation features. Most of the articles and videos on entering beginning bank balances are still based on the older versions Dynamics 365 Business Central or Dynamics NAV.
Just thought I would share with the community on setting up your beginning bank balances if you’re moving to Business Central from your legacy accounting /ERP software
The goal is to have your bank balance match your G/L account. Just posting whatever is in your G/L to your bank is not the way to go and you’ll run into trouble later in future reconciliations.
Do it right initially will save you tons of trouble later.
Here are the steps to enter the beginning balances of your bank when you’re migrating from your legacy ERP/accounting software to Dynamics 365 Business Central.
What You Need
As with anything, preparation is key. There are a few things you need before entering your beginning bank balance.
Assuming you’re going live with Business Central on December 1st, they are:
Previous bank reconciliation before you go live. For example, if you plan to go live on December 1st, you’ll need the bank statement from November 30th.
A list of outstanding checks and transactions not cleared by the bank up to November 30th
Your reconciled G/L bank balance from your old legacy system. This means you need to do one last bank reconciliation from your legacy system up to November 30th
How do you know if you have everything put ready? Here’s a simple formula:
Your G/L balance for your cash/bank account as of November 30th +/- Outstanding bank transactions that’s not been cleared = Amount on the Bank Statement as of November 30th
G/L Balance as of 11/30 = 10,000 Outstanding uncleared checks and deposits as of 11/30 = 2,000 The bank statement that you got from the bank on 11/30 should be 12,000.
If the above formula is not true, do not proceed! Get it right so it can save you headaches down the road.
How This is Going to Work
Once you post the entries via General Journal (make sure the Account Type is Bank Account), run your first bank reconciliation as November 30th, or the period before you go live.
When you run your bank reconciliation for November 30th, manually enter the previous bank statement total in the Bank Statement Line section. DO NOT run the bank feed import for this period as nothing you import can be reconciled against.
Click on Matching -> Match Manually to match the entry that you’ve made for the Beginning Bank G/L Balance entry.
Once the matching is done for the beginning entry, you can post the reconciliation.
For your bank reconciliation for the following period, all of the unreconciled transactions will display for you to clear against.
Now you may be asking “But wait Alex, we won’t get your bank statement until a few days after we go live!”
You don’t need your bank beginning balances as soon as you go live. We typically get the bank beginning balances (and other non-operation related balances) setup about a week after go live.
I’ll admit that I’m a bit of an old school person when it comes to implementing Dynamics 365 Business Central (formerly called Dynamics NAV).
I believe when you make a decision to implement Dynamics 365 Business Central for your business, the transition should be smooth with minimal disruption.
In fact, these transition often make your process better as it sorts out (and eliminates) processes that are convoluted. It forces everyone within the company to at least take a hard look at what they’re doing and why they’re doing it.
I believe the transition process should be fully planned out and accommodations be made based on the individual circumstances for each company.
Furthermore, during the implementation process, there should be full understanding of:
What’s being done and what changes are going to occur
What’s being delivered by your Business Central partner
What’s being prepared by your own staff
But lately, I’m noticing a disturbing trend in our industry.
Disturbance in the Business Central Force.
When I speak to people that have implemented Business Central, they’re less than enthusiastic about going live with the software.
Digging deeper, I find a lot of common issues on why these business were less than happy with what they got. Here’s a list of the few common comments:
Received little to no training (online training was not sufficient)
No audit or testing on data being converted
Balances transferred over did not match
Disconnect between the users and the implementers
No response for requests for enhancements or modifications
No response for questions they have about the usage of the system
I think the cause of this is the current trend for implementing cloud based software is the automated or templated approach.
The idea is you list out all of the steps that are required to implement a new software, in this case, Dynamics 365 Business Central and you systematize it. In another words, it make it repeatable so anyone can go through the checklist and implement the ERP software.
The goal is so the solution center can hire anyone off the street at a discount, give them minimal training, and just have them follow the template with the customer to get them running in Business Central.
All this so the solution can offered at a lower priced to attract new customers who has no idea what’s going on.
Unfortunately, these low cost implementation trend is a race to the bottom. There’s always going to be the guy that can offer something at a lower price.
In most cases, to lower the price of implementation is to cut corners.
Sure, you can start using Dynamics 365 Business Central if all of your requirements are part of the template (which is the bare minimum). For anything else, you have to wait or you’ll need to figure out how to go around it on your own. The person that’s hired or contracted to help you will certainly not know the answer.
Accepting Less Than Mediocracy
I mean, I wouldn’t be as shocked if the people I spoke to received mediocre service and support. I’m more shocked that people just choose to accept less than mediocre service and support.
Some of the problems the people I spoke to seemed indifferent to their situation:
Accept that requests for modifications takes weeks (sometimes months) to deliver
Accept no answers to their questions
Accept that they should hire internal staff to maintain Business Central
Accept the inconsistencies of working with contractors to the solution center
Accept their numbers are off right from the beginning
Accept the lack of training
Accept that you cannot have a deeper discussion about your business problems and how it can be addressed
I can go on and on, but I keep wondering… At what point in time was this considered acceptable? Did I miss something?
Everyone talks about the destination. Nobody talks about the process.
Companies are excited about Business Central. It is the best mid-market ERP software. The number of improvements Microsoft has made is astounding. This is not including the strong integration with the existing Office 365 suite.
It’s exciting even for me, as a partner, to see what a fully deployed solution can do. We use it internally, and let me tell you, it has made deployment of these features a whole lot easier and a whole lot palatable. Not to mention how quickly and how organized we can operate as a cohesive team.
No one really pays attention to the question “exactly how do we get there”? Sure if you get a check list and go down the list to see what’s on the template and what’s being delivered, but is your business transformation really off of a templated checklist that you didn’t even create?
I’m not sure why companies are so accepting of their situation.
Do they feel that they’re trapped because they’re scared of what will happen to their data in the cloud (which is managed by Microsoft) if they switch software vendors?
Do they feel like it’s expensive to get a new vendor in? Too much hassle since they’ve gone through it once already?
Do they feel that these problems are just temporary and they’ll just magically go away?
Or worst, do they feel every software vendor is the same and “that’s just how it is”?
Whatever it is, I sincerely hope that this is just a phase. And I hope we, as an industry, can do better.
Assuming on 9/30/21, you’re asked to do a sales analysis for the month of August. When the costing is analyzed for the sales made on 8/30/21, the COGS (Cost of Goods Sold) that accounting recognize will be $2.00 per piece.
This means that if we’re printing reports based on Value Entry posting date filter from August 1st, 2021 to August 31st, 2021, the profit margin would be 60% per piece!
Not bad! For the accounting department, the cost is indeed $2.00 per piece since that’s the only amount that was recognized and costed for in that period.
Some companies makes an accrual on the General Ledger side for the expected cost of good sold, but that’s a separate topic.
However, this number to business owners and management, of course, is incorrect.
In actuality, the cost of the item should be $4.00 per piece because each piece came in at $3.00 with an additional $1.00 in freight charges. The profit margin of the item should be 20%, instead of 60%.
Another scenario is you’re running the sales report from 9/1/21 to 9/15/21, you would show 0 quantities sold, but the cost recognized in that period would be $12.00 ($1.00 in the additional vendor cost + $1.00 in the freight cost * 6 pieces sold).
If you did not check this report and you present this report to the management, be prepared field a load questions on the integrity of both you and the numbers.
Both Accounting Cost and the True Cost are correct! Do not assume otherwise!
It’s just a matter of how the user wants to look at the numbers. For the accounting department, they need the numbers to be recognized in the proper periods so the previous period numbers does not get changed. For management, they want to see the true cost of sales transaction. What to do?
As a simple rule, the Value Entry table Cost Amount (Actual) field stores the accounting cost that’s recognized for that period. The Item Ledger Entry table Cost Amount (Actual) field stores the true cost. Note that the Cost Amount (Actual) on the Item Ledger Entry table is a flowfield to the Value Entry table.
Most Business Central reports dealing with Contribution Margin uses Value Entry table only.
One thing to note when presenting the report off of the item ledger to the management, depending on when you post the vendor invoice and other landed cost charges, the profitability number will change.
This means that, in our example, the profitability report ran on 9/1/21 will be different than the same report with the same filters ran on 9/20/21. However, in my experience, once you properly explain this concept to accounting and management, they will understand.
Note that you can also use the Value Entry table for calculating True Cost. However, just filter on the Valuation Date instead of the Posting Date.
Sometimes, how a computer system does accounting will be different than how traditional accountants does accounting.
One of the subjects that always come up for debate is how COGS (Cost of Goods Sold) is calculated.
In Dynamics 365 Business Central, when a purchase of inventory is made, 4 General Ledger accounts are hit:
Direct Cost Applied
When the items are sold, 4 General Ledger accounts are hit:
In this case, the COGS is automatically calculated and posted to your COGS account. How Dynamics 365 Business Central calculates COGS is based on your Costing Method in conjunction with running the Adjust Cost – Item Entries process.
The COGS posted by Business Central is ALWAYS correct. Don’t assume otherwise!
How Accountants Calculate COGS
Typically, when a CPA (Certified Public Accounts) report COGS, it’s calculated and displayed based on the following formula:
+ Purchases (for the period)
– Inventory Adjustment (for the period)
– Ending Inventory = COGS (Cost of Goods Sold)
This formula gives all sorts of problems when trying to produce a proper financial statement to the CPA to do their tax returns in Business Central.
What ends up happening is that company will believe they need to purchase all sorts of fancy reporting tools when the out of the box Account Schedules functionality will work just fine.
This calculation is actually pretty easily defined in the NAV account schedules.
Let’s say you have the following Chart of Accounts:
The way to set the formula to give the proper COGS based on the CPA formula on the Account Schedules is the following:
A010 – Beginning Inventory (Row Type = Beginning Balance) A020 – Purchases (Row Type = Net Change) A030 – Inventory Adjustment (Row Type = Net Change) Set it to NOT show A040 – Direct Cost Applied (Row Type = Net Change) Set it to NOT show A050 – Inventory Adjustment (Formula = A020 + A030 + A040) A060 – Ending Inventory (Row Type = Balance at Date) B010 – COGS (Formula = A010 + A020 – A050 – A060)
After you setup the accounts schedule, you can use the Overview functionality to check your work.
In this example, I used the standard Business Central COGS account to check my work.
Boom! The COGS I calculated and the standard BC COGS matches! Good to go!
What was the point for lines A040 to A060? (Read only if you’re interested in the accounting and costing aspect of it)
The reason why we need to set this up is because of purchase returns. When items are returned using the purchase return order (or purchase credit memo), the cost being used to return the product is deducted based on your costing method, NOT what you typed in as the direct unit cost on the purchase return/credit.
Confused? Don’t be.
Let me explain, when you invoice a purchase order for $10.00 for item A, the following happens:
Now, suppose you need to return this product to the vendor and the vendor is only willing to give you credit for $8.00. When you post the credit memo, the following will happen:
+ $8.00 Accounts Payable
– $8.00 Inventory (assuming this is what’s on the Unit Cost (LCY) field on the purchase credit memo line)
– $8.00 Purchases
+ $8.00 Direct Cost Applied (assuming this is what’s on the Unit Cost (LCY) field on the purchase credit memo line)
Additional, NAV will make these 2 entries:
– $2.00 Inventory
+ $2.00 Direct Cost Applied
The extra $2.00 entries are posted because the cost of the item is $10.00. That’s the actual price paid for the item to the vendor so that’s the cost it’s should be relieved from inventory.
So you can say the sum of Purchases and Direct Cost Applied is the difference between vendor returns and the vendor purchases.
For most companies, these amounts are lumped together to be displayed under inventory adjustment on the income statement. You can separate it out as a different line item on your income statement if you wish.
It doesn’t matter if you turn on Exact Cost Reversing or if your client says “this doesn’t happen, we always use the same cost that we bought it at”. ALWAYS do this.
The formula gets a little more involved when you start involving Item Charges (landed cost assignments). You can play around with the account schedule to see if you’re able to get it going. If not, reach out!
This example assumes that you set Direct Posting to NO on the G/L accounts described.
As you understand the concept behind this, you can easily incorporate Item Charges, WIP, etc into this formula. Basically, everything that gets accrued into Inventory needs to be part of this formula.
Most companies would want to allocate additional line items into the calculation of COGS without accruing the expenses to the inventory. This is fine, you’ll just need to add the other costs into the formula. However, the total of your COGS will be different than Business Central’s COGS.
Separate from the point of this post, as a rule of both thumbs, you always turn off the Direct Posting field on the inventory accounts. Always. ALWAYS. No exceptions.
If you want to make G/L entries to the inventory account, create a separate Inventory G/L accounts and combine them together on your Account Schedules to have them appear as one when you present it to your CPA or auditors. Making G/L entries directly to the inventory accounts is a one way ticket to reconciliation headache between the Inventory Valuation and General Ledger.
If everything is setup properly, you’ll find that the COGS formula you setup will match, to the penny, the COGS account on your Chart of Account.
It’s that time of the year again. Close out the year! Most companies align their fiscal year to the calendar year so right around this time, we usually get a ton of support calls from people that forgot how to close the year end and need a quick refresher.
So I made a quick 2 minute video on how to do year end processing. Why? Because you can’t go through a 10 minute video in 2 minutes and I know you have a ton of other things to do.
I’m using the older version of NAV, but the menu tree is the same as in prior versions. To access the accounting period, in the video, the NAV 2013 has the icon pointing to Department – Financial Management – Accounting Period. If you go to Financial Management -> Setup in version 4.0 and 5.0, you’ll see the accounting period.
If you’re using Dynamics 365 Business Central, you can just search on the term Accounting Periods and you’ll be able to start on the same process:
The processes are exactly the same from version Navision version 1.2 to Dynamics NAV 2018 to the newest version now called Dynamics 365 Business Central.
In reality, however, there really are two ways: there’s the option of setting up your warehouse with Directed Pick and Put-away…and the wrong way.
In almost all companies where bin location management (or warehouse management using wireless scanners) is desired, setting up your warehouse with Directed Pick and Put-away will be the correct decision.
Assuming you’ve done the necessary prep work in your warehouse for a warehouse management system, you will then need to configure the existing Locations in Business Central to be warehouse enabled.
Basically, to enable a location for Direct Pick and Put-aways, you will need to run a process called Create Warehouse Location from the Location List.
Then, you will need to enter the Location you wish to enable the Directed Pick and Put-away for, in addition, the Adjustment Bin.
When you click on the Create Warehouse Location process, it will do the following:
Enable Directed Pick and Put-away on the Location Card
Move all inventory in the Location to the Adjustment Bin setup in the Location Card
The purpose of the Create Warehouse Location process is that it will move your inventory quantities to the Adjustment Bin.
Moving the items to the Adjustment Bin allows you to do a physical count using the Warehouse Physical Inventory Journal or the Warehouse Item Journal to move the items from the adjustment bins into the regular physical bins in the warehouse.
Any remainder inventory quantities not accounted for using the warehouse journals will be counted as Positive or Negative Adjustments when you run the Calculate Warehouse Adjustment process from the Item Journal.
For the location (or warehouse) to be able to properly process orders with Directed Pick and Put-away enabled, you need to associate each Bin with a Bin Type Code and a Zone Code.
However, when you’re setting up the Zones and the Bins for the Location for the first time, you will NOT be able to define a Zone Code or a Bin Type Code against the Bin.
If you try to define a Zone and a Bin Type for the Adjustment Bin, you will counter an error saying the Zone Code must be blank.
The reason you’re getting this error is because you need the Directed Pick and Put-away marked as true on the Location Card in order to fill in the Zone Code and the Bin Type Code.
But you can’t mark the Directed Pick and Put-away as true if there are inventory quantities in the location. Which is enabled by running th Create Warehouse Location process.
And you shouldn’t run the Create Warehouse Location unless the Adjustment Bin is setup with the proper Zone Code and the Bin Type Code.
We have quite the problem here…
What if I Run the Process Without Zone and Bin Type?
Yes, you can run the process without defining the Zone and the Bin Type on the Bins, but that’s not what you should do. In fact, that’s where most people get in serious trouble.
After the Create Warehouse Location process is finished, you’ll want to go in and fill in some additional bins or begin setting up other bins in the warehouse.
However, if you go into the Bin screen to setup additional bins, you’ll encounter this error:
That’s correct. In order to add additional bins in a location with Direct Pick and Put-away enabled, BC requires you to have a Zone Code and Bin Type Code.
Now you’re screwed.
If you continue using WMS in the current setup, you’ll have some bins with Zones and some bins without Zones.
When you do adjustments, it will not properly move items from the bins with Zones and bins without Zones, leaving you with tons and tons of inventory quantity problems that are too many to list in this article. That’s pretty much a nightmare.
You’re now in a world of hurt.
The Correct Way to Enable Warehouse Location
Before you run the Create Warehouse Location process, make sure you set up all of the Zones and the Bin Type codes with the zones.
How how do we do this if the system is blocking us from doing the right thing?
The answer is by forcing it using Rapidstart.
When you create the Bins, you need to utilize RapidStart Configuration Package to FORCIBLY fill in the Adjustment Bin Code WITH Zone Code and Bin Type Code.
The 4 fields you need on the Bin table are:
Bin Type Code
This way, the bins will be inserted with the proper Zone Code and Bin Type Code when the adjustment entries are created.
Processing future transactions within the warehouse will flow normally and all in the world will be well again.
This is the conundrum when enabling a Location to be Directed Pick and Put-away. During the setup of the warehouse, you’re not able to use Zones. After the location has been converted to have full WMS, you’re required to use Zones.
While I’m not sure if Microsoft will address this issue in the future, at least, now you’ll know how to fix it.
That being said, having your warehouse move off of a manual-based process to an automated (or computerized) one, in itself, is often not an easy task as it requires buy-in from the warehouse users and reconfiguring the warehouse process so it all flows so flows in a smooth and consistent way…but this will be a topic for a separate article.