HOWTO - Sashipa development

ComputedTable: For displaying an agregated list.

In a table, you have duplicated data. You wish to display an agregated list of these duplicated data.

Preliminary

There is no problem for displaying an agregated list when each row has a different primary key. In this case, you just have to write a specific selectQueryBuilder. For example the number of countries can be displayed near the continent label because each row is identified by a continent primary key.

In this document, we see problem when data rows doesn't contain a real primary key.

In our example, we'll work on a Drink table with a text column Producer. So if two drinks have the same producer, the producer name will be duplicated.

  CREATE TABLE Drink (
    Id_Drink integer not null primary key,
    Drink_Name varchar(100) not null,
    Producer varchar(100)
  );

We wish now to create a list of all producers, then a Producer card that contains linked drinks list.

Declaring a computedTable

First we declare a computed table that contains a computed primary key. With this computed primary key an agregated value will be able to be used as filter. A computedTable is built on a query, like a SQL view.

Computed tables are added at the end of the environment element:

    ...
    </dbmsSet>

    <computedTableSet>
      <computedTable name='tblAgregateProducer'>
        <selectQuery db='dbDemo' distinctRequired='yes'>
          <fromStatement>
            <mainInstanceTable schemaTable='tblDrink' />
          </fromStatement>
        </selectQuery>
        <computedColumnList>
          <computedColumn name='tblAgregateProducer_Producer' pk='yes'>
            <instanceColumn schemaColumn='tblDrink_Producer' />
          </computedColumn>
        </computedColumnList>
      </computedTable>
    </computedTableSet>

  </environment>

A computed table is made with a selectQuery without the select statement, and the available computed columns. A computed column has a name, and we specify those that constitute the computed primary key.

Creating a listForm that works on a computedTable

Here is the code for creating a listForm that displays producers, with the number of linked drinks.

  <listForm db='dbDemo' delete='no'>
    <bounds x='10' y='10' w='500' h='300' />
    <computedTableRef computedTable='tblAgregateProducer' />
    <instanceColumnList>
      <instanceColumn schemaColumn='tblDrink_Producer' letterCount='38' />
      <agregatInstanceColumn type='countStar' letterCount='6' sort='desc'>
        <title>Nb Drinks</title>
      </agregatInstanceColumn>
    </instanceColumnList>
    <doubleClicScreen screen='SCtblAgregateProducer' />
  </listForm>

Remarks:

Adding and deleting are forbidden, because a producer isn't a real record.
The computed table is used.
Column instances can be created, they just have to be consistent with the query of the computed table. NB: It's impossible to define a new query that would use the computed table.
Number of drinks is an agregate column based on the sql expression 'count(*)'.

Creating a cardForm that works on a computed table

Now we can look at the card screen that displays a row of our computed table.

  <screen name='SCtblAgregateProducer'>
    <title>Producer card</title>
    <formSet>

      <cardForm db='dbDemo'>
        <title>Producer card</title>
        <location x='10' y='10' />
        <computedTableRef computedTable='tblAgregateProducer' />
        <fieldContainer>
          <textField>
            <computedColumnRef computedColumn='tblAgregateProducer_Producer' />
          </textField>
        </fieldContainer>
      </cardForm>

      <listFormRef listForm='FLtblDrink'>
        <title>Drinks</title>
        <bounds x='10' y='100' w='500' h='300' />
        <selectQueryBuilder>
          <castFilterSet autoCastFilter='no'>
            <pkCastFilter>
              <computedTableRef computedTable='tblAgregateProducer' />
            </pkCastFilter>
          </castFilterSet>
        </selectQueryBuilder>
      </listFormRef>
      
    </formSet>
  </screen>

Remarks:

The cardForm is a read-only cardForm, because the displayed record isn't a row in a real table. The field will display a computed value.
The listForm waits for a primary key of the computed table as filter.

Top