table.column.chain

A table.column.chain is used for two distinct purposes. It can be used to put constraints onto a column, and it can be used to generate calculated values for a column that are based on other columns in the same row.

A constraint chain goes through one or more tests. If the chain returns a value, the constraint is considered to have failed, and the value is taken to be the error message. If the chain gets to the end without returning a value it will return an empty string and be considered to have succeeded.

A calculation chain goes through one or more tests looking for a match. If it finds a match it returns the Chain Return Expression. If no match is found it returns a type-appropriate blank value, which is zero for all numbers, empty string for all character/text types, or null for dates and date-times.

table table_id:
   column column_id:
      chain chain_id:
         # a condition check
         test 00:
            compare: Chain Comparison Expression
            return: Chain Return Expression
         test 01:
            compare: Chain Comparison Expression
            return: Chain Return Expression
         # the unconditional default return value
         test 02:
            return: Chain Return Expression 

table customers:
   # a credit limit, user-entered
   column amt_crlimit:
      suffix: _crlimit
   # the sum of the user's orders
   column amt_orders:
      suffix: _orders
      automation_id: SUM
      auto_formula: orders.amt_final
   # the sum of the user's invoices
   column amt_invoices:
      suffix: _invoices
      automation_id: SUM
      auto_formula: invoices.amt_open
   # put the two together and put a constraint on the result
   column amt_exposure:
      suffix: _exposure
      chain calc:
         # unconditional return
         test 00:
            return: @amt_orders + @amt_invoices
      chain cons:
         # only one test.  If it does not match, chain returns 
         # empty string and the constraint passes.
         test 00:
            compare: @amt_exposure > @amt_crlimit
            return: Credit Limit Exceeded            

Properties

''chain chain_id". A chain can be named 'cons' or 'calc', all lowercase. No other chains are recognized, any other chain name will be ignored. A 'cons' chain defines a constraint, a 'calc' chain defines a calculation.

chain.test test_id. A chain consists of one or more tests. Each test must be given a unique test_id. The test_id values can be anything, but keep in mind that they will be evaluated in their sort order. Best practice is to name tests '00', '01', and so on.

chain.test.compare. A Chain Comparison Expression.

chain.test.return. A Chain Return Expression.