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

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

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

  • Comments 14
  • Likes
The article will take you though the steps to add a custom entity to the Advanced Find screen. 
I wrote the article some years ago but a question from a business partner has caused me to update it for Sage CRM 2017 and Sage CRM 2018. 

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.
  • Hi Jeff, I followed the above steps on 6.1k and it worked.  BUT, my view links 2 custom entities (a parent and a child called Equipment and EquipmentHistory).

    If I create vSearchListEquipment and join Equipment and EquipmentHistory, I can do an Advanced Find but I can only filter on the Equipment fields.  If I filter on any EquipmentHistory fields it gives me a sql error saying that the field does not exist in Equipment.  Any ideas?

  • Hi,

    I have exactly the same problem. As long as you search on fields of the main entity table it works fine, but when you search on a field of the linked tables there's an SQL error saying that the field is not available. I suppose the search is looking in the custom entity table instead of using the view.

  • Just followed above in 6.2E and it does not work. I only have the one table (Contract) in the view and I have nominated the fields. The same and similar works for other areas such group building groups for MyCRM but no joy on Advanced Find at all. Mike

  • Hi Jeff,

    I am currently struggling to get the interactive dashboards as well as the groups to show my Custom entity on 7.2 sp2.

    I have a "Process" table which is a child of the "Cases" table via a "ProcessLink" table.

    I have based the view on vSearchListCases (results are distinct and appear fine) and have added the translations (checked and double checked spelling etc.).

    The entity is not appearing on the Groups or Gadgets entity selection lists.

    Any hints to where I can start troubleshooting?

  • 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.

  • 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.

  • 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.

  • 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?

  • 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

  • odalton

    Did you find a solution to the EAccessViolation error you were getting? I'm getting the same error after adding the list view.

  • Have you logged a case for the problems that you are experiencing?

  • Like odalton and jcaravan, I too get the EAccessViolation error.  I believe it has something to do with the Saved Search button.  The log shows it stopping here:


    |Address |Module      |Unit              |Class                 |Procedure/Method              |Line     |


    |1A64FB7E|eware.dll   |wsTargetLists.pas |TSavedSearchHelper    |AddSavedSearchButtons         |4952[27] |

    |1A64FA98|eware.dll   |wsTargetLists.pas |TSavedSearchHelper    |AddSavedSearchButtons         |4925[0]  |

    |1A437F0A|eware.dll   |wsAdvancedFind.pas|TWebAdvancedFind      |BuildScreen                   |920[58]  |

    |1A437B0C|eware.dll   |wsAdvancedFind.pas|TWebAdvancedFind      |BuildScreen                   |862[0]   |

    |1A437792|eware.dll   |wsAdvancedFind.pas|TWebAdvancedFind      |BuildContents                 |850[164] |

    Thanks, as always,

    Chris P.

  • I think this will need to be logged as a case.

  • thanks for posting this article, I think this will need to log as a case