HOWTO - Sashipa development
You have two fkFields on the same cardForm, and you wish that when user selects a value in the first one, the second one takes the value as filter.
I propose you to work on a database with Groups of Persons. Groups make Journeys. Each journey is organized by a person of the group. Here is the SQL script (with HSQL syntax):
CREATE TABLE Group (
Id_Group integer not null primary key,
Group_Name varchar(100) not null
);
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)
);
CREATE TABLE Journey (
Id_Journey integer not null primary key,
Ref_Group integer not null,
Ref_Person_Organizer integer,
Journey_Label varchar(100) not null,
FOREIGN KEY (Ref_Group) REFERENCES Group (Id_Group),
FOREIGN KEY (Ref_Person_Organizer) REFERENCES Person (Id_Person)
);
|
A group contains many persons. A journey is done by a group and organized by a person of the same group.
In the Group card, we wish to choose the organizer among persons that reference the group. So user will have to choose the group before the person.
All specific code is in the query of the dependent fkField:
<cardForm db='dbBase'>
<title>Journey card</title>
<location x='10' y='10' />
<cardSchemaTableRef schemaTable='tblJourney' queries='sudi'
multipleInsert='yes' updateAfterInsert='no' />
<fieldContainer>
<readOnlyTextFkField name='fieldGroup'>
<schemaFkRef schemaFk='fk_tblJourney_tblGroup' />
<go screen='SCtblGroup' />
<choose screen='SItblGroup_For_tblJourney' />
</readOnlyTextFkField>
<comboBoxFkField>
<schemaFkRef schemaFk='fk_tblJourney_tblPerson' />
<go screen='SCtblPerson' />
<selectQueryBuilder>
<castFilterSet filterSensitive='no' />
<whereStatementBuilder>
<criteriaBuilder mode='and' emptyAction='stuck'
defaultSubEmptyAction='stuck' >
<researchCriteria>
<castSchemaValue>
<fkCastFilter>
<instanceFk schemaFk='fk_tblPerson_tblGroup' />
</fkCastFilter>
</castSchemaValue>
<fieldRef field='fieldGroup' />
</researchCriteria>
</criteriaBuilder>
</whereStatementBuilder>
</selectQueryBuilder>
</comboBoxFkField>
<textField>
<schemaColumnRef schemaColumn='tblJourney_JourneyLabel' />
</textField>
</fieldContainer>
</cardForm>
|
Remarks:
The leading field has a name (here fieldGroup).
The query ignores filters (filterSensitive='no'), it's more safe.
The researchCriteria element contains a castSchemaValue, that waits for a value of the foreign key fk_tblPerson_tblGroup (ie. a primary key value of the Group table). It takes value in the fieldGroup field.
Attributes defaultSubEmptyAction='stuck' and emptyAction='stuck' mean that the field is stuck when the researchCriteria doesn't work. So, the choice of the person will be stuck if there isn't a selected group.
We change our database: we take a table Journey that references a table Country, that references a table Continent. Here is the SQL script (HSQL syntax):
CREATE TABLE Continent (
Id_Continent integer not null primary key,
Continent_Name varchar(100) not null
);
CREATE TABLE Country (
Id_Country integer not null primary key,
Ref_Continent integer not null,
Country_Name varchar(100) not null,
FOREIGN KEY (Ref_Continent) REFERENCES Continent (Id_Continent)
);
CREATE TABLE Journey (
Id_Journey integer not null primary key,
Ref_Country integer not null,
Journey_Label varchar(100) not null,
FOREIGN KEY (Ref_Country) REFERENCES Country (Id_Country)
);
|
In the journey card, we wish to allow user to choose first a continent, and then, choose a country among countries of the continent. Of course only the country reference is recorded. So it's more complex than the previous case because the continent field has to be initialized.
Here is the Sashipa code for fkFields in the Journey cardForm. Explications follow the code.
<!-- Continent isn't saved, but selected -->
<comboBoxFkField name='fieldContinent' save='no'
dependentOfMainSelectQueryBuilder='no'>
<schemaFkRef schemaFk='fk_tblCountry_tblContinent' />
<defaultSelectedValue>
<selectQueryBuilder>
<castFilterSet stuckWhenNoFilter='yes' autoCastFilter='no'>
<pkCastFilter>
<instanceTable schemaTable='tblJourney' />
</pkCastFilter>
</castFilterSet>
<selectStatementBuilder>
<instanceColumnList>
<instanceFk schemaFk='fk_tblCountry_tblContinent' />
</instanceColumnList>
</selectStatementBuilder>
<fromStatementBuilder>
<mainInstanceTable schemaTable='tblCountry' />
<fkJoin join='inner'>
<instanceFk schemaFk='fk_tblJourney_tblCountry' />
</fkJoin>
</fromStatementBuilder>
</selectQueryBuilder>
</defaultSelectedValue>
<go screen='SCtblContinent' />
</comboBoxFkField>
<!-- Country dependent of Continent -->
<comboBoxFkField>
<schemaFkRef schemaFk='fk_tblJourney_tblCountry' />
<go screen='SCtblCountry' />
<choose screen='SCtblCountry' />
<selectQueryBuilder>
<castFilterSet filterSensitive='no' />
<whereStatementBuilder>
<criteriaBuilder mode='and' emptyAction='stuck'
defaultSubEmptyAction='stuck'>
<researchCriteria>
<castSchemaValue>
<fkCastFilter>
<instanceFk schemaFk='fk_tblCountry_tblContinent' />
</fkCastFilter>
</castSchemaValue>
<fieldRef field='fieldContinent' />
</researchCriteria>
</criteriaBuilder>
</whereStatementBuilder>
</selectQueryBuilder>
</comboBoxFkField>
|
Remarks on the dependent fkField (the second one). It's similar to the previous section:
The leading field has a name (here fieldContinent).
The query ignores filters (filterSensitive='no'), it's more safe.
The researchCriteria element contains a castSchemaValue, that waits for a value of the foreign key fk_tblCountry_tblContinent (ie. a primary key value of the Continent table). It takes value in the fieldContinent field.
Attributes defaultSubEmptyAction='stuck' and emptyAction='stuck' mean that the field is stuck when the researchCriteria doesn't work. So, the choice of the country will be stuck if there isn't a selected continent.
Remarks on the first fkField: this is a unsaved field (save='no') that doesn't take value in the cardForm query (dependentOfMainSelectQueryBuilder='no').
Moreover, a defaultSelectedValue element is defined, for selecting the Continent that is referenced by the Country of the journey. I give you here a few remarks about the element defaultSelectedValue:
We use an element defaultSelectedValue, that will work on the same way when mode is adding and when mode is consultation/modification. It's possible because we use dependentOfMainSelectQueryBuilder='no' in our field, so the field doesn't take the cardForm status, and takes always its default value.
Add to this, the query won't work in adding mode: stuckWhenNoFilter='yes'. So this query won't select a Continent when adding a Journey. This query is only used for finding the Continent of the Country of the Journey. So it's used only when the Journey card is opened in consultation/modification mode.
The castFilter waits for a primary key value of the Journey table. It's necessary because the field is contained by a Journey card.
The value returned by the query, is a value of the foreign key fk_tblCountry_tblContinent. It is also a value of the Journey primary key too.
The from statement contains a join between tblCountry (used in the select statement) and tblJourney (used in the where statement). NB: the select statement isn't defined here, so it will be created by default by the fkField.
A little bit complex, but possible ;)
Be carefull ! A Melba limitation forces you to declare the leading field (ie. the field with a name) before dependent fields.
© Copyright 2003 Sashipa-Melba Team. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License as much as this note clearly appears.