Jump to content
bazzer747

Filter on InternalCalc field

Recommended Posts

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?

Share this post


Link to post

Basic information. Type of Database?

Share this post


Link to post

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?

 

image.thumb.png.4b2950f77ca059e5f878fc7f370020a9.png

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 by emailx45

Share this post


Link to post

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).

Share this post


Link to post

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.

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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).

Share this post


Link to post
3 minutes ago, bazzer747 said:

Interesting. How would an InternalCalc field be calculated then?

In original SQL query?

Share this post


Link to post

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.

Share this post


Link to post

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)...

 

 

Share this post


Link to post

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 by Virgo
Correction

Share this post


Link to post
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?

Share this post


Link to post

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. 

Share this post


Link to post

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').

Share this post


Link to post

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 by Uwe Raabe

Share this post


Link to post
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 by Virgo
correction

Share this post


Link to post

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.

Share this post


Link to post

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.

Share this post


Link to post

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!

image.thumb.png.28c515905905b667c4a615ebf7f41284.png

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 by emailx45

Share this post


Link to post

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;

  • Like 1

Share this post


Link to post
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 by emailx45

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×