StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » BrowseDialog results sorting issue

BrowseDialog results sorting issue

Govinda Berrio — Fri, 05 Oct 2012 09:12:03 GMT

Hello,

I am having an issue with the sorting in the browse dialog search result grid.

Most of the time sorting in the browse dialog results is notan issue, but one of our customers put some data in there that seems to havecaused a problem.

As you can see from the screenshot I’ve attached, the“08-4882” and “01-9993” Product numbers are out of order. This is afterclicking the header to make sure that it is sorting. I have even tried using the Default Column Sort option in the Browse Results Column Properties.

This is a big problem for them since they need to search for substrings of the product number.

I have also attached a simple project where I have reproduced this problem. I have it pointed at the AdventureWorks database Production.Product table. But I changed the ProductNumber field to match those that I am finding cause a problem. You should be able to reproduce this issue by changing ProductNumber value for a group of products that you can load up easily in the BD. Here is the list for help:

GOVTEST AB123 GOVTEST1 11-2883 08-4882 01-9993 11-0021 12-1887 10-3774

Is there a fix for this?

Thank You!,

Govinda

RE: BrowseDialog results sorting issue

Ivan George Borges — Fri, 05 Oct 2012 09:12:03 GMT

By the way... sorry I forgot. You are welcome! ;)

RE: BrowseDialog results sorting issue

Ivan George Borges — Fri, 05 Oct 2012 09:11:30 GMT

Yep, strange.

But I have a feeling the sorter is thinking that that would be a date or something like that, showing Month-Year.
Well, I don't know... but as soon as I get some time over here I will investigate the source code to see if I find it. That is, after I get some sleep and after I get the chance to see if the sun is still outside... :D

RE: BrowseDialog results sorting issue

Govinda Berrio — Fri, 05 Oct 2012 08:57:14 GMT

Thanks Ivan,

That will do as a workaround.

Hopefully the SF guys will see this thread and fix this.

It is strange that others have not noticed this and posted something. This can't be the first time some has needed to sort numbers with hyphens in the BD search results.

Govinda

RE: BrowseDialog results sorting issue

Ivan George Borges — Thu, 04 Oct 2012 20:41:55 GMT

Hi Govinda.

I created a sample with the StrataframeSample database and was able to reproduce your issue.
Did you notice that for the codes that start with numbers, the sorter is only taking into cosideration the second part of your product code, the number after the dash (-) ?

So, noticing that, I came up with a quick and dirty solution that could get you going tomorrow morning.

On the BrowseDialogLayout, set your Column Population Type to PopulatedThroughEvent. Then, use the BD RowPopulating event to define your code as follows:

[codesnippet]    Private Sub BrowseDialog1_RowPopulating(e As MicroFour.StrataFrame.UI.Windows.Forms.RowPopulatingEventArgs) Handles BrowseDialog1.RowPopulating
        With CType(e.BusinessObject, Products)
            '-- set Product Code
            e.Values(0).DisplayValue = "(" & .prod_SKU & ")"
        End With
    End Sub
[/codesnippet]

That is it, put "(" and ")" around them all. This way, the sorte won't get confused and will sort them all as strings.

Hope it helps.

RE: BrowseDialog results sorting issue

Govinda Berrio — Thu, 04 Oct 2012 18:51:41 GMT

Hi Edhy,

I am expecting it to sort as a character string. When I run that query sorting by ProductNumber, which is NVARCHAR, I get the correct rusults (see screenshot). When I paste the list into Excel and sort, I get the correct results. For some reason, not in the browse dialog.

Unfortunately, I can't cast to integer because the ProductNumber values will have hypens. Most will look like this: "08-4882".

Thanks,

Govinda

RE: BrowseDialog results sorting issue

Edhy Rijo — Thu, 04 Oct 2012 17:29:55 GMT

Hi Govinda,

Have you try going to SQL Management Studio and do a "SELECT * FROM TableName ORDER BY ProductNo" and see if the order shows up the way you are expecting?

When you have character values which values may start with zeros "0", sorting may not always be the way you are expecting, some developers even create a calculated field in SQL with the value of of the ProductNo as integer for sorting purpose ex:
"SELECT *, CAST(ProductNo AS INTEGER) AS ProductNoToBeSorted FROM TableName ORDER BY CAST(ProductNo AS INTEGER)"