StrataFrame Forum

BrowseDialog results sorting issue

http://forum.strataframe.net/Topic31480.aspx

By Govinda Berrio - 10/4/2012

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
By Edhy Rijo - 10/4/2012

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)"
By Govinda Berrio - 10/4/2012

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
By Ivan George Borges - 10/4/2012

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:

    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


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.
By Govinda Berrio - 10/5/2012

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
By Ivan George Borges - 10/5/2012

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... BigGrin
By Ivan George Borges - 10/5/2012

By the way... sorry I forgot. You are welcome! Wink