HOWTO - Sashipa development

Field: How to define selected values.

You wish to display a computed value in an unsaved field of your cardForm. This value doesn't match to a database column.

Look at the example database, there are Boxes contain Products. Here is the SQL script for creating tables (with HSQL syntax):

  CREATE TABLE Box (
    Id_Box integer not null primary key,
    Box_Name varchar(100) not null
  );
  CREATE TABLE Product (
    Id_Personne integer not null primary key,
    Ref_Box integer not null,
    Product_Label varchar(100) not null,
    Price integer,
    FOREIGN KEY (Ref_Box) REFERENCES Box (Id_Box)
  );

In the Box card, we wish to display the sum of product prices. For doing this, we replace the schemaColumnRef element by a selectedValue, which contains a selectQueryBuilder. Example:

  <cardForm db='dbExample'>
    <title>Box card</title>
    <location x='10' y='10' />
    <cardSchemaTableRef schemaTable='tblBox' updateAfterInsert='no'
                        multipleInsert='yes' queries='sudi' />
    <fieldContainer>
      <textField>
        <schemaColumnRef schemaColumn='tblBox_BoxName' />
      </textField>

      <!-- Computed field -->
      <textField readOnly='yes' save='no'>
        <label>Sum of prices</label>
        <selectedValue>
          <selectQueryBuilder>
            <castFilterSet stuckWhenNoFilter='yes' />
            <selectStatementBuilder>
              <instanceColumnList>
                <agregatInstanceColumn type='sum' schemaColumn='tblProduct_Price' />
              </instanceColumnList>
            </selectStatementBuilder>
            <fromStatementBuilder>
              <mainInstanceTable schemaTable='tblProduct' />
            </fromStatementBuilder>
          </selectQueryBuilder>
        </selectedValue>
      </textField>

    </fieldContainer>
  </cardForm>

Remarks:

The label is required for computed fields.
The attribute stuckWhenNoFilter='yes' means that the query won't be executed in adding mode. If we set this attribute to 'no', the field will display the sum of all product prices (of all boxes) in adding mode.
The agregatInstanceColumn element has type = 'sum' and works on the Price column.
The from statement contains the table tblProduct. So the query is able to receive as filter: a Product primary key value or a foreign key value. Here it will be filtered with the current primary key value of the cardForm.

For details on selectQueryBuilder, please see the SelectQueryBuilder HOWTO.

Top