As far as the SDK goes it’s not that different from creating any other field, in fact the way you create a new field itself is identical. The difference is in the setting of a few new properties of the fielddescriptor.
Let’s look at an example here:
EX: Creating a simple calculated field
//this must be a number, decimal or currency
Act.Framework.Database.FieldDescriptor fd = new FieldDescriptor("testField", RecordType.Contact, FieldDataType.Decimal);
//Since this is a decimal, let’s give it some precision
fd.Attributes[FieldProperty.DecimalPrecision] = 2;
//ColumnCalDefinition is where the formula lives. This formula must be able to be recognized by SQL, Actual field names must be used as seen here.
fd.ColumnCalDefinition = "CUST_dec1_011906681 + CUST_dec2_011911584";
//ColumnCalDisplay is a list of fields used in the formula, this should be comma delimited, and is required (Helps with Join statements)
fd.ColumnCalDisplay = "CUST_dec1_011906681, CUST_dec2_011911584";
//Now let’s persist this field we just created and add it to the field list
So, as you can see, the main difference between calculated field creation and the creation of regular fields really breaks down to setting up the ColumnCalDefinition and the ColumnCalDisplay properties correctly. The ColumnCalDefinition contains what is essentially a SQL statement. In this example, it has two fields (CUST_dec1_011906681 & CUST_dec2_011911584) which are specified with the addition operator “+”. The ColumnCalDisplay is simply a list of the fields required for the calculation, the reason this is needed is that it is much easier to separate this from the definition for the SQL engine as it can simply add the display line to a join statement when it executes the calculation in the definition.
Now that we have broken that down, let’s look at a few more examples of ColumnCalDefinition(s):
//Add 3 fields together
fd.ColumnCalDefinition = "CUST_dec1_011906681 + CUST_dec2_011911584 + CUST_dec2_011911305";
//Add 2 fields and multiple by 3rd field – note the parenthesis, very important and useful
fd.ColumnCalDefinition = "(CUST_dec1_011906681 + CUST_dec2_011911584) * CUST_dec2_011911305";
//Multiply one field by a constant value (25%)
fd.ColumnCalDefinition = "CUST_dec1_011906681 * .25";
//Multiply one field by a constant value (25%) then divide all of that by another fields value
// Note: If the divisor is ZERO the result will always show as blank!
fd.ColumnCalDefinition = "(CUST_dec1_011906681 * .25) / CUST_dec2_011911584";
So, as you can see there is really very little limit on what can be done for the savvy SQL programmer
Now that we have covered the creation and configuration of these fields I want to give you some important tips about how calculated fields work that I think all devs and users need to understand.
First of all is the issue of when the calculation (for a calculated field) is executed. It is only calculated when the entity is persisted/saved. This is simply how it is designed due to the fact that our calculations are done via SQL during any save operation (“update” in SQL parlance). Where this comes up the most is when users are filling in field data for an entity and one or more of the fields used in the calculation of another field (IE: a calculated field) are changed and upon tabbing out or moving to another field DOES NOT update the calculated field. Again, it will only calculate when the entity is “Saved”. That is via either an explicate (Click the UI’s save button, or in code save the entity) save or implicate (move off the entity) save.
The other issue is that you can NOT use a calculated field (In the samples above - “testField”) as an operand in the ColumnCalDefinition of another calculated field. The reason for this is pretty simple, without this check you could let users get into a situation of an infinitely recurring calculation (IE: the coding equivalent of the infinite loop.) While there are other ways to address this, this is very clean and decisive way to block the possibility of it ever occurring. In the Act! User interface (Define fields) it will not give you any field that is calculated as an option to add to a user defined calculated field. If you attempt to use a calculated field as an operand via the SDK, it will throw an exception.
In summary, what we have delivered for v16.0 Act! Is a very simple yet powerful way to create and configure calculated fields that are robust, stable, predictable and accessible for developers and users. I hope everyone really enjoys and leverages this feature as I know there has always been a pent up demand for a simple to use calculated fields capability built into Act!.
Please let me know what you think and any question you may have!