Company and Person Workflow Progress with Tracking tab in CRM

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Company and Person Workflow Progress with Tracking tab in CRM

  • Comments 24
  • Likes

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:

  • comp_status
  • comp_createdby
  • comp_createddate
  • [additional fields to be tracked]
    and finally
  • comp_progressnote

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:

  • comp_status (Hyperlink to runblock: CompanyProgressDetailBlock with Custom Id Field: comp_companyprogressid)
  • comp_createdby
  • comp_createddate
  • comp_progressnote

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.


Comments
  • Hi, I have tried running through this post and have got to the section where I need to add the comp_progressnote, but when I select new I am getting a prefix for the field "coma_".

    I have tried changing this to comp_progressnote but get an error message and cannot save the field, message as follows:

    "A valid column name for this table is of the form coma_value"

    Please can any asist with this?

    Regards,

    Martin Conner

    CIM services Limited

  • Did you run the update statement?

    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'

  • Yes, and I have run the query you have posted again and I have noticed that in the messages section following executing the query I get the the following:

    (1 row(s) affected)

    (0 row(s) affected)

    I have queried the Custom_edits table with the same where clause and this returns no data.

    If I try where colp_colname = 'coma_companyid' I get no data.

    If I try where colp_entity = 'companyprogress' then I get 7 lines one of which CopIp_ColName is comp_companyid not coma_companyid.

    Please advise.

  • Hi Jeff,

    I have exactly the same problem as Martin.

    Any ideas as to where I/we have gone wrong?

    Thanks

    Simon

  • Hi Jeff,

    Problem resolved. I managed to change the column prefix in the table back to what it should be

    Thanks

    Simon

  • Cool!

  • Hi Jeff,

    I have got to the point of adding the CompanyProgress table, and its based fields. All modified to comp_xxxx. When i attempt to add any of the comp_xxxx fields i'm getting the the following error: This column name already exists on this table, please enter a different column name - comp_status"

    Please advise where i may have went wrong?

    James McCracken

  • no answer?

  • Sorry!  I am running a training class this week.  I have been a bit maxed out.  I will try and investigate this tonight.  It has been some years since I looked at this area.

  • Hi James.

    Just to help you out a bit earlier then Jeff can ~ this is a known issue with the current version and, we are just waiting for the fix in 7.1 G.

    In the meantime, you will find the field has been created in the new table (through SQL).

    You might need to refresh your metadata/IIS to help it appear through the screen though.

    I have just recently gone through these steps to add a person workflow to a system so if you would like more assistance, I am happy to help.

    Regards,

    Penny Vaskess

    RDA Group

  • Penny

    Thank's for your input.

  • I am trying to establish workflow for Company as well, and have followed the first step in the instructions, but keep getting this error message. "ErrorInSequences SQL Error" - so I cannot even add the table.

    Are the above instructions the same for Release 7.1C?

  • Renee

    I think this is because of the prefix you are using is the same as company.  "comp".  Try changing this to 'coma' and then carry on through the article.  I hope that you are not doing this however on a live system but rather a backup!

  • I was using a different pre-fix, and still the same error message. I decided not to add the progress table, but just do regular workflow in company, and that seems to be working fine...(and of course on the "test" server first) Thank you for your quick response!

  • Hi Jeff,

    I could not get the status box to show in the summary tab, also, I cannot see the workflow actions i've created.

    Is there some changes that need to be made when applying the above to Sage CRM v7.2?

    Regards