Please see the article "Reports, Saved Searches and Groups" for background information on how Groups are defined in Sage CRM.

This article is relevent when needing to create Groups that are based on a view that returns multiple instances of the same person record. This maybe because of needing to create a linking to an external database table (see "Creating Groups on External Tables") or where a new entity (e.g. project or event) has been created in Sage CRM using the Advanced Customization Wizard (aka Entity Wizard).

Consider this:

I created a new Entity "Project" in Sage CRM using the Advanced Customization Wizard. I then created a view vPersonProject in Person entity in order to create a Group.

The view was very simple:

[code language="sql"]
CREATE VIEW vPersonProject
AS
SELECT *
FROM PERSON left outer join Project
ON proj_PersonId = Pers_PersonId
WHERE Pers_PersonId IS NOT NULL
[/code]

I then enabled the "group view" and "report view" options.

But I discovered that although the view could be found in new report screen it was missing in the group creation screen.

I then ran the SQL statement in SQL Analyser

[code language="sql"]
select distinct(pers_personid) from vpersonproject;
[/code]

And I found that because the same person can be the parent of multiple projects, there could be multiple links. The SQL Statement would therefore show multiple records for same person.

Changing A System Parameter

There is a setting in custom_sysparams that controls whether a system can have multiple instances of the same person record in a group.

I had to change the parameter "allowduprecs" (allow duplicate records) in custom_sysparams table and change the value to 'Y'.

Many System Parameters are controlled through the interface but I needed to change this in the database by running a SQL statement.

[code language="sql"]
update custom_sysparams set parm_value = 'Y' where parm_name = 'allowduprecs';
[/code]

I then had to refresh the meta data, but once that had been done the view was available in Group definition.