When defining a field in a type, that field can be designated as being entered by the user or calculated. If the field is calculated, it is not displayed to the user when the record is being edited. In this case, the value is either set through actions (through tasks that are invoked or events being raised), or it is set by a formula on the field itself.
To set a formula on a field you enter a simple DbfScript into the Expression entry box on the field definition.
Simple DbfScript gives you the ability to use everything in DbfScript except program control statements such as IF, LOOP, WHILE and EXIT SCRIPT. It also looks like a formula, much like formulae in Excel, and so the formula should always be in a format that can be evaluated to a value that can be assigned to something.
The DbfScript is executed with the variables #input and &path provided, and these are used to access the other fields in the record.
For example, if you had two fields in the record called SubTotal and SalesTax, then you could add a field called Total with a formula such as:
You can also reference drilldown (Hamburger icon) in aggregate functions, such as putting:
This will automatically recalculate if the number of Orders in the drilldown changes.
In addition, you can access sub-fields of fields that hold links to other records. WorkflowFirst will automatically retrieve the linked record and allow access to fields within that record.
This formula multiplies the Price field by the rate of the selected currency. Because the currency is selected in a dropdown field called Currency, by referring to /Currency/Rate, the system knows that this field must be accessed via another record. So it loads the selected currency record, takes the Rate value from the record, and uses that value in the calculation.
What's more, this creates a live link between the selected value and the result: If the rate changes, this formula will automatically be recalculated to reflect the new value. This kind of dynamic updating saves a lot of time that would otherwise have to be spent implementing complex workflow programming to achieve the same result. For the most part, WorkflowFirst hides this from you and seamlessly connects these implicit links in formula much the same way as Excel does.
Although you cannot use an IF statement in an expression, you can achieve a conditional statement by using the IIf function (The letter 'I' followed by 'If'). This takes three parameters. The first is the condition, which must be true or false. The second is the value to use if the condition is true, and the third is the parameter to use if the condition is false. For example:
You can also access previous records in the formula which you may do to create a running total, for example. To do this, you access the variable #previous which provides access to all of the fields of the previous record.
For example, to keep a running total of PaymentAmount, you might use a formula such as:
This also means that rows will be recalculated if records are deleted or inserted, as the #previous value would change.
Keep in mind, however, that #previous always refers to the previous row based on the default sort order for that type. If the sort order changes, then the behavior of this formula will no longer work as expected.
Any fields that reference themselves in a previous row will invoke special processing when field values are changed. Because a change to one record can affect all of the subsequent records, known as a waterfall effect, updating a single record can have a significant performance impact. Care should be taken to take this into consideration when writing business logic.
Because of this overhead, it may be preferable to move the waterfall effect of the field's calculation into DbfScript, in an event handler that runs on idle, to more efficiently calculate changes to whole lists that occur when one record changes. Since these can be processed on idle and throttled so they only run once for multiple updates, your application will end up performing significantly less work and will reduce the overall impact of updating the table.
On another note, it's important that the formula used in the expression cannot produce an error. If the formula produces an error then the original action or update will not complete and an error will be displayed to the user. The error may not make much sense to the user so you should be careful to ensure the formula will not fail.
Next Topic: Recalculating
Up Since 1/16/2019 8:20:12 PM