Searching in RadGridView numbers with separators.

3 Answers 190 Views
GridView Separator
Joanna
Top achievements
Rank 1
Iron
Joanna asked on 07 Sep 2022, 09:48 AM | edited on 07 Sep 2022, 09:51 AM

I use RadGridView in WinForms. There is search field, when I set AllowSearchRow property to 'true'.

When I search there numbers everything is ok until I put separator of thousands. First screen 'Clipboard01' without separators.

Second and third screen 'Clipboard02' and 'Clipboard03' shows how it looks like when i put separator or write number without separator.

I will mention that numbers in grid have 'space' as separator of thousands.

How you can see nothing is found.

Have you any idea how to handle separators of thousands in the search engine?

3 Answers, 1 is accepted

Sort by
0
Accepted
Dess | Tech Support Engineer, Principal
Telerik team
answered on 14 Sep 2022, 07:48 AM

Hello, Joanna,

I am sorry that I missed to specify that It is important to subscribe to the CreateRowInfo event at design time. While handling this case, I have also prepared the following KB article:

https://docs.telerik.com/devtools/winforms/knowledge-base/grid-searching-in-formated-decimal-columns#search-results-and-highlighted-cells 

I have attached the sample project for your reference. Please give it a try and see how it works on your end.

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Joanna
Top achievements
Rank 1
Iron
commented on 15 Sep 2022, 05:49 AM

I think it's the best solution for me. I also added SearchProgressChanged event to show only records I searching:


private void RadGridView1_SearchProgressChanged(object sender, SearchProgressChangedEventArgs e)
{
	if (e.SearchFinished == true && e.SearchCriteria != null && e.SearchCriteria != string.Empty)
	{
		List<int> indexes = new List<int>();
		foreach (GridSearchResultCellInfo c in e.Cells)
		{
			if (indexes.Contains(c.RowInfo.Index) == false)
				indexes.Add(c.RowInfo.Index);
		}

		foreach (var row in this.radGridView1.Rows)
		{
			if (indexes.Contains(row.Index) == false)
				row.IsVisible = false;
			else
				row.IsVisible = true;
		}
	}
	else if (e.SearchFinished == true)
	{
		foreach (var row in this.radGridView1.Rows)
			row.IsVisible = true;
	}
}

 

One more time thanks for help.

1
Dess | Tech Support Engineer, Principal
Telerik team
answered on 07 Sep 2022, 12:44 PM

Hello,

Following the provided information, I was able to replicate the undesired behavior when the culture's NumberGroupSeparator is set to " "

        public RadForm1()
        {
            InitializeComponent();

            CultureInfo culture = new System.Globalization.CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = culture;
            NumberFormatInfo ni = new NumberFormatInfo();
            ni.NumberGroupSeparator = " ";
            culture.NumberFormat = ni;


            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Price", typeof(decimal));

            dt.Rows.Add(1, "Test", 461892.65);
            dt.Rows.Add(1, "Test", 12);
            dt.Rows.Add(1, "Test", 5461.34);

            this.radGridView1.DataSource = dt;
            this.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;

            GridViewDecimalColumn decimalColumn = radGridView1.Columns["Price"] as GridViewDecimalColumn;
            decimalColumn.FormatString = "{0:n2}";

            this.radGridView1.AllowSearchRow = true;
        }

 

These are the regional settings on my computer:

It is important to note that the same culture should be applied to the column itself:

            GridViewDecimalColumn decimalColumn = radGridView1.Columns["Price"] as GridViewDecimalColumn;
            decimalColumn.FormatInfo = culture;

Thus, the search functionality will highlight the formatted cell values that match the criteria:

I have attached my sample project for your reference.

I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

0
Joanna
Top achievements
Rank 1
Iron
answered on 08 Sep 2022, 05:53 AM | edited on 08 Sep 2022, 06:03 AM

Yes, this is it. It's enough to set FormatCulture to decimal column. Thank you for your help.

But one more thing... there is the way to search decimal number without 'space' when I have decimal column setted as culture with space separator of thousands?

For example in column I have number 2 500,00 and I want write 2500 in search field. 

Dess | Tech Support Engineer, Principal
Telerik team
commented on 08 Sep 2022, 08:36 AM

Hello, Joanna, 

Note that the search functionality in RadGridView is purposed to search for the matches considering the formatted cells' values /not the raw cell's value/ according to the applied culture to the column. The formatted text is actually highlighted then. That is why it is required to enter the NumberGroupSeparator when searching in order to find an exact match.

However, if you want to highlight the result no matter of the formatted text, a better option is to use custom filtering where you have full control over what rows to be visible or not according to the raw value. More information about the custom filtering functionality is available in the following help article:
https://docs.telerik.com/devtools/winforms/controls/gridview/filtering/custom-filtering 

I would recommend you to have a look at our Demo application >> GridView >> Filtering >> Custom Filtering example which is quite useful on this topic. 
I have also prepared a sample code snippet for your reference which result is illustrated in the gif file file. Note that this is just a sample approach and it may not cover all possible cases. Feel free to modify and extend it in a way which suits your requirements best. 
            this.radGridView1.EnableFiltering = true;
            this.radGridView1.ShowFilteringRow = false;
            this.radGridView1.EnableCustomFiltering = true;
            this.radGridView1.CustomFiltering += radGridView1_CustomFiltering;
            this.radTextBox1.TextChanged += radTextBox1_TextChanged;    

        private void radTextBox1_TextChanged(object sender, EventArgs e)
        {
            this.radGridView1.MasterTemplate.Refresh();
        }

        private void radGridView1_CustomFiltering(object sender, GridViewCustomFilteringEventArgs e)
        {
            if (string.IsNullOrEmpty(this.radTextBox1.Text))
            {
                this.radGridView1.BeginUpdate();
                e.Visible = true;
                for (int i = 0; i < this.radGridView1.ColumnCount; i++)
                {
                    e.Row.Cells[i].Style.Reset();                   
                }

                this.radGridView1.EndUpdate(false);
                return;
            }
            this.radGridView1.BeginUpdate();
            e.Visible = false;
            for (int i = 0; i < this.radGridView1.ColumnCount; i++)
            {
                string text = e.Row.Cells[i].Value.ToString();
                decimal filterValue;
                if ((decimal.TryParse(this.radTextBox1.Text, out filterValue) && text.Contains(filterValue.ToString())) 
                    || text.Contains(this.radTextBox1.Text))
                { 
                    e.Visible = true;
                    e.Row.Cells[i].Style.CustomizeFill = true;
                    e.Row.Cells[i].Style.DrawFill = true;
                    e.Row.Cells[i].Style.BackColor = Color.FromArgb(201, 252, 254);
                }
                                else
                {
                    e.Row.Cells[i].Style.Reset();                    
                }

                this.radGridView1.EndUpdate(false);
            }
        }
Joanna
Top achievements
Rank 1
Iron
commented on 08 Sep 2022, 10:24 AM

Ok, so I think Custom Filtering is the solution for me.

Thanks again for help.

Joanna
Top achievements
Rank 1
Iron
commented on 09 Sep 2022, 09:38 AM

Above is working, but performance is not so good. I put 3000 records to your example app and after each char I write in search box is about 3-4 seconds delay. When there are 6000 records it's already 10 seconds. We have app with thousends of records and it doesn't look good.
Dess | Tech Support Engineer, Principal
Telerik team
commented on 09 Sep 2022, 10:04 AM

The possible solution that I can suggest is to postpone the custom filtering execution, e.g. with 2 seconds until the user stops typing:

            t = new Timer();
            t.Interval = 2000;
            t.Tick += T_Tick;

        private void T_Tick(object sender, EventArgs e)
        {
            this.radGridView1.MasterTemplate.Refresh();
            t.Stop();
        }

        Timer t = new Timer();
        private void radTextBox1_TextChanged(object sender, EventArgs e)
        {
            t.Stop();
            t.Start();
        }

I believe that it would fit your scenario.

Joanna
Top achievements
Rank 1
Iron
commented on 12 Sep 2022, 09:58 AM

This is a partial solution to the problem. Fact, it actually looks better when typing text. However, after typing all text, I still have to wait about 10 seconds with 6000 records.

In your standard search engine, everything works in the blink of an eye.

Dess | Tech Support Engineer, Principal
Telerik team
commented on 12 Sep 2022, 10:12 AM

Indeed, the search functionality is much faster because it is being executed on a separate thread compared to the filtering/custom filtering behavior executed on the main UI thread. However, the searching considers the formatted cell's value to highlight the matches. That is why it requires the correct culture and user's input as it was previously discussed. Feel free to choose this functionality that fits your requirements best. 

Joanna
Top achievements
Rank 1
Iron
commented on 12 Sep 2022, 02:02 PM

I understand your explanation, but my app users won't be so understanding. The average user wants to search the record automatically and won't wait for the results for 10 seconds. What tool should I use to find a decimal number like 2 500.00 by typing only 2500.00 (no one puts a space in the search). And I have to use your standard search function - it works much faster and is very user friendly. Did other users not inform you earlier about searching for this number - problem? I'm sure there are many other programs out there that have numeric columns, not just strings. I am sure such a professional company can solve this problem, thanks in advance.
Dess | Tech Support Engineer, Principal
Telerik team
commented on 13 Sep 2022, 12:47 PM

Hello, Joanna, 

Yes, no one is expected to type the formatted numeric value. However, the search functionality is designed to highlight the formatted text as it highlights the precise characters that match the search criteria.

The possible solution that I can suggest is to disable the highlighting and implement your own custom search logic. Then, the CellFormatting event will be used to highlight the cells that contain search matches:
        public RadForm1()
        {
            InitializeComponent();
          
            CultureInfo culture = new System.Globalization.CultureInfo("en-US");
            NumberFormatInfo ni = new NumberFormatInfo();
            ni.NumberGroupSeparator = " ";
            culture.NumberFormat = ni;
            System.Threading.Thread.CurrentThread.CurrentCulture = culture;

            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Price", typeof(decimal));

            dt.Rows.Add(1, "Test", 461892.65);
            dt.Rows.Add(1, "Test", 12);
            dt.Rows.Add(1, "Test", 5461.34);

            this.radGridView1.DataSource = dt;
            this.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;

            GridViewDecimalColumn decimalColumn = radGridView1.Columns["Price"] as GridViewDecimalColumn;
            decimalColumn.FormatInfo = culture;
            decimalColumn.FormatString = "{0:n2}";

            this.radGridView1.CellFormatting += RadGridView1_CellFormatting;
            this.radGridView1.AllowSearchRow = true;
            this.radGridView1.MasterTemplate.MasterViewInfo.TableSearchRow.HighlightResults = false;
            this.radGridView1.CurrentRowChanging += RadGridView1_CurrentRowChanging;

 
        } 

        private void RadGridView1_CurrentRowChanging(object sender, CurrentRowChangingEventArgs e)
        {
            if (e.NewRow is GridViewSearchRowInfo)
            {
                e.Cancel = true;
            }
        }

        private void RadGridView1_CellFormatting(object sender, CellFormattingEventArgs e)
        {
            if ( e.Row.SearchCache.Contains(e.Column))
            {
                e.CellElement.DrawFill = true;
                e.CellElement.GradientStyle = GradientStyles.Solid;
                e.CellElement.BackColor = Color.LightBlue;
            }
            else
            {
                e.CellElement.ResetValue(LightVisualElement.DrawFillProperty, ValueResetFlags.Local);
                e.CellElement.ResetValue(LightVisualElement.GradientStyleProperty, ValueResetFlags.Local);
                e.CellElement.ResetValue(LightVisualElement.BackColorProperty, ValueResetFlags.Local);
            }
        }

        private void RadGridView1_CreateRowInfo(object sender, GridViewCreateRowInfoEventArgs e)
        {
            if (e.RowInfo is GridViewSearchRowInfo)
            {
                 e.RowInfo = new CustomSearchRow(e.ViewInfo);
            }
        }

        public class CustomSearchRow : GridViewSearchRowInfo
        {
            public CustomSearchRow(GridViewInfo viewInfo) : base(viewInfo)
            {
               
            } 
            protected override void OnSearchProgressChanged(SearchProgressChangedEventArgs e)
            {
                base.OnSearchProgressChanged(e);
                if (e.SearchFinished)
                {
                    foreach (GridViewRowInfo row in this.ViewTemplate.Rows)
                    {
                        row.InvalidateRow();
                    }
                }
            } 

            protected override bool MatchesSearchCriteria(string searchCriteria, GridViewRowInfo row, GridViewColumn col)
            {  
                bool result= base.MatchesSearchCriteria(searchCriteria, row, col);
                string rawValue = row.Cells[col.Name].Value + "";
                if (rawValue.Contains(searchCriteria))
                { 
                    return true;
                }
                return result;
            } 
        }

The attached gif file illustrates the achieved result. I hope that it will fit your custom requirement.

Joanna
Top achievements
Rank 1
Iron
commented on 14 Sep 2022, 07:38 AM

Are you sure there's all code? I copied it to my sample and doesn't work, not searching decimals, when I write without 'space' as in your gif.

I attached a screen.

Tags
GridView Separator
Asked by
Joanna
Top achievements
Rank 1
Iron
Answers by
Dess | Tech Support Engineer, Principal
Telerik team
Joanna
Top achievements
Rank 1
Iron
Share this question
or