HOWTO - Sashipa development

ResearchForm: How to use one field for building criteria with two columns.

You have a researchForm contains two fields for searching into two columns in the result table. You wish to provide a single field for searching into the two columns.

How to do

We are going to modify the researchForm of the DemoContact application. We wish to search for the name and the first name from one single field. Here is the code:

  <researchForm>
    <title>Search for Contacts</title>
    <location x='10' y='10' />
    <fieldContainer>
      <textField name='fieldNameFirstname'>
        <label>Name or first name</label>
        <schemaColumnRef schemaColumn='tblContact_ContactName' />
      </textField>
      ...
    </fieldContainer>
    <listFormRef listForm='FLtblContact'>
      <redefineBounds x='0' y='100' />
    </listFormRef>

    <researchFormCriteriaBuilder mode='and' emptyAction='stuck'
                                 defaultSubEmptyAction='ignore' >
      <excludedFieldsFromAutoResearch>
        <fieldRef field='fieldNameFirstname' />
      </excludedFieldsFromAutoResearch>

      <criteriaBuilder mode='or' emptyAction='ignore' defaultSubEmptyAction='stuck'>
        <researchCriteria>
          <castSchemaValue>
            <instanceColumn schemaColumn='tblContact_ContactName' />
          </castSchemaValue>
          <fieldRef field='fieldNameFirstname' />
        </researchCriteria>
        <researchCriteria>
          <castSchemaValue>
            <instanceColumn schemaColumn='tblContact_ContactFirstname' />
          </castSchemaValue>
          <fieldRef field='fieldNameFirstname' />
        </researchCriteria>
      </criteriaBuilder>
    </researchFormCriteriaBuilder>

  </researchForm>

Explication: first we give a name to the field on which we are going to work. Here fieldNameFirstname.

After that, all the added code is contained in the researchFormCriteriaBuilder element. This element provides criteria by default if we doesn't specify anything. But here we specify two child elements:

excludedFieldsFromAutoResearch: a set of fields to exclude from the auto-built default criteria.
criteriaBuilder: this element is a criterion that will be added to the default criteria. NB: a criteriaBuilder can contain several criteria.

In our example we exclude the field fieldNameFirstname from the auto-built default criteria. Then we define a criteriaBuilder that will separate its sub-criteria by a SQL 'OR' (mode='or'). We create two child researchCriteria. The first one searchs for content of the field fieldNameFirstname into the column tblContact_ContactName. The second one searchs for content of the same field into the column tblContact_ContactFirstname.

Note about the textField fieldNameFirstname: this field isn't dependent of the column tblContact_ContactName, neither of the column tblContact_ContactFirstname. Nevertheless we have to specify an element schemaColumnRef schemaColumn='tblContact_ContactName'. This declaration is just used for giving the working data type to the field. So we can use one or the other column, it's not important.

How it works

Fyi, I explain here how it works:

The element researchFormCriteriaBuilder creates a criteriaBuilder that contains the manually defined criterion if it exists and those created by default for non-excluded fields. After that, the criteriaBuilder is added to the where statement of the listForm(Ref) selectQueryBuilder.

Handy info

If you wish to be safe about the generated query, you can specify a log file for SELECT queries:

  <serverSet>
    <server architecture='clientDatabase'>

      ... element dbConnection ...

      <logStorage>
        <mainLog><logFileStorage>
          <uri>Melba_DemoContact_Main.log</uri>
        </logFileStorage></mainLog>
        <updateLog><logFileStorage>
          <uri>Melba_DemoContact_Update.log</uri>
        </logFileStorage></updateLog>
        <selectLog><logFileStorage>
          <uri>Melba_DemoContact_Select.log</uri>
        </logFileStorage></selectLog>
      </logStorage>

    </server>
  </serverSet>

In this way each SELECT query will be logged before to be executed, in the file Melba_DemoContact_Select.log. Do a research in your generated software, then edit the log file, you'll find the query at the end of the file.

Top