Modify field on another table in a workflow action

Hints, Tips and Tricks

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

Modify field on another table in a workflow action

  • Comments 1
  • Likes

When choosing a field for the Display Field For Amendment action, you can select fields from the view that are not on the table the rule is based on.  For example you can select the comp_primaryuserid if you use the vSummaryOpportunity view to base your rule on.  However this will not work by design:  You cannot modify fields using a workflow action that are not in the table that the workflow rule is based on.
You might say that the rule is based on a view and not a table but actually when you select the vSummaryOpportunity table view CRM automatically fills in the table for you based on the entity that the view is setup under.

So how do you make changes to fields in other tables?

Here is a workaround to do this.  In this case it illustrates how to change the comp_primaryuserid in an opportunity workflow, but you can adapt it for other fields:

1.  Create a User Select field on the Opportunity entity called oppo_changeaccountmanager

2.  Create a table level script on the Opportunity entity with the following code:

function InsertRecord()
{
    if (Values('oppo_primarycompanyid')!='') {
        comp = eWare.FindRecord("Company", "comp_companyid=" + Values('oppo_primarycompanyid'));
        Values('oppo_changeaccountmanager') = comp.comp_primaryuserid;
    }
}
function UpdateRecord()
{
    oldoppo = eWare.FindRecord("Opportunity",WhereClause)
    if (oldoppo.oppo_changeaccountmanager!=Values('oppo_changeaccountmanager') && Values('oppo_changeaccountmanager')+''!='undefined') {
            comp = eWare.FindRecord("Company", "comp_companyid=" + oldoppo.oppo_primarycompanyid);
            comp.comp_primaryuserid = Values('oppo_changeaccountmanager');
            comp.SaveChanges();
    } else if (oldoppo.oppo_primarycompanyid!=Values('oppo_primarycompanyid') && Values('oppo_primarycompanyid')+''!='undefined') {
        comp = eWare.FindRecord("Company", "comp_companyid=" + Values('oppo_primarycompanyid'));
        Values('oppo_changeaccountmanager') = comp.comp_primaryuserid;
    }
}

function DeleteRecord()
{
  // Handle delete record actions here
}

function PostInsertRecord()
{
  // Handle post insert record actions here
}

3.  Create a table level script on the Company entity with the following code:

function UpdateRecord()
{
    oldcomp = eWare.FindRecord("Company",WhereClause)
    if (oldcomp.comp_primaryuserid!=Values('comp_primaryuserid')) {
        var sql = 'UPDATE Opportunity SET oppo_changeaccountmanager = ' + Values('comp_primaryuserid') + ' WHERE oppo_primarycompanyid=' + oldcomp.comp_companyid;
        eWare.ExecSql(sql);
    }
}

function InsertRecord()
{
  // Handle insert record actions here
}

function PostInsertRecord()
{
  // Handle post insert record actions here
}

function DeleteRecord()
{
  // Handle delete record actions here
}

4.  Now setup your workflow action to display the oppo_changeaccountmanager field for amendment

 

Comments
  • Hallo Jack,

    thank you for this Post.

    I tried to adapt it to my need. I have to change the comp_status field inside a Communication-Workflow i created.

    It dosenĀ“t work and i think it depends on the "whereclause" becaus in Communiction there is no company_id availible, only in Comm_link table.

    Can you give me a advice how to solve this.

    Best Regards

    Weber Stephan