Order Lines Part Two

Now that we have defined an orders table that contains the price that was FETCHed from another table, we must multiply that price by the quantity to get the extended price.

Adding Qty and Price_extended

To begin with, we will take our definition of order lines from the previous tutorial and add the qty column.

We also need to add a column for extended price. Now most database designers at this point will give this column a name like 'extended_price' and make it the same type and precision and scale as the 'price' column. This is very easy to do in Andromeda, we can simply tell it that we want to use the 'price' column again, but this time we want to rename it slightly:

table orders_d:
   module: orders
   description: Order Lines
 
   foreign_key orders_h:
      primary_key: "Y"
      uisearch: "Y"
   foreign_key items:
      primary_key: "Y"
      uisearch: "Y"
   
   column price: 
      automation_id: FETCH
      auto_formula: items.price
   
   column qty:
      uisearch: "Y"
   column price_extended:
      suffix: _extended 
      uisearch: "Y"

By convention, all Andromeda applications use Latin Notation when forming column names, which causes similar columns to appear coherently in lists. This means we don't make columns like 'price' and 'discount' and 'extended', but rather 'price' and 'price_discounted' and 'price_extended'. The adjective always follows the noun in Latin Notation.

We should note that the column qty is is defined in andro_universal.add, that is why we did not need to declare it anywhere.

The Formula For price_extended

Now we will use a chain to declare the calculation of price_extended. A chain is slightly more complicated than a FETCH, because it has the ability to consider many cases and possibly return different values, like a CASE WHEN in SQL or an switch in other languages. In this case we want an unconditional calculation, so there is only one test in the chain:

table orders_d:
   module: orders
   description: Order Lines
 
   foreign_key orders_h:
      primary_key: "Y"
      uisearch: "Y"
   foreign_key items:
      primary_key: "Y"
      uisearch: "Y"
   
   column price: 
      automation_id: FETCH
      auto_formula: items.price
   
   column qty:
      uisearch: "Y"
   column price_extended:
      suffix: _extended 
      uisearch: "Y"
      chain calc: 
         test 00:
            return: @qty * @price

This syntax declares a 'calc' chain. (There are also constraint chains that we will see in later tutorials). Inside the chain is a test, which has an unconditional return value. When a column is named in a chain, it must be preceeded by an @ sign, to distinguish columns from literal values.

Again we have to stress that the big advantage we have here is that Andromeda will build a database that performs these calculations automatically and will not allow user input to replace them with bad values. So no programming code is required, as of now we have written to only a single file, the Database Specification File, and everything has flowed automatically from that.

On a final note, if you are following these tutorials and cutting-and-pasting, the ADD File Format absolutely requires only one space between the arguments in the chain. This is considered a known weakness and will be relaxed in the future.

In the next tutorial we will see how to sum the lines to the order total.