Some thoughts about removing a component.

13 Jan 2012 4:09 AM

Comments 11
Likes

I didn't intend to write this as an article. Like many of my posts it was simply going to be an answer to a question in the forums but when it grew in length I decided it would work better as an article. But I must stress that these are only my initial thoughts so I would expect that partner experts out there would have many very valid points to raise to complete the answer to the question "how do you remove a component"?

Several articles have been written about the component manager before. Some of the frequently read ones include these

And there are others that discuss the changes made to the component manager in particular versions of Sage CRM. But it appears I have evaded the discussion how to remove a component until now.

But let's start at the beginning. The component manager is essentially a way of applying a set of customizations to Sage CRM. When you run the component it can add new columns, new tables, new screens. It can change the structure of the database and the meta data that controls the look and behaviour of Sage CRM screens.

Because the component manager directly changes the database and the meta data records there is no simple undo. This is consequence of the current design of the tool.

If you look at the example components created by the Sage team that are made available in the Marketplace then you will see that the component has an uninstall option when it is run. This is a flag in the component parameters.

What this does is remove custom elements from the visual interface but it does not change the database structure. The columns that the initial run of the component created are still left in place. The API of the component manager does not offer the ability to remove columns or tables.

You can start to 'back out' the component changes manually but this not the simplest of tasks. If the component changed a value in a row of meta data the historic value is not stored. This means at 'uninstall' the component could not know to what value the meta data should be restored. That is only a decision a human can make.

Note: Each meta data row that has been added or changed by the component will be marked with the component name in the xxxx_component column. This allows you to query the meta data tables, the ones that start with 'custom_xxxxxx' and see which rows were changed by a particular component. BUT if you have edited that structure through the interface with the component manager 'switched on' then you may find the the component label has been overwritten with a different component name as any meta data row can only be associated with a single component.

You can manually change and remove meta data structures from within the Administration screens. This means that the removal of a component should not be a coding task.

The way in which I think I would approach the manual removal of a component is the reverse in which a component add elements. E.g.

Translations that have been added or updated
Workflows that have been added or updated
Reports that have been added or updated
Table level scripts that have been added or updated
Container Block items that have been added or updated
Tab groups that have been added or updated
Fields on screens that have been added or updated
Lists/Grids that have been added or updated
Screen Objects that have been added or updated
Columns that have been added or updated
Views that have been added or updated
Tables that have been added or updated
Database links that have been added or updated

Note: If you manually remove a field/column then it would be removed from all the screens and lists in which it is used. Please see the documentation: http://help.sagecrm.com

Before embarking on the manual removal of the component you should find out as much information about the component from whoever created it.

You should also view carefully the actual code of the component in the xxxx.es file to try and understand the changes it has made.

And lastly! Do not start changing meta data until you have backed up the system!

Comments

sroe

20 Apr 2012 1:23 PM

Hello, the link community.sagecrm.com/.../Default_CSH.htm is not working for me! Just getting on the start page of administration help.

Greetings
Jeff Richards

23 Apr 2012 1:52 PM

Have you tried using another browser?
sroe

4 May 2012 2:06 PM

No, i just tried it in IE9. This url is redirected: community.sagecrm.com/.../Default.htm
Michele Gaw

11 Dec 2013 3:23 AM

Help! We are using 7.2 and have a new install of Sage CRM. We are trying to remove a custom entity that was created with a v 7.1 component entitled "Advanced Customization Wizard". We attempted to remove it by following a community post entitled Undo custom entity creation. We followed the instructions exactly. However, the enity is still in the CRM even though the entity's table is gone from SQL. I see in your article here you say the removal should not be a coding task...we followed a coding task. Can you point me to instructions on how you can manually change and remove meta data structures from within the Administration screens.

Any help you can provide would be greatly appreciated.
Jeff Richards

11 Dec 2013 8:06 AM

Michele

If some of the aspects of the entity still remain in the system it will be because there are still records in meta data tables which describe that objects. When the Advanced Customization Wizard runs it inserts new rows into the Meta Data tables. These are the tables that start with 'custom_' in the database.

In each of those tables is a column called 'xxxx_component'.

In the custom_captions table this will be called 'capt_component'. If the entity created by the Advanced Customization Wizard was called 'Project' then each of the new rows created by the wizard will be labelled with 'project_component. BUT watch out as some existing rows are updated. These would be structures like the list, screens and tab groups which have had new fields/options added to them.

You can then see which rows have been changed by searching each custom table in turn. e.g.

select * from Custom_Captions where capt_component = 'project_component'
Michele Gaw

11 Dec 2013 10:05 PM

Jeff...Thank you for the response. I ran the suggested select statement and determined which tables the component changed. I then ran a delete statement to delete the rows discovered in the select statement. The following message was returned.

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The DELETE statement conflicted with the REFERENCE constraint "FK_FieldSecurity_CustomEdits". The conflict occurred in database "CRM", table "dbo.FieldSecurity", column 'FdSe_ColPropsID'.

The statement has been terminated.

(16 row(s) affected)

Msg 547, Level 16, State 0, Line 7

The DELETE statement conflicted with the REFERENCE constraint "FK_CustomScreens_ScreenObjects". The conflict occurred in database "CRM", table "dbo.Custom_Screens", column 'SeaP_ScreenObjectsIDFK'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 9

The DELETE statement conflicted with the REFERENCE constraint "FK_CustomEdits_CustomTables". The conflict occurred in database "CRM", table "dbo.Custom_Edits", column 'ColP_CustomTableIDFK'.

The statement has been terminated.

(9 row(s) affected)

I have looked at the referenced tables and columns in the message but have not figured out how they relate back to the added component so I don't know what to remove.

After I received the above message, I ran my initial select statement and found that I still have component data in the Custom_Edits table and Custom_ScreenObjects.

Can you help me with instructions on how to identify and remove the data from the fields in the returned SQL message so I can then remove the component data from the Custom_Edits and Custom_ScreenObjects?

Any help would be greatly appreciated!
Jeff Richards

12 Dec 2013 8:02 AM

Michele

The removal or change of the meta data should not be a coding task. You shouldn't fiddle directly with meta data in SQL as this can cause problems. I hope you have a backup of your system.

The error message you are experiencing in the SQL are referential integrity messages.

An old but still useful article is this one: community.sagecrm.com/.../quot-insights-quot-into-meta-data.aspx

This article links to other that start to explain the role of meta data.

I should have stressed that once you have found that there were some items of meta data related to the custom entity you would need to carry out the changes in the interface.

A system administrator should be able to make all the changes needed through the Sage CRM admin screens.

The different meta data table hold definitions of the different structures.

Custom_screenobjects defines the 'header' record for tabs groups, screens and lists.

Custom_screens defines the fields included in screens

Custom_lists defines the columns included in lists

Custom_tabs defines the options defined in the tab menus.

Custom_edits defines the properties of fields e.g. entry type

The removal of the custom table would be the reverse in which a component add elements. This can be done in the different admin areas

1.Translations that have been added or updated

Administration -> Customization -> Translations

2.Workflows that have been added or updated

Administration -> Advanced Customization -> Workflow

Administration -> Advanced Customization -> Escalation

3.Reports that have been added or updated

User reports area

4.Table level scripts that have been added or updated

Administration -> Customization -> CustomEntity - TableScripts tab

5.Container Block items that have been added or updated

Administration -> Customization -> CustomEntity - Blocks tab

6.Tab groups that have been added or updated

Administration -> Customization -> CustomEntity - Tabs tab

7.Fields on screens that have been added or updated

You will have to go into each of the children of the Custom Entity and remove the foreign key reference from the screens. For example if the CustomEntity has Companies then you would need to go to

Administration -> Customization -> Company - Screens tab

Then look into each screen to remove the reference to the Custom Entity

8.Lists/Grids that have been added or updated

Administration -> Customization -> CustomEntity - Lists tab

9.Screen Objects that have been added or updated

Administration -> Customization -> CustomEntity - Screen tab

10.Columns that have been added or updated

You will have to go into each of the children of the Custom Entity and remove the foreign key field from the system. For example if the CustomEntity has Companies then you would need to go to

Administration -> Customization -> Company - Fields tab

11.Views that have been added or updated

Administration -> Customization -> CustomEntity - Views tab

12.Tables that have been added or updated

Administration -> Advanced Customization -> Tables and Databases

13.Database links that have been added or updated

This only needs to be done if the new entity exists in an external database and the link is not used by any other table.

Administration -> Advanced Customization -> Tables and Databases
Michele Gaw

12 Dec 2013 4:26 PM

Richard:

Thank you for your response.

A backup of the system was made before an attempt to remove the entity was made. Are you saying that the backup needs to be restored? And if so, is that just a database restore? Is there a way to tell if the system needs to be restored.

This is a new setup of a system and it has not gone live yet. There is minimal test data in the fields...one company setup....one person setup. It is at the point of entity creation and screen design.

I am learning to be the system administrator.

I will go through your recommendations and see if the entity can be removed.

Thank you for your assistance.
Michele Gaw

12 Dec 2013 10:04 PM

Jeff: I have been through the information you have suggested. When I get to item 6, I begin to start having issues. There does not seem to be a way to delete the items on this screen. Same with items 8 & 9.

6.Tab groups that have been added or updated

Administration -> Customization -> CustomEntity - Tabs tab

8.Lists/Grids that have been added or updated

Administration -> Customization -> CustomEntity - Lists tab

9.Screen Objects that have been added or updated

Administration -> Customization -> CustomEntity - Screen tab

If you drill down into the items, there is still no delete button.

Can you please tell me how to remove these items from within the interface.

Any help would be greatly appreciated!
Jeff Richards

13 Dec 2013 8:16 AM

Michele

Is your Sage Business Partner helping you through this exercise? They should have the expertise to be able to help you.

Backups are part of the normal routine of looking after a database. But when you are planning on making a dramatic change to the database they are essential. I would also strongly recommend the use of a sandbox or test environment that allows you to practice customizing the system. Please see the article: community.sagecrm.com/.../reasons-why-a-crm-sandbox-environment-is-recommended.aspx

A backup for a live environment is needed so that you have a clear restore point. It allows us to recover back to a previous point when everything worked as expected.

But returning to the questions.

If the entities can not be deleted through the interface this is possibly due to the fact that they have been created with a setting that says 'do not allow delete'.

The structures of tabs, lists and screens are all defined as two part structures. There is a header record (stored in the custom_screenobjects table) and then child records. The child records for tabs, lists and screens are held in the custom_tabs, custom_lists and custom_screens meta data tables respectively. If we can remove the header records through the interface then the child records will be removed too.

I assume that you have access to the database.

The following steps should first be carried out on a sandbox test environment. But whatever you do, take a back up.

Run the query

select * from custom_screenobjects where cobj_name like '%entityname%'

NB - obviously change 'entityname' to the name of your custom entity e.g. project.

You will see that there is a column called 'cobj_allowdelete'.

Look carefully in the list to make sure that all these objects are marked as belong to the correct component. You may want to run the SQL like this:

select * from custom_screenobjects where cobj_name like '%entityname%' and cobj_component = 'project_component';

To allow the screen objects for this custom entity to be deleted then you need to run a query like the following.

update custom_screenobjects

set cobj_allowdelete = 'Y'

where cobj_name like '%project%'

and cobj_component = 'project_component'

You will then need to refresh all meta data to ensure that this change is picked up by Sage CRM's admin screens. You do that in the following screen.

Administration -> System -> Metadata

Once the meta data has been refreshed you will be able to return to the definition of tabs, screens and lists for the custom entity and you will be able to drill down and the delete button will now be present.

I hope this helps.

BUT test, test, test, Any action may have unforeseen consequences.
Michele Gaw

16 Dec 2013 8:23 PM

Thank you Richard.

The entity has been removed.

Some thoughts about removing a component.

Hints, Tips and Tricks

Some thoughts about removing a component.