Adding Advanced Find and Mass Update and Groups for a Custom Entity (Project) in Sage CRM

The article will take you though the steps to add a custom entity to the Advanced Find screen.

To do this you will need to have already created the custom entity.
I have made the assumption that you have created an entity called Project.

Once we have the Project entity in existence we can create the view that the Advanced Find will use. This needs to be called vSearchListproject.

I defined the view in the Customization area for the project entity and used the SQL shown below (Note: I based my view on vSearchListOpportunity and removed reference to the account entity) :

CREATE VIEW vSearchListProject
SELECT RTRIM(ISNULL(dbo.vPersonPE.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_LastName, '')) AS Pers_FullName,
RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneAreaCode, ''))
+ ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber, RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxCountryCode, ''))
+ ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxNumber, '')) AS Pers_FaxFullNumber,
dbo.vPersonPE.phon_MobileFullNumber, dbo.vPersonPE.Pers_EmailAddress, dbo.vPersonPE.Pers_PhoneCountryCode, dbo.vPersonPE.Pers_PhoneAreaCode,
dbo.vPersonPE.Pers_PhoneNumber, dbo.vPersonPE.Pers_FaxCountryCode, dbo.vPersonPE.Pers_FaxAreaCode, dbo.vPersonPE.Pers_FaxNumber,
dbo.vPersonPE.Pers_PersonId, dbo.vPersonPE.Pers_CompanyId, dbo.vPersonPE.Pers_PrimaryAddressId, dbo.vPersonPE.Pers_PrimaryUserId,
dbo.vPersonPE.Pers_Salutation, dbo.vPersonPE.Pers_FirstName, dbo.vPersonPE.Pers_LastName, dbo.vPersonPE.Pers_MiddleName, dbo.vPersonPE.Pers_Suffix,
dbo.vPersonPE.Pers_Gender, dbo.vPersonPE.Pers_Title, dbo.vPersonPE.Pers_TitleCode, dbo.vPersonPE.Pers_Department, dbo.vPersonPE.Pers_Status,
dbo.vPersonPE.Pers_Source, dbo.vPersonPE.Pers_Territory, dbo.vPersonPE.Pers_WebSite, dbo.vPersonPE.Pers_MailRestriction, dbo.vPersonPE.Pers_CreatedBy,
dbo.vPersonPE.Pers_CreatedDate, dbo.vPersonPE.Pers_UpdatedBy, dbo.vPersonPE.Pers_UpdatedDate, dbo.vPersonPE.Pers_TimeStamp,
dbo.vPersonPE.Pers_Deleted, dbo.vPersonPE.Pers_LibraryDir, dbo.vPersonPE.Pers_ChannelID, dbo.vPersonPE.Pers_UploadDate, dbo.vPersonPE.pers_SecTerr,
dbo.vPersonPE.Pers_WorkflowId, dbo.vPersonPE.Pers_AccountId, dbo.vPersonPE.pers_intforeignid, dbo.vPersonPE.pers_intid, dbo.vPersonPE.pers_intlastsyncdate,
dbo.vPersonPE.pers_promote, dbo.vPersonPE.pers_ConflictResDate, dbo.vPersonPE.pers_departmentcode, dbo.vPersonPE.Pers_OptOut, dbo.Project.proj_ProjectID,
dbo.Project.proj_CreatedBy, dbo.Project.proj_CreatedDate, dbo.Project.proj_UpdatedBy, dbo.Project.proj_UpdatedDate, dbo.Project.proj_TimeStamp,
dbo.Project.proj_Deleted, dbo.Project.proj_Secterr, dbo.Project.proj_Name, dbo.Project.proj_WorkflowId, dbo.Project.proj_Status, dbo.Project.proj_UserId,
dbo.Project.proj_ChannelId, dbo.Project.proj_CompanyId, dbo.Project.proj_PersonId, dbo.vCompanyPE.Comp_EmailAddress,
dbo.vCompanyPE.Comp_PhoneCountryCode, dbo.vCompanyPE.Comp_PhoneAreaCode, dbo.vCompanyPE.Comp_PhoneNumber,
dbo.vCompanyPE.Comp_FaxCountryCode, dbo.vCompanyPE.Comp_FaxAreaCode, dbo.vCompanyPE.Comp_FaxNumber, dbo.vCompanyPE.Comp_CompanyId,
dbo.vCompanyPE.Comp_PrimaryPersonId, dbo.vCompanyPE.Comp_PrimaryAddressId, dbo.vCompanyPE.Comp_PrimaryUserId, dbo.vCompanyPE.Comp_Name,
dbo.vCompanyPE.Comp_Type, dbo.vCompanyPE.Comp_Status, dbo.vCompanyPE.Comp_Source, dbo.vCompanyPE.Comp_Territory,
dbo.vCompanyPE.Comp_Revenue, dbo.vCompanyPE.Comp_Employees, dbo.vCompanyPE.Comp_Sector, dbo.vCompanyPE.Comp_IndCode,
dbo.vCompanyPE.Comp_WebSite, dbo.vCompanyPE.Comp_MailRestriction, dbo.vCompanyPE.Comp_CreatedBy, dbo.vCompanyPE.Comp_CreatedDate,
dbo.vCompanyPE.Comp_UpdatedBy, dbo.vCompanyPE.Comp_UpdatedDate, dbo.vCompanyPE.Comp_TimeStamp, dbo.vCompanyPE.Comp_Deleted,
dbo.vCompanyPE.Comp_LibraryDir, dbo.vCompanyPE.Comp_ChannelID, dbo.vCompanyPE.Comp_SecTerr, dbo.vCompanyPE.Comp_WorkflowId,
dbo.vCompanyPE.Comp_UploadDate, dbo.vCompanyPE.comp_SLAId, dbo.vCompanyPE.Comp_PrimaryAccountId, dbo.vCompanyPE.comp_intforeignid,
dbo.vCompanyPE.comp_intid, dbo.vCompanyPE.comp_intlastsyncdate, dbo.vCompanyPE.comp_promote, dbo.vCompanyPE.Comp_OptOut
dbo.vCompanyPE ON dbo.Project.proj_CompanyId = dbo.vCompanyPE.Comp_CompanyId LEFT OUTER JOIN
dbo.vPersonPE ON dbo.Project.proj_PersonId = dbo.vPersonPE.Pers_PersonId
WHERE (dbo.Project.proj_Deleted IS NULL)

I marked the view as a 'Groups View' with the translation 'vSearchListProject'.

Once you have created the view the translations needed to create the project option on the Advanced Find drop down have to be added to the custom_edits metadata table.

To do this you need to go to Administration -> Customization -> Translations

Add the translation

Caption Code: Project
Caption Family: AdvFindEntities
Caption Family Type: Choices
US Translation: Project
UK Translation: Project

As you can see in the screen shot below you can now move to the Advanced Find screen and you can search for your Projects.

The screen is fully featured and you can create saved searches for the Project Advanced Finds. The Mass Update feature for the custom entity is available if you have enabled the "Allow mass update and update territory" option in the Administration -> System -> System Behavior screen.

Note: If you have also created a view for the custom entity that is enabled as a "reports" view, then you can use the Advanced Find to support the custom entity reports. You can also create groups for your custom entity from the Advanced Find screen.
Note: The Grid that is used for the AdvancedFind will be XXXXXGrid where XXXXX is the name of your custom entity. In my case ProjectGrid. To extend the columns available to the grid I also changed the view that the list block used to my new view vSearchListProject.
  • I had the same problem as Abyss_zim.

    I added a list with the appropriate name (i.e. {entitynameGrid}), which has all of the fields from the underlying view (which selects * from the custom entity, so all fields are included).

    I now get the error below.....

    An unexpected event has occurred: EAccessViolation: Access violation at address 1A6455AE in module 'eware.dll'. Read of address 0000009F

  • Managed to get the entity to show in the list, able to add fields for the search but when I click find

    " An Unexpected Event has occurred: Exception: List marketingGrid no found"

    Do I need to create a list screen to complete this?

  • Have a look at again at the view that you have defined on the custom entity. The views that are used by the systems entities like person, opportunity etc which are then used to create groups are designed so that each row returns a unique person or company id. The systems can be changed to allow groups to be based on views which return duplicate companyid and personids but by default they would be suppressed.

  • Hi all,

    Thanks for the post.

    I have been battling to replicate this process.

    I have managed to get the custom entity advanced find to work fine, but for some reason I cannot create groups/gadgets based on the entity.

    I have deleted and recreated the translations and made sure the views are sound.

    Any hints on where to start looking will be much appreciated.

  • Interactive Dashboards are based on 4 different type of data source. These are

    1) Reports

    2) Dynamic Groups

    3) Saved Searches

    4) Advanced Finds.

    Once you have created your new view, you then have to create the data source that uses it.