Extract Validation Input Titles and Messages from D365 Templates Using VBA

I’ve got a couple of D365/BC projects I’m involved with at the moment. In both cases the data is being loaded via D365 and I need to supply templates to the client.

I don’t know if you’ve ever been involved in loading data into D365 but if you’ve been used to BC’s Configuration Packages then you’re in for a nasty shock.

One of the key things you can do to keep yourself out of trouble is to make sure that the data the client supplies is of good quality. So you need to make sure that they know what sort of data goes in each field and how long each field is (and any other constraints, like Booleans or Option lists)

In Configuration Packages the relevant information is in a note on the header and even if you copy and transpose it to make it a bit more readable, you don’t lose the notes.

In D365 however the same information can be seen in the cells below the headers – the fields if you like. It’s stored in excel not as notes but as Validation Input Titles and Messages. So, if you transpose the Headers, you lose all that info that you need your customer to have.

This is annoying. I needed to find a way to extract that information and show it in a cell so I could send the template to a less techy person and they could easily see what it was I wanted from them.

 These days, my first port of call when messing around in excel is Power Query. But a quick Google left me without answers. So I turned back to an old friend: VBA.

I’ll confess to never having been much good with VBA. And to it having been several years since I needed it (thanks Power Query!) But, flaking rust everywhere, I managed to cobble something together and, after a few iterations, hit upon a little chunk of code that worked and didn’t hang every time it couldn’t find anything.

And here it is:

Sub looptest()

Dim c As Range

For Each c In Range(“D3:FZ3”)

       If c.Validation.InputTitle <> “” Then

       c.Value = c.Validation.InputTitle & ” ” & c.Validation.InputMessage  

       End If 

 Next c

End Sub

The result looks like this:

And then, because the descriptions are quite long, I’ve copied and pasted (transpose) it to show the headers as a vertical list with the data type descriptions and field lengths to their left.

I hope someone finds this useful. It was a bit of a headscratcher for me so putting in my blog means there’s one more place for the next person looking for a solution to this problem to find it.

Intercompany Sales Orders, Purchase Orders and Journals

As an appendix to my previous post about Intercompany Setup and Operation I thought I should complete the picture by describing some other stuff. This is about sending Intercompany Sales Orders, Purchase Orders and Intercompany General Journals

If you are using items or resources and supplying them from one company to another of your companies then this allows you to generate a sales order in one and automatically create the corresponding purchase in the other with no extra keying.

The process for Sales Orders and Purchase Orders is identical. I’ll talk about Sales Orders here but the two are interchangeable.

Assuming you’ve done the setup as described in the previous article, create a Sales Order as normal. The Customer in this case is the Customer you created for the Intercompany Partner you are transacting with.

I placed the order in the Cronus UK company. I am selling an ATHENS Desk to the Cronus EU Customer.

Instead of doing the usual and releasing the order, (1) go to Functions and (2) click on Send IC Sales Order. This will send the order to the Intercompany Outbox

and then on to the Intercompany Partner’s IC Inbox

The user at Cronus EU needs to accept the Inbox transaction (Functions/Accept). This moves the transaction to the Handled IC Inbox and, more importantly, creates a Purchase Order to match the Sales Order in the other company.

The sales and purchase orders can then be posted as normal as the stock is received and shipped and then invoiced.


In the sending company prepare an Intercompany General Journal

Posting it will move it to the IC Outbox and send it to the IC Partners IC Inbox

As before, with the Sales Order, hit Function and then Accept. This opens the Complete IC Inbox Action popup. Fill this in and click OK.

Side note: You might find you need to set up a General Journal Template for IC General Journals as I’ve done here

Clicking OK Creates the Intercompany General Journal, which can then be posted.

And that’s it. A very quick romp through Intercompany Sales Orders and Journals. As before, set up is key.

What I am finding with clients with multiple companies is that an individual user will take responsibility for a single company. They may be very confident that they have performed the setup correctly but in reality, they have only done it in their company. This means they have only done half the job. Users at both ends of the transaction must cooperate for Intercompany to work properly.

Bulk Deletions in BC Using Configuration Packages

So you’ve got a whole bunch of items to delete. Perhaps your procurement department got really enthusiastic about a new range of products and listed them all and, then when sales looked at them they hated them.

You don’t want to sit there for hours deleting them one at a time so what can you do?

In the old days, you’d nip into the object designer and delete what you didn’t want. But in BC you can’t.

This cropped up today as a support case for a customer of mine. One of my helpdesk colleagues was wondering about a SQL script but I’d seen a session that José Miguel Azevedo had given at BC Bootcamp last year and I knew there had to be a better way.

First: A WARNING!!!

I was pretty pleased with this solution when I came across it, so I quickly wrote up this blog post and posted it. I’d spoken about it on Twitter so I posted a link there too.

Pretty quickly some folk who’ve been doing this a lot lot longer than me – folk I really respect – chipped in with some warnings about my solution and some truly horrifying stories.

Before you start on this please test in a Sandbox or test company.

The function I describe here, using configuration packages, deletes all records in the table, then commits, and only then reloads the records in the package.

I repeat…it DELETES all records in the table and only re-imports what you tell it to. Don’t do this when anyone else is on the system. You really don’t want to run into a conflict whilst doing this.

And final piece of advice: don’t muck about with anything else whilst doing the deletions. It might be tempting to edit some records too. Don’t do it. Keep it simple.

Configuration Packages

We’re all familiar with Config Packs, right? They’re just for loading data into BC during implementations, aren’t they?

Well, no. They’re not just for that. They can help you with bulk deletions too. Let’s take a look.

1         What to delete

In my Sandbox I created a dozen copy items of the good old ATHENS desk and I want to get rid of them.

First of all I need to check that there are no outstanding orders or item ledger entries because you must make sure you don’t delete an entity that has transactions against it. I’d usually do this with Power BI (list of items with an aggregated count of item ledger entry lines)

This is important because, whilst you usually cannot delete a record with open documents or ledger entries against it, using this function you can. It’s easy to screw up here and leave orphaned ledger entries which is messy and will beg questions when the auditor comes calling. 

2         Create the configuration package

I’ve created a really simple one here. It’s one table (27: Items) and I only need two fields: No. and Description.

3         Export the pack to Excel

Export the table to excel.

And then open it up.

Very, very carefully, delete the records you don’t want in BC anymore and save the spreadsheet.

4         Reimport the data

Reimport the data and, once it’s uploaded, check the “Delete Table Records Before Processing” checkbox. This will delete all the records in the table and only reimport what you’ve asked it to in the spreadsheet you’ve imported. Obviously this is a very useful but VERY dangerous feature. This is why you need to make your BULK_DELETE package clearly different and separate from all the others.

5         Apply the package

Once the package is imported you can see that there are fewer package records than Database records.

Click the “Apply Package” button in the ribbon and you then see that the deletions have occurred.

Go to Items and there they are: gone!

This can be done for any entity in BC: Customers, Vendors, Contacts; but the same rules apply. They have to have no ledger entries and mustn’t feature on any current document.

Remember…this is a very powerful feature that will permanently delete records. 


Deleting records using Edit in Excel

I wrote the above post whilst trying to solve a problem for a client who is on BC v14 (using the Nav desktop client – sighs sentimentally). In their case I’ll have to use the solution described. However in newer versions there is a safer method that doesn’t allow the user to delete records with ledger entries.

Again, I created a dozen duplicate records. This time using the PARIS Guest Chair

Click on the Edit in Excel button

This downloads an excel file to your desktop that is connected to BC. Highlight the lines you want to delete and delete them just like you would in any excel file. 

Now simply click “Publish”. This reloads the file back to BC. Your deletion of the lines passes an instruction back to BC to perform the deletions on those records. 

The following message pops up to let you know what you’re about to do (as opposed to configuration packages which give you a similar message after the event!)

Having had a play around with this I can see definite benefits as compared to my earlier solution. My favourite, though, is that it will not allow you to delete anything you shouldn’t. 

So if you’re lucky enough to be on a newer version (I think this is v16 onwards) this is a much safer option for bulk deletes.

Intercompany Setup and Operation

1         Introduction

The Intercompany functionality in Business Central allows for transactions to run smoothly between different companies on the same database without having to key documents on both ends of the transaction.

The user creates a sales invoice in company A (the Sending Company) and this – via various elements of set up – will create the equivalent purchase invoice in company B (the Receiving Company).

The guiding principle to be followed throughout is that for every element in company A, an equivalent element needs to exist in company B. I think of it like a plug and a socket. A three-pin plug will successfully connect to a three-pin socket but if you have two pin socket you will fail.

With that in mind let’s look at the elements involved in set up

2         Set up

2.1         Company Information

In the Company Information window, you will need to make sure that the IC Partner Code field is populated with a Code you will recognise when using it in other companies.

It’s a good idea to use the Display Name from your Companies table.

This will be the code you use when you set up this company as an Intercompany Partner in another company.

The IC Inbox can be set to Database. This allows for communication between companies in the same database. The other option is “File Location” which can be used for IC transactions between companies in different databases.

2.2         Customers and Vendors

For each Company you want to transact with you will need to set up a customer and/or a vendor depending on whether you will be “selling” or “buying” goods or services from your partners, or both.

Generally, in the Sending Company you will need a Customer who corresponds to the Receiving Company.

This example from CRONUS UK shows the customer representing CRONUS EU

In the Receiving Company a corresponding Vendor must be set up to represent the Sending Company. Here, in CRONUS EU you can see the vendor representing CRONUS UK

It is important to note that the IC Partner Code field in both is populated with the code you created in company information.

2.3        Intercompany Partners

The next step is to establish the link between the Sending and Receiving Companies. This is done in the Intercompany Partners table. The example is from CRONUS EU (as the Sending Company) for CRONUS UK (as the Receiving Company)

The fields are pretty self-explanatory, but I’ll go through them in order

  • Code: Intercompany Partner Code from the Receiving Company
  • Name: The name of the IC Partner
  • Currency Code: The Currency Code the transactions will be made in. If you leave this blank it will default to the Local Currency (LCY) set in the General ledger Setup. In this case it is EUR.
  • Inbox type: choice between email and Database. Database means that your IC partner is on the same NAV Database as you. 
  • Inbox Details: this will auto populate when the Code is filled in
  • Accept Transactions: This is for incoming transactions. When it is ticked an incoming Sales Invoice automatically creates a corresponding Purchase Invoice in the Receiving Company
  • Customer No.: The Customer No. created in the Sending Company to correspond to the Receiving Company
  • Receivables Account: the G/L Account No. of your Accounts Receivable (i.e. money you are owed by Customers)

In the Receiving Company the matching record looks like this.

  • Vendor No.: the Vendor No. created in the Receiving Company to correspond to the sending Company.
  • Payables Account: the G/L Account No. of your Accounts Payable (i.e. the money you owe to Vendors)

2.4         Intercompany Chart of Accounts

It’s a good idea to set this up whilst you’re doing everything else. It’s a chart of accounts agreed between all IC partners, so you can make it quite simple if you need to. The G/L Account No’s passed to the Receiving Company by the transaction from the Sending Company are mapped using the “Map-to G/L Acc. No.” field in the Receiving Company’s Intercompany Chart of Accounts.

However, there maybe occasions on which you don’t want a like-for-like mapping. Imagine you have sold services in one company to another. In the sending company this would be an Income GL whereas in the receiving company it would be an Expense. This is dealt with in the main Chart of accounts. Each account has a field called “Default IC Partner G/L Account”.

Imagine you are selling advertising services to your partner company. You would raise a Sales Invoice for GL Acct 10100 Income, Services but your partner will want the Purchase Invoice charged to an expense GL. In the example below I’ve mapped it to 30200 Advertising Expense. When the transaction is passed to the Partner it will appear with the Default Partner G/L Account 30200. No editing of the PI is required and no extra keying.

2.5.1        Set Up Dimensions and Intercompany Dimensions

Dimensions for Outgoing Transactions: In the sending company go to the Dimensions page. There needs to be mapping at both the Dimension level AND the Dimension Value level. You can see here that AREA and DEPARTMENT are mapped but BUSINESSGROUP and CUSTOMERGROUP is not.

Fortunately, there is an easy solution to this. In the Actions tab on the ribbon at the top of the page is a button: “Map to IC Dim. with Same Code”. Click this button and it will map at the Dimension and Dimension Value level by duplicating the entries in the Dimension Values and creating them as IC Dim Values. Obviously, you will need to ensure that these correspond with identical values in the Receiving company’s IC Dimensions

Intercompany Dimensions for Incoming Transactions: The same functions exist here but with one extra: “Copy From Dimensions”. This function creates IC Dimensions for the Dimensions already existing in the Receiving Company. Again, make sure that the Dimensions and Dimension Values have matches in the Sending Company.

3         Process

3.1         Sending Company

In the Sending Company a Sales Invoice is raised to the Customer corresponding to the Receiving Company (as set up in the Intercompany Partner table)

Once it has passed through the approvals process it can be posted.

This passes the invoice to the Intercompany Outbox and, providing the setup is all correct it it processed directly and will appear in the Sending Company in the Handled Intercompany Outbox Transactions

3.2         Receiving Company

In the Receiving Company, again assuming set up is correct, the transaction is passed via the Intercompany Inbox to the Handled Intercompany Inbox Transactions

If the Auto. Accept Transactions box is ticked on the Intercompany Partner then a PI will automatically be created with no further keying on the part of the users in the Receiving Company

This can now be posted and the IC transaction is complete.

4         Troubleshooting

I hope you can see that this is relatively simple to set up as long as you keep the pairing principle in mind at all times. In my experience pretty much all troubleshooting will boil down to this. When I was setting this up in my sandbox I fully expected to pass the first transaction through smoothly. But I’d forgotten to map the IC Chart of accounts AND one of the IC Dimensions in the receiving company.

Fortunately BC will pop up the usual gnomic error messages that should guide you to where the missing or mismatched pieces of the puzzle are.

Hope that’s helpful. Happy IC transacting!

Red Highlights, Red Italics

Ledger Entries with Red Highlights Bold and Italic: What Do They Mean?

As a user of Nav for the past ten years one of the things I’ve appreciated has been those handy highlights in the customer ledger and vendor ledger entries telling me which imvoices are overdue.

When I was MD of a wholesale company, credit control was vitally important to us and I spent a good proportion of my time with my credit control manager, chasing up slow payers and discussing on-going credit limits.

Nav does and incredibly useful thing by presenting overdue invoices in bold and red.

So, that’s great and very handy but I clearly remember looking at those other red entries. The ones not in bold but in italics. Somehow, I never asked, and, as I had the overdue stuff that mattered to me highlighted, I never needed.

Fast forward ten years and I’m now a Business Central consultant. The meaning of those red italic entries ahs never cropped up in a customer conversation and so my shameful ignorance has never been exposed

Until now.

I point out the usefulness of the bold entries to a client. “What are the italic ones?” he asks, innocently.

And there it is. I don’t know. I’ve never known. I tell him and tell him that, over lunch I’ll find out. And I do!

Not to keep you in suspense, the entries in red italics are closed entries that were closed after the due date. For an invoice, for example, this means that the Customer paid but paid late. From a credit control point of view this is really useful. If a the majority of a customer’s ledger entries are in red italics then they have a history of late payment and are worthy of your particular attention.

How to prove it?

As you can see from the highlighted example, it was due  on 12/05/2021. When I look at the applied entries, the payment was posted on an earlier date. What gives? Surely that means that the invoice was paid in time?

But no! The date we need is the Closed At Date and, of course that’s not available to view on the Customer Ledger Entries.so where do you go to find it.

If yoiu highlight the entry you are interested in and click CTRL+ALT+F1. You’ll get the About This Page window which shows you every single field in the Customer Ledger Entry table and, because you highlighted the line, it will show you the contents of those fields for that line.

And there you have it. The entry was Closed At (i.e. Applied at) 19/05/2021 which was later than the due date. 

Deferral templates and how they work..

Deferral templates and how they work…

I was talking to a client the other day, all full of confidence, and she asked me a question about Deferral Templates that caught me out.

What she wanted to know was how deferrals work in NAV/BC if the invoice is posted in the middle of the month. Is there an element of pro rata or does the deferred amount just get posted back into the beginning of the month on a date earlier than the original invoice. 

My mind went blank. It’s a few years since I’d set up a deferral template and, whilst I knew that I knew, I simply couldn’t remember. 

I immediately fessed up and told her I’d go and brush up and get back to her (never try to bluff. It’s always best to confess and come back when you’ve found out the answer). so I did and I thought I’d share the result.

I did this in NAV 2019 but it’s pretty much unchanged in BC.

Deferral will take the amount of an invoice or journal and spread it across the current and following periods according to a set of rules. The rules are set in a Deferral Template. The Template looks like this:

It’s good practice to name your template in such a way as you don’t get mixed up. This one is for deferral across six months in a straight line and is using a sales G/L for the Deferral Account.

Deferral %: This determines the amount of the invoice that will be deferred. Here we’re deferring all of it. But if you were to set this to 80, it would post 20% of the invoice value directly and the defer the rest over the following months

Calc. Method and Start Date: These two fields tell NAV how you want to calculate the deferral. Specifically:

Straight Line: Defers by dividing the amount to be deferred by the number of periods specified depending upon the Start Date. Here, we’re using Posting Date so it will make seven deferral postings. The first and last are pro rata and the middle five will be equal and posted on the first day of the period. The last, pro rata’d posting will also be dated at the start of the final period.

It will do a similar Pro rata calc if you choose End Of Period as the start date in that it will calculate the amount left to the end of the period, defer that first, then five equal payments followed by a final pro rata payment to make up the remaining sum. Whereas Beginning of period and Beginning of Next Period will do exactly what they say they will.

Equal Per Period: This is pretty self-explanatory. The amount to be deferred is split equally by period but again it pro rata’s if the posting date is in the middle of the period

Days Per Period: Will divide up the deferral by the number of days and allocate the amount according to the number of days in each accounting period. So if you are using calendar months it will calculate a different amount for February and March. Similarly if you use a 5-4-4 pattern to each quarter it will calculate a higher amount in the five week period.

No. of Periods: This is simply the number of periods over which you wish to calculate the deferral. Bear in mind that, as shown above, some combinations of Calc Method and Start Date will pro rata the first and last months of the deferral giving you an extra month.

Period Desc: This allows for you to provide a dynamic posting description for your deferral entries as seen in the G/L entries above. The text in the template here is “Sales Deferred for %4 %6”.

  • %4 returns the Month name of the period in which the entry is posted
  • %6 returns the fiscal year of the posting date

Other codes are

  • %1 = Day number
  • %2 = Week number
  • %5 = Accounting period name (from the account period table)

Custom Report Layouts in Word

Custom Report Layouts give me the fear. When I was freelance one of my clients asked if I could slightly amend their Sales Invoice print. Figuring that I could google how to do it, I agreed. Long story short: I royally screwed it up and somehow managed to mess up the invoice print for all the other companies on their tenant (and there were A LOT)

The silver lining to this story is that, when they called their Partner to sort out my mess, the Partner stumbled round like a bull in a china shop too before getting it right!

Anyway, one of my current clients needed a custom Reminder document so I thought I’d have another bash. I’ll be honest, it’s not an intuitive process. I found a few videos that were helpful but none that addressed the issue I wanted to solve, namely how to include a list of the overdue invoices, whether there is one or twenty.

So, here’s what I did, including my accidental solution!

  1. Activate the Developer tab in Word
  2. Download the layout in Word format
  3. Take whatever template your client has given you (including logos, layouts fonts etc)
  4. Clear the downloaded template and paste in your client’s template instead.
  5. Click on the Developer Tab in Word and then on the XML Mapping Icon

6. This will open up the XML Mapping pane on the right of the screen.

7. Click on the dropdown. Because you are using the downloaded word template (albeit you’ve overwritten it) the bottom schema on the list will be the schema that relates to the report. Here, I’m using the Reminders report. If you look in Custom Report Layouts you’ll see that this is report no. 117.

8. The schema offers a nightmarish list of fields that are available to you to add to the report. I found it useful to download the original again and compare the report it outputs against the schema properties

9. You can now start to see the values that are output versus the weird looking XML mapping codes

10. To start to populate your document you are best advised to use tables to position your data. As you can see below, I’ve used a table for the customer address and, lower down the document I’ve used another for the headers and lines of the reminder report. It’s actually quite a good idea to include borders on your tables until you’ve postioned everything. You can always remove them later.

11. Positioning the various XML Controls on the document is relatively easy.

      1. Locate your cursor in the cell of the table you want to populate
      2. Then right click the control
      3. Choose from the dropdown (I chose Rich Text because my client wanted to use a specific font)
      4. This will add the control at the point in the document where your cursor was.

12. The biggest headache I encountered was in trying to show multiple lines on the report. It’s actually easy once you know how but this is one of those things that the internet seems to be curious silent on. I stumbled upon this by accident. As you can see from the schema it’s broken down into indented layers. If you right click on one of the outdented “Headers” the Insert Content Control option gives you a different choice from if you right click on a “Line”. It says Repeating (rather than Rich Text, Plain Text etc). If you choose this it will give you a box into which you can fit other XML Controls such as document lines.

13. In my example I’ve got a reminder with three invoices all of which are overdue. As you can see, I’m only using a single line of XML controls but, because they are nested inside a Repeating control, they all come through. Without the Repeating control the report would only return the first line on the reminder.

So there you have it. To use the report go to the Report Layout Selection, chose the report and change the Selected Layout field to Custom Layout.

There’s an ellipsis (…) in the next field, Custom Layout Description, Click on that and pick the layout you’ve designed.

Play around with this on other reports and see what you can come up with.

Fixed Assets

I set up Fixed Assets in Business Central like a man walking barefoot across a floor full of Lego. There’re so many things to remember I nearly always painfully trip up.

Having said that, the first time I did it, it took me nearly a week. This time it was less than a day. There’s something to be said for repeating a task until you can’t get it wrong.

Here are the big bits of Lego that you might want to avoid.

There are sooo many things to set up before you can do anything fun like load the assets and transactional data. Posting Groups are always the obvious thing so start there. Lots of companies will have separate GL codes for different types of FA. I’ve always set up a Posting Group per FA Subclass Code meaning the two codes will be the same. Like all Posting Setups the FA one looks complicated but (depending on your client’s Chart of Accounts) it can be pretty straightforward

You could waltz off now and load the Assets but don’t. Finish off setting stuff up otherwise you’ll end up stumbling around going back and forth. Your check list looks like this:

  • Depreciation Book
  • No. Series
  • FA GL Journal
  • FA Journal
  • FA Journal Setup

Deprecation Books let you establish different methods of depreciation. Should you need to you can have more than one per each Asset. I’ve never encountered a situation where it was necessary, so I normally set up a single one called COMPANY. There’s a ton of stuff that you could use for fine tuning here and it’s worth spending some time checking it all out. My go-to settings are

Default Final Rounding Amount is the amount you’re prepared to allow BC to scoop into the final depreciation so as not to leave small amounts left to be depreciated in the final depreciation of the Asset. With these settings I’ll leave anything over £10 to be depreciated next time. However, my current client owns a lot of expensive plant and so I’d set theirs much higher as the sums involved monthly are much higher.

You’ll notice that all the GL Integration settings are off. This is crucial when loading all of the opening balances. If you’re intending for all of your FA postings to simultaneously post to the GL you’ll switch these back on before you post your first depreciation. Similarly, the Allow Changes in Depr. Fields is switched on. It’s almost a nailed on certainty that there will some issues with the dates supplied by your client and you’ll need to be able to change things.

You’ll need a No. Series for Fixed Assets and for FA Journals. If you’re using the original FA’s numbers from the legacy system set the number series to Manual Nos. and uncheck the Default Nos. (but switch back afterwards)

The two Journal templates will need to be set up but don’t panic this is just the same as setting up other journal templates. However…there’s one other thing you’ll need to do if you want to avoid this message:

This weird little blighter is hidden deep. Don’t use the Tell Me box to search for it. That’s exactly what they’re expecting you to do. No, you have to go and look here

You have to say which journal templates you’re going to use where. If you don’t do this, literally nothing you try to do that involves posting a journal will work and it’ll be horribly frustrating.

OK. You can load stuff now. You’re going to need two Configuration Worksheets:

  • Fixed Assets 5600
  • FA Depreciation Book 5612

The first is nice and easy. You’re just loading the basic details of the Fixed Assets, Number, Description. You can add dimensions here too. And don’t forget the Posting Group. If you’ve followed my advice from earlier this’ll be the same as the FA Subclass.

The FA Depreciation book is a different matter. This is where you dictate how the Asset will be depreciated. You can decide on the depreciation method. I’m not going to lie: I’ve only ever used Straight-Line and Manual. Straight-Line does what it says: if you’ve got £1200 to depreciate over a year, it’ll depreciate at £100 a month. Manual, on the other hand, does what it says. It skips the depreciation and allows you to set the amount yourself. If you have assets that don’t need to be depreciated (Land is a common one), use Manual as the depreciation method.

You’ll also add the acquisition date (when the asset was bought), the Depreciation Starting Date and Ending Date. This is where the exercise can sometimes go awry when you’re doing this for a client using data from their legacy system. You need the Start and end date so that BC can calculate the number of depreciation months and years. Don’t be tempted to put these into your configuration worksheet: BC will ignore them and you’ll go mad loading and reloading the data to try to make them appear. Well…you do if you’re me anyway. My chronic inability to remember this is the reason for this blog post!

The trick is to do a bit of excel jiggery pokery to calculate the necessary end date. But watch out for leap years!

The Fixed Depr. Amount is there to save your bacon when the client’s dates and amounts don’t deliver the depreciation they are expecting. You can use this field to set the monthly depreciation to the required amount. You might have to go back and play with this after you’ve loaded everything and run your first depreciation.

Now it’s time to load the figures. You’re trying to get back to the NBV for each asset. You need to do this as three journals: Acquisition Costs, Depreciation to the last Year End, Depreciation YTD. If there’s been any Appreciation or Write-Downs it might be worth including them on a separate journal.

Once everything’s loaded and the NBV’s match the source data, run a test Calculate Depreciation to check everything’s in order. If it’s not check back – I bet it’s to do with dates. When those are fixed and you’re happy with the results remember to turn the GL Integrations back on and the Allow Changes in Depr. Fields OFF before posting your first round of Depreciation.

And there you have it. Fixed Assets for beginners (and me for the next time I do it)

Year End: Or…How to Close the Year and Close Income Statement

The process of Closing the Income Statement in Navision or Business Central (or Year End as most of us normal mortals call it) creates a journal which debits all the income and credits all the expenses in the P&L and creates a balancing entry in the Balance Sheet, usually the GL for P&L Brought Forward.

Year End can be run as many times as you like which enables you to run it both at your actual Year End and after posting adjustments following an audit. The screenshots here are all taken from NAV 2019 but the commands, menus and principles are all the same in Business Central.

Go to “Accounting Periods” and highlight the last month of the financial year you wish to close

  1. Click “Close Year”

  2. This locks the month end dates so that they cannot be altered

  1. Now go to “General Journals” and create a new template as shown

  2. In Chart of Accounts click on “Close Income Statement” and fill in the pop up as shown
  3. In the “Document No” field fill in a doc no that will distinguish your year end postings from your other General Ledger postings.
  4. In the “Retained Earnings Acc” field, click the dropdown and chose the General Ledger (GL) code you want to save your retained earnings to (usually P&L Brought Fwd)
  5. Under “Posting Description” number the field so that you can distinguish between journals if you need to run it more than once
  6. Populate the “Dimensions” field with your default dimensions again, to allow for ease of navigation later. It’s important to remember to do this as failure to do so can lead to reporting problems down the line.
  7. Click “OK” and this will create the journal.
  8. Check the journal and post.

The journal has now been posted and is dated with the year end date prefixed with a C

This denotes a date – in the example shown – between 24:00 on 29/10/17 and 00:00 on 30/10/17 and can be used in filters

Steps 1 – 4 only need to be performed once each year. If you come to do a second year end (say after audit adjustments have been posted) then you can start at step 5.

Note: it’s useful to make a note of the last G/L Entry number so you can easily identify entries that have been posted back into the year after this process has been run.