Summary:

It is possible to edit the metadata for a primary entity's ID field so that it can be used in groups. However, the ID will not then appear in the group contents. Editing the field's metadata through SQL is not supported, though a number of other methods exist for displaying unique IDs against entity records.

Symptoms:

Consider the Person entity. It has a unique ID field named Pers_PersonId. It is possible to make this field available for selection when creating a group by running the following SQL, then running a metadata refresh:

Update Custom_Edits
Set ColP_System = 'N'
Where ColP_ColName = N'Pers_PersonId'

On exporting the group contents, the Person ID will not be included. Foreign key fields (such as Pers_PrimaryAddressId) may be included in the export.

Cause:

This is by design. The Sage CRM metadata should not be manipulated directly through SQL, except in the case where it is done to resolve an issue. This customisation is unsupported.

Resolution:

There are a number of methods of resolving this issue. What approach is taken will depend on what is desired form the system.

In the case where the actual entity ID is required, the ID field can be aliased off to a custom fields on the entity's table. This custom field can then be included in the view test.

  1. Create a new Integer field named Pers_MirroredPersonId in Administration | Custmomisation | Person | Fields.
  2. Create a new view named vPersonGroupViewWithId. This view will be used for our group. Note that the view enumerates all required fields individually. This is be cause carrying out a SELECT * will result in the dummy field's data being included in the view. This would result in an error.

    CREATE VIEW vPersonGroupViewWithId AS
    SELECT Pers_PersonId, Pers_PersonId AS Pers_MirroredPersonId,
       Pers_FirstName, Pers_LastName, Pers_Salutation
       Pers_EmailAddress,
       Pers_PhoneCountryCode, Pers_PhoneAreaCode, Pers_PhoneNumber,
       Pers_CreatedBy, Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate,
       Pers_ChannelID, Pers_PrimaryUserId, pers_SecTerr
    FROM vPersonPE

    In this view, the Pers_PersonId field ias aliased off as Pers_MirroredPersonId.
  3. Using this view as the source for the group should make the ID available for export via the dummy field.

In the case where all that is required is a unique record ID for the entity (rather than the actual ID stored internally), other options exist. The most straightforward option is to create a new custom field on the entity, and have it use the StoredProc (Stored Procedure) entry type. This entry type can then call the eware_default_values stored procedure. This stored procedure is used when assigning unique references to Cases and other entities, and can be customised.

In the example below, a new custom field has been added to the Person entity. It has been given the name Pers_ReferenceId. This sample will result in that field on the Person record being given a unique reference in the format user ID-incrementing number whenever a new record is added.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[eware_default_values]
   @table_name nvarchar(50),
   @identity_name nvarchar(50),
   @field_name nvarchar(50),
   @logon_no int,
   @extravalue nvarchar(50)='',
   @default_value nvarchar(128)='' OUTPUT
AS

BEGIN
/* Declare variables to be used */
   DECLARE @unique_value int
   DECLARE @ErrorSave INT
   SET @ErrorSave = 0

   IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
   BEGIN
      EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
      SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
   END
   ELSE IF (@table_name='Solutions') AND (@field_name = 'Soln_ReferenceId')
   BEGIN
      EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
      SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
   END
   ELSE IF (@table_name='Person') AND (@field_name = 'Pers_ReferenceId')
   BEGIN
      EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
      SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
   END
   ELSE BEGIN
      SELECT @default_value=''
   END

   -- Save any nonzero @@ERROR value.
   IF (@@ERROR <> 0)
      SET @ErrorSave = @@ERROR   

   -- Returns 0 if neither SELECT statement had
   -- an error; otherwise, returns the last error.
   RETURN @ErrorSave
END