Remove vertical

Fisherman

I have a rather unusual problem.

Some time ago, we purchased a vertical solution for several lines of business. It used it's own tables inside of the NAV database, but also added fields to base NAV tables for their purposes.

We're now trying to remove the vertical and get back to "vanilla", or as close as possible.

In the process, my business owners want to preserve the data in the database not related to the vertical. I've done the research in our database and have determined that I can remove the data from the vendor's tables without impacting base NAV functions, and have found a way to remove the tables themselves once emptied (all of this after creating an archive copy, obviously). I've created a new database with newly merged objects that do not contain the vertical's changes, and was trying to restore the scrubbed data (with vertical table data removed and tables deleted) from production into this new database. I thought I had read that NAV would only restore data to those columns that existed in both the backup and the restore target, though now I'm not sure about that as it's proving to not be the case since the restore is failing on those vendor-added fields.

Is there any way to do this within NAV?

Alex_Chow

Import the vanilla table object into your scrubbed verticle database. Then do a backup and restore.

Fisherman

Alex

I had thought about that, but haven't tried it yet.

To date, I've only scrubbed tables that were owned by the vertical provider - I haven't tried to delete values from fields that they've added to NAV tables.

So, let's say I have the Item table, and the provider inserted a new field - let's call it NewField - and that field has values. If I have another database with the Item table, but without NewField, will NAV allow me to import that version of the Item Table, or do I first have to clear the field of values?

Fisherman

Nevermind. Just tested it with cronus. Importing the new table (without the field) yields the same results. The field must be empty before it can be removed.

Looks like my scrubbing routine is going to get a bit more complicated.

Alex_Chow

Fisherman wrote:

Nevermind. Just tested it with cronus. Importing the new table (without the field) yields the same results. The field must be empty before it can be removed.

Looks like my scrubbing routine is going to get a bit more complicated.

Yes, the field must be cleared out.

Also, I would use CLEAR() instead of just setting the variable to blank.

Taking out verticles and modifications is a pain the ass.

Fisherman

Alex Chow wrote:

Taking out verticles and modifications is a pain the ass.

I agree.

I try to make my changes in my own tables/codeunits with very small footprint inside of base NAV... of course... my changes are typically a lot smaller than a vertical.

David_Singleton

I would do this direct in SQL.

Fisherman

David

It's interesting you mention that. I'm actually a .Net/SQL Developer... picked up C/AL because my company elected to use NAV.

It had occurred to me to use SSIS to move the data from my old database to the new one - query the target schema and use it to dynamically build queries to migrate the data for only those columns that are in both places. I had avoided that as a last resort though because of so many years of "don't update NAV directly from SQL" admonishments from various sources...

It hadn't occurred to me to do the same exercise, but just kill the column contents en masse.... any issues you can see in doing that?

David_Singleton

Fisherman wrote:

David

It's interesting you mention that. I'm actually a .Net/SQL Developer... picked up C/AL because my company elected to use NAV.

It had occurred to me to use SSIS to move the data from my old database to the new one - query the target schema and use it to dynamically build queries to migrate the data for only those columns that are in both places. I had avoided that as a last resort though because of so many years of "don't update NAV directly from SQL" admonishments from various sources...

It hadn't occurred to me to do the same exercise, but just kill the column contents en masse.... any issues you can see in doing that?

I think many people update NAV databases direct form SQL. The issue is if they know what they are doing or not. The actually updating of the data, stuff like time stamps and field validity is really simple. The hard part is knowing what will happen to that record next year after the accountants close the financial year and then run adjust cost, or run one of those custom routines that you only use once a year.

The point is that if you fully understand the impact then it should be safe.

On the topic you mention "don't update NAV directly from SQL" the same applies, does that person know what they are doing or not. I remember once a customer having a complete mess in Inventory costing, so I just told them we will delete all the Item and Value entries, and all GL entries related to those. I have done this plenty of times, but I would never recommend it to someone that is not 100% sure what they are doing. You need to look at each case on its merits and solve it in a way that is the best for the client.