In CRM it is quite common to need to create a view that performs a calculation or derives information to be displayed on screen or in a report.
An example of this can be found in the Case management area. For example if you logon to a demo system as Kylie Ward (WardK) and navigate to the My CRM menu and view the Case List you will see a column showing the SLA status of each case in graphical form. Cases within the SLA show a green image, those on the borderline show an amber image and those where the SLA has been broken show a red image.
The Case List is based on the list block "caselist" which in turn uses the view "vListCases". You can find these defined under the
Administration>Customization area for the Cases entity.
The view "vListCases" has the contents
CREATE VIEW vListCases AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, CASE WHEN Case_Status <> 'closed' AND Case_SLACloseBY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmberCloseBy < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color, Pers_PersonId, Pers_CreatedBy, Cases.*, Comp_Name, Comp_CompanyId, Comp_CreatedBy, Pers_SecTerr, Comp_secTerr, Pers_PrimaryUserId, Comp_PrimaryUserId, Pers_ChannelId, Comp_ChannelId, Chan_ChannelId, Chan_Description, Comp_EmailAddress, Pers_EmailAddress FROM Cases LEFT OUTER JOIN Person ON Pers_PersonId = Case_PrimaryPersonId LEFT OUTER JOIN Company ON Comp_CompanyId = Case_PrimaryCompanyId LEFT OUTER JOIN Channel ON Comp_ChannelId = Chan_ChannelId WHERE Case_Deleted IS NULL
The important thing here is the CASE statement
CASE WHEN Case_Status <> 'closed' AND Case_SLACloseBY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmberCloseBy < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color
which evaluates whether the green, amber or red colour should be used.
The column "case_color" DOES NOT exist in the database. It is an entirely derived "alias".
In the browser the column that contains the image has the title 'SLA Status'. In order for this derived column to look like it is part of CRM it must have meta data to control its properties.
The custom_edits table is the metadata table responsible for defining the way that a field looks and behaves in Sage CRM. The custom_captions table defines the screen prompts and translations used.
You can run the follow statements against the CRM database:
select * from custom_edits
where colp_colname = 'case_color'
select * from custom_captions
where capt_family = 'colnames'
and capt_code = 'case_color'
This will show how the derived column "case_color" has been defined.
Because the column has been described in the custom_edits table its properties can be controlled. For example the "case_color" column has been set in the CaseList list block to "Show Select as Gif". The entry in the custom_captions table allows the correct translations to be provided for multilingual implementations.
If we now create a view with a calculation and we want to have control of the correct display of the derived column then we will must also make sure that there is an entry in the custom_edits and custom_captions table for that column.
There are 2 easy ways of providing such information. The first is to create a dummy or empty field on one of the tables involved in the view. This column although added to the database will not have data entered into it. This idea of a dummy field is quite often used for advanced screen customization techniques.
The second way of easily entering the column is to create a link from CRM to the view as if it were an actual table. Creating a link to the view defined in CRM's meta data would allow the main CRM interface to be used to describe the derived field.
This can be found under the
Administration> Advanced Customization> Tables and Databases
are of the product. The linking to an external table is described in the System Administration guide.