Imagine you have a requirement to a ensure that a particular opportunity field contains certain data as an Opportunity record is inserted. In addition this information should be partially derived from the parent company data. For example the oppo_customerref field should be populated with a values based on the first 5 letters of the company name plus the user logon.

Validation rules are one obvious way of ensuring that the submitted data is within tolerance. But in this case we need to check the data, and ensure that it is corrected and NOT interrupt the submit. Validation rules when they are fired typically stop the commit.

So what other mechanisms could I use to calculate the value of a field as it is being inserted?

Methods that can be used to derived a value on the initial insert screen include:

I have written about the StoredProc datatype previously.

In the Workflow Primary rule you can use the Set Column value action to fix the new field value to what you want.
But Workflow is a very large subject and to find out more about Primary rules you will need to refer to the System Administration guide.

You can also use the Create Script of either the workflow action field or the static screen (OpportunityDetailBox) to set the value. DefaultValue is a property that is typically used with a new record. You have fully scripting capabilities here and can reference any data in the system to derive the DefaultValue for the new record.

Although you are working in the opportunity you are able to reference the information in the associated company using the ContextInfo method

e.g.

var myCompName = CRM.GetContextInfo("company","comp_name");
DefaultValue = myCompName.substring(0,5)+"/"+CurrentUser.user_logon;

Note:

The 3 techniques that I have outlined above all have a particular failing. They make the assumption that the Company for which the opportunity is to be created is in context as the new Opportunity behaviour is invoked. So if we are creating a new opportunity for Gatecom then it assumes we are already looking at Gatecom. But this is not necessarily how the system will work. I can create the opportunity and then look up the company and person records with which to associate it, or I can started in the context of Gatecom and then switch to Eurolandia as the parent company.

This means the only sure way of calculating the value of the field is after the screen has been submitted.

Here Validation scripts can not be used as we have no way of changing the passed values. We are left with TableLevel scripts

In TableLevel scripts the Values() collection contain the values passed from the form for the database and this can be overwritten

e.g.

if (Values("oppo_myfield")!="Not Correct")
{
Values("oppo_myfield")="Correct";
}

TableLevel scripts can include code associated with the 4 different event functions:

In our case it is obvious that the InsertRecord and PostInsertRecord events would fire. If we consider the actual events that take place when we insert a new Opportunity through the standard New Opportunity workflow in Sage CRM then in fact the following behaviour takes place.

We can see that as well as the InsertRecord and PostInsertRecord events there is scope for an UpdateRecord event being used.

This is may all seem a little peculiar but it is because the new opportunity screen is made up of screens belonging to the Opportunity and OpportunityProgress tables. The Update event function fires because of the interaction between the Workflow tables.

In the PostInsert and Update the information about the company can be retrieved from the record opportunity itself using the WhereClause

e.g. var myRecord = CRM.FindRecord("Opportunity",WhereClause)

It may seem logical to put the script you want in the PostInsert event function but because of the peculiarity of the Workflowed nature of the Opportunity screen any Values that we calculate can get overwritten by the save screen actions.

This means that we end up putting the code into the UpdateRecord event function

function UpdateRecord()
{
var oppoRecord = CRM.FindRecord("Opportunity",WhereClause);
var compRecord = CRM.FindRecord("Company","comp_companyid="+oppoRecord.oppo_primarycompanyid);
Values("oppo_customerref")=compRecord.comp_name.substring(0,5)+"/"+CurrentUser.user_logon;
}

Note:

The following entities have very similar workflow behaviour.

Much of what has been discussed here for Opportunity TableLevel scripts applies for the other similar entities.