Custom Group View in Sage CRM for MyCRM Groups Tab
This article describes the basics of creating a custom group view in Sage CRM that can be used in the MyCRM Groups tab of Sage CRM.
Views used in groups have a few specific requirements that need to be met so that your view will show up in view list and work correctly for all users. This article will help you understand the requirements, so you can avoid any frustration from your view not being available in the selection list. Some basic knowledge of SQL is strongly recommended, and it is not recommended to ever modify a system or core view. Always remember to backup your CRM database before making any changes in administration area of your production CRM system.
First, you will want to make sure you understand which primary entity (Company, Person, Opportunity, etc.) you need to create the group view under. Group views will only show up for entities that are set as Top Level or Primary entities. You can easily see which entities are primary entities by going to Administration and then Customization. All the primary entities will be listed on this screen. The secondary entities will be in the selection list on the bottom of the screen.
Selecting the correct primary entity will help you keep your views that return similar data in the same area, so they will be easier find in the future. If you are creating a view to look at all the people across all companies, It is recommended to create that view under the person entity and not the company entity. If you are only looking at each company’s data long with its primary person, then creating the view at the company entity would be the most logical.
Once you have chosen the primary entity you want to create your view under, you can create the view by going to Administration, Customization, select the primary entity, and click on the view tab. In this example, we will be creating a Cases view. We want to make a view so we can have a group to see all of the cases and the company and person they belong to.So, we will include all Case fields (Cases.*), the Company Name (Comp_Name), and the person name (Pers_FirstName, Pers_LastName). It may help to build your views in SQL Management Studios then copy into Sage CRM to make sure all field names are correct, and the view is returning the desired results.
My SQL Code:
Select Cases.*, Pers_FirstName, Pers_LastName, Comp_Name
from Cases
LEFT JOIN Company on Comp_CompanyId = Case_PrimaryCompanyId and Comp_Deleted is null
LEFT JOIN Person on Pers_PersonId = Case_PrimaryPersonId and Pers_Deleted is null
Where Case_Deleted is null
In Sage CRM make sure you have the Groups View: selected, and it’s very helpful to give the view a Translation so its easier to read the view name. If you leave the translation blank the user will simply see the View Name (Ex: vMyCasesGroupView).
While this view will save and now show up in the list of views for in the group area, there are a few fields we are missing, so your non-system administration users can run this view with security and without errors. For EACH primary entity we include in the view, we must include the following fields so security can be added to the group view.
Required Primary Entity Fields:
_secterr – Territory field
_createdby – Who created the record
_channelId – Team field
_primaryUserId (or_assignedUserId) – Who the record is assigned to
If these fields are not added for each primary entity in the view, your users will be able to select this view but they could end up seeing the error below.
SQL Error from the log file:
Invalid column name ‘comp_secterr’
Here is the modified view with all the proper fields added.
My SQL Code:
Select Cases.*, Pers_FirstName, Pers_LastName, Comp_Name, Comp_SecTerr, Comp_CreatedBy, Comp_ChannelID, Comp_PrimaryUserId, pers_SecTerr, Pers_CreatedBy, Pers_ChannelID, Pers_PrimaryUserId
from Cases
LEFT JOIN Company on Comp_CompanyId = Case_PrimaryCompanyId and Comp_Deleted is null
LEFT JOIN Person on Pers_PersonId = Case_PrimaryPersonId and Pers_Deleted is null
Where Case_Deleted is null
Now you are able to run the view correctly.
Hopefully, this article has helped you to quickly make a custom group view that is easy to find and will run without errors.