How to Deal With NSF Checks in NAV (Without Using Reverse Transaction)

I was at the NAVUG user group in NYC last week. There, I learned that NAV2017 now allows users to cancel posted sales invoices and sales credit memos. While I’m sure some users will welcome these changes (and I’ve yet to see exactly how they’ll work), my first impression is that this isn’t such a great idea.

Part of my reasoning is that, honestly, I believe that people who “pay for their mistakes” are more likely to stop making them.

But I also hesitate because I don’t like reversing transactions in NAV more generally.

Using Reverse Transaction in Dynamics NAV

NAV has at least three “reverse transaction” options: General Ledger Entries, Customer Ledger Entries, and Vendor Ledger Entries.

When a customer bounces a check, you could usually reverse that in the customer ledger.

To better understand my hesitation about reversing transactions, let’s look at a payment reversal as an example.

Let’s start with payment 2596 (below) in the Dynamics NAV Cronus USA database.

Click Applied Entries to see where this payment was applied:

And here’s the invoice:

So, we see this payment was applied against one invoice.

Let’s see what happens when we choose Reverse Transaction:

This is what shows up:

As you can see, there are three customer payments on the same date with the same information, which is confusing.

But the key issue is that your only option to continue is by clicking “reverse.” You can’t control the posting date—it automatically goes back to the original date of the payment. This creates several issues:

  • If you have proper date controls in place, you can’t post the entry at all. This means reverse transaction only works when the period is open. (See my post Month End Closing in Dynamics NAV for more on this.)
  • Even if you’re in the same period, report numbers will change in unexpected ways.
  • At best, you’ll need to have two different solutions.

My Alternate Solution for Dealing With a Bounced Check

Here’s my suggested method:

  • Choose the appropriate payment and click “Unapply Entries”:

When you do, this page appears:

Strangely, on this page, you can control the posting date. (Even though from an accounting standpoint, Unapply Entries doesn’t effect any ledger balance, while Reverse Transaction does.)

Click “Unapply” at the top of the page.

Now, the payments (and matching invoices) show as unapplied:

After we’ve unapplied the entries (so the next good check from the customer can be applied correctly), we can “refund” through a cash receipt journal and “apply” this refund to the now-open payment.

We create the refund here:

Then, we click Apply Entries and get to this screen:

We can use this process at any time—regardless of when the customer check bounced.

A side note: I somewhat accept that reversing general journals isn’t the worst thing if you’re doing it to correct an error on your part. However, I’ve had many occasions where people ask, “Why did this report change?” when someone reverses an entry just a little too fast.

What’s your process for dealing with NSF checks?

 

Lawson and Dynamics NAV

Since 1995, I’ve worked extensively with Lawson software. Until this month, that is, as my last Lawson client is moving into Dynamics NAV.

I’ve learned a lot about NAV over the last few years. And while I do miss Lawson at times, Lawson’s time has passed for my core clients (who aren’t in health care or government).

Why My Clients Have Moved From Lawson

Before I explain, note that my observations about Lawson are largely based on my for-profit clients in the greater NY area. Generally, these clients fall into two large categories:

  1. Companies that grew into the multi-billion dollar category

As these clients grew, they saw no real added functionality in Lawson and have therefore moved to SAP and Oracle mostly. This move was often driven by the desire to consolidate on a single ERP—and they didn’t feel Lawson had the functionality they wanted or a strong interest in meeting their needs.

  1. Mid-market companies for whom Lawson got too expensive

Many of these companies bought Lawson in the mid-90s/early 2000s to run on Unix and/or AS400 systems. At the time, Windows/SQL server solutions didn’t have the power to run $100 million dollar companies (or if they did, they needed an awful lot of support).

But over the years, Lawson’s maintenance grew and grew—to the point where I’ve seen companies cut maintenance costs by over 80-percent by moving to different packages. The crazy thing is that Lawson will cut deals with new customers or re-licenses, but they won’t do the same for clients on maintenance. Then again, even if existing clients could get a deal, the costs in the Lawson ecosystem would probably still be higher than in the Dynamics world.

As a Dynamics NAV Consultant, I can say that Dynamics NAV is not a good fit for companies in the first category. (I do know some former Lawson consultants have moved clients to Dynamics AX, but I don’t know enough about AX to comment.)

I will say that, in recent years, Lawson made some changes to make it a little more user friendly. (My experience with Lawson runs from version 6.0 to version 9.0.1—or thereabouts.)

Where Dynamics NAV Beats Lawson

  1. Easier data entry

I base this statement on the experience of a client who recently moved from Lawson to NAV. Fundamentally, Lawson is based on a batch process developed over 30 years ago. And consequently, many processes require multiple screens and steps. If you’ve been using Lawson for a long time, you may not even notice some of these steps (e.g. Bl121, Bl122 and Bl123 to post an invoice).

But when these steps go away—and all you have to do is push “post”—life is good and the time savings are real.

  1. More flexible account string and better online analysis

Lawson has been promising a more flexible account string since 1998. (I recall hearing it for the first time at a sales conference presentation.) Other than Lawson’s stillborn strategic ledger, nothing much happened. Of course, you can get creative with attributes, activities and account categories in Lawson, but it’s challenging. And you have to build lots of custom reports to get the data out.

NAV Dimensions, in contrast, is much more flexible. And NAV has many more built-in tools for analysis that really work.

  1. Jet Reports for financial report writing

Lawson’s RW100 Report Writer seemed like a great tool once. But that was over 20 years ago. While there are many levels to Jet Reports, even with the basics you can build your financial report in Excel. Yay!

(For my thoughts on the best uses of Jet Reports vs. SSRS, see this post.)

  1. Manufacturing and warehouse functionality

Most of my Lawson clients only needed financials and procurement. And Lawson was fine for that. But even though these clients weren’t full manufacturers, they could have sometimes used more advanced warehouse or manufacturing pieces. These pieces are included with NAV but were never part of the Lawson package.

  1. Simpler administration and installation

Over the years, folks in the Lawson world came to expect ever more complex installs. Between WebSphere and Tivoli, a basic install took days (and sometimes even a week) per server. And a lot of this was for stuff that really only benefitted big users.

A Dynamics NAV install is basically a day’s work. Not coincidentally, the skills you need to keep the back end going are also easier to train or outsource.

  1. Built in workflow

Standard Lawson had some basic ways to control workflow. With security, for example, you could allow some folks to enter data and others to post batches. But that’s not real workflow.

NAV comes with workflow. And as more companies try to get away from paper, NAV provides a solid starting point out of the box.

  1. A larger third-party ecosystem, at less cost

No ERP software gives you everything you want. But because NAV is so widely installed, it has a large variety of third-party ISVs to fill the gap. In addition, where $25K-$50K was the typical starting point in the Lawson world, many NAV solutions are a fraction of that.

Where Lawson Beats Dynamics NAV

But all is not sweetness and light. There will be days when you miss Lawson. This isn’t a matter of having to “just get used to something new.” Rather, some things in NAV don’t work as well:

  1. Lawson Add Ins for MS Office

Lawson Add Ins, and its backing technologies, are the best Excel integration tools I’ve seen. And I say that based on my experience with Lawson, Oracle EBS, Dynamics GP and Dynamics NAV. Yes, third-party options are available. But Add-Ins is just a wonderful tool.

  1. The ability to maintain multiple balance sheets in one company

NAV is based on a separate data sets for each company—which is fine if you’re a mid-sized company that doesn’t have many entities. But if you’re used to paying bills for multiple entities out of one company, you’ll need to seriously look at your process or consider third-party products that build on NAV capabilities.

Are you on Lawson and considering your next steps? Or have you made a decision to move and are in the process?

 

AR Revaluation Report – Dynamics NAV

In my last series of posts, I wrote about NAV currency setup, entering foreign currency sales invoices and how adjust currency amounts can be run at period end to create gain loss entries.

In that last post, I promised to show you a sample report that would allow you to see the gain loss before you actually post it.

And here it is.

Aged Accounts Receivable in NAV

The Aged Accounts Receivable report in NAV has the option to print results in the customer’s currency:

If we run this report for Beef House (49525252), and select Print Detail, we see the following:

We can see the transaction in euros as well as a report total in USD. Which is all good.

However, it’s important to note: The USD total reflects the current rates as of the cut off date for the report whether or not we’ve actually posted gains and losses to the ledger.

How do I know this? Let’s start by looking at the rates. (I had to add a rate to the system to create the example.) Checking the exchange rates, we can see that the system should choose the 1.4 rate because that’s the latest rate before the cut off date.

(If you do the math, you’ll see that indeed 30315.02 /21,653.58 = 1.4.)

However, when we look at the customer ledger entries, we something else.

For example, let’s take the first invoice (103005) and look it up in the Customer Ledger. If we multiply 1,245.92 x 1.4 we’d expect to see 1744.33.

However, if we look at the local currency amount (marked misleadingly with a $ sign) we see that it’s 1541.20.

Which, if you do the math, is a rate of 1.237, which is the rate as of 1/1/2016.

If we choose Detail Customer Ledger Entries, we would find there’s no revaluation for the transaction. (For more on this, see our post Understanding NAV Currency: Part 3 Month End Revaluation.)

Red Three Open AR Unrealized Gain/Loss Report

As part of my forthcoming set of free SSRS reports for NAV, I’ve created a report that shows both the current value (which should tie to the ledger), the prospective revaluation, and the prospective gain and loss.

For example, here’s a small screenshot for Beef House:

You can see the current calculation (1.237) and the new at 1.4 (which ties to the NAV report).

I’ve built this report using my shared database view.

I’m in the process of putting all my code together, but I’ve also included it below.

Let me know if you’d like to have the stored procedures and/or the SSRS report when they’re ready. I’m hoping to have them put together by mid-December.

 

Using Totaling Accounts with SQL in Dynamics NAV

If you’re like many of my clients, you didn’t clean up your chart of accounts when you installed your new ERP. Or maybe you did but then your business changed. So, instead of having a nice set of account ranges, like those in the sample Cronus companies, you need to create odd groupings of accounts.

Totaling Accounts are your first option in NAV. And they work well. However, if you want to use them to write reports using SSRS or another SQL tool, they aren’t that easy to work with. But I’ve built some code to make it a bit easier.

A Simple Example—Using “Or” and “BETWEEN” in SQL

Let’s start by looking at the chart of accounts in CRONUS USA, Inc. (I’ve applied a filter to show the income statement.)

Like most sample companies (and unlike most of my customers), the data is beautifully organized with total accounts having nice ranges.

Scrolling down a little, we find the total range for the revenue accounts:

To use this range in SQL, we use the command “BETWEEN,” as in the following query:

And we get the following results using SSMS:

That was pretty easy.

Another easy conversion of totaling accounts to SQL is with the “|” or “OR” command.

Let’s say we wanted to create nonconsecutive totaling accounts of 41100 and 43100. We can easily do that in NAV. (To keep the chart clean, I placed the totaling accounts at the bottom, starting with an alpha character):

(If you end up using a lot of totaling accounts, you should nail down some naming conventions.)

Converting the “|” sign to SQL requires just an “OR” statement

And the results look like this:

The Full Solution

The problem with these examples is that they only work on a “one off” basis. And usually, you need more than a few totaling accounts when you get started.

Here’s an example of one totaling account for customer who has almost 70:

750532..750544|760450..760950|900040|750547

This requires a combination of BETWEENs and ORs. Which isn’t something you want to do every time you write a query. Further, with this kind of mess, you often want to see what you’ve included in your ranges—so you can compare and see if you’ve duplicated accounts in the account schedule. (Something I’m planning to do in the future.)

List all Accounts in Totaling Accounts

Fortunately, there is a way to list all accounts in totaling accounts. Here’s a sample report that I built:

The SQL here is pretty complicated. It was an interesting challenge as T_SQL isn’t the best string manipulation language (which generally isn’t a problem).

I created the stored procedure using my “multi company view model.”

I’m planning to include this procedure in my forthcoming set of free SSRS reports for Dynamics NAV.

Here’s the code:

Save Totaling Accounts for Future Reporting

While it’s nice to have the listing, I also like having all my totaling accounts ready to use for any other SQL reporting.

We can adjust the above SQL to update a permanent table that we can use again and again. The table is called TotalingPosting. I use it with my other queries as part of my overall reporting solution, and it allows me to create reports over multiple companies.

And once you have the report, you can create SQL based on totaling accounts in NAV.

For example, I populated a TotalingPosting table for Cronus US. Now, I want to run a report to show all GL Entries for total account 49950.

(Again, the Cronus setup is remarkably clean so it’s not all that realistic. But it will serve our purposes for this example.)

A query of TotalingPosting looks like this:

And returns:

PostingNo_

41100

41200

41450

42100

42200

42300

42400

43100

43200

43300

43400

44100

44200

44300

44399

44400

45000

45100

45200

Now I can build a simple query against my GL Entry table by joining to the TotalingPosting table. (Note: This kind of simple join will only produce correct results for one total account at a time.)

If you have any questions, please let me know.

If you’d like the complete code set when it’s ready or the SSRS report, let me know that as well.

 

Understanding NAV Currency, Part 4: Receiving Cash Against a Foreign Currency Receivable

In my previous post, I showed how to perform a month end revaluation in Dynamics NAV with an unpaid invoice. In this post, I’ll show you how to receive cash against a foreign currency receivable.

To recap: We have an open invoice that was worth 15000 MXP when we invoiced and 17250 MXP when we closed the month. The peso continued to fall, and it’s now worth 20000 when we receive the cash on November 20th.

We start by going to Cash Receipt Journals and make a receipt for the full amount, in this case 1150 USD.

We’ll use BO-USD which is our USD bank account. We’ll use account 01454545, which is the bill to account for Spotsmeyer’s Furnishings.

We first create a batch, called CURRTEST.

We add the receipt:

The key fields are:

Posting Date: 11/20/16 (to prove the change in rates)

Document Type: Payment

Document Number: TEST123

Account Type: Customer

Account No.: 01454545

Currency Code: Defaults to USD

Amount: -1150.00 (because of how NAV handled positives and negatives)

Once we have the entry, we click “Apply Entries” (see yellow arrow in above screenshot).

Once applied, our invoice is the first to appear:

We highlight the line and click “Set Applies to ID” (see yellow arrow above).

Once we’re done, we can simply click OK and return to the previous page.

Before we post the entry, let me point out the options box next to the currency code:

This options box allows adjustments to the exchange rate.

So now, let’s post the entry and take a look at what happens.

First, let’s look at Customer Ledger Entries, filtering for 10/01/16..11/30/16:

We see both the invoice and payment. And the amounts—which represent USD—match. Excellent.

Moving right, we see that Amount ($)— which represents the local currency MXP—also match. Also excellent.

But what happened to the gain and loss?

We can drill into Detailed Ledger Entries for the invoice:

We can see that the unrealized gain was reversed. But where is the realized gain? (We expect it to be a full 11500, twice the unrealized gain.)

Let’s look at Detailed Ledger Entries for the payment:

We see here that the payment was valued at 23,000. The offset goes partly to the original invoice and partly to realized gain. This makes sense.

If we look at General Ledger Entries, we can see related information:

For our purposes, we care about G/L 9330, which has the credit we expect for realized gain:

This concludes our series on basic NAV currency transaction processing.

I’m continuing to work on the unrealized gain report as well as issues of consolidation.

Again, please let me know if you have any questions.

 

Understanding NAV Currency, Part 3: Month End Revaluation

In my last post, I entered a sales invoice in NAV in a foreign currency. The invoice was posted on 10/5 and had a value of 1150 USD or 11,500 MXN (or MXP as Cronus has it, which still irks me).

Now, it’s month end. As the invoice isn’t paid, we need to perform a revaluation (as it’s known in standard accounting parlance). The NAV system uses the terminology “adjust exchange rate.” (For more on revaluation, see my posts on exchange and revaluation and revaluation and translation.)

Read more…

Understanding NAV Currency, Part 2: Entering a Foreign Currency Sales Invoice

In our previous post on understanding NAV currency, we covered the topic of NAV currency setup, including general ledger, currency and exchange rates.

In this post, we’ll continue the discussion by entering a foreign currency sales invoice in NAV.

As with our previous post, we’ll use Cronus Mexico for our examples.

But before we start, let’s set up our currencies and exchange rates to make our examples super obvious.

Read more…

Understanding NAV Currency, Part 1: Setup

I’ve done a lot of work with currency accounting from a systems perspective. Now that I’m working in NAV, I thought it would be useful to explain how NAV works with foreign currency amounts in a series of posts.

In this series, I’ll cover the following topics:

  1. General Ledger Setup
  2. Currency Setup
  3. Exchange Rate Setup
  4. Entering a Foreign Currency Sales Invoice
  5. Revaluation (i.e. revaluing the open amount at period end)
  6. Receiving Cash Against a Foreign Currency Receivable.

In this post, I’ll cover the first three topics: how to set up the general ledger, currency and exchange rates.

I’ll use Cronus Mexico for all my examples.

Read more…

Month End Closing in Dynamics NAV

If you know anything about NAV, you know it doesn’t really have a hard month end close. So, apologies for the somewhat misleading title of this post.

However, month end close remains a topic for discussion for folks switching to Dynamics NAV from other systems (like Lawson) that have a more traditional, hard period end.

Consequently, clients will ask for my advice on how to implement some level of period end control in NAV. (Yes, NAV does have a year-end process. But I’ll save that topic for another post.)

To help these clients, I’ll walk them through the following period end control options:

  • Accounting periods
  • Date control in General Ledger Setup
  • Date control in User Setup.

Read more…

Using SSRS with Dynamics NAV—Creating Views to Write SSRS Queries

Our consultants write hundreds of SSRS reports each year. Now that we’re working with Dynamics NAV, we want to use our SSRS and SQL skills without having to train everyone in the intricacies of C/AL development.

While C/AL uses SSRS to render reports, we only see value in going native C/AL when reports are tied to database updates or are a key part of a process—such as test reports or posting printouts.

As I covered in Understanding the NAV Database, one of the challenges in working with NAV is that each company has its own set of tables. And this is one area where C/AL comes in handy.

Take, for example, “Purch. Inv. Header” (for posted purchase invoices). Using the Cronus sample companies, we have three different tables:

When writing a query for SSRS, you can deal with this in several ways:

  1. You could write your report for just one company (which works fine if you only have one company).
  2. You could create stored procedures with Dynamic SQL. (Your procedure would take the company as a parameter and then build the SQL. It would be ugly.)
  3. You could create views of the underlying tables and write your queries over those views.

If you’re dealing with more than one company, the third option is your best bet. In this post, I’ll show you how to execute it.

Using Views to Write SSRS Queries

Let’s illustrate using the example above. Instead of using the tables, we create a straightforward UNION of all three tables.

In addition, instead of using full company names (with the ridiculous punctuation), we create abbreviated names for each company (e.g. Cronus USA –> CUS):

Now, let’s look at the output. We can see all the data, identified by the appropriate company:

Once we’ve built the view, we can treat it like a table. For example, if we build a view on the above called dbo.[vNAV_Purch_ Inv_ Header], we can write a query like this:

(Hint: Company choice is a parameter, not a table name.)

Building Views Automatically

While most ERP systems have hundreds of table, usually you only need a couple dozen views (at most) to handle the vast majority of your reporting needs. But that doesn’t mean you should build all those views manually. It’s too time consuming and prone to error.

(Further, we like to place these views in a different “reporting” database. That way, we don’t create objects in our NAV database that NAV doesn’t know about.)

To that end, I’ve created a simple stored procedure (below) that includes a list of companies and tables we can create views over. We can run this procedure any time, and the views will automatically be altered or created:

Views and Performance Issues

While I’ve used these views with good success, I haven’t tested them with large data volumes. I suspect that if you combined multiple companies in one report, performance could suffer. Unfortunately, UNIONS aren’t great for performance.

Solving the performance issue is beyond the scope of this blog post. It will require either an indexed view or perhaps a set of reporting tables to resolve.

One more note: If you look at Execution Plan when selecting a “company” from within the view, you’ll see that the number of actual and estimated records is the same—whether you go directly to a table or use a view/company combination.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult