To Multi-Purpose or Not to Multi-Purpose
If you were to look in my garage you’d find a very important member of our family. Her name is Vanna, and she’s not just your ordinary mini-van. She is the Swiss army knife of mini-vans. For years she was a youth bus, taking loads of kids to church events. She’s also been a fishing vehicle, and doesn’t seem to mind that I load her down with fishing poles, coolers, bait, and a fish bucket that always seems to come back more empty than full. When my son moved to college she was a moving van packed to the ceiling with no visibility out the back window. She’s also a great landscaping tool, hauling bags and bags of mulch, top soil, plants, and even trees. You would be amazed at the many ways I’ve found to use Vanna!
But Vanna has taught me that she has limits. For example, it is not wise to load her down with concrete stepping stones, planters, bird baths and other concrete yard items. Apparently, her little round black knees begin to buckle under the pressure, and she’s go flat. It’s also impossible to get a jack under Vanna’s wheels when she’s loaded down with concrete.
Multi-purposing is a beautiful thing. But knowing when you need a multi-purpose tool and when you need a specialty tool is very important.
When it comes to multi-purposing in databases, just say NO! One of the biggest mistakes I’ve seen in databases is the reuse of a standard NAV field for a secondary purpose. Most commonly, I’ve seen this in the “Name 2” field of the customer card.
Here’s what happens. Let’s say we have membership number we need to store on every customer in our database. We want to be able to show this on reports and forms/pages. To make this happen, it would take three changes to hold the data. We’d need to add the new field to the customer table, and to the customer card form and page.
But instead of creating a field on the customer card to hold the data, we decide its less time consuming to multi-purpose the “Name 2” field. The field is empty on all our customer records, and we already have it shown on our customer card, so why not! So we happily go about populating this field with the membership number for all our customers, and finish with a sense of satisfaction.
But in just a few short hours, we get a call from our Accounts Receivable department wanting to know why there is a strange number in the customer’s address field on the customer statements.
Now we have to modify the function in Codeunit 365 “Format Address” that is creating the customer addresses. We didn’t see the “Name 2” field used in the addresses before because it was blank, and the code that creates the address compresses the array of address data to leave out blank lines. Let’s call this modification to Codeunit 365 “correction 1”.
We’re about to go to lunch when the phone rings again and it’s our sales department. They want to know why there’s a mysterious number showing on the sales order address, and when the order is invoiced, it shows on the invoice. If they create a credit memo, it shows there too. We shake our head in disbelief because we didn’t put the membership number on the sales order - we only put it on the customer table. But after a quick look at the Sales Header table code, we find that when a Sales Header of any kind (Quote, Order, Invoice, etc…) is created, NAV pulls in all the necessary data from the customer to the Sales Header table.
We smack ourselves in the forehead for not thinking of this, and say “No problem!” We go into the Sales Header code and remove “Name 2” from the Sales Header. Let’s call this “correction 2”.
Meanwhile, we’re adding the membership number (i.e. “Name 2”) to all the reports we need it on. We see the need to put a key on the field to sort the records reported by membership number. In the back of our heads we silently think “hmmm…seems strange to put a key on a Name 2 field”, but we keep going, being sure to document this so that the next developer doesn’t think it was a mistake.
Then the purchasing department calls us. They have a drop ship order that has an odd number in the Ship-To Address. Shaking our head in disbelief, we look at the “Name 2” field of the Sales Order, and it’s blank. We look at the “Ship-To Name 2” on the Purchase Header, and there it is, our membership number. After careful considerations given to the noted gremlin in the database our users have sometimes reported, we review the Purchase Header code.
We find that the Purchase Header pulls its Ship-To Address from the Customer Card when no Ship-To Code is used. Now we have to modify the Purchase Header code to not populate the “Ship-To Name 2” field of the purchase order with the Customer’s “Name 2” field. Let’s call this correction 3.
We’re now at the point to where our so-called corrective modifications equal the number of modifications it would have taken to add a separate field and do things the right way. We should also consider the business problems we’ve caused, but, what’s done is done, so we continue on.
It’s now late afternoon and our Customer Service department calls. They have a new customer with an address that won’t fit in the Customer’s “Name”, “Address”, and “Address 2” fields. They need to use “Name 2” for the address, but see it has a number in it.
Now, as you should be wrapping up and getting ready to go home, you now have to create a new field to hold the membership number (which you should have done in the first place), create a report to move the membership numbers to the new field, and add back the code you removed in correction 1, 2, and 3.
It’s official. You’ve now had a bad day, and it’s just going to be worse as you have to undo all your hard work. I’d tell you this is much like when I had to un-load all the concrete from Vanna, fix the flat tires, and re-load the concrete into a pickup truck to get it home!
I’m a firm believer that standard NAV fields should keep their original purpose, and custom data should go in custom fields. It’s not that difficult to add a field to a NAV table, and to add it to a form or page for entry. If there’s no code against the field for validity, you’re talking a ½ hour of work tops.
It would be far too much typing for me to explain to you how to add a field to a table. Instead, I would refer you to a very good book by David Studebaker titled “Programming Microsoft Dynamics NAV 2009”. Of course, if you’re using an older version, you’ll find books online for pre-2009 versions as well.
When considering adding a field to NAV, here are a few rules and considerations:
Always use a field number between 50,000 and 99,999. This number range is for custom fields.
NEVER add the field from SQL. In order for NAV to use the field, it has to be defined in NAV.
Think about the purpose of the field. Is it an e-mail address or phone number that requires special formatting? There’s a property you can set on the field to help with this.
Should the field contents be limited to a short list of options? Look at how an OPTION type field works.
Should this field be filled with the data from another table? Maybe you want to create a flowfield.
Should changes to the field require special permissions? You may want to add code to handle this.
Lastly, adding a field to a table should not be something you do on a whim. Carefully consider things like the source of the data, how will the data be used, how often the data changes, the various methods for receiving and inputting the data, validations to the integrity of the data, and so forth. Knowing the data – and protecting it - in your NAV application will make you a strong developer. Likewise, knowing how much concrete weighs, and how much weight your vehicle can support is also important.