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.
