Hi,
I use a TTMSFMXGrid filled by livebindings on a stored procedure.
I set options.filtering.dropdown:=true
and options.filtering.multicolumn:=true;
I implemented the solution given in previous post (set condition:='' in filterSelect event and refresh data)
when first clicking on a filter, the grid is correctly filtered, but when I click on a second dropdown filter, only one (sometimes two) values are displayed in the drop down list.
Is it an issue with livebindings or am I doing something wrong?
Hi,
The is because the data in the grid, after a filtering does not contain the correct values of the original dataset, only the filtered data, so the items in the dropdown list will be matched with the current values in the grid. You will need to manually clear and add new values per column that can be used for filtering:
procedure TForm1.TMSFMXGrid1NeedFilterDropDownData(Sender: TObject; Col,
Row: Integer; AValues: TStrings);
begin
AValues.Clear;
AValues.Add('item 1');
AValues.Add('item 2');
AValues.Add('item 3');
end;
Kind Regards,
Pieter
I'm not sure to understand. This is how my aplication works/
At opening, datas are displayed without filter:
Country State/Province
USA Alaska
USA Florida
Europe Spain
Europe Germany
...... ......
I click the dropdown button in the Country header to select USA. Then I set condition:=''; and refresh my dataset, adding the condition "country=USA", the grid is refilled with the new dataset like this:
Country State/Province
USA Alaska
USA Florida
When I click the dropdown button in the State header to select Florida, the dropdownlist is empty. It should contain Florida and Alaska?
As I said previously, I set MyGrid.options.filtering.Multicolumn:=true;
You say that the grid, after a filtering does not contain the correct values of the original dataset. But when the dataset is closed and fully reloaded, it should contain the actual data of the dataset
After the filter, the only unique value is USA, the values from florida and alaska are missing, which the grid does not know about. the filter list is based on the new values. To override this, and the execute a new filter statement on Florida, after USA is selected, you need to implement the OnNeedFilterDropDownData which asks for the items that needs to be displayed in the list.
procedure TForm1.TMSFMXGrid1NeedFilterDropDownData(Sender: TObject; Col,
Row: Integer; AValues: TStrings);
begin
AValues.Clear;
AValues.Add('USA');
AValues.Add('Florida');
AValues.Add('Alaska');
end;
The sample only illustrates which values need to be added, you will need to execute a Distinct SQL statement which retrieves all values from the Country field (column 1) and the State/Province Field (Column 2)
Kind Regards,
Pieter
do you mean that the values in the dropdownList aren't filled automatically from the values displayed in the column?
I thought it was working that way.
regards
Hi,
The values in the filter are automatically filled from the values displayed in the column if you do not filter the dataset. The difference between filtering on the grid, and filtering on the dataset is, that with the first option, the values are hidden, but still accessibile, while filtering on the dataset only displays the filtered values and there is no access to the hidden values. So the filter list does not know which values to add after filtering. This can be overcome by manually adding the correct values to the filter with the above approach
Kind Regards,
Pieter
Pieter Scheldeman2013-10-08 10:00:57
Actually, the dataset is not really filtered.
When getting a filter in then onFilterSelect event, i set condition:='' then I do the following:
myTBindSourceDBX.active:=false;
BindingSource.SubSQLDataSet is filled with a new query (including a new condition according to filter select)
then
BindingSource.Active:=true;
So, it's a brand new dataset that is linked to the grid, containing only the desired data. The dropdownlist should contain all distinct values of the dataset for this column.
Am I wrong?
The new dataset that is linked, containing only the desired data is the reason why the filter dropdownlist does not contain all the required values by default. That is also the reason why you need to add the correct values directly from the database. Each Active := False / Active := True combination clears the grid with the data, and fills the grid with new data.
Kind Regards,
Pieter
I agree, but what about the filter?
The first time the grid is filled, the filter contains all data displayed for each column.
In my example the filter for the column "state" contains Alaska, Florida,Spain, Germany
Once the grid is filled with new data, current filter should be cleared and automatically filled with the new data, just like it does at first filling of the grid.
Instead of this behaviour, all filters are empty. I can't run through the whole dataset(several hundreds of records) to fill the dropdown list each time the user clicks on a filter select.
Isn't there any method to reproduce the initial behaviour when the grid is cleared and refilled?
I suggest to load the data one time directly from the database and store the values in a stringlist for each column, you might need to declare a collection that holds a column, and then use that stringlist to return in the OnNeedFilterDropDownData event?
Kind Regards,
Pieter
Pieter Scheldeman2013-10-09 10:54:36
I think I'll give up with this functionality. I don't see the interest of using it, if I have to code everything by myself.
Thanks for your help
best regards