Add a non-database field for computing line totals

When using a generated data source, you may need to add a field to compute a line total. This computed field can then be used to provide aggregates for grouping.

The situation: We have a price and a quantity in our record, but we do not have a total (price * quantity). How do we add this to our business record, and to our generated data source?

You first follow the procedure to generate the data source. You have the .4rdj, you have build the program, and you have the generated data schema (.xsd).

In the Document View, select the tab showing the .4rdj file. The record should be displayed.

Right-click in the record and select Add field from the contextual menu. The field is added to the record. Rename the field by changing the name property. For this example, I name the field line_total. Change the dataType property to be a DECIMAL. Save your changes.

Rebuild all.

Below are two methods for computing the value for the total.

Populate using the computeFields function

Open the generated code, and look for the function and point called "computeFields". For example, when using Java:

protected void computeFields()
  {
    /*<POINT Name="Account_userid_Collection.computeFields">*/ /*</POINT>*/
  }

Within this point, you would add the code to compute the total. The following example is for a generated Java application:

/*<POINT Name="Account_userid_Collection.computeFields" Status="MODIFIED">*/ 
  nextRow.line_total = nextRow.lineitem_unitprice.multiply(new java.math.BigDecimal(String.valueOf(nextRow.lineitem_quantity)));
/*</POINT>*/

You now have a field that computes the total, based on two database columns, and this total is streamed across with each record.

Populate in the SQL statement

When you add a non-database field to the record, it is included in the SQL statement and set to NULL by default. For example, when using Java, the line_total is added as such:

public Account_userid_Collection() throws ClassNotFoundException, SQLException
        {
            fields = Record1.class.getFields();
            String line_total_sqlExpr = "NULL as line_total";
            /*<POINT Name="Account_userid_Collection.sqlExpr">*/ /*</POINT>*/
            String sqlRequest = " "
                        + " SELECT lineitem.orderid, "
                        + " lineitem.linenum, "
                        + " lineitem.itemid, "
                        + " lineitem.quantity, "
                        + " lineitem.unitprice, "
                        + " " + line_total_sqlExpr + " "
                        + " FROM lineitem "
                        + " INNER JOIN orders ON ( lineitem.orderid = orders.orderid ) "
                        + " INNER JOIN account ON ( orders.userid = account.userid ) "
                        + "  "
                        + " ORDER BY   account.userid ASC, "
                        + "                         orders.orderid ASC";

You can use the sqlExpr POINT to change the NULL default, replacing it with the SQL needed to compute the total:

/*<POINT Name="Account_userid_Collection.sqlExpr" Status="MODIFIED">*/ 
  line_total_sqlExpr = "(lineitem.quantity * lineitem.unitprice) as line_total";
/*</POINT>*/

You now have a field that computes the total based on two database columns, and this total is streamed across with each record.