HOWTO - Sashipa development

How to filter a fkField list.

You have two tables, the first one references the second one and the second one references the first one. You wish to use the primary key of the first table card, as filter in the fkField of the second table card.

We are going to take a concrete example. We have Groups of Persons, and for each group we have a responsible. Just below is the SQL script for creating table (here with the HSQL syntax).

  CREATE TABLE Group (
    Id_Group integer not null primary key,
    Group_Name varchar(100) not null,
    Ref_Person_Responsible integer
  );
  CREATE TABLE Person (
    Id_Person integer not null primary key,
    Ref_Group integer not null,
    Person_Name varchar(100) not null,
    Person_FirstName varchar(100),
    FOREIGN KEY (Ref_Group) REFERENCES Group (Id_Group)
  );
  ALTER TABLE Group ADD CONSTRAINT fkgrp_pers
    FOREIGN KEY (Ref_Person_Responsible) REFERENCES Person (Id_Person);

Foreign keys are crossed: a person references one group and a group references one or zero person.

In the group card we wish to choose the person responsible among the only persons who reference the group.

In fact it's the default behaviour for a fkField because the fkField select query always try to use the current filter. On the other hand we have to stuck choice of the responsible person when the fkField is in adding mode. Otherwise the fkField will contain the whole of the Person table. Here is an example:

  <screen name='SCtblGroup'>
    <title>Group card</title>
    <formSet>
      <cardForm db='dbBase'>
        <title>Group card</title>
        <location x='10' y='10' />
        <cardSchemaTableRef schemaTable='tblGroup' queries='sudi'
                            multipleInsert='no' updateAfterInsert='yes' />
        <fieldContainer>
          <textField>
            <schemaColumnRef schemaColumn='tblGroup_GroupName' />
          </textField>
          <comboBoxFkField>
            <schemaFkRef schemaFk='fk_tblGroup_tblPerson' />
            <go screen='SCtblPerson' />
            <choose screen='SCtblPerson' schemaFk='fk_tblPerson_tblGroup' />
            <selectQueryBuilder>
              <castFilterSet stuckWhenNoFilter='yes' />
            </selectQueryBuilder>
          </comboBoxFkField>
        </fieldContainer>
      </cardForm>

      <listForm db='dbBase' delete='yes'>
        ...
      </listForm>
    </formSet>
  </screen>

Remarks:

A comboBoxFkField can be used (a readOnlyTextFkField is not required) because a group contains a little number of persons.
The attribute stuckWhenNoFilter='yes' makes the field stuck when the cardForm is in adding mode.
The listForm content and the available fkField choices are always similar.
The attribute schemaFk='fk_tblPerson_tblGroup' will give a default value to the field working on the foreign key 'fk_tblPersonne_tblGroupe', in the card of Person.

In the Person card, the fkField that works on the fk_tblPerson_tblGroup foreign key ignores the filter of the card. Otherwise the fkField'll propose only the single group that references the current person !

  <screen name='SCtblPerson'>
    <title>Fiche Person</title>
    <formSet>
      <cardForm db='dbBase'>
        <title>Fiche Person</title>
        <location x='10' y='10' />
        <cardSchemaTableRef schemaTable='tblPerson' queries='sudi'
                            multipleInsert='no' updateAfterInsert='yes' />
        <fieldContainer>
          <readOnlyTextFkField filterSensitive='no'>
            <schemaFkRef schemaFk='fk_tblPerson_tblGroup' />
            <go screen='SCtblGroup' />
            <choose screen='SItblGroup_For_tblPerson' />
          </readOnlyTextFkField>
          <textField>
            <schemaColumnRef schemaColumn='tblPerson_PersonFirstname' />
          </textField>
          <textField>
            <schemaColumnRef schemaColumn='tblPerson_PrenomName' />
          </textField>
        </fieldContainer>
      </cardForm>
    </formSet>
  </screen>

Top