Search Server Behavior Advanced Configuration
The following sections details advanced scenarios in using the DataAssist Search server behavior that require additional configuration.
When making comparisons against date columns, DataAssist Search server behavior configures date comparisons using the complete mm/dd/yyyy format. The user interface only provides a list of available columns to select when specifying the location in the recordset that the date value comparison is made against. (see DataAssist Search server behavior for information on adding comparisons using the Plus button). When selecting a column from the list to retrieve the date value, the date returned from the database is in the mm/dd/yyyy format and therefore, the value compared against it must also be in the same format. Partial comparisons specifying only a month or a year will not work in this scenario.
It is possible to make comparisons of partial date information (month, year, etc.) by using database syntax to evaluate the value returned from the date column. This requires manually entering an evaluation string in the column for the specified comparison, as well as changing the comparison type to either Number or Text, depending on the format in which the value is returned by the date evaluation string.
This configuration has a form element called Bdate compared against the value found in the database column UserBDate. The comparison type is D for date to correlate with the database column's type. This format would require that the value retrieved from BDate is in the mm/dd/yyyy format.
However, to only compare against specific parts of the full date retrieved from the column, an expression can be manually entered in the column field which evaluates the column and returns only the value you wish to compare against.
It is in this manner that full date values can be evaluated using DataAssist Search server behavior. Additional date part syntax allows filters for values of years, days, etc., allowing comparisons for more specific scenarios involving date columns.
The four database models supported in DataAssist Search server behavior all use their own syntax for evaluating date database columns. Please refer to your documentation for your database type to determine the appropriate syntax for your use.
Multiple applications of DataAssist Search server behavior to the same recordset
DataAssist Search server behavior can be applied more than once to a single recordset. The first application of DataAssist Search server behavior to the Recordset MUST have a default query value. This is typically a comparison on the Primary Key column of the table or query. The most commonly used default query statement is one that will not effect the following applications of DataAssist Search server behavior.
Example:
The unique key column of the JournalEntries table is JournalID. Therefore, the default query statement is "WHERE JournalID<>0". This will not affect any of the other applications of DataAssist Search server behavior that follow.
You will note that in your successive applications to the Recordset, the first "Separator" of your query entries is not ignored. Therefore, if the DataAssist Search server behavior variable is in the request, the generated query will be appended to the previous application with that separator. This is why it is crucial to declare a "Default WHERE Clause" in the first DataAssist Search server behavior application.
Applying DataAssist Search server behavior to a recordset that contains an existing WHERE clause
DataAssist Search server behavior can be applied to recordsets with existing SQL Parameters and existing WHERE clauses. You will note that if a WHERE clause is already in the SQL string for the Recordset, the first separator of your query entries is not ignored. Therefore, if the DataAssist Search server behavior variable is in the request, the generated query will be appended to the existing WHERE clause with that separator.
- Date Part Evaluations
- Multiple applications of DataAssist Search server behavior to the same recordset
- Applying DataAssist Search server behavior to a recordset that contains an existing WHERE clause
When making comparisons against date columns, DataAssist Search server behavior configures date comparisons using the complete mm/dd/yyyy format. The user interface only provides a list of available columns to select when specifying the location in the recordset that the date value comparison is made against. (see DataAssist Search server behavior for information on adding comparisons using the Plus button). When selecting a column from the list to retrieve the date value, the date returned from the database is in the mm/dd/yyyy format and therefore, the value compared against it must also be in the same format. Partial comparisons specifying only a month or a year will not work in this scenario.
It is possible to make comparisons of partial date information (month, year, etc.) by using database syntax to evaluate the value returned from the date column. This requires manually entering an evaluation string in the column for the specified comparison, as well as changing the comparison type to either Number or Text, depending on the format in which the value is returned by the date evaluation string.
This configuration has a form element called Bdate compared against the value found in the database column UserBDate. The comparison type is D for date to correlate with the database column's type. This format would require that the value retrieved from BDate is in the mm/dd/yyyy format.
However, to only compare against specific parts of the full date retrieved from the column, an expression can be manually entered in the column field which evaluates the column and returns only the value you wish to compare against.
It is in this manner that full date values can be evaluated using DataAssist Search server behavior. Additional date part syntax allows filters for values of years, days, etc., allowing comparisons for more specific scenarios involving date columns.
The four database models supported in DataAssist Search server behavior all use their own syntax for evaluating date database columns. Please refer to your documentation for your database type to determine the appropriate syntax for your use.
Multiple applications of DataAssist Search server behavior to the same recordset
DataAssist Search server behavior can be applied more than once to a single recordset. The first application of DataAssist Search server behavior to the Recordset MUST have a default query value. This is typically a comparison on the Primary Key column of the table or query. The most commonly used default query statement is one that will not effect the following applications of DataAssist Search server behavior.
Example:
The unique key column of the JournalEntries table is JournalID. Therefore, the default query statement is "WHERE JournalID<>0". This will not affect any of the other applications of DataAssist Search server behavior that follow.
You will note that in your successive applications to the Recordset, the first "Separator" of your query entries is not ignored. Therefore, if the DataAssist Search server behavior variable is in the request, the generated query will be appended to the previous application with that separator. This is why it is crucial to declare a "Default WHERE Clause" in the first DataAssist Search server behavior application.
Applying DataAssist Search server behavior to a recordset that contains an existing WHERE clause
DataAssist Search server behavior can be applied to recordsets with existing SQL Parameters and existing WHERE clauses. You will note that if a WHERE clause is already in the SQL string for the Recordset, the first separator of your query entries is not ignored. Therefore, if the DataAssist Search server behavior variable is in the request, the generated query will be appended to the existing WHERE clause with that separator.