Avoiding Hard Coding and Creating Setup Reports for Projects
Part 1: I try not to hard-code things
The other day, I was writing an import for a customer where they needed to take a file from their payroll processor, pull it into their system, and consolidate data from different fields in the file by department into a multitude of G/L accounts in a General Journal.
This kind of thing happens regularly when you’re importing data, and the instant temptation is to just hard-code the G/L account numbers into the code that does the importing. That works great, until the new CFO or controller comes along and wants to rearrange the G/L accounts to which the import is posting. Then someone has to call the developer, because the code has to be changed. And that’s generally not a happy someone making that phone call, because they HAD to call a developer and then deal with a bunch of system changes.
There’s a better way to handle this than hard-coding, and that’s by using the setup tables in Microsoft Dynamics NAV.
When you have a scenario where you need to pull data into the system from a file import and don’t have the values in the import, you don’t have to hard-code those values into the import—you can add those values to a setup table instead.
I try to look at what I’m writing and add a field for it to the appropriate setup table. If it’s something related to sales orders, I add a new field to the Sales & Receivables Setup table. If it’s a change to the way purchase orders should work, I add a new field to the Purchases & Payables Setup table. If we need to make some special notes related to inventory processing, I add the new field to the Inventory Setup table.
In the case of the payroll import, it was several new fields added to the General Ledger Setup table to handle pay, benefits, taxes, and insurance. It was quite the complicated undertaking, and it took a little extra work to add the fields to a table instead of coding them straight into the system, but it’s worth it when the G/L needs to be rearranged—all that needs to be done for the import is change a few values in the General Ledger Setup table, which the client can easily do on their own.
Part 2: I wrote Part 1 three weeks ago, and I have endured multiple traumatic events since then
What I didn’t mention about the payroll import is that it’s for a new implementation, and we’re not scheduled to go live for a while. Weeks, at least—and so far the project seems to be doing OK (knock on wood).
So, I have to be honest here: I’m not going to remember the four different G/L accounts the client wants to use when it comes time for go-live.
Even when I’m not working on projects for a new implementation, there is often a period between when I write something and when it goes into production. Generally, that time period is measured in days, but I’ve seen it turn into weeks or even months due to expansions in scope, changes in personnel, sudden crises erupting, etc.
So, when I have some fields to write in a setup table, I generally write a setup report to go along with it. This report is intended for one-time only use, and it just writes the intended values to the new fields in the setup table so that I don’t have to remember them, or remember where I wrote them down, or look them up from the test system where I just threw in some stuff to get started and it’s not actually right, or worry about the values on my whiteboard getting erased.
The setup report is generally a pretty simple affair. It’s a report marked as Processing Only, so there’s no layout to work with. I generally name it something like “TEMP – Payroll Import Setup” so that I know it’s only meant for use once. I also generally put it at the top of the available object range so that it stands apart. If I’ve worked with a client enough to have done multiple projects that needed setup reports, I’ll keep the last two or three and overwrite the oldest one as necessary. Finally, I base it on the Integer table and filter the Integer table so that the data processing section of the report only runs once.
The nice thing about doing this is that if I have a really big project that sets values in multiple setup tables, I don’t have to remember what to touch at go-live. Instead, I just run the report and it does it all for me.
So, the next time you need to set things in some NAV setup tables, try writing a one-time report as part of your project (just don’t forget to run the report after go-live).
I welcome comments on whether this method works for you, or if you have your own way of handling this type of data.