TcxDBDataFilterCriteria.IgnoreOrigin Property

Specifies whether to use the TField.FiledName or the TField.Origin property to represent a field in the FilterText property.

Declaration

property IgnoreOrigin: Boolean read; write; default True;

Property Value

Type	Default
Boolean	True

Remarks

The IgnoreOrigin property allows you to override the choice of fieldnames used in the FilterText. IgnoreOrigin specifies whether to use the FiledName or Origin properties of dataset fields when creating the FilterText.

By default, IgnoreOrigin is True and TField.FiledName property values represent fields in the FilterText. Usually, you can leave it set to True unless there are ambiguous field names in an underlying dataset. This problem can occur if the dataset represents a query which joins several tables with the same field name, but only if you specify FilterText as a part of the query’s SQL statement via the OnBeforeChange event.

To correctly identify fields in this case, you can assign valid full field names (Products.ID, for instance) to the Origin properties of dataset fields and then set IgnoreOrigin to False. Now FilterText will be created using the values of the Origin properties instead of field names.

The TField.Origin property specifies the name of the field in its original database table. It is assigned at design time by the Fields editor when the dataset is a query object. The Origin property can be changed freely depending on your needs.

The default value of the IgnoreOrigin property is True. For ADO queries, the IgnoreOrigin property usually should be set to True. ADO queries automatically assign unambiguous filed names when joining tables with same field names. BDE queries do not process this situation correctly and so you should consider setting IgnoreOrigin to False.

The following example describes a situation when IgnoreOrigin should be set to False.

Consider the query component which selects data from the Customer and Products tables shipped with ExpressQuantumGrid demos:

SELECT * FROM Customer C, Products P
WHERE C.ProductID = P.ID

The Customer table contains the ID and ProductID fields. The Products table contains the ID field and this corresponds to the ProductID field in the Customer table.

The specified query selects customers from the Customer table and, for every customer, it adds corresponding fields from the Products table to provide information on the products bought.

The following screenshot shows a portion of data loaded by the query (this does not present all fields from the query):

Note that both the Customer and Products tables contain a field with the same name (ID). In the query result, the ID field of the second table is automatically renamed to ID_1.

You are not able to apply a filter condition using this field name in the SQL statement. For instance, the following query will generate the error (“Undefined field name ID_1”):

SELECT * FROM Customer C, Products P
WHERE C.ProductID = P.ID

AND ID_1 = 3

By default, IgnoreOrigin is True and this makes the data controller use the TField.FieldName property value to construct FilterText.

Suppose you apply a filter condition to the ID_1 field via the data controller. The field will be represented by the ID_1 string in FilterText (because IgnoreOrigin is set to True by default). As shown above, this FilterText cannot be specified as a part of the WHERE clause in a SQL statement.

The filter condition for the SQL statement should be constructed using the full name of the ID_1 field:

SELECT * FROM Customer C, Products P
WHERE C.ProductID = P.ID

AND P.ID = 3

P.ID is a full name for the ID_1 field. To make the data controller automatically use the correct name (P.ID instead of ID_1) for the FilterText, you need to:

1. Create persistent fields for fields in the query via the Fields Editor.

For this purpose, double-click the query icon or select the Fields Editor option from the component’s popup menu. This activates the Fields Editor:

Right click the editor’s client region and select the Add all fields option:

This loads all fields from the query and creates a persistent TField object for each of them. Now it is possible to customize the field properties.

2. Change the Origin property of the ID_1 field to “P.ID”. To do this, select the ID_1 field and change its property value in the Object Inspector:

3. Set the data controller’s IgnoreOrigin property to False. Now the data controller will automatically use the value of the Origin property instead of FieldName when constructing FilterText.

Note that the data controller will now use the Origin property for all fields when constructing FilterText. So you need to assign correct names to the Origin property of all the other fields as for the ID_1 field.

After this, the data controller will generate valid FilterText which can be used in a SQL statement. The following code shows how to change the WHERE clause of a query via the OnBeforeChange event. It is assumed that the SQL property of the query consists of three lines (the third line is empty) and the SQL command is specified as follows:

SELECT * FROM Customer C, Products P
WHERE C.ProductID = P.ID

In an OnBeforeChange event handler, FilterText is added as a third line to the SQL statement and this combines the existing condition in the query (C.ProductID = P.ID) with the condition set by the user. For instance, when a user applies the condition “= 3” to the ID_1 field, the query’s SQL property will identify the following command:

SELECT * FROM Customer C, Products P
WHERE C.ProductID = P.ID

AND (P.ID = 3)

Delphi

procedure TForm1.tvCustomerDataControllerFilterBeforeChange(
  Sender: TcxDBDataFilterCriteria; ADataSet: TDataSet;
  const AFilterText: String);
begin
  with Query1 do
  begin
    DisableControls;
    try
      Active := False;
      if AFilterText <> '' then
        //add condition to the query
        SQL[2] := 'AND ' + AFilterText
      else
        SQL[2] := '';
      Active := True;
    finally
      EnableControls;
    end;
  end;
end;

C++

void __fastcall TForm1::tvCustomerDataControllerFilterBeforeChange(
      TcxDBDataFilterCriteria *Sender, TDataSet *ADataSet,
      const AnsiString AFilterText)
{
  Query1->DisableControls();
  try {
    Query1->Active = false;
    if (AFilterText != "") {
      //add condition to the query
      Query1->SQL->Strings[2] = "AND " + AFilterText;
    } else {
      Query1->SQL->Strings[2] = "";
    }
    Query1->Active = true;
  } __finally {
    Query1->EnableControls();
  }
}