automatic sorting???

Posted by: wolde on 8 September 2017, 12:45 pm EST

  • Posted 8 September 2017, 12:45 pm EST

    hello,
    I´ve got this piece of code to define a row filter and try to apply automatic sorting.
     
                    FarPoint.Win.Spread.HideRowFilter rowFilter =
    new FarPoint.Win.Spread.HideRowFilter(Receiver.ActiveSheet);
    Receiver.ActiveSheet.RowFilter = rowFilter;

    for (int i = 0; i < functionParams.FilterSelection.Length; ++i)
    {
    SimpleResultFilterItem filterItem = functionParams.FilterSelectionIdea [I];
    FarPoint.Win.Spread.FilterColumnDefinition filterColumn =
    new FarPoint.Win.Spread.FilterColumnDefinition(filterItem.ColumnIndex, FarPoint.Win.Spread.FilterListBehavior.Custom);

    MultiValueFilterItem columnFilter = new MultiValueFilterItem(Receiver.ActiveSheet);
    columnFilter.Constraints = filterItem.Constraints;
    columnFilter.FirstRowIndex = 2;
    filterColumn.Filters.Add(columnFilter);

    rowFilter.ColumnDefinitions.Add(filterColumn);
    Receiver.ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;
    Receiver.ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);
    }
     rowFilter.ShowFilterIndicator = true;
    }
     How can I make a column filtered without user intervention. The column filter is displayed and I try to activate it with:
    ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);
    ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;

    But the column gets only filtered when I click the filter in the header cell.

    I would appreciate any advice.

    Wolfgang

  • Replied 8 September 2017, 12:45 pm EST

    hello Scott,
    what does AllowAutoFilter mean?

    This shows the custom filter entry but doesn´t filter:
    a) //Receiver.ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;
    Receiver.ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);

    or b) Receiver.ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);
    Receiver.ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;
    ...
    Receiver.ActiveSheet.RowFilter = rowFilter;

    This doesn´t show the custom row filter:
    ...
    Receiver.ActiveSheet.RowFilter = rowFilter;
    ...
    Receiver.ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);

    Receiver.ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;

    Do you copy or hold a reference to the row filter? In any way I don´t understand how the filter can automatically be applied to the column without the user to select the filter manually.

    Wolfgang
  • Replied 8 September 2017, 12:45 pm EST

    Wolfgang,


    You need to switch these lines of code. The AllowAutoFilter needs to be turned on before filtering the column.


    ActiveSheet.AutoFilterColumn(filterItem.ColumnIndex, columnFilter.DisplayName, 0);
    ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;


    Also, there is no need to pass the third parameter to the AutoFilterColumn method. This is only used for the AllString when the AllString is one of the items in the list also.

  • Replied 8 September 2017, 12:45 pm EST

    Scott,
    I tried all variations of
    AutoFilterColumn(3, null | "" | "= AnyTerm", 0 | 1);



    "= AnyTerm" is the display name of the filter item.

    and none worked. Either this method has a bug or it doesn´t work with a filter item.
    What´s the meaning of passing a filter string and a filter item index if the filter item has its own filtering.

    Regards

    Wolfgang

  • Replied 8 September 2017, 12:45 pm EST

    Scott,
    with FilterListBehavior.Custom there is only one filter item in the filter list. Tell me how to use this one item. I don´t use a filter string and the only item index I can see is 0.

    It would be easiest if there is a method like this:
    AutoFilterColumn(columnIndex, filterItemIndex) or
    AutoFilterColumn(columnIndex, filterItemReference)

    What if I try this one?
    AutoFilterColumn(3, null, 0)
    Maybe this one?
    AutoFilterColumn(3, null, 1);
    Let´s see.

    Thanks a lot anyway for your fast replies.

    Wolfgang

  • Replied 8 September 2017, 12:45 pm EST

    Wolfgang,


    You need to turn on the AllowAutoFilter property in order for the AutoFilterColumn method to work.

     
    		FpSpread1.Sheets(0).Columns(0).AllowAutoFilter = True
    FpSpread1.Sheets(0).AutoFilterColumn(0, "134", 0)
     
  • Replied 8 September 2017, 12:45 pm EST

    Hello Scott,
    I´m learning but without getting smarter. I improved my code like this:
     
                    FarPoint.Win.Spread.HideRowFilter rowFilter =
    new FarPoint.Win.Spread.HideRowFilter(Receiver.ActiveSheet);

    Dictionary<int, string> filterItemMap = new Dictionary<int, string>();
    for (int i = 0; i < functionParams.FilterSelection.Length; ++i)
    {
    SimpleResultFilterItem filterItem = functionParams.FilterSelectionIdea [I];
    if (filterItem.Constraints.Length == 0)
    continue;

    FarPoint.Win.Spread.FilterColumnDefinition filterDefinition =
    new FarPoint.Win.Spread.FilterColumnDefinition(filterItem.ColumnIndex, FarPoint.Win.Spread.FilterListBehavior.Custom);

    MultiValueFilterItem columnFilter = new MultiValueFilterItem(Receiver.ActiveSheet);
    columnFilter.Constraints = filterItem.Constraints;
    // TODO FirstRowIndex must come from the model.
    columnFilter.FirstRowIndex = 2;
    filterDefinition.Filters.Add(columnFilter);
    rowFilter.AddColumn(filterDefinition);
    filterItemMap.Add(filterItem.ColumnIndex, columnFilter.DisplayName);

    }

    Receiver.ActiveSheet.RowFilter = rowFilter;
    rowFilter.ShowFilterIndicator = true;

    foreach (KeyValuePair<int, string> filterItemPair in filterItemMap)
    {
    System.Collections.ArrayList createdFilterItems =
    Receiver.ActiveSheet.GetDropDownFilterItems(filterItemPair.Key);

    int k = 0;
    for (; k < createdFilterItems.Count; ++k)
    if (createdFilterItems[k].Equals(filterItemPair.Value))
    break;

    Receiver.ActiveSheet.AutoFilterColumn(filterItemPair.Key, filterItemPair.Value, k);
    Receiver.ActiveSheet.Columns[filterItemPair.Key].AllowAutoFilter = true;
    FarPoint.Win.Spread.FilterColumnDefinition filterDef = rowFilter.GetFilterColumnDefinition(filterItemPair.Key);
    }
    }
     
    While looping through the DropDownFilterItems all items are set properly. But when displaying the grid afterwards my filter items are replaced by the default ones. Why does the row filter in this case lose its FilterColumnDefinition?

    Wolfgang
  • Replied 8 September 2017, 12:45 pm EST

    Hello Scott,
    thank you for your help!

    Wolfgang
  • Replied 8 September 2017, 12:45 pm EST

    Wolfgang,


    I have found this to be a bug. When you have custom filters and you try to programatically filter by the custom filter, it does not do anything. I have reported this as bug #21018 for the development team to fix for the next maintenance release.

  • Replied 8 September 2017, 12:45 pm EST

    Scott,
    I found a workaround!
                    FarPoint.Win.Spread.HideRowFilter rowFilter =
    new FarPoint.Win.Spread.HideRowFilter(Receiver.ActiveSheet);

    Dictionary<int, string> filterItemMap = new Dictionary<int, string>();
    for (int i = 0; i < functionParams.FilterSelection.Length; ++i)
    {
    SimpleResultFilterItem filterItem = functionParams.FilterSelectionIdea [I];
    if (filterItem.Constraints.Length == 0)
    continue;
    // Look at this! This does the magic!
    FarPoint.Win.Spread.FilterColumnDefinition filterDefinition =
    new FarPoint.Win.Spread.FilterColumnDefinition(filterItem.ColumnIndex, FarPoint.Win.Spread.FilterListBehavior.Default);

    MultiValueFilterItem columnFilter = new MultiValueFilterItem(Receiver.ActiveSheet);
    columnFilter.Constraints = filterItem.Constraints;
    // TODO FirstRowIndex must come from the model.
    columnFilter.FirstRowIndex = 2;
    filterDefinition.Filters.Add(columnFilter);
    rowFilter.AddColumn(filterDefinition);
    filterItemMap.Add(filterItem.ColumnIndex, columnFilter.DisplayName);
    Receiver.ActiveSheet.Columns[filterItem.ColumnIndex].AllowAutoFilter = true;
    }

    Receiver.ActiveSheet.RowFilter = rowFilter;
    rowFilter.ShowFilterIndicator = true;

    foreach (KeyValuePair<int, string> filterItemPair in filterItemMap)
    {
    System.Collections.ArrayList createdFilterItems =
    Receiver.ActiveSheet.GetDropDownFilterItems(filterItemPair.Key);

    int k = 0;
    for (; k < createdFilterItems.Count; ++k)
    if (createdFilterItems[k].Equals(filterItemPair.Value))
    break;

    Receiver.ActiveSheet.AutoFilterColumn(filterItemPair.Key, filterItemPair.Value, k);
    }
     The FilterListBehavior.Custom doesn´t work at all as we guessed, didn´t we? Here you got the bug. I´d like to have the custom behaviour, too. Because the filter list is easier to read. In the meantime we´ll get along with this.

    Regards,
    Wolfgang
  • Replied 8 September 2017, 12:45 pm EST

    Wolfgang,


    Could you post a small zipped project reproducing the issue you are seeing for us to debug?

  • Replied 8 September 2017, 12:45 pm EST

    Scott,
    I really tried your proposals and also had a look at this:
    http://www.clubfarpoint.com/Forums/forums/thread/21122.aspx

    This explanation is clear to me. But these methods don´t exist.

    With FarPoint.Win.Spread.FilterListBehavior.Custom in the FilterColumnDefinition there is only one filter item in the filter item list as opposed to the documentation stating the filter item "All" has always index 0. Even if there is none? How should I know the index of my filter item when the Farpoint code populates and manages this filter list and I don´t have access to it. I can only set something in the code, run the programm and see something in the filter list and guess if it will work when I will insert it in the code. This seems strange to me. Is there any other way to do this more predictably?

    Scott, while testing I discovered that the filter item list sometimes gets populated very slowly. I assume that the filter list is filled upon an event when clicking on the column header cell and afterwards gets painted. So before there is no index 0 and whatsoever. Hence AutoFilterColumn(.., ..., filterItemIndex) doesn´t work with FarPoint.Win.Spread.FilterListBehavior.Custom.

    After applying the filter item manually the filter list is refilled and surprise, surprise there is an "All" filter item at index 0 and the custom filter item at index 1. It´s also inconvenient to apply AllowAutoFilter and AutoFilterColumn to filter. Why can´t there be an easy way like AutoFilterColumn(Filter

    Anyway I will try several FilterListBehavior to understand if this is the reason of failure and hopefully get a sample project to send you.

    Regards,

    Wolfgang
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels