Periods in Microsoft Dynamics NAV 5

Business Central/NAV Developers Forum
1

Hi there,

I am in the business of extracting data from several accounting tools so they can be represented in our software tools. Right now I am working on a Navision (what we call a) collector.

I am a bit puzzled by Microsoft Dynamics NAV 5. I see a periods table named …$Accounting Period. The periods have no numbers (but who cares anyway). Anyhow, the bookings are located in the table named …$G_L Entry. In this table I don’t see any reference to a period in which the booking took place.

Furthermore, we were able (in our test application) to create JournalEntry bookings in 2011 … without a previously created financial year and periods! How is this possible? Is this periods table totally useless? Should I rather extract from date to date than from period to period?

[:|]

2

You need to read the NAV documentation on Financials. I don’t think you’re a customer or partner so you will have to buy it from Microsoft. This will help you a lot and answer the majority of your questions.

The G/L Entry has a posting date. The accounting period has a starting date. From there you should be able to tell what accounting period it was in.

3

Hi,

You’re not alone D.J. Lachmann. I was a bit confused about this accounting period too. I use NAV 5. I see some uses of accounting period table especially when we run reports based on date filters. One thing that confuses me is the fact that the accounting department is able to post transactions out of the existing accounting period. For example, if your current accounting period runs from Jan 2010 through Dec 2010 and if you have not opened a new accounting period for year 2011, then how is it possible for transaction be posted into Year 2011. That’s what I came to find out recently. My expectation was that NAV ( like any other accounting systems such as Oracle Financials or SAP) would not allow transactions be posted out of the existing opened accounting (fiscal ) period.

Any reason why NAV is allowing this to happen or am I missing something here?

Thanks,

4

Yes, you can post to future, un-created accounting periods. You cannot post to closed accounting periods. The most likely reason being that while the periods don’t have end dates on them, the last one in the list will technically run from that start date until the end of time. So December 2010 to December 2999 or whenever.

It’s actually a really easy modification to not allow this if you want your NAV partner to do it for you.

5

Well, that would be a good thing to do, I guess. I’ve seen journal entries entered in the system like long years ahead because of transposition error( like instead of typing 2011 you could mistype 2041). I’ve got Application Devloper license and test database and would like to know how to fix it. Would you please share some idea?

Thanks,

6

Kef,

In GL Setup there are 2 fields - Allow Posting from & Allow Posting to. Moreover, it’s extended to User level in User setup.

In practice I have seen that most companies set these dates current +/- 1 month, the rights to change this have very limited number of users, and if someone needs to post a transaction outside the set up limits, s/he must contact the manager or even CFO in smaller companies. So you avoid even elementary input errors in posting dates, not to mention intentional misuse.

And yes, Accounting period as separate field does not exist in Navision by design. When I started working with Navision approx 10 years ago, I was confused, too, but over years I’ve got used to it…

7

Yes, the best way is to accurately set your allowed posting dates in the G/L Setup and User Setup tables. It won’t prevent you from going past the last accounting period, but it will prevent you from posting 20 years ahead [:)]

8

Here is how I see it. Accounting Period table tells the system which periods are open for posting. When you create a new accounting year, you are basically provide the system the start date, no of periods and period length. I see these inputs as constraints as to how the system allows postings. Then, within the framework of opened accounting period you can tell the system through General Ledger(G/L) Setup which specific periods (for instance months ) are open for positing. In other words, “Allow Posting From” and “Allow Posting To” dates must strictly fall under the opened accounting period. Any attempt to set a range beyond the opened accounting period should be rejected. Finally, you can set another layer of posting constraint through User Setup by telling the system which employees are allowed to post including the beginning and ending of posting period. Again, here we have to assume that the “Allow Posting From” and “Allow Posting To” dates must fall under the next higher level (G/L Setup posting dates).

Based on my accounting experience, companies need to have utmost two or three accounting months opened at a time just to comply with the matching principle of accounting. Accruals and adjusting entries can be used to deal with the timing issue.

In my opinion, any attempt beyond any of the opened accounting periods should be rejected by the system. Looks like the basic structure is there but the business logic is not fully compete. I hope Microsoft Engineers will look in to and fix this issue the coming releases.

Thanks,

9

That’s your interpretation of how it should work, but it might not be the same for other companies. It’s working great for tens, or even hundreds, of thousands of companies. I can almost guarantee that this will never change with NAV as it is how it has been working for versions prior to when I even started with it.

I only partially agree with you. I think the original point, that you should only be able to post to a date that has a valid open accounting period makes sense. I agree that the allowed dates should have to fall in an open accounting period.

I do not agree about the User Setup dates having to fall within the G/L Setup dates. But again, this is all enforceable out of the box, you just don’t get an error message. If you’re careful about setting up your dates every month and have proper year end closing procedures it’s quite easy to make sure everything is setup correctly. And if your company needs it to work differently, with error messages, it’s a pretty quick customization.

10

This would be too restrictive, and cause problem sooner or later for many users, however, IF some client requests such functionality it can be easily coded, as Matt already said.

In my practice there have been many cases, when user requests to implement some strict & non-overridable rule --not only this one under question, different ones-- and then asks to how to remove it just for this one particular case…. Common cause - this exception happens once or twice a year, we didn’t mean it to be absolutely forbidden, just want to have full control over that one rare case when we still need it. Problems start, if the system is not so easy customizable as Navision - if it is hard-coded rule, it takes time to redesign - that for I always try to avoid highly restrictive business rules whenever possible and explain to customer the consequences which may arise.

Returning to main subject - Periods. Those are needed in paralel with date, because there are at least 2 “fictitious” periods, which can’t be squeezed into a date-type value, and they are:

FY_CLOSE - here goes not only Income statement closing transactions, but many more - e.g. FA & Inventory revaluations to current market values, write-offs of hopeless customer debts, etc etc. Navision’s “C-dates” solves only the first type…

AUDIT - everything that is changed on the request of auditors AFTER unaudited balance sheet is prepared. In many countries legal regulations demand easy reproduction of both balances - “raw” and audited. Navision does not meet this requirement at all.

A modification to include Periods and their correct handling throughout the system is possible, but is a HUGE, read expensive, redesign. Moreover, later upgrade of such mod to next Navision version will be much more complicated job, as with any highly modificated system, and here core functionality is modified, not just added some new functionality.

BTW - do you know, how “C-dates” are stored in SQL base? Normal have time part 00:00:000, C-dates - 23:59:999 [:)]
And “empty” dates actually contain 1753-01-01, as ALL fields in ALL tables have NOT NULL constraint…

11

So … what you are all basically saying, is:

  • I should extract the data and (if no periods are created for the timespan to be extracted) look at the month of the entrydate to ‘assign’ it a period.
  • So … if there are no periods created for a certain timespan everything in January ‘lands’ in period 1, everything in February ‘lands’ in period 2, etc.
  • But … if someone later on decides to divide the year into 4 quarters or 1, 2, 3 or 4 weeks-periods my next extraction over the same year looks like rubbish compared to the previous extraction.
  • And … if someone started his business in April, it will be period 4? I can imagine the IRS wanting to know where periods 1, 2 and 3 went!
    OK, I’ll make some intelligent code and renumber it to be period 1.
  • But … now he discovers he forgot to enter certain transactions that have be placed (for whatever reason) before April. After doing so he extracts again and suddenly period 1 of the previous extraction is now period 2 of this extraction!

You all really got to be kidding me. If it took you all quite a while to get used to it, it can’t be a proper solution!

Software should be easily to comprehend and to operate. If you create software of the kind of ‘you’ll get used to it’ you’re quite lousy at your job. If I would do it and defend it, I could right away clean out my desk and go home.

So … if no one can offer me a case in which no predefined periods are actually a benefit, I still declare it to be extremely odd (to put it mildly) and in my book it should have been solved a looooong time ago.

[:S]

Once again: I am in the business of extracting data from several accounting tools so they can be represented in our software tools.
So … I am always busy to make all the crooked datamodels fit into our (maybe just as crooked, who am I to judge) datamodel. I don’t have the liberty to alter settings on the instances of our clients, even presenting suggestions is way out of my ballpark. So … I have to put up with all the strange brainfruits accounting programmers come up with … but this one really takes the cake (so far).

If I sounded too strong and harsh I hereby offer my apologies [:$] … but I just can’t wrap my head around this.

12

Noone says it IS a benefit - has never been! Here I totally agree, reread what I wrote above… And I am in ERP business for ~25 years now, from which in Navision are only the last 10 or so.

What I did say, was that if normal periods were not included in Navision from the very begining, it’s too late to do it now, at least as a modification to base product.

When MS acquired all 4 ERP systems years ago (Great Plains & Solomon in NA and Navision & Axapta in Europe) their plans were to make ONE joined product - but who remebers now Project Green? If there are hundreds of thousands of installations, it’s impossible to make radical changes in next version of product - ERP system must maintain bacward compatibility with data accumulated over years, it is not as a new version of game where nobody bothers, and the newest technologies are easily included.

Getting rid of native database would allow to use MSSQL at full blow, now Navision uses half-percent of SQL capabilities to maintain compatibility, but only with upcoming v7 MS at last will RIP the Native. And this is only the first step - C/AL is so tied with Native it should be replaced, too, which actually means it is a NEW product, not just next version. What do do with existing clients? And Partners, who will need both to maintain earlier product for those clients who are not willing to upgrade and hire / train a second “set” of staff to deal with the new version?

Well, this my “essay” has gone far off-topic already - it’s rather about problems every large enough software development project meets sooner or earlier, as technologies change faster we can catch up with them…

13

And a separate answer closer to topic [:)]

almost 100% granted IF previous defined periods were “monthly” AND FY begins on 1st of Jan

such decisions can not be made neither backwards nor in the middle of FY, only starting a new FY, and even then are rare, as they usually match taxation reporting periods or accounting periods of mother company, if such exists, and those both are rare to change.

on the first business year you can easily start with, say, 4th period if your first business day doesn’t match your intended FY start, which almost never happens, especially in countries where having FY # CY is allowed only in special businesses (e.g. agriculture or like). IRS understands that easily - as they are the authority then who regulates WHEN your FY starts.

then it will become one of those special “fictitious” periods I already mentioned, let’s call it OPENING - everything that is done after incorporating and before starting operations. BTW it can span quite a long time - IRS considers you being “dormant” and if properly reported, over this period reporting to them is very limited.

14

So …

Suppose I have to extract the data of a new client who has been working for x years without pre-defined periods. I run into lots of troubles due to the lack of periods. Can I ask my client to create periods afterwards? Can (s)he still create periods in previous years?

If so, will it have an effect on the behaviour of the Navision tool?

15

Actually I haven’t seen a case, when these “periods” as Navision understands them are NOT created - usually they are, and normally one FY ahead of the current one.

He can create them in previous years, one by one in hronological order, as actually nothing depends on them - although there is a function on Periods form, that “closes” the FY, that actually means nothing more than marking it “closed” in that same form (underlying table).

Income statement closing is completely different procedure, you even CAN POST to such “closed” FY, if not restricted by PostingDate limits in setup, but that you know already…

But question is - is it necessary? Anyway you’ll have to use your own methods to add period info to the extracted data in the way you need for your purposes - what difference it makes from where period boundaries are fetched - that Navision table or some source in your systems?

16

My problem is that we deliver an extraction tool that handles several accounting software tools like:

  • Exact
  • Axapta
  • JDE
  • Kirp
  • Navision
  • Oracle
  • SAP
  • and many more locals

In order to prevent incorrect input data we look at the period table. That way our users can only extract years that were actually created in the period table.
Furthermore the user can only choose from the periods that were actually created. So, if a FY has 12 periods (s)he can never extract from period 1 → 13. This is all generic code and works for all the above mentioned accounting tools. Deviating from this standard only for Navision is something you always want to avoid if not really necessary.

When recreating my own period table by code I encounter several hurdles:

  • Suppose FY 2010 has no periods. To recreate a period table by code I am obliged to look to a previous year (if there is one) to see what periods where used there. Where they quarters? I’ll have to extract 2010 in quarters as well. Where they two weeks periods … etc.
  • If I recreate a period table by code and there was no previous year it does make the most sense to make it a 12 period FY. If my client (for whatever reason) decides to create a period table after all and makes it a quarterly FY my previous extraction will look like rubbish compared to the new one. The customers conclusion will always be: “The software that shows rubbish is the one that is wrong”. By the time I figured out he created a period table afterwards I’ll have a large phone bill, an irritated service desk and no one to present the bills to.
  • If someone closes shop from January till February (totally overhauls the place or whatever) and restarts accounting in March and there are no periods created for this FY my initial code would suggest it is period 3.
    My slightly improved code would be so smart to look if there were transactions in January or February. It wouldn’t find any and therefore suggest March is period 1.
    But … the company has been around for x years previous to the overhaul. So, my even more improved code would check for a transaction in a previous year. If there is (at least) one March would be period 3. If there’s none it would be period 1.

All in all it creates loads of loopholes that have to be considered and need appropriate actions. I’ll bet my … on it I can’t foresee 'm all.

So, all in all: Asking my customer to create a period table with each FY in it saves me loads of programming and testing, possible safeguards my customer form loads of irritations, makes my product more reliable and saves my image & skin. Sounds like a good reason to add the following notification to my manual: “In order to use this product properly each financial year that needs to be extracted needs to have a definition of its periods”.

Wouldn’t you say?

17

Well, well… and here we are again, made a full circle & back at starting point [:D]

Long time ago when I was a programmer myself, I’ve stuck a couple of times like you now - code suddenly stops working, because we haven’t taken into consideration ALL possible & likely impossible cases…

With great difficulty you have squeezed Navision into your processing algorithm, as it differs from majority of other systems, and then there comes the 100 and 1st guy who didn’t bother to fill in some data which is of key importance to your code, but practically useless for him, so he left it out. Even worse, now he blames YOU to be guilty in all seven sins…

We all laugh about sometimes silly do’s & dunot’s in different user/consumer instructions, but this is how they are born [;)]

This exact case is at least curable, let him create those poor periods… As I wrote already, majority of Navision users DO fill in this info, there is minimal possibility that you’ll get another such client again - but who knows, so the warning wouldn’t be excessive, too.

That for it is said that only 5% of code deals with the main task - other 95% processes unexpected what-ifs, starting from typing hello in a date field to unbelievable, but still theoretically possible combinations of many parameters…

18

What a great thread this is. I haven’t laughed so much for weeks. Imagine a thread like the following to see how I read it.

The worst this is we eventually find that the poster has never driven a Ferrari, he has never sat in one, he has never even seen one driving down the street. All his experience on Ferrari is based on looking at the engine and nothing more.

Step one, buy a Ferrari, step 2 learn to drive the Ferrari, step 3 start to take the Ferrari apart and then relate how the engine fits with the rest of the design of the car, Step 4 start making improvements.

19

Boy! Did you crack me up!

Your interpretation just sounds quite like what Ferrucio Lamborghini said to Enzo Ferrari after driving his first Ferrari. Enzo (like you) didn’t agree with Ferrucio and challenged him to do a better job … and boy!, did he show him!*

You are probably the kind of person that uses everything just the way it was designed. You probably don’t think one inch out of the box, think about usefulness and user-friendliness. I agree that inventing the wheel all over again isn’t very useful but what makes you think that the wheel as we know it now is really at the end of it’s evolution?

If you would buy a Ferrari and you got an audio system with it without the speakers, wouldn’t you be flabbergasted and amazed? Maybe even slightly annoyed or irritated?

By the way - not that it is of any useful information - I know how a Ferrari drives.

So what you basically say is: If you want to be a mechanic just analyse the object to the fullest. I agree but in this case I am not the ‘mechanic’ of a Ferrari but of a Lamborghini, a Mercedes, a Fiat, a Hyunday, a Land Rover, a Volkswagen, a Triumph Bonneville and a Ducati as well. Sorry but I couldn’t buy, drive, dissect and rebuild 'm all. Excuse me for expecting all the pedals in the same configuration. Excuse me for expecting them to all have a steering wheel (of some sort). Excuse me for …
I guess you got it by now.

Thanks for the (NOT) constructive response.

  • True story, really happened