Adding Derived Fields in Views into Meta Data

I originally wrote about creating derived fields in a view in the article "Custom_Edits and Derived Fields Created in Views". This discussion is a continuation of the ideas expressed in that article and will show how meta data definitions of a calculated field can be easily added into Meta Data.

For this article I want to consider an existing List block. The general options for customizing Lists have been described in the article "Choices in Customizing Lists" and the opportunity list block (opportunitylist) is based in turn on the view vListOpportunity.

The default vListOpportunity view contains

CREATE VIEW vListOpportunity AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, Pers_PersonId, Pers_CreatedBy, Pers_SecTerr, Pers_PrimaryUserId, Pers_ChannelID, Pers_EmailAddress, Comp_Name, Comp_CompanyId, Comp_CreatedBy, Comp_SecTerr, Comp_PrimaryUserId, Comp_ChannelID, Chan_ChannelId, Chan_Description, Comp_EmailAddress, Opportunity.*, (COALESCE(Oppo_Forecast, 0) * COALESCE(Oppo_Certainty, 0)) / 100 AS Oppo_Weighted FROM Opportunity LEFT JOIN Person ON Pers_PersonId = Oppo_PrimaryPersonId LEFT JOIN Company ON Comp_CompanyId = Oppo_PrimaryCompanyId LEFT JOIN Channel ON Oppo_ChannelId = Chan_ChannelId WHERE Oppo_Deleted IS NULL

The default view already contains the two derived fields, pers_fullname and oppo_weighted. These view fields are partially defined in Meta Data. The meta data for a field is defined in the custom_edits and custom_captions tables. The custom_edits table defines the general behaviour of a field especially in screens and the custom_captions table provides the translation for the field which would be used in screens and as the header for columns in lists, groups and reports. A good starting point to learning more is the article "How does Meta Data create a Screen?".

pers_fullname

select * from custom_edits where colp_colname = 'pers_fullname' (defined)
select * from custom_captions where capt_code = 'pers_fullname' (defined)

oppo_weighted

select * from custom_edits where colp_colname = 'oppo_weighted' (no definition)
select * from custom_captions where capt_code = 'oppo_weighted' (defined)

Note:

Because the derived field is defined both in the custom_edits and custom_captions tables the fields will appear under the customization screens for easier editing.

Administration -> Customization -> Person


Example Business Requirement

Consider the business requirement that the number of days that a sales opportunity has been in progress should be be able to be displayed in the list of opportunities.

The opportunity table contains the two date fields

  • oppo_opened
  • oppo_closed

Note: This example makes the assumption that the oppo_closed field is populated when a sales opportunity is won. The oppo_closed date is going to be null where the opportunity is still In Progress or where the opportunity has been Lost, in which case the date difference between oppo_open and the current system date will be used.

The view above can be altered to allow the calculation to take place.

SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, Pers_PersonId, Pers_CreatedBy, Pers_SecTerr, Pers_PrimaryUserId, Pers_ChannelID, Pers_EmailAddress, Comp_Name, Comp_CompanyId, Comp_CreatedBy, Comp_SecTerr, Comp_PrimaryUserId, Comp_ChannelID, Chan_ChannelId, Chan_Description, Comp_EmailAddress, Opportunity.*, (COALESCE(Oppo_Forecast, 0) * COALESCE(Oppo_Certainty, 0)) / 100 AS Oppo_Weighted, CASE WHEN oppo_closed is null then datediff(day, oppo_opened, getdate()) else datediff(day, oppo_opened, oppo_closed) end as oppo_daysinprogress FROM Opportunity LEFT JOIN Person ON Pers_PersonId = Oppo_PrimaryPersonId LEFT JOIN Company ON Comp_CompanyId = Oppo_PrimaryCompanyId LEFT JOIN Channel ON Oppo_ChannelId = Chan_ChannelId WHERE Oppo_Deleted IS NULL

Note: The derived field may have been called anything BUT it is strongly advised that you follow the naming convention of columns within CRM using the standard table name prefix (comp_, oppo_, case_ etc), so a derived field based on company information should be called comp_xxxxxxx, e.g. comp_myfield.

Defining the Field in Meta Data

The SQL above defines a view field called oppo_daysinprogress. If we want to have this field used in lists and reports etc then we need at the minimum to insert an entry in the custom_captions table to provide a translation for the field's caption.

This can either be done manually using the translation feature or it can be done using the component manager using the 'DerivedFieldWizard' component found amongst the Example Components and Developer Examples gallery that is accessible by members of the Developer Program.

Manually Adding a Field Caption

A derived field can have a translation associated with it be navigating to the translation screen

Administration -> Customization -> Translations

For example to create a new translation for the field oppo_mynewfield you would need to click the new translation button and enter the following

Caption Family: colnames
Caption Code: oppo_mynewfield
US Translation: my field name

Once the entry is saved the information will be used to control the field or column caption used in lists and reports.

Using the Derived Field Utility

The component can be downloaded from the Example Components and Developer Resources gallery. You will need to be a member of the Developer Program to access this file.

The wizard will insert the definition for the field in both the custom_edits and custom_captions table.

Anonymous
  • Stephanie

    I am not sure I understand what it is you wish to achieve. The creation of the view that contains a derived column that is then included in the meta data is one thing and the highlighting of the row is another.

  • I would like to have the view specify that only rows where the derived value is greater than or not equal to zero. Is this possible?

    The derived field is as follows: 'ISNULL((prod_licensecount * sum(OrIt_quantity)),0) as No_of_License'.

    I tried adding prod_licensecount > '0' and prod_licensecount != '0', with and without ''.

    I also tried to highlight those rows in black using API script on the list for ('no_of_license', 'contains', '0'), but that did not work either.

    Is this possible?

  • The transaltion does not work. Use the Derived Field Wizard - it works perfectly with Sage 7.1 SP1 and the field appears in the Report Designer as an actual field in the table. Thank you Mr sagecrm for writing the wizard.

  • The REQUIRED fields on adding a new translation are: Caption Code (Oppo_MyNewField), Caption Family (Tags) and Caption Family Type (ColNames). I hope this helps.