My FNCDatagrid have a Column with numbers and a Column with Dates.
The default Filter for both is "equal" this works if i select for one number or one Date. If i set the Filter to "grater" ord "lower" no numbers or dates will be found.
Maybe the Numbers are filled in as String.
I want to see if someone is older than 6 but no Datalines are shown.
Where is the data coming from? Is it added as a string or a number?
The data comes from a Databaseadapter that is bound to a Database.
Which field type?
This is the SQL Statement:
SQLNormal := Format('SELECT Kunden.Nachname, Kunden.Vorname, Kunden.Strasse, Kunden.PLZ, Kunden.Ort, ' + ' Kunden.Tel, Kunden.Mobil, Kunden.Pflegekasse, Kunden.VersNr, Kunden.Suchbegriff, Kunden.KdNr, ' + ' DateDiff(''%s'', Kunden.GeburtsDatum, DATE()) - ' + ' IIf(Format(Kunden.GeburtsDatum, ''%s'') > Format(DATE(), ''%s''), 1, 0) AS [Alter], ' + ' Angestellte.Vorname & '' '' & Angestellte.Nachname AS AngName, ' +
' Gebiete.Nr, Gebiete.Beschreibung AS GebietName, Kunden.Fax, Kunden.ID, ' + ' Kunden.Zusatz, Kunden.Anrede, Kunden.AngNr, Kunden.EMail, Kunden.Bemerkung, ' + 'Kunden.RMail, Kunden.Passiv, Kunden.PassivGrund, Kunden.Warteliste, ' + ' Kunden.EingabeDatum, Kunden.Gebiet, Kunden.Pflegegrad, Kunden.Guthaben, ' + ' Kunden.GuthabenDatum, Kunden.Leistungen, Kunden.GeburtsDatum, Kunden.Achtung ' + 'FROM (Kunden Left JOIN Gebiete ON Kunden.Gebiet = Gebiete.Nr) ' + 'Left JOIN Angestellte ON Kunden.AngNr = Angestellte.AngNr ' + 'WHERE (Kunden.Warteliste = false AND Kunden.Passiv = false) ' + 'ORDER BY Kunden.Nachname;', [differValue, fmtValue, fmt2Value]);
The Field 'Alter' is a calculated field.
At the Database Geburtsdatum is a Datefield.
We have tested this here on a calculated field accessed as an integer
procedure TForm1.ClientDataSet1CalcFields(DataSet: TDataSet);
begin
ClientDataSet1Lengthmm.AsInteger := ClientDataSet1Lengthcm.AsInteger * 10;
end;
For the date, we have tested this here as well with a date-time field, and the filtering works as expected.
How to set a field as Integer or Date after dataset.open.
At the Adapter or the Dataset.or the Grid?
It seems that all my fileds are String.
no Integer or date can be sortet with 'smaller than'
i tried to set the fieldtypes in my UniDAC-Dataset:
qKunden.FieldByName('KdNr').SetFieldType(ftInteger);
qKunden.FieldByName('Alter').SetFieldType(ftInteger);
qKunden.FieldByName('GeburtsDatum').SetFieldType(ftDate);
but this always do not help.
What about the Filter to Date.
I have german dateformat '20.01.1900' can i set the filtertype to german format so i can say Filter = larger then and Filtervalue= 20.01.1900
Hi,
This will only work when LoadMode := almAllRecords. Use the OnFieldToData event and the following code to convert the string to a field.
procedure TForm1.TMSFNCDataGridDatabaseAdapter1FieldToData(Sender: TObject;
ACell: TTMSFNCDataGridCellCoord; AMasterField, AField: TField;
var ACellValue: TTMSFNCDataGridCellValue; var Handled: Boolean);
begin
if AField.FieldName = 'Alter' then
begin
ACellValue := AField.AsInteger;
Handled := True;
end;
end;
New Info:
The problem is not at the filtering itself. It is in the changing of the filtercombobox.
I set the default Filter and this do work fine.
But if i change the Filter in the filterCombobox nothing changed.
That is what i set to the Filter while initialisation (CreateForm):
Options.Localization.FilterTypeStartsWithText := 'Startet mit';
Options.Localization.FilterTypeEndsWithText := 'Endet mit';
Options.Localization.FilterTypeContainsText := 'Enthält';
Options.Localization.FilterTypeNotContainsText := 'Enthält nicht';
Options.Localization.FilterTypeEqualText := 'gleich';
Options.Localization.FilterTypeNotEqualText := 'nicht gleich';
Options.Localization.FilterTypeSmallerThanText := 'kleiner als';
Options.Localization.FilterTypeLargerThanText := 'größer als';
Options.Localization.FilterTypeLargerOrEqualThanText := 'größer oder gleich';
Options.Localization.FilterTypeSmallerOrEqualThanText := 'kleiner oder gleich';
Options.Localization.FilterTypeEmptyText := 'leer';
Options.Localization.FilterTypeNotEmptyText := 'nicht leer';
// Alle Filter in den Spalten vorbelegen mit ihrem Default-Filter Wert
grd.ColumnCount := ColMaxK + 1;
for i := 0 to ColMaxK do
begin
col := Columns[i];
col.AddSetting(gcsFilterDefaultType);
// Überprüfung des Spaltentitels (Headers)
if SameText(col.Header, 'GeburtsDatum') or SameText(col.Header, 'Alter') then
col.FilterDefaultType := gftLargerThan //gftEqual
else If SameText(col.Header, 'KdNr') then
col.FilterDefaultType := gftLargerThan
else
col.FilterDefaultType := gftStartsWith;
end;
With this all work fine now but after changing the Filter-Combobox to somthing other, nothing change in the filter.
Maybe i have to change the defaultfilter after changing the Filtercombobox?
The code is ok, but we have seen issues with this as well and already applied improvements related to the default filter type. We'll release an update on Wednesday, so please keep an eye out, this should improve the behavior. Report back when you still see issues after installing the next update when it comes available.
Thanks
With the new Version it works.
But i have to Set the correct Datatype in the DatabaseAdapter like you show:
but another Problem comes up:
If i set a Filter, it shows the filtered Data in the Grid.
In my formular i show also the data from the selected Data record in DBEditFields. if i scroll with the cursor up i can see one record before the topest record. If i scroll with the mouse wheel i can scroll may records down and up outside the filtered data. In this case the selected record in the DataGrid is no more shown.
If you have LoadMode = almAllRecords, it filters in the grid, not at dataset level. Meaning, the dataset still contains all records. When scrolling in the dataset, it can select a record that is not visible. Please switch to LoadMode = almBuffered instead. If you cannot work with LoadMode = almBuffered, then you will need to override the default filtering and use OnApplyFilter event from the database adapter, and apply filtering directly at dataset level.
With loadMode=almBuffered the grid only show 20 rows and the rest is whithout data.
After i testet with loadMode=almBuffered and then back to LoadMode = almAllRecords the Cursor is working fine. But the scroll-wheel from the mouse goes through all records.
We are investigating this here and will propose a workaround or solution, but it will require direct filtering on the dataset. When filtering on the grid, and not on the dataset, they cannot be in sync. stay tuned.
In buffered mode, can you provide me with a screenshot of what is going on? And a code snippet which demonstrates there is missing data? The real solution for filtering is to stick with buffered mode.