Filter on InternalCalc field

[bazzer747 25]

Hi

I have a table with a field called 'cTaxYear', which is created as an Internal Calculated field (fkInternalCalc), it's a 7 character string field and contains, for instance, '2008-09', being a tax year (here in the UK) from 7Apr2008 to 6Apr2009.

 

The calculation is based on the date a dividend is paid out and works out what tax year that dividend falls into. It works perfectly well in dbgrids etc.

 

However, when I try to create a filter, for instance to filter all the records with a tax year of '2008-09' the dbgrid displays nothing.

There are about 150 records which do contain '2008-09' in the 'cTaxYear' field and the filter expression is 'cTaxYear = ' + QuotedStr( '2008-09' );

 

Reading about filters I see it says 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' (which is why I created an fkInternalCalc field)) but says nothing more about any settings that should be used (like maybe setting indexfieldnames).

 

Does anyone know for sure that an InternalCalc field can actually be used in a filter expression? Or if it definitely can't?

[Stano 143]

Basic information. Type of Database?

[bazzer747 25]

Oops, sorry.

 

I'm using a MSSQL  database.

[Guest]

hi @bazzer747

try this sample, but exist many other way: like a more simple is use your "SQL select" to filter your records! none code is necessary on app, you see?

 

type
  TForm1 = class(TForm)
    FDMemTable1: TFDMemTable;
    FDMemTable1id: TIntegerField;
    FDMemTable1myvalue: TCurrencyField;
    FDMemTable1myFieldCalculated: TStringField;
    FDMemTable1myDate1: TDateField;
    FDMemTable1myDate2: TDateField;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    btnFilteringMyFiscalYear2008: TButton;
    btnUNFILTERINGrecords: TButton;
    procedure FDMemTable1CalcFields(DataSet: TDataSet);
    procedure btnFilteringMyFiscalYear2008Click(Sender: TObject);
    procedure FDMemTable1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
    procedure btnUNFILTERINGrecordsClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses
  System.DateUtils,
  System.StrUtils;

{
  Checks whether a TDate value is in range.
  The DateInRange routine checks whether the TDate value ADate is between AStartDate and AEndDate.
  If the AInclusive Boolean parameter is true, then the limits are included; if it is false,
  ... the limits are excluded. By default, AInclusive is true.
  Note: The AStartDate value should be before the AEndDate value.
}
var
  // Date format in Brazil = dd/mm/yyyy
  // here, i dont take care about "time", only "date"
  lMyFiscalYearStart      : TDate;
  lMyFiscalYearEnd        : TDate;
  lMyFiscalYearForThisTest: string;

procedure TForm1.btnFilteringMyFiscalYear2008Click(Sender: TObject);
begin
  FDMemTable1.Filtered := false; // force un-filtering!
  FDMemTable1.Filtered := true;
end;

procedure TForm1.btnUNFILTERINGrecordsClick(Sender: TObject);
begin
  FDMemTable1.Filtered := false;
end;

procedure TForm1.FDMemTable1CalcFields(DataSet: TDataSet);
var
  lDate1: TDate;
  lDate2: TDate;
  //
  lTrue1: Boolean;
  lTrue2: Boolean;
begin
  // for didactic test ok? use as you want... (fields directly on test)
  (*
    lDate1 := TDate(FDMemTable1myDate1.Value);
    lDate2 := TDate(FDMemTable1myDate2.Value);
    //
    if (lDate1 >= lMyFiscalYearStart) and { }
    (lDate2 <= lMyFiscalYearEnd) then
    FDMemTable1myFieldCalculated.Value := lMyFiscalYearForThisTest
    else
    FDMemTable1myFieldCalculated.Value := 'Year-Month'; // another conditions
  *)
  //
  // ----------------
  //
  lTrue1 := System.DateUtils.DateInRange(FDMemTable1myDate1.AsDateTime, lMyFiscalYearStart, lMyFiscalYearEnd);
  lTrue2 := System.DateUtils.DateInRange(FDMemTable1myDate2.AsDateTime, lMyFiscalYearStart, lMyFiscalYearEnd);
  //
  if lTrue1 and lTrue2 then
    FDMemTable1myFieldCalculated.Value := lMyFiscalYearForThisTest
  else
    FDMemTable1myFieldCalculated.Value := 'Year-Month'; // another conditions
end;

procedure TForm1.FDMemTable1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  // ContainsStr() case-sensitive
  // ContainsText() case-insensitive
  //
  // you can use this event to create your filter HERE, then, is not necessary use "CalcFields event" above (used only for show some value resulted of some calculating
  //
  if System.StrUtils.ContainsText(FDMemTable1myFieldCalculated.Value, lMyFiscalYearForThisTest) then
    Accept := true
  else
    Accept := false;
end;

initialization

// here, you have a initialization before another event on form!!!
// because, if the dataset is opened on desigtime, the filter occurrs before "oncreate"
// but you can change this value where you want...
lMyFiscalYearStart       := StrToDate('07/04/2008');
lMyFiscalYearEnd         := StrToDate('06/04/2009');
lMyFiscalYearForThisTest := '2008-09';

finalization

end.

 

hug

Edited January 14, 2021 by Guest

[bazzer747 25]

Thanks for the information, that's certainly one way to go.

 

I have setup the internal calc field so am keen to understand if it really is possible to filter on that field (as the official documentation seems to suggest).

[Guest]

You should peruse the documentation and sources of your chosen DAC (Data Access Components).

The TDataSet base class does not implement very much at all.

[bazzer747 25]

Hi

I have. I've read the documentation on the Embarcadero wiki site, and I have Cary Jenson's book 'Delphi in Depth: Firedac', and quoted what it says above. It isn't in enough detail to say specifically you can filter on an internal calulated field, but it does say:  'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' which says you can use filtering on an internal calculated field.

 

But I'm trying it and it doesn't - but that maybe because there are some details or constraints required to do this which I can't find anywhere.

[Virgo 18]

How is this field calculated? Because if it is calculated in OnCalcField event, then it is not really InternalCalc field.

[Uwe Raabe 2064]

1 minute ago, Virgo said:

Because if it is calculated in OnCalcField event, then it is not really InternalCalc field.

If it is declared as fkInternalCalc, its value can be set in the OnCalcField event when the dataset State is dsInternalCalc.

[bazzer747 25]

Interesting. How would an InternalCalc field be calculated then?

 

Yes, the calculation is done on the OnCalcField, and works fine displaying all the correct calculations (which I see in a dbgrid).

[Virgo 18]

3 minutes ago, bazzer747 said:

Interesting. How would an InternalCalc field be calculated then?

In original SQL query?

[bazzer747 25]

Sorry, no. The original SQL query returns all the records, of which I want to filter just some of them. I can use a new SQL query to do this job (as I have done in the past) but I wanted to try using an internal calc field to see if this is quicker/more efficient (which I believe it is).

 

All I want to know for certain is that I can filter on an internal calc field. If I can I'll dig a lot deeper with testing etc. to see why it isn't working. I would have thought a simple 'Filter = 'QuotedStr( 'cTaxYear' ); statement, where cTaxYear holds the relevant data would work, but it doesn't (and I've checked the values from this variable against the internal calculation results and they match.

[Virgo 18]

Ok, documentation seems to indicate, that InternalCalc fields are supposed to work, like you describe. But in my testing in older Delphi version I cannot manually add them to dataset (unlike calculated fields)...

 

 

[Virgo 18]

You could try adding somethin like following to select (example is for Firebirdsql, mod is different in MSSQL

CASE
WHEN EXTRACT(MONTH FROM DATEFIELD) < 4 THEN CAST(EXTRACT(YEAR FROM DATEFIELD) - 1 AS CHAR(4)) || '-' || CAST(MOD((EXTRACT(YEAR FROM DATEFIELD)), 100) AS CHAR(2))
WHEN EXTRACT(MONTH FROM DATEFIELD) > 4 THEN CAST(EXTRACT(YEAR FROM DATEFIELD) AS CHAR(4)) || '-' || CAST(MOD((EXTRACT(YEAR FROM DATEFIELD)+1), 100) AS CHAR(2))
WHEN EXTRACT(MONTH FROM DATEFIELD) = 4 THEN
    CASE
    WHEN EXTRACT(DAY FROM DATEFIELD) < 7 THEN CAST(EXTRACT(YEAR FROM DATEFIELD) - 1 AS CHAR(4)) || '-' || CAST(MOD((EXTRACT(YEAR FROM DATEFIELD)), 100) AS CHAR(2))
    WHEN EXTRACT(DAY FROM DATEFIELD) >= 7 THEN CAST(EXTRACT(YEAR FROM DATEFIELD) AS CHAR(4)) || '-' || CAST(MOD((EXTRACT(YEAR FROM DATEFIELD)+1), 100) AS CHAR(2))
    END
ELSE
NULL
END cTaxYear

 

But that does gives 2006-7 instead of 2006-07....

Edited January 15, 2021 by Virgo
Correction

[Uwe Raabe 2064]

1 hour ago, bazzer747 said:

Yes, the calculation is done on the OnCalcField, and works fine displaying all the correct calculations (which I see in a dbgrid).

Can you provide some code?

[Virgo 18]

I just managed to get InternalCalc field in my older version of Delphi and there it still behaves as normal calculated field in filters and sorting (not working). Maybe it depends on particular TDataSet descendant having support for this. 

[bazzer747 25]

procedure Tdm.fdqDivsCalcFields(DataSet: TDataSet);
begin

//PART OF ...

if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2018') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2019') ) then fdqDivscTaxYear.AsString:= '2018-19';
if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2019') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2020') ) then fdqDivscTaxYear.AsString:= '2019-20';
if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2020') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2021') ) then fdqDivscTaxYear.AsString:= '2020-21';
END;

 

And in the click on a radio group:

  with dm.fdqDivs do
    begin
      Filtered:= False;
      if (cAll =  'ALL') AND (cTaxYear =  'ALL') then Exit; //No filter required

      if (cAll <> 'ALL') AND (cTaxYear =  'ALL') then Filter:= 'Whose = '        + QuotedStr( cWhose   );
      if (cAll =  'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'DividendDate = ' + QuotedStr( cTaxYear );
      if (cAll <> 'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'Whose = '        + QuotedStr( cWhose   ) + ' AND cTaxYear = ' + QuotedStr( cTaxYear );
      Filtered:= True;
    end;

 

cWhose is a letter value indicating a persons initial. cTaxYear holds the selected value to filter on (eg '2018-19').

[Uwe Raabe 2064]

According to the docwiki, InternalCalc fields are only supported in TClientDataSets:

Quote

dsInternalCalc

Internal Calc

DataSet open. An OnCalcFields event is underway for calculated values that are stored with the record. (client datasets only)

On the other hand, FireDAC mentions an example of using fkInternalCalc.

Edited January 15, 2021 by Uwe Raabe

[Virgo 18]

23 minutes ago, Uwe Raabe said:

According to the docwiki, InternalCalc fields are only supported in TClientDataSets:

 

Ok, so it is dependant of actual TDataSet descendant.

Anyway, did some reading and for Interbase/Firebirdsql fields declared in server as COMPUTED BY fields are also InternalCalc fields in Delphi (with IBX and components forked from it). Basically very specific usage.

Edited January 15, 2021 by Virgo
correction

[Lajos Juhász 295]

According to my test with calculated fields you can use only the OnFilterRecord event to filter the data.

In my test calculated field didn't worked as expected with the filter property. Is it a bug or buy design I have no idea only @Dmitry Arefiev could give an answer.

[bazzer747 25]

I'm misunderstanding a little here. I am using OnCalcFields AND it is working - I can see the result in a dbgrid. And the documentation indicates you can filter on an internal calculated field. I see what that link and you say about it but it doesn't ring true does it if the calculation is doing what it should do? And they give an example using it which seems to refute what they say. It must be a nightmare trying to keep the documentation up to date.

 

However, It's looking like it's a no goer for me then. I'll use the old SELECT statement to filter the records. Pity Cary Jensen didn't note an issue in his in-depth book (I'll drop him a line, but no doubt he's inundated with questions about his book), it would've saved me some time, but hey-ho, you learn something every day.

 

Thanks for your time helping me with this.

[Guest]

Data.DB.TField.FieldKind on HELP SYSTEM:

Quote

• Indicates whether a field represents a column in a dataset, a calculated field, or a lookup field. 
• Use FieldKind to determine whether a field is a data field, a calculated field, a lookup field, or an aggregate field. The value of FieldKind can be changed programmatically, but in practice FieldKind is set at design time when creating Field components with the Fields editor. 
• Note: Fields calculated by SQL servers or the Borland Database Engine to display the results of a query that returns a live dataset have a FieldKind of fkInternalCalc, not fkCalculated. This is because the field values are stored in the dataset. Calculated fields in a client dataset that are calculated in an OnCalcFields event handler but stored in the dataset also have a FieldKind of fkInternalCalc instead of fkCalculated. Unlike regular calculated fields, internally calculated fields can be used in filter expressions. They can be edited, but the changes are discarded. To prevent editing, set the ReadOnly property to True.
• Note: The FieldKind property of a field is only stored with the field object if the value is fkInternalCalc.
• Note: For example, for Data.DB.TAggregateField, the FieldKind property is set to fkAggregate. 

---------------------------
MY OBSERVATION ABOUT DIFFERENCE BETWEEN: "fkInternalCalc" AND "fkCalculated":

As "fkInternalCalc" will be part of the Dataset, then it can be used as a filter because it will have its values defined before the filter goes into action.
The "fkCalculated" field will have its value defined according to a condition not yet observed by the filter, so it may be the cause of the filter failing, that is, if I don't know what to filter, how can I filter some thing?

-- when Filter is TRUE

FireDAC.Comp.Dataset.pas
1) procedure TFDDataSet.SetFiltered(AValue: Boolean); line 7157

Data.DB.pas
1) procedure TDataSet.SetFiltered(Value: Boolean); line 13942

FireDAC.Comp.Dataset.pas
1) procedure TFDDataSet.SafeFilteringUpdated; line 7126
2) procedure TFDDataSet.DoFilteringUpdated(AResync: Boolean); line 7114

unit1.pas (from Applicative of user)
1) run "OnFilterRecord" if defined to filter record: 
----> "Accept" or not Accept this record? True or False!!!

FireDAC.Comp.Dataset.pas
-----------------------------------  
1) function TFDDataSet.DoFilterRow(AMech: TFDDatSMechFilter;  ARow: TFDDatSRow; AVersion: TFDDatSRowVersion): Boolean;
  ...
 .... go to line: 7193 --> EndForceRow;
 ... 
 ... anothers calls other procedures pertinents
 ...
 ... came back to "OnFilterRecord" of user unit1.pas
 ...

<< THIS IS HERE IS REPEAT FOR ALL RECORDS UNTIL END FILTER ALL >>

-----------------------------------
unit1.pas
1) came back to unit1.pas (from applicative of user) and use "OnCalcFields" events
   but dont execute its line, just "GO TO END" of procedure.
   Then, appears that is not executed at all... because none attribuition is done in
   calculated-fields!

-----------------------------------
from here, many many many calls is done!
--- procedures (include to SetFilter, etc...)
--- message to system
--- 
... on the end, return to applicative screen with records filtred as expected

 

my same project with a little changes and observations: using "fkInternalCalc" and working!

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDMemTable1.Filtered := false;
  // FDMemTable1myFieldCalculated
  // FDMemTable1myIntCalField
  // FDMemTable1.Filter   := 'myDate1 = '#39'15/07/2008'#39; // OK = works!!!
  // FDMemTable1.Filter   := 'myIntCalField = '#39'2008-09'#39; // OK = works!!!
  // FDMemTable1.Filter   := 'myFieldCalculated = '#39'2008-09'#39; // NOT OK = does notworks!!!
  FDMemTable1.Filtered := true;
  //
end;

procedure TForm1.FDMemTable1CalcFields(DataSet: TDataSet);
var
  lDate1: TDate;
  lDate2: TDate;
  //
  lTrue1: Boolean;
  lTrue2: Boolean;
begin
  // for didactic test ok? use as you want... (fields directly on test)
  (*
    lDate1 := TDate(FDMemTable1myDate1.Value);
    lDate2 := TDate(FDMemTable1myDate2.Value);
    //
    if (lDate1 >= lMyFiscalYearStart) and { }
    (lDate2 <= lMyFiscalYearEnd) then
    FDMemTable1myFieldCalculated.Value := lMyFiscalYearForThisTest
    else
    FDMemTable1myFieldCalculated.Value := 'Year-Month'; // another conditions
  *)
  //
  // ----------------
  //
  lTrue1 := System.DateUtils.DateInRange(FDMemTable1myDate1.AsDateTime, lMyFiscalYearStart, lMyFiscalYearEnd);
  lTrue2 := System.DateUtils.DateInRange(FDMemTable1myDate2.AsDateTime, lMyFiscalYearStart, lMyFiscalYearEnd);
  //
  if lTrue1 and lTrue2 then
  begin
    FDMemTable1myFieldCalculated.Value := lMyFiscalYearForThisTest;
    FDMemTable1myIntCalField.Value     := lMyFiscalYearForThisTest
  end
  else
  begin
    FDMemTable1myFieldCalculated.Value := 'Year-Month'; // another conditions
    FDMemTable1myIntCalField.Value     := 'Year-Month'; // another conditions
  end;
end;

 

my FDMemTable field definitions

    object FDMemTable1id: TIntegerField
      DisplayWidth = 10
      FieldName = 'id'
    end
    object FDMemTable1myvalue: TCurrencyField
      DisplayWidth = 10
      FieldName = 'myvalue'
    end
    object FDMemTable1myDate1: TDateField
      DisplayWidth = 10
      FieldName = 'myDate1'
    end
    object FDMemTable1myDate2: TDateField
      DisplayWidth = 14
      FieldName = 'myDate2'
    end
    object FDMemTable1myFieldCalculated: TStringField
      DisplayWidth = 22
      FieldKind = fkCalculated
      FieldName = 'myFieldCalculated'
      Calculated = True
    end
    object FDMemTable1myIntCalField: TStringField
      DisplayWidth = 27
      FieldKind = fkInternalCalc
      FieldName = 'myIntCalField'
      Size = 7
    end

 

hug

Edited January 15, 2021 by Guest

[Lajos Juhász 295]

Calculated fields are working in FireDAC and you can use them to display data. However, in order to filter data instead of filter property you can use the OnFilterRecord event. For Example:

myQuery.Filter:='cTaxYear = ' + QuotedStr( '2008-09' );
myQuery.Filtered:=true;

You can use:
 

For query myquery and form myFormFRM you can write:

myQuery.OnFilterRecord:=FilterTaxYear;
myQuery.Filtered:=true;

procedure myFormFRM.FilterTaxYear(DataSet: TDataSet;
  var Accept: Boolean);
begin
   Accpet:=VarToStr(DataSet['cTaxYear'])='2008-09';
end;

[bazzer747 25]

Lajos,

Are you referring specifically to Internal Calculated fields here?

[Guest]

2 hours ago, Uwe Raabe said:

According to the docwiki, InternalCalc fields are only supported in TClientDataSets:

On the other hand, FireDAC mentions an example of using fkInternalCalc.

hi @Uwe Raabe

 

if fact, it shoud works for any TDataset descendent, please look my sample above using "InternalCalcField"

https://en.delphipraxis.net/topic/4250-filter-on-internalcalc-field/?tab=comments#comment-36995

 

Here, we can see that the "InternalCalcFields" are really stored phisically in files - look my myIntCalcField in myData.xml

same dont belong to my table as others, it is real in my storage!

 

myDatas.xml

Edited January 15, 2021 by Guest