On Repurposing Dynamics NAV Table Fields, or The Forbidden Fruit of Danger
Sometimes, there’s a request to add a new field to a Microsoft Dynamics NAV (Navision) table. You might hear that request and think, “Hey, I don’t need to actually add a new field for that. I can just re-use a totally different field and change the caption and it will be fine. We’re not using that field anyway, so no one will ever notice.”
This is a terrible idea. Please do not do this. At bare minimum, you’ve created a bunch of confusion—from then on, you have to remember that the Left Widget Dialog Code is being used throughout the system to hold New Fur Color. At worst, you’ve broken a feature that you didn’t know someone was using, and then you have to run around fixing production issues like a chicken with your head cut off.
There are other things you should do instead, and I’ll review them here.
To start with, if you can add the field to the table, add it to the table. An existing field will be used in places that you probably can’t think of—particularly if it’s a base NAV field. Most of the time, this won’t even be difficult. The table will generally have plenty of space. If you have this option, then you’re in luck. Just add the new field, stop reading here, and go have a sandwich.
Sometimes, though, adding a new field to the table isn’t immediately an option. There are some tables in NAV that get a lot of modifications. Sales Header and Sales Line come immediately to mind; I’ve seen NAV installations where those tables had so many fields from add-ons and customizations that NAV’s table size limit had been reached. And inevitably, the customer said that they knew they’d already added a bunch of custom fields to the table, but they needed a few more, because it was important for compelling business reasons.
That “important for compelling business reasons” bit may sound like sarcasm, but it’s not. There are lots of good reasons for adding custom fields to a table, even a bunch of custom fields. We just need to be smart about it.
Before we talk about how to fix the problem, we need to talk about the problem itself. There’s a hard limit on the potential size of a record in a NAV table. It’s not based on the number of fields in a record, but instead on the maximum possible size of all the fields in the record. Boolean fields take up the least amount of space, followed by options and integers and decimals, and code and text fields take up the largest amount of space.
The temptation when you run out of space for fields in a table is to re-use one of the existing fields and just change the caption. This is still a bad idea. Instead of repurposing a field, you need to do one of two things: add a new subtable, or find a way to make the existing fields shorter to fit in your new fields.
If you only have one new field to add, and it’s a small field, like a Boolean or a numeric value, then you might be able to get away with making an existing field smaller. You should ONLY ever try to make custom fields smaller. DO NOT try to make a base NAV field or an add-on field smaller. It will cause problems with your upgrades and make you cry bitter tears of sadness. Custom fields are different, though. You might be able to find a custom field that isn’t being used or that you can make shorter. Doing this requires a thorough database analysis—you need to check out all the places the field is used and all the values that have ever been put into it, including things like TRANSFERFIELDS. If you have a tool like Object Manager, this becomes much easier. (Helpful hint, though—if the field has never been filled on a record ever, or if it was used for something years ago that no longer gets used, you might be able to truncate the values in the old records and make the room you need.)
If you can’t truncate an existing custom field, or if you have multiple new fields to add and there’s just no way to make room, you should create an additional table to store the new data. Use the same primary key fields and values as your original table. You need to link to the sub-table whenever you display one of the record fields, and it needs to be updated along the original record. This is an unpleasant undertaking, since it takes a lot of development, but it’s still better than repurposing a field. (This is worthy of its own chapter in a book on NAV development, actually.)
About the only time you can comfortably repurpose a field is when you’re using a temporary record variable and you need to just track something in the temporary table. At that point, you can go ahead and repurpose fields as necessary rather than adding them to the table. It’s not a perfect practice, since it’s still confusing, but sometimes you have to choose the best of several unpleasant alternatives.
If you have any further questions about this or other development questions, please contact one of our development experts at ArcherPoint. If you liked this, you might like to read more of Tom Hunt’s blogs, or check out our collection of Development Blogs.