TTMSFNCFilterBuilder

TTMSFNCFilterBuilder is a flexible filter builder component that enables users to define and manage complex filtering rules. It provides support for multiple operators, groupings, and logical conditions, allowing dynamic filter expressions to be created for data sets or queries. This control is the logical building block that is used by other controls for their filtering mechanism.

How to use the TTMSFNCFilterBuilder

Before working directly with the TTMSFNCFilterBuilder, it is advised to first utilize the built-in filtering functions of the controls that integrate with the filter builder (e.g. TTMSFNCFilterDialog, TTMSFNCDataGrid, ...). Working on the filter builder independently may not always yield optimal results.

The TTMSFNCFilterBuilder provides these key functionalities:

Possible to use one filter for multiple components.
Parsing filter text into structured Groups and Expressions while performing initial validation checks, such as detecting mismatched parentheses, incorrect group operator positions, and other structural issues.
Generating a valid filter text representation based on the groups and expressions defined within the Filter property.
Validating an input array against the Filter property, ensuring data matches the defined filtering criteria.

1. Application-Wide FilterBuilder or Specific Instance for Each Need

The TTMSFNCFilterBuilder offers flexibility in how it is used within an application. You can create a new instance of the filter builder whenever needed, allowing for custom filtering configurations in different parts of your application. Alternatively, you can utilize the globally declared TMSFNCFilterBuilder property to maintain a consistent filtering mechanism across the entire application. This approach is useful when you want to apply a uniform filtering logic throughout multiple components without manually managing multiple instances.

uses
  …, TMS.TMSFNCFilterBuilder;

procedure UseFilterBuilder(Sender: TObject);
var
  fb: TTMSFNCFilterBuilder;
begin
  fb := TTMSFNCFilterBuilder.Create;
  try
    fb.ParseExpression('[Name] LIKE ' + QoutedStr('C%'));
  finally
    fb.Free;
  end;

  //Or you can use the global FilterBuilder
  TMSFNCFilterBuilder.ParseExpression('[Name] LIKE ' + QoutedStr('C%'));  
end;

2. Parsing and Validating Filter Text

The TTMSFNCFilterBuilder can analyze raw filter text based on the Parse Format and convert it into structured Groups and Expressions. During this process, it performs initial validation checks to ensure the filter text is correctly formatted. This includes detecting mismatched parentheses, verifying group operator positions, and identifying syntax errors that could lead to unexpected behavior.

The default formatting is as followed and supports the SQL filter formatting, and uses some predefined words to parse the filter text correctly: - Group Format: (#GROUP) to combine multiple expressions with the same group operator OR and AND. - Column Format: [#COLUMN] defines the column name that needs to be checked against (also used for the data type of the value). - Expression Format: eg. starts with LIKE '#VALUE%' this defines the expression where the value should be placed. Also note that the % sign is a multi character wildcard in SQL and is also configurable.

This makes it possible to create expressions as the following examples, when the format type is set to fftDelphiDataSetFormat:

  [Name] LIKE 'K*' AND [Status] LIKE 'Abroad') OR ([Name] LIKE 'S%' AND [Progress] > 50) OR [Available] = True

or with the other predefined format used for our TMS FNC DataGrid fftUniversalExpressionFormat

  [Name] = "K*" & [Status] = "Abroad") | ([Name] = "S*" & [Progress] > 50) | [Available] = True

The ParseExpression returns a boolean value to indicate if the text was set successfully. If this is not the case, different events are thrown for the different errors that are checked on or you can use the all-round OnParseError event. - OnParseErrorParenthesis - OnParseErrorOperatorMismatch - OnParseErrorOperatorPosition - OnParseErrorInvalidExpression

3. Generating Filter Text from Defined Expressions

Based on the Groups and Expressions set within the Filter property, the TTMSFNCFilterBuilder can construct a valid filter text string. This allows users to dynamically build filter queries that are compatible with other components or external filtering mechanisms.

To have the optimal result it is best to define the different Columns/Data Fields used, within the filter.

uses
  ..., TMS.TMSFNCFilterBuilder, FMX.TMSFNCValue;

var
  g1, g2: TTMSFNCFilterBuilderGroup;
begin  
  TMSFNCFilterBuilder.ClearFilter;

  TMSFNCFilterBuilder.Filter.GroupOperator := fgoOR;
  g1 := TMSFNCFilterBuilder.FilterBuilder.Filter.AddANDGroup;
  g1.AddExpression('Name', feoStartsWith, 'K');
  g1.AddExpression('Status', feoEqual, 'Abroad');

  g2 := TMSFNCFilterBuilder.FilterBuilder.Filter.AddANDGroup;
  g2.AddExpression('Name', feoStartsWith, 'S');
  g2.AddExpression('Progress', feoLargerThan, 50);  

  TMSFNCFilterBuilder.Filter.AddExpression('Available', feoEqual, True);

  Edit.Text := TMSFNCFilterBuilder.FilterText;
  // Will generate with default format as:
  // ([Name] LIKE 'K%' AND [Status] = Abroad) OR ([Name] LIKE 'S%' AND [Progress] > 50) OR [Available] = True
end;

4. Validating Data Against the Filter Property

The TTMSFNCFilterBuilder provides a built-in mechanism to validate input data arrays based on the defined filter expressions. This allows developers to easily check whether a given set of values meets the filtering criteria without having to write custom validation logic. By passing an array of values to the filter builder, the function automatically evaluates the data and determines if it matches the specified conditions. This simplifies data validation and ensures consistency in filtering across applications.

With ValidateFilterRow you can check the input for the different columns for one example.

uses
  ..., TMS.TMSFNCFilterBuilder, FMX.TMSFNCValue;

var
  InputRow: TArray<TTMSFNCValue>; //TValue will also work in FMX and VCL.
begin
  TMSFNCFilterBuilder.Columns.Clear;

  //The DataColumns property will check if the colmun name is already in use and if not create a new column.
  TMSFNCFilterBuilder.DataColmuns['Name'].DataType := fdtText;
  TMSFNCFilterBuilder.DataColumn['Status'].DataType := fdtText;
  TMSFNCFilterBuilder.DataColumn['Progress'].DataType := fdtNumber;  
  TMSFNCFilterBuilder.DataColumn['Available'].DataType := fdtBoolean;    

  //The order of the values should match the order of the columns in the filter builder.
  InputRow := ['Sarah','Abroad', 60, False]; //--> Matches Filter  
  InputRow := ['Sarah','Abroad', 40, False]; //--> No Match
  InputRow := ['Kurt','Abroad', 40, False]; //--> Matches Filter
  InputRow := ['Alex','Abroad', 40, False]; //--> No Match  
  InputRow := ['Alex','Abroad', 40, True]; //--> Matches Filter

  if TMSFNCFilterBuilder.ValidateFilterRow(InputRow) then
    ShowMessage('Matches Filter')
  else
    ShowMessage('No Match');
end;

If you have a larger list of inputs you can use ValidateFilterArray. You can choose to have the input array by column or by row, which you can indicate in the function with the AByRow property (Default False). This returns an array of Boolean values for each row.

uses
  ..., TMS.TMSFNCFilterBuilder, FMX.TMSFNCValue;

var
  InputArray: TArray<TArray<TTMSFNCValue>>; //TValue will also work in FMX and VCL.
  OutputArray: TArray<Boolean>;
begin
  TMSFNCFilterBuilder.Columns.Clear;

  //The DataColumns property will check if the colmun name is already in use and if not create a new column.
  TMSFNCFilterBuilder.DataColmuns['Name'].DataType := fdtText;
  TMSFNCFilterBuilder.DataColumn['Status'].DataType := fdtText;
  TMSFNCFilterBuilder.DataColumn['Progress'].DataType := fdtNumber;  
  TMSFNCFilterBuilder.DataColumn['Available'].DataType := fdtBoolean;    

  //The order of the values should match the order of the columns in the filter builder.
  // If you work with the data structured in columns:
  InputArray := [
                  ['Sarah','Sarah', 'Kurt', 'Alex', 'Alex'],
                  ['Abroad', 'Abroad', 'Abroad', 'Abroad', 'Abroad'],
                  [60, 40, 40, 40, 40],
                  [False, False, False, False, True]
                ];
  OutputArray := TMSFNCFilterBuilder.ValidateFilterArray(InputArray, False);  

  // This is an Array created of Rows, set the second parameter to True to validate by row.
  InputArray := [
                  ['Sarah','Abroad', 60, False],
                  ['Sarah','Abroad', 40, False],
                  ['Kurt','Abroad', 40, False],
                  ['Alex','Abroad', 40, False],
                  ['Alex','Abroad', 40, True]
                ];

  OutputArray := TMSFNCFilterBuilder.ValidateFilterArray(InputArray, True);// [True, False, True, False, True]
end;

Propeties

Property	Function
`DataColumn['Name']`	Returns the `Column` of the data column with that the name. If it is not found a new column will be created.
`DataColumnDisplayName['Name']`	Retrieve the user-friendly display name for a column.
`DataColumnType['Name']`	Determines the data type of the column (e.g., string, number, date).
`ParseFormat`	Specifies the expected format when parsing filter text into expressions.
`FilterText`	Stores the raw filter expression as a text string.
`DisplayFilterText`	Holds a formatted version of `FilterText` for UI display with the column `DisplayName`.
`Filter`	Represents the structured filter definition, which is a Group containing the groups and expressions.
`DataColumns`	Maintains a collection of available data columns for filtering.
`FormatType`	Defines the format used when converting filter expressions to text. (Default is fftDelphiDataSet)

Methods

Method	Function
`GetExpressionInformation(AExpression: string): TTMSFNCFilterBuilderExpressionInformation`	Retrieves the detailed information of an expression.
`GetExpressionText(AColumnName: String; ADataType: TTMSFNCFilterBuilderDataType; AExpressionOperator: TTMSFNCFilterBuilderExpressionOperator; AValueText: string; AValue: TTMSFNCValue; AInverted: Boolean): string`	Generates a filter expression text based on column name, data type, operator, and values.
`DeleteGroup(AGroup: TTMSFNCFilterBuilderGroup): Boolean`	Removes a specified group from the filter structure.
`DeleteExpression(AExpression: TTMSFNCFilterBuilderExpression): Boolean`	Deletes a specific filter expression from the builder.
`GetFormatSettings: TFormatSettings`	Returns the current format settings used for parsing and generating filter text.
`GetColumnByName(AColumnName: string): TTMSFNCFilterBuilderColumn`	Retrieves a data column by its internal name.
`GetColumnByDisplayName(AColumnDisplayName: string): TTMSFNCFilterBuilderColumn`	Retrieves a data column using its display name.
`PopulateFilter(AGroups: TArray<TTMSFNCFilterBuilderGroup>; AExpressions: TArray<TTMSFNCFilterBuilderExpression>): TTMSFNCFilterBuilderGroup`	Constructs a filter using an array of groups and expressions.
`PopulateFilterGroups(AGroups: TArray<TTMSFNCFilterBuilderGroup>): TTMSFNCFilterBuilderGroup`	Builds a filter from an array of groups.
`PopulateFilterExpressions(AExpressions: TArray<TTMSFNCFilterBuilderExpression>): TTMSFNCFilterBuilderGroup`	Builds a filter from an array of expressions.
`isExpressionValid(AExpression: string): Boolean`	Checks whether a given filter expression is valid.
`ValidateFilterRow(AInputData: TArray<TTMSFNCValue>): Boolean`	Validates a single row of input data against the filter conditions.
`ValidateFilterArray(AInputArray: TTMSFNCFilterValidateInputArray; AByRow: Boolean = False): TTMSFNCFilterValidateOutputArray`	Validates an entire dataset, either as a full array or row-by-row.
`AddDataColumn(AName: string; ADataType: TTMSFNCFilterBuilderDataType = fdtAutomatic; ADisplayName: string = ''): TTMSFNCFilterBuilderColumn`	Creates and returns a new data column for filtering. Allowing specification of name, data type, and display name.
`ParseExpression(AFilterText: string): Boolean`	Parses a filter text string and converts it into a structured filter representation.
`ClearFilter`	Removes all groups and expressions, resetting the filter.

Events

Event	Function
`OnColumnsChanged`	Triggered when a one of the `DataColumn` properties is modified.
`OnParseFormatChanged`	Fires when the `ParseFormat` setting is updated.
`OnGetFormatSettings`	Allows customization of format settings before parsing or generating filter text.
`OnParseErrorParenthesis`	Raised when there is a mismatch in parentheses during expression parsing.
`OnParseErrorOperatorPosition`	Triggered when an operator is incorrectly placed within an expression or group.
`OnParseErrorOperatorMismatch`	Fires when a group operator mismatch is detected during parsing. (Should be the same within a gruop)
`OnParseErrorInvalidExpression`	Raised when an expression is deemed invalid due to incorrect syntax.
`OnParseError`	A general event that captures all parsing errors, including operator and parenthesis issues.
`OnGetFilterText`	Allows customization of the filter text before it is displayed or processed.
`OnGetGroupText`	Enables modification of the textual representation of a `Group`.
`OnGetExpressionText`	Enables modification of the textual representation of an `Expression`.
`OnExpressionTextParsed`	Fires when an `Expression` has been successfully parsed.
`OnFilterTextParsed`	Fires when the entire filter text has been successfully parsed into groups and expressions.
`OnValidateGroup`	Allows validation of a `Group` before it is applied.
`OnValidateFilter`	Allows validation of the entire filter before execution.
`OnValidateExpression`	Allows validation of an `Expression` before it is applied.
`OnExpressionAdded`	Fires when a new `Expression` is added to the filter builder.

TTMSFNCFilterBuilderColumn

Columns play a crucial role in the TTMSFNCFilterBuilder, as they define the structure and characteristics of the data being filtered. By specifying column properties, you ensure that filter expressions are correctly formatted and validated based on the data type. This is especially important when working with text, date/time, or numerical values, as each requires proper parsing and formatting to maintain accuracy in filtering. Configuring columns correctly helps prevent invalid filters and enhances usability by providing the option to add meaningful display names.

Property	Function
`Name: string`	The internal name of the column, used to reference it in filters.
`DisplayName: string`	The user-friendly name displayed in the UI for better readability.
`DataType: TTMSFNCFilterBuilderDataType`	Defines the type of data in the column (e.g., text, number, date) to ensure correct formatting and validation. Defaults to `fdtAutomatic`, which tries to detect the type automatically.

TTMSFNCFilterBuilderGroup

Groups in TTMSFNCFilterBuilder allow for the logical structuring of filter expressions by combining multiple conditions using group operators. They help define how individual expressions interact, enabling complex filtering logic with AND / OR operators. Groups can contain both expressions and nested groups, making it possible to create hierarchical filtering rules. Properly configuring groups ensures that filters are evaluated correctly and produce the expected results. The Filter property of the TTMSFNCFilterBuilder is the root group of the filter structure.

Group Properties

Property	Function
`FilterText: string`	Returns the textual representation of the group’s filter, combining its expressions and subgroups.
`GroupOperator: TTMSFNCFilterBuilderGroupOperator`	Determines whether expressions within the group are combined using `AND` or `OR`. Defaults to `fgoAND`.
`Groups: TTMSFNCFilterBuilderGroups`	Holds a collection of nested groups, allowing for complex hierarchical filters.
`Expressions: TTMSFNCFilterBuilderExpressions`	Stores the list of filter expressions that belong to this group.

TTMSFNCFilterBuilderExpression

Expressions in TTMSFNCFilterBuilder define the actual filtering conditions applied to data. Each expression consists of a column, an operator (e.g., equals, contains, greater than), and a value to compare against. Expressions also support inversion (negating the condition) and case sensitivity for precise control over filtering behavior. Properly configuring expressions ensures accurate data filtering and expected results.

Property	Function
`Column: TTMSFNCFilterBuilderColumn`	Specifies the `DataColumn` to which the expression applies.
`ExpressionOperator: TTMSFNCFilterBuilderExpressionOperator`	Defines the comparison operator (e.g., `equals`, `contains`, `greater than`). Defaults to `feoNotEmpty`.
`Value: TTMSFNCValue`	Holds the comparison value for the expression (e.g., a number, text, or date).
`IsInverted: Boolean`	If `True`, the expression logic is inverted (e.g., "does not equal" instead of "equals").
`CaseSensitive: Boolean`	Determines whether text comparisons are case-sensitive. Defaults to `False`.

TTMSFNCFilterBuilderParseFormat

The Parse Format properties in TTMSFNCFilterBuilder define how filter expressions are interpreted when converting between filter text and structured expressions. These settings allow customization of logical operators, comparison operators, and wildcard characters, ensuring compatibility with different query languages or user-defined filtering rules. Properly configuring the parse format ensures that filter text is correctly parsed and validated.

When defining a custom format, ensure that the placeholders #COLUMN, #GROUP, and #VALUE are included, as they are essential for correctly parsing the filter expressions.

The default formatting is fftDelphiDataSet.

Property	Delphi DataSet	Universal Expression	Function
`StringDelimiter`	`'`	`"`	Defines the delimiter for string values.
`SingleCharWildCard`	`_`	`?`	Defines the wildcard symbol for single-character matches.
`MultiCharWildCard`	`%`	`*`	Defines the wildcard symbol for multiple-character matches.
`AndOperator`	`AND`	`&`	Defines the logical AND operator in filter expressions.
`OrOperator`	`OR`	`\|`	Defines the logical OR operator in filter expressions.
`NotOperator`	`NOT`	`NOT`	Defines the logical NOT operator for negations.
`ColumnFormat`	`[#COLUMN]`	`[#COLUMN]`	Defines how columns are referenced in filter expressions.
`GroupFormat`	`(#GROUP)`	`(#GROUP)`	Defines how filter groups are formatted.
`EqualOperator`	`= #VALUE`	`= #VALUE`	Defines the equality operator for numerical comparisons.
`EqualStrOperator`	`LIKE '#VALUE'`	`= "#VALUE"`	Defines the equality operator for string comparisons.
`NotEqualOperator`	`<> #VALUE`	`!= #VALUE`	Defines the inequality operator.
`SmallerThanOperator`	`< #VALUE`	`< #VALUE`	Defines the "less than" operator.
`SmallerThanOrEqualOperator`	`<= #VALUE`	`<= #VALUE`	Defines the "less than or equal" operator.
`LargerThanOperator`	`> #VALUE`	`> #VALUE`	Defines the "greater than" operator.
`LargerThanOrEqualOperator`	`>= #VALUE`	`>= #VALUE`	Defines the "greater than or equal" operator.
`ContainsOperator`	`LIKE '%#VALUE%'`	`= "#VALUE"`	Defines the operator for checking if a value contains a specific string.
`StartsWithOperator`	`LIKE '#VALUE%'`	`= "#VALUE*"`	Defines the operator for checking if a string starts with a specific value.
`EndsWithOperator`	`LIKE '%#VALUE'`	`= "*#VALUE"`	Defines the operator for checking if a string ends with a specific value.
`EmptyOperator`	`IS NULL`	`= ""`	Defines the operator for checking if a value is empty.
`NotEmptyOperator`	`IS NOT NULL`	`= "*"`	Defines the operator for checking if a value is not empty.