HOWTO - Sashipa development

How to display an indirect listForm in a card.

In the same screen than your cardForm, you want to display indirect linked data in a listForm. Indirect linked data references other table, which references the cardForm table.

Preparation

First I give you the database SQL script on which we are going to work (here with HSQL syntax) :

  CREATE TABLE Organization (
    Id_Organization integer not null primary key,
    Name varchar(100) not null
  );
  CREATE TABLE Section (
    Id_Section integer not null primary key,
    Ref_Organization integer not null,
    Name varchar(100) not null,
    FOREIGN KEY (Ref_Organization) REFERENCES Organization (Id_Organization)
  );
  CREATE TABLE Person (
    Id_Person integer not null primary key,
    Ref_Section integer not null,
    Name varchar(100) not null,
    FOREIGN KEY (Ref_Section) REFERENCES Section (Id_Section)
  );

There are Organizations, that contain several Sections, that contain several Persons. We wish to display a list of Persons in the Organization card screen.

Problems :

It's easy to display persons in a listForm from an Organization primary key as filter (we just need a join). Problems will appear when adding is enabled from the listForm: in the new Person cardForm, the Section fkField should be filtered with the Organization primary key. Idem when opening the Person cardForm for modification.

The Organization card screen

Look at the code of the Organization card:

  <screen name='SCtblOrganization'>
    <title>Organization card</title>
    <formSet>

      <cardForm db='dbDemo'>
        <title>Organization card</title>
        <location x='10' y='10' />
        <cardSchemaTableRef schemaTable='tblOrganization' queries='sudi'
                            updateAfterInsert='yes' multipleInsert='no' />
        <fieldContainer>
          <textField>
            <schemaColumnRef schemaColumn='tblOrganization_Name' />
          </textField>
        </fieldContainer>
      </cardForm>

      <listForm db='dbDemo' delete='yes'>
        <title>Persons (in Sections)</title>
        <bounds x='360' y='10' w='300' h='400' />
        <selectQueryBuilder type='list'>
          <castFilterSet autoCastFilter='no'>
            <fkCastFilter>
              <instanceFk schemaFk='fk_tblSection_tblOrganization' />
            </fkCastFilter>
          </castFilterSet>
          <fromStatementBuilder>
            <mainInstanceTable schemaTable='tblPerson' />
            <fkJoin join='inner'>
              <instanceFk schemaFk='fk_tblPerson_tblSection' />
            </fkJoin>
          </fromStatementBuilder>
        </selectQueryBuilder>
        <instanceColumnList>
          <instanceColumn schemaColumn='tblSection_Name' >
            <title>Section</title>
          </instanceColumn>
          <instanceColumn schemaColumn='tblPerson_Name' />
        </instanceColumnList>
        <doubleClicScreen screen='SCtblPerson' />
        <insertScreen screen='SCtblPerson'
                      schemaFk='fk_tblPerson_tblSection' />
      </listForm>

    </formSet>
  </screen>

Explications:

The castFilter is the foreign key from Section that references Organization. It waits for an Organization primary key value as filter. This filter is of course the available filter in the Organization card screen.
A join is defined between the Person table (the main table of the listForm) and the Section table (that contains the castFilter foreign key). Join type is 'inner' because all the Person rows to display have a defined foreign key that references Section.
The insert screen is the Person card screen. The target foreign key for the parent filter value is fk_tblPerson_tblSection. So the fkField that will work on fk_tblPerson_tblSection will have to accept as filter an Organization primary key.

The Section card hasn't new notions. We can see the Person card.

The Person card screen

In this card, the distinctive feature is the combo-box query that allows to choice a Section. Look at this query:

  <screen name='SCtblPerson'>
    <title>Person card</title>
    <formSet>
      <cardForm db='dbDemo'>
        <title>Person card</title>
        <location x='10' y='10' />
        <cardSchemaTableRef schemaTable='tblPerson' queries='sudi'
                            updateAfterInsert='no' multipleInsert='yes' />
        <fieldContainer>

          <comboBoxFkField>
            <schemaFkRef schemaFk='fk_tblPerson_tblSection' />
            <go screen='SCtblSection' />
            <choose screen='SCtblSection' />

            <selectQueryBuilder distinctRequired='yes'>
              <castFilterSet autoCastFilter='no'>
                <pkCastFilter>
                  <instanceTable schemaTable='tblOrganization' />
                </pkCastFilter>
                <pkCastFilter>
                  <instanceTable schemaTable='tblSection'
                                 nickName='sec2' />
                </pkCastFilter>
                <pkCastFilter>
                  <instanceTable schemaTable='tblPerson'
                                 nickName='pers2' />
                </pkCastFilter>
              </castFilterSet>

              <fromStatementBuilder>
                <mainInstanceTable schemaTable='tblSection' />
                <fkJoin join='inner'>
                  <instanceFk schemaFk='fk_tblSection_tblOrganization' />
                </fkJoin>
                <fkJoin join='inner'>
                  <instanceFk schemaFk='fk_tblSection_tblOrganization'>
                    <startInstanceTable schemaTable='tblSection'
                                        nickName='sec2' />
                  </instanceFk>
                </fkJoin>
                <fkJoin join='left'>
                  <instanceFk schemaFk='fk_tblPerson_tblSection'>
                    <startInstanceTable schemaTable='tblPerson'
                                        nickName='pers2' />
                  </instanceFk>
                  <referencedInstanceTable schemaTable='tblSection'
                                           nickName='sec2' />
                </fkJoin>
              </fromStatementBuilder>
            </selectQueryBuilder>

          </comboBoxFkField>

          <textField>
            <schemaColumnRef schemaColumn='tblPerson_Name' />
          </textField>
        </fieldContainer>
      </cardForm>
    </formSet>
  </screen>

Cast-filters...

When opening a card in adding mode, the card isn't filtered. But the calling screen filter - if it's existing - can be given to one fkField, if we define a 'schemaFk' attribute in the insertScreen element.

By this way, our combo-box will be able to be filtered in adding mode, with the Organization card filter.

Let see role of each castFilter:

The first castFilter waits for an Organization primary key. So it will be active when user'll add a Person from the Organization card.
The second one waits for a Section primary key. So it will be active when user'll add a Person from the Section card.
The third one waits for a Person primary key. So it will be active when the card will be opened with existing data for modification.

The from statement...

Several join are needed. The main table is the Section table. The referenced Organization is joined, then Sections of this Organization. Then the persons for these Sections are 'left'-joined.

In brief:

Section (default) - Organization (default) - Section (sec2) - Person (per2)

We don't specify columns to display in select statement. So it will contain by default the user key of the main table (here the default instance table of Section). The three castFilters work on the three others table instances, in order to display Sections for the current Organization.

Last remarks about the Person join:

This join forces us to agregate query result (distinctRequired='yes'). Otherwise Section rows will be multiplied by the number of their persons.
This is a 'left'-join because we wish to display all directions for the current Organization, even those without persons.

Top