I REFUSE!

When my kids have visitors to the house almost always, without fail, they show them our laundry room.  It’s sort of their way of saying “See how crazy my mom is!”  Call it an idiosyncrasy, an obsession, or whatever you want, but I like things orderly and in their place, and I simply REFUSE to sort anyone else’s dirty laundry! 

Our laundry room has, not one, not two, but five laundry hampers.  Each one has a laminated, color coded sign hanging from it to describe the clothing that goes into the hamper: Dark Colors, Bright Colors, Bleachable Whites, Delicates, and Jeans.  There’s a place for everything and everything goes in its place. Because of that, my laundry room is neat and efficient.  I can start a load of laundry in less than a minute.

I also REFUSE to sort through a cabinet full of items in my bathroom.  There is a bin for everything – Skin Care, Hair Care, First Aid, Dental, Cleaning Supplies - you get the picture.  And yes, the bins are labeled (I love my label maker!). 

As NAV developers, we have to learn to say “I REFUSE” to a few things for the sake of maintaining an efficient and orderly database. When adding data to a database, you have to think long term.  Once data is there, it’s usually not removed.  Databases change hands, security measures evolve - or even dissolve, and what is in your database can become a threat to your company. 

I’ve come up with a short list of things a NAV developer should refuse to store in a NAV database.  This is by no means an all-inclusive list, and I encourage you to add to the list (see that comment box below?).

Things A NAV Developer Should Refuse to Store in NAV:

1. Credit Card Numbers: Want to lose customers?  Let their credit card numbers be breached.  We’ve all heard the reports of this happening at several large corporations over the past few years.  PCI Compliance regulations are not only guidelines, but laws in several states.  Not only could you lose customers, but you could incur some hefty fines as well.  There are several websites that discuss PCI Compliance, but you should check your individual states laws.

1. Social Security Numbers: Through the experience of a family members a few years ago, I’ve learned that all it takes for someone to get a credit card in your name is your SSN, Birthdate, and a signature.  Some businesses, such as health care institutes and government services, must have this on file.  But storing this unencrypted is bad data management.  Just REFUSE to do it! 

1. Images:  It could be document images (PDF’s), Employee headshots (in the Payroll Granule), or Item Images, but this seems to be most commonly done to the item table.  Although it may seem logical to add a product image to each of your items so that it can appear on your website, or in your catalog, think of how your database will grow, and how often your item records are retrieved from your database.  Images, which are stored as a blob data type in NAV, are heavy in byte size, and cause your database to grow fast!  Additionally, only bitmap types are allowed.  Bitmap format images do not have the quality you’d want to put on a website.  A simple alternative is to store the images in a set location on your server OUTSIDE the database, and store only the path to the image in your Item card. You can even allow the image to be uploaded to the server through NAV so you always record the proper image path. (TIP: To prevent an employee from uploading something like their resume or pictures of their laundry roomJ, consider securing not only the function, but the initial directory in which the image must reside to be used in this path.  I often create a separate function in Codeunit 412 Communication Dialog Management to ensure the initial directory of the file upload is set so that a user doesn’t get full rights to load whatever they want.)

1. Calculated Data: I’ve seen this done more than once, and it’s just not a good practice.  Rather than reading and calculating the data each time you need it, some developers think it’s a good idea to do it periodically and store it away in the database table.  For example, creating fields representing a set aging periods to store the customer’s aged balances on the customer card.  This creates an overhead of data that is redundant and continually outdated.  NAV has transactional data tables to store this information, such as Customer Ledger Entry, Item Ledger Entry, Vendor Ledger Entry, and so forth.  The proper alternative is to create a flowfield that would access the transactional records using flowfilters and calculate the value for you on the fly.  This field data type holds no data – just calculation instructions.  When you’re ready to use it, you just issue a CALCFIELDS command to the field and voila!  There’s your value!

1. Null Values: In NAV, null values are not allowed. You have to have a value.  So if it’s not allowed in NAV, then why do I bring it up?  Because it is possible to get it into NAV by using outside sources, such as SQL Stored Procedures.  Why are Null value evil and vile?  Well, let’s say you have a boolean field, which NAV says can either be false(0) or true(1).  If you put a NULL value in the field, when it is displayed, it appears to be FALSE, having no checkmark.  However, if you filter for “MyBooleanField = FALSE”, it doesn’t show up because it’s not false, it’s NULL.  It doesn’t contain a SQL value of ‘0’.  It has no value at all.   This, my friends, is how problems are produced.  And what happens when NAV reads a NULL?  You get a big fat error.  Null values should never be allowed in NAV, and likewise writing to NAV tables from outside sources should never be done.  When you add data from outside sources, you bypass all the code that is on the field triggers.  So, if I have code in MyBooleanField to set yet another field, it never gets done.  (The first few lyrics of the Ray LaMontagne song “Trouble” seem to be a befitting theme song for this type of development!)

Go ahead…say it with me: I REFUSE TO TRASH MY DATABASE! Sometimes the developer has to be the keeper of the database and police how it is used.  Otherwise, our happy little playground could turn into an unorganized mess…much like the huge bag of laundry my son brings home from college about once a month.  That’s right, once a month.  Lord, have mercy!

The Challenge

Use the comments box below to tell us about data issues you’ve seen in NAV databases.  What have you seen that should not have been in the database, and why?