Need Advice - Is deleting user defined columns a good idea?

Sonal10 · ‎08-15-2018

Dear Experts,

We have a situation where many tables have a lot of user-defined columns, and I think these were defined at least 4 years ago (that's before I joined here). There is a lot of data and I believe these additional fields (table columns) were added to cater to things unavailable at that time. They obviously hold a lot of data but some of which may not be relevant if we were to plan to revert to the Out Of the Box modules.

The question is what would be your general opinion or advise regarding

1. Deleting these fields completely to minimize confusion?

2. Would there be any way to export and keep this data available in an archived state - so can still be accessible?

3. I believe it will surely have a huge impact if these fields are used on forms/ reports/ scheduled script executions etc.. Would there be an easy way to determine or work out the Impact analysis?

Thanks in advance.

Regards,

Sonal

scott barnard1 · ‎08-15-2018

Hi

You can archive the data but if you remove the columns then you can restore it back later.

However it isn't a trivial task to remove these old customisations smoothly

That said reducing the gross number of columns on any given table helps with performance, particularly in a heavy use instance.

I did something similar recently and here are the steps I went through by table

1. Make a note of column name and label

2. Script search on the names and check each item returned. For Script includes check what calls the script include.

3. Check workflows on each table and related for script references to those columns

4. Check reports for those names and also reports that may use them as filters.

5. Review condition scripts on the various types of scripts for references to them

6. Check Transform Maps and import sets for any usage

7. Check Notifications for any $name body content or descriptions.

8. Check record producers etc for scripts setting any value

9. Check variables and see if any ref those fields. Overcheck any item they are on.

10. Check any integrations are not using them etc.

Now this is all ok as it tells you how big a job you may have just to make a decision on going forward. You may decide it's too much effort to consider etc

Then you need to consider if these columns exist as variables and you are using the variables tab on the various task forms then removing that column will lose that historical data so if someone does an audit of an item that was provided and the removed column contained justification or other key info then the record will be missing necessary data etc

View solution in original post

Jamsta1912 · ‎08-15-2018

Hi Sonal,

To assess the impact of deleting the columns, you can search all scripts (UI Actions, Businness Rules, Script Includes, Inbound Actions, Client Scripts) and also UI policies, to find out where the fields are being referenced. If it turns out they're not referenced anywhere and were really just being used to store data that's no longer being used, it would be OK to then delete the fields. Before doing so you could set up a report on the affected tables, to show just those records where the fields are populated, and export those records to a CSV / Excel for archiving.

But if you are unsure how the fields are being used, for instance if you find references to them in scripts and are then unsure of the impact of disabling or amending those scripts, it's safer to just leave the columns in the dictionary. You can remove them from forms and list views so that they aren't causing any confusion. So if in doubt, it's best to NOT delete them. They're unlikely to impact performance or do any other harm.

Jamie.

Sonal10 · ‎08-15-2018

Thanks, Jamie - makes total sense to me.

I am just afraid of having the need to restore some ancient data in future (as scary as it sounds!).

But I will see at my options to export to CSV/Excel as per your suggestion and see if the data can be usable in that way - if required later.

Regards,

Sonal

scott barnard1 · ‎08-15-2018

Hi

You can archive the data but if you remove the columns then you can restore it back later.

However it isn't a trivial task to remove these old customisations smoothly

That said reducing the gross number of columns on any given table helps with performance, particularly in a heavy use instance.

I did something similar recently and here are the steps I went through by table

1. Make a note of column name and label

2. Script search on the names and check each item returned. For Script includes check what calls the script include.

3. Check workflows on each table and related for script references to those columns

4. Check reports for those names and also reports that may use them as filters.

5. Review condition scripts on the various types of scripts for references to them

6. Check Transform Maps and import sets for any usage

7. Check Notifications for any $name body content or descriptions.

8. Check record producers etc for scripts setting any value

9. Check variables and see if any ref those fields. Overcheck any item they are on.

10. Check any integrations are not using them etc.

Now this is all ok as it tells you how big a job you may have just to make a decision on going forward. You may decide it's too much effort to consider etc

Then you need to consider if these columns exist as variables and you are using the variables tab on the various task forms then removing that column will lose that historical data so if someone does an audit of an item that was provided and the removed column contained justification or other key info then the record will be missing necessary data etc

Sonal10 · ‎08-15-2018

Wow, Scott - much appreciated.

Thank you for the detailed approach. I am surely going to invest some time to do this analysis.. but given the time it's going to take - looks like I will have to plan for it later on.

Regards,

Sonal