Automatically Filtering Reports on Custom Entities by the Currently Assigned User in Sage CRM.

Hints, Tips and Tricks

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

Automatically Filtering Reports on Custom Entities by the Currently Assigned User in Sage CRM.

  • Comments 1
  • Likes

It is a common business requirement to add new custom entities into Sage CRM.  This can be easily done either by using the Advanced Customization Wizard or by using the slightly more manual process of adding the new table under the Administration screens.  See:

Administration -> Advanced Customisation -> Tables and Databases

and the Create Table option.

Once the new entity has been created you may have to ensure that other business requirements are met, such as limiting access to data in reports based on which user is the assigned owner of the new entity.

For example in my system I added a new entity called Project using the Advanced Customization Wizard.  The projects when created are assigned to different users.

  • User X is assigned Project A
  • User Y is assigned Building B,C and D
  • User Z is assigned Project E

Each of these users are in the same territory but in my example the business needed to be certain that when a report was run the users only retrieve the Projects to which they are assigned.

In order to create the report I needed to build a new view on the entity and mark it as a report view.

The SQL of the report joined the custom Entity, in my case, Project, with the user table.

[code language="sql"]
CREATE VIEW vProjectReport
AS 
SELECT dbo.Users.*, dbo.Project.*
FROM dbo.Project INNER JOIN
dbo.Users ON dbo.Project.proj_UserId = dbo.Users.User_UserId
[/code] 

Another important point is that the custom entity was created by the Advanced Customization Wizard.

When the entity was created using the wizard a description of the new table and its relationships were entered into the meta data table custom_tables.

This can be seen by using

[code language="sql"]
select * from Custom_Tables where Bord_Name = 'project'
[/code] 

The important field for automatic report filtering by assigned user is bord_assigneduserid.  

This allowed me to create a report that will be filtered by the current user.

 

Comments
  • I see that this is a few years old, but in my project I have a requirement for a custom owner field on the quote entity. The owner field is a user select field. If I update the metadata for the quotes table and set the bord_assigneduserid to reference my owner field, then this would solve my issue. However, is that change to the quote entity entry in custom_tables a supported change?