Creating Groups on Custom Entities in Sage CRM v7.1

Hints, Tips and Tricks

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

Creating Groups on Custom Entities in Sage CRM v7.1

  • Comments 15
  • Likes

Note: I have mentioned Sage CRM v7.1 in the title because that is the version with which I worked.

I have created a new custom entity called 'Project' using the Advanced Customization Wizard (aka Entity Wizard, Main Entity Wizard).  

The requirement:

The requirement was to be able to use data from the project table in groups used for outbound emails, and within the Interactive Dashboard as data sources for List Gadgets.

This article will demonstrate how to build the group to use as a data source in outbound emails and within the interactive dashboard.

The Project entity is a child of the following entities.  The foreign key fields are listed.

  • Company (proj_companyid)
  • Person (proj_personid)
  • Opportunity (proj_opportunityid
  • Team (proj_channelid)
  • User (proj_userid)

The entity is the parent of the following entities.

  • Cases
  • Communications
  • Documents

All groups are based on Views.

Creating the View

The view is created under

Administration -> Customization -> Project

The SQL is based on the view that is called vSearchListCase.

I have used that view because it is also a view used for Groups and correctly joins the Person and Company records.


CREATE VIEW vSearchListProjects
AS 
SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName,
RTRIM(ISNULL(Pers_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneAreaCode, '')) + ' ' 
RTRIM(ISNULL(Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber, RTRIM(ISNULL(Pers_FaxCountryCode, '')) + ' ' + 
RTRIM(ISNULL(Pers_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_FaxNumber, '')) AS Pers_FaxFullNumber,  vPersonPE.*, 
Project.*, vCompanyPE.*, Address.* FROM Project LEFT JOIN vCompanyPE ON proj_CompanyId = Comp_CompanyId LEFT 
JOIN vPersonPE ON proj_PersonId = Pers_PersonId LEFT JOIN Address ON Pers_PrimaryAddressId = Addr_AddressId WHERE 
proj_Deleted IS NULL
 

Once the view has been created it can used to create Groups.

I called my group "All Projects (group)".  The view created above allows all fields from the company, person, address and the project entity.

You can add search criteria if necessary on any of the included fields.  I did not do that as I wanted my group to return all records.

Once the group has been saved you will be warned "This is a custom entity or legacy group. Not all of the Group actions can be performed on it."

But I have found that I can carry out the following tasks

New Document

  • The Mail Merged Letter can include any fields from the Project Entity. BUT the documents are stored at the level of the Company and Person, not attached to the Project.

New Task

  • Tasks will be created linked to the Company and Person BUT NOT the Project.

New E-mail

  • Email Templates can be created for the custom entity (Project).

  • These templates can include any of the fields from custom entity (Project) included in the group.

Export to File

  • This will include any fields from the custom entity (Project) within the displayed fields of the group.

 

Interactive Dashboards

The Group is also available for Interactive Dashboards as a data source for List Gadgets.

Note:  This group is not available for use in Outbound Calls.

 

 

 

Comments
  • Can we use a view to retreving information from different tables related to the project and send e-mail with the related information in the body of teh e-mail?  For exemple.project activities.

  • Please see also

    community.sagecrm.com/.../fields-available-in-email-templates.aspx

    Mail Merge for custom entities uses vSearchListUser.

  • In SP2 on legacy group the actions New document and Export to excel is no longer available Is this normal?

    What make a group to be identified as legacy group?

  • 'Legacy Group' terminology is based on old Target Lists. "Groups" were called "Target Lists" in releases before Sage CRM v6.1 when the terminology (and behaviour) changed.

    The goal was to show that warning for old target lists. The message will appear when you are using a group that has been built off a custom entity and which could potentially return duplicate records.

    So is not an error message but a warning message.

  • Thnak you Richard for the explanation.

    This means all groups based on a custom entities will be a legacy group?

    Is its normal that with 7.1SP2 we do not have access to export or create a mailmerge with these groups?

  • Hi Jeff

    Were on Version 7.1.f  and am not able to see some of the actions. The only one we can see is the new email action, but we used to create documents in bulk. Is it possible to get the new document / mail merge button back.

    Thaks

    Adam

  • We have upgraded one of our client system from 7.1 c to 7.1 g and now the export to file and Start Mail Merge are not available.

    Can any body help.

    Krish

  • The above issue is only happening for a customized group created from a customized page.

    Krish

  • Krish

    Explain exactly the issue that you have.  Do the buttons display?  Does an error take place when try and use the feature?

  • Hi Jeff - I have a custom entity called Delivery and I have created a view as follows

    SELECT DELIVERY.*, vcompanyPE.*, address.*  

    FROM DELIVERY join vcompanyPE on del_companyid = comp_companyid LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId where del_deleted is null and comp_deleted is null

    We are at 7.1.h and when I use the view in a group I get warned that This is a custom entity or legacy group. Not all of the Group actions can be performed on it.

    When I open the group I do not have the option to start a mailmerge and I really need it.

    Is there a work around for this?

    John

  • John

    Please see these articles about getting Mail Merge to work on custom entities in versions later than Sage CRM v7.1sp2.

    community.sagecrm.com/.../sage-crm-v7-1-sp2-ensuring-a-new-custom-entity-has-mail-merge-capabilities.aspx

    community.sagecrm.com/.../adding-document-filing-and-mail-merge-functionality-to-your-existing-custom-entities.aspx

  • Thanks Jeff - It is now working

  • I have done this but when I actually go to send the emails (past the email template stage) it says that it is not associated with a person or company, when it clearly is. Any ideas?

  • Hannah,do other actions work?  Can you carry out a mail merge?  I don't know why the email merge should not work, so you may need to have a look at the SQL and system logs to try and workout what is going on.

  • I finally figured out how to get the "Export to File" option to come back for custom entities in 7.2.

    Find the custom entity record in the Custom_Tables table and set the Bord_HasCommunication field to "Y"

    NOTE: if your custom entity was created with "Has Communications" then you probably did not lose the "Export to File" feature.

    Hope this helps!

    Kevin.