Many thanks must go to a colleague in the UK professional services team for this article.
Introduction
Customers often request the ability to track Company or Person changes in much the same way that CRM offers with Opportunity and Case entities. As standard, CRM does not offer a CompanyProgress or PersonProgress table but these can be set up quite easily.
The approach shown here creates a CompanyProgress table and uses custom content on the Company Summary screen to display a Status Panel. A Tracking tab is then added to the Company tabs.
Although the instructions here show creation of Company tracking exactly the same principle can be used for Person tracking and can, in principle, be applied retrospectively to Custom entities where tracking is required as an afterthought.
Tracking in CRM
In CRM a tracked field is a field that is present on both the main entity table and the tracking table (Progress table) and CRM automatically links fields with the same name in the two different tables, such that changes in one are replicated in the other. In Custom_Tables, the main entity table record will have the name of the tracking table in the Bord_ProgressTableName column – if this data is absent, tracking will not take place, and tracking records will not be created although CRM will still automatically link fields.
When a tracked field is present on a screen that is being submitted (i.e. in Save mode), a progress record is created recording the changes even if that field is read-only or hidden. Thus, if the intention is to use tracking solely for workflow, tracked fields should not be present in the Company Entry Screen (CompanyBoxLong), otherwise a progress record will be created any time anyone updates the Company Entry Screen.
The major constraint of the approach shown here is that CRM does not provide Primary rule functionality for Company or Person Entities with the Primary rule screen embedded in the New Company/Person screen. However, careful workflow design and automation using InsertRecord() in Table Level Scripts should enable you to avoid problems with the absence of tracked fields in the New entry screens.
Set up Tracking with CompanyProgress Table
1. Create Table CompanyProgress:
Table Name: CompanyProgress
ID Field Name: comp_companyprogressid
Column prefix: comp
Company Id Field: comp_companyid
Leave other fields blank/default.
Note: It is not possible in Sage CRM to set the column prefix to a pre-existing prefix. You will need to use something similar (e.g. coma) to create the table then edit database Custom_Tables, Custom_Edits and Custom_Captions metadata records accordingly.
e.g.
update custom_tables
set
bord_idfield = 'comp_companyprogressid'
where bord_name = 'companyprogress'
update custom_edits
set colp_colname ='comp_companyid'
where
colp_colname = 'coma_companyid'
and
colp_entity = 'companyprogress'
Also alter the column names in SQL and do a metadata refresh, All.
2. Create the following additional fields on the CompanyProgress table within the fields tab
Administration -> Customization -> companyprogress
Field: comp_progressnote
Caption: Tracking note
Properties: Multiline Text (create on Company table as well)
Field: comp_status
Caption: [this is automatically matched with existing field]
Properties: [this is automatically matched with existing field]
Additional fields to be tracked can be added to the table but these must exist in and match the fields in the Company table.
3. Link the CompanyProgress table to the Company table. Run the following Queries in SQL:
UPDATE Custom_Tables
SET Bord_ProgressTableName = 'CompanyProgress'
WHERE Bord_Caption = 'Company'
UPDATE Custom_Tables
SET Bord_ProgressNoteField = 'comp_progressnote'
WHERE Bord_Caption = 'CompanyProgress'
Run a Metadata Refresh, All.
Set up Tracking Tab
4. Within the administration screens for
Administration -> Customization -> companyprogress
create a new Entry Screen CompanyProgressSummary and add the columns:
5. On CompanyProgress create new Container Block CompanyProgressDetailBlock and add screen CompanyProgressSummary. Show Continue button: Yes
6. On CompanyProgress create the CompanyProgressList screen on table CompanyProgress. Add the following columns:
Note: the last column will not emulate the .dll-generated Progress column found in out-of-box tracking.
7. On CompanyProgress create new List Block CompanyTrackingBlock, associated list: CompanyProgressList. Set the properties "Show New button" to No
8. On Company Tabs, add Tracking tab Action: runblock, CompanyTrackingBlock
9. Enter the following translations for the List block results
Administration -> Customization -> Translations
Caption Family:CompanyProgress
Caption Code: NoRecordsFound
US translation: No Progress Entries Found
Caption Family:CompanyProgress
Caption Code: RecordFound
US translation:Progress Entry Found
Caption Family:CompanyProgress
Caption Code: RecordsFound
US translation: Progress Entries Found
Create and Display the Status Panel
This section is based on an a previous article called "Adding a new Panel to a Summary screen e.g. Company Summary".
10. On Company, create new Screen CompanyStatusBox and add all fields to be tracked to it, ensuring that none are present in CompanyBoxLong.
11. On Company, create new EntryGroup Block CompanyStatusBlock, associated list: CompanyStatusBox, all Show buttons set to No.
12. Create new translations for to allow the safe passage of client side javascript in a create script. See the article called "Passing client side Scripts in a field oncreate script".
Caption Family: scripttags
Caption Code: scriptopen
US translation: US: <script>
Caption Family: scripttags
Caption Code: scriptclose
US translation: US: </script>
13. On Company, create new Company field comp_dummy, Text 1, Caption: Dummy, set to ReadOnly for everyone and add to CompanyBoxLong in an inconspicuous location. Add the following CreateScript
Note: that with a large number of System Action "Act" values, a switch statement is more compact, Person Acts will differ:
var blnShow=false;
switch (Values("Act"))
{
case "140":blnShow=true;
break;
case "160":blnShow=true;
break;
case "200":blnShow=true;
break;
case "400":blnShow=true;
break;
case "520":blnShow=true;
break;
case "523":blnShow=true;
break;
default:blnShow=false;
}
if(blnShow)
{
var strHTML="";
var compId = CRM.GetContextInfo("company","comp_companyid");
if(compId>0)
{
var StatusPanel = CRM.GetBlock("CompanyStatusBlock");
StatusPanel.DisplayButton("1") = false;
StatusPanel.DisplayForm = false;
var compRec = CRM.FindRecord("company","comp_companyid="+compId);
strHTML += StatusPanel.Execute(compRec);
}
Caption = CRM.GetTrans("scripttags", "scriptopen");
Caption +="var strPanelHTML ='";
Caption +=strHTML;
Caption += "';";
Caption +=CRM.GetTrans("scripttags", "scriptclose");
}
else
{
Hidden = true;
}
14. On Company, add the following Custom Content (integrating with any existing code) to CompanyBoxLong:
<script>
window.attachEvent("onload",writePanel);
function writePanel()
{
if(!document.getElementById("comp_name"))
{
var strTD="";
for(i=0;i<document.all.length;i++)
{
if(document.all[i].tagName=='TD' )
{
if(document.all[i].className.search(/ROWGap/i)>-1)
{
strTD = document.all[i].innerHTML;
strTD += strPanelHTML;
strTD += "<TR><TD class=ROWGap colSpan=6>";
strTD += "</TD></TR>";
document.all[i].innerHTML = strTD;
break;
}
}
}
}
}
</script>
Create the Company Workflow
15. Enable Company Workflow in CRM.
16. Create a Company Workflow using fields to be tracked.
17. Add the Tracking note field to the bottom of each Workflow screen on a Set Column Value = [blank] so that a new tracking note can be added with each progress record. Each time a workflow rule is saved a new entry appears with Tracking note on the Tracking tab and tracked field changed values will appear in the Progress Summary screen.