Sage CRM 2018: Add a view for custom entities to support the sending of emails in workflow and escalation rules.

Hints, Tips and Tricks

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

Sage CRM 2018: Add a view for custom entities to support the sending of emails in workflow and escalation rules.

  • Comments 6
  • Likes

This article has been written for Sage CRM 2018 but it is also true for Sage CRM 2017 because it references changes to the way emails are sent by workflow and recorded as communications within Sage CRM 2017.

Imagine that you have created a new custom entity called 'Project' for your system. Sage CRM provides the Advanced Customisation Wizard to allow you to do just this very easily as you can see in the image below.

You should notice that I created this entity as a child of company and person.

Depending on the options that you select when running the Advanced Customisation Wizard, Sage CRM will create an example 'stub' workflow. You can see that I have started to customise this workflow by adding a 'Send Email' rule.

I ran into a problem when I tried to create the Workflow Action 'Send Email'.

I therefore needed to create a new view called 'vNotificationProject'.

I added the view under the customisation area of the new entity 'Project'

Administration -> Customisation -> Project

This is the SQL used.

SELECT Project.*, vPersonPE.*, vCompanyPE.*, Users.*, Escalations.*
FROM Users INNER JOIN Project
ON Proj_UserId = User_UserId AND Proj_Deleted IS NULL
LEFT JOIN vPersonPE ON Proj_PersonId = Pers_PersonId AND Pers_Deleted IS NULL
LEFT JOIN vCompanyPE ON Proj_CompanyId = Comp_CompanyId
LEFT JOIN Escalations ON Proj_ProjectId = Escl_RecordID AND Escl_TableId = 10240 AND Escl_Deleted IS NULL
WHERE User_Deleted IS NULL

I based my view on the existing 'vNotificationOpportunity' view.

As I mentioned above, this assumes that the custom entity 'Project' is a child of Company and Person.
I also had to check in the metadata table 'custom_tables' to get the correct ID for the field 'escl_tableid'. This will be different in your system.

Once I had created the view then the workflow action 'Send Email' could be defined and the emails sent and stored as communications.

Comments
  • Hi Jeff,

    How would this view need to be structured if you did not select 'Add to My CRM' when the entity was created???

    My new entity is CareRepair but when it sends the email I get an error in the communication insert saying - Invalid column name 'Comm_vNotificationCareRepairID'. The communication field is comm_CareRepairID!?!

    Thanks

    Roger

  • Roger

    You can create Communications (Tasks, Appointments) unrelated to a user.  The cmli_comm_userid field can be null (although there will be a comm_link record created.

    But don't know about whether escalation rules and notifications (email) require the user data.  I suspect that they do.  I will ask by Support and Dev colleagues.

  • Roger

    Sorry for the slight delay in my response.  I have been discussing this with a colleague in development.  

    I understand that you have a field called comm_CareRepairID.  But that you get a message "Invalid column name 'Comm_vNotificationCareRepairID'".

    My colleagues in development looked at the code around Workflow rules and the Send Email action to see whether this requires a userid to be populated in the cmli_comm_userid field.

    It doesn't.  We know we can create a comm_link record without a user (the cmli_comm_userid  field can be NULL).  This is pretty much the same behaviour to tasks, appointments in the main UI.

    This still leaves us wondering how the reference to a view gets mentioned here.

    My colleague looked at this issue and apparently the error is not related to the selection of “Add to My CRM” option of the Advanced Customization Wizard nor the cmli_comm_userid field.

    What happens is that when we try to create a new communication record the Comm_ProjectId/Comm_CareRepairID column from the Communication table tries to be populated, but it doesn’t exist.

    This column is being created only when the “Has Communication” option is being selected when running the Advanced Customisation Wizard.

    I hope this helps.

  • Hi Jeff,

    I have done this for a custom entity that is a child of a primary (very similar to Quotes). I am able to create an email to send in workflow, however I am unable to include Company fields without getting an error (Access violation at address 1A91668A in module 'eware.dll'. Read of address 00000010).

    I have tried adding a view named vSummaryBid to link the tables. My logic:

    When I create an email template for Quotes, it is pulling from vSummaryQuote. The linked entity fields are not available in the 'Choose Field' drop-down, but I am able to send emails with Company fields included without error.

    To me, the missing link is how to reference vSummaryBid with vsearchlistuser for email template data. Is this accurate? If so, how can this be done?

    Any insight you have would be much appreciated.

    Here is my notifications view:

    CREATE VIEW vNotificationBids AS SELECT Bids.*, vPersonPE.*, vCompanyPE.*, Users.*, Escalations.*, Projects.* FROM Users

    INNER JOIN Projects ON Proj_PrimaryUserID = User_UserID AND Proj_Deleted IS NULL

    LEFT JOIN vPersonPE ON Proj_PersonID = Pers_PersonID AND Pers_Deleted IS NULL

    LEFT JOIN vCompanyPE ON Proj_CompanyID = Comp_CompanyID AND Comp_Deleted IS NULL

    INNER JOIN Bids ON Bids_ProjectsID = Proj_ProjectsID AND Bids_Deleted IS NULL

    LEFT JOIN Escalations ON Bids_BidsID = Escl_RecordID AND Escl_TableID = 10241 AND Escl_Type IS NULL AND Escl_Deleted IS NULL

    WHERE User_Deleted IS NULL

    Thank you. Steph

  • I have noticed if using escalation rules to send out emails to CRM users when opportunities match the criteria, if more than one opportunity matched the criteria then the communications that are created are linked to the same opportunity (the first ID in the list) sage CRM support have been notified and has been tested in 2017 R3 and 2018 R2

  • That's good to know.