none
ROW NUMBERING in Query DataGrid

    Question

  • Hello again!

    application has query which would be nice to have row numbering, but for screen presentation only,

    i.m. after displaying query with specific filter, these row numbers are of no importance,

    they are just needed to provide easier way to discuss and position rows...

    something like this

    it would be nice to have "1.", "2.", "3." ....  on the left side of the DataGrid...

    is there a way of making something like this without too much complicating?

    THANKS AGAIN!

    Tuesday, April 10, 2012 9:00 PM

Answers

  • Hi Miroslav,

    I have been toying with this problem for a bit.  I followed this suggestion which sort of displayed the row numbers, although there is some delay in the displaying of the numbers.  I believe any new rows will not have any row numbers, unless you specifically place them into the datagrid.

    I created a dummy screen data item "RowNumber" of type String and made it part of a data grid item.  Then I changed its "Label position" property to "Left-aligned".  Finally, I placed the following code in the data grid's collection Loaded event.

    Private Sub Customers_Loaded(succeeded As Boolean)
       Dim idx As Integer = (Customers.Details.PageNumber - 1) * Customers.Details.PageSize
       For Each cust As Customer In Customers
          idx += 1
          FindControlInCollection("RowNumber", cust).DisplayName = idx.ToString + "."
       Next
    End Sub

    Hope this works for you.  I think I'll be leaving it as it is unless someone else has a simpler idea.  

    Initially I contemplated using RIA but did not want to go there, fearing the need to change the data grids in the application to accommodate for row numbering.

    Best regards,

    CT


    • Marked as answer by Miroslav Marinković Tuesday, May 22, 2012 9:30 AM
    • Edited by cte00 Tuesday, May 22, 2012 10:43 AM Included tip by Miroslav
    Tuesday, May 22, 2012 7:59 AM

All replies

  • The only way I can think of doing this is either by using a custom grid control, or changinging the dataset in code and adding the row numbers there after you have pulled the data from the data layer.
    Wednesday, April 11, 2012 1:25 AM
  • Glenn,

    Thanx for input!    Yes, this one looks simple before actually trying to figure out how to solve it...

    btw, have You seen such custom grid control, does it exist?

    that would be the best way, much better than generating bunch of numbers to be discarded and having column for them...

    Wednesday, April 11, 2012 9:58 AM
  • Hello again!

    i wonder if anyone has some good idea on this topic?

    still havent figured out how to deal with this...

    Tuesday, April 24, 2012 6:48 AM
  • Hi

    Is the solution based on WCF RIA services or is the datasource directly  linked to the database?

    Is the Grid editable?

    Asked this, because I was contemplating a view as the basis of your entity, like in this example (based onAdventureWorks)


    SELECT ROW_NUMBER() OVER(ORDER BY LastName) AS RowNbr,
        LastName, FirstName
    FROM [Person].[Contact]

    Jan D'Hondt - Database and .NET development

    Tuesday, April 24, 2012 12:04 PM
  • Thanx for Your input.

    it's directly linked, database is created within LS.

    Grid is not editable, it is actually query that returns results differently based on parameters

    (it's Bill Of Materials for sewing shirts, based on shirt color and size - it will give out a bit different values...)

    ughh   i am stuck with this, and it would mean a lot to client, but i do not have any ideas...

    Tuesday, April 24, 2012 1:34 PM
  • Is it allowed to have a view as the base of the entity upon which you create the query. Do the rownumbers have to be consecutive? Because if your query filters or sorts in a different manner, the ROW_NUMBER will not always be consecutive. But at least you can offer your client a quick and cheap solution.


    Jan D'Hondt - Database and .NET development

    Tuesday, April 24, 2012 2:13 PM
  • Jan,

    yes - they need to ne consecutive - and they are just for illustration, for easier going through the list...

    EXAMPLE - two users could talk over the phone and one would say "what about position 23.?", for example...

    it sure sounds easy, but i havent got a clue!  :)

    Tuesday, April 24, 2012 2:40 PM
  • Miroslav,

    Add a Computed Property and try something along the lines:

        partial void LineNumber_Compute(ref int result)
        {
          if (this.Order == null) return;
          result = Order.OrderDetail.OrderBy(od => od.Id).Where(od => od.Id <= this.Id).Count();
        }

    If it works for you as it does for me, thank William Stacey.

    Thanks, Keith


    E tenebris lux. ±

    The return in a void is a bit of a dumbo! I'm sure you can correct it accordingly!

      if (this.Order != null)
          result = Order.OrderDetail.OrderBy(od => od.Id).Where(od => od.Id <= this.Id).Count();

    • Edited by PlusOrMinus Tuesday, April 24, 2012 4:02 PM
    Tuesday, April 24, 2012 3:22 PM
  • Thanx, i will try this and be back :)

    Tuesday, April 24, 2012 3:29 PM
  • I have to admit I do not understand the LineNumber computed property example you give.

    What entity is 'this'? Is it the OrderDetail? Does the entity need to be on the many side of a one to many relationship? if so, what about entities at the one side?

    for instance I tried to make this example work for the AdventureWorks Products table. But I connot see how. Could you elaborate on this or show us a link to another example.


    Jan D'Hondt - Database and .NET development

    Wednesday, April 25, 2012 7:23 AM
  • Jan,

    same here...

    to illustrate this more, here is how the table actually looks (Query).

    this is for APPAREL INDUSTRY - dresses, and so on...   on the left hand side of the query - there are COLOR list and SIZES list.

    basically - when you choose COLOR of dress - and SIZE of dress, it will show what materials are needed.

    so - output get RECALCULATED very, very, often....   and this is the problem...

    i would just need in QUERY, before the pictures, row numbers, like 1-2-3-4-5-6, as it is a bit confusing without them to users...

    for them - SIMPLE REQUEST...   for me...  NIGHTMARE :)   i.m., it is hard to explain to them what a problem it is, as You might imagine :)

    ugh...   i am open to all sugestions!  :)

    THANKS GUYS, You are such a great support!

    Wednesday, April 25, 2012 11:57 AM
  • I believe that William created the above example to be used in conjunction with example database and the Master-Details screen that evolves during the original Beth Massi training videos and you should have a suitable OrderDetails entity by completion of:

    http://msdn.microsoft.com/en-us/lightswitch/ff961914

    I've adopted this approach for my apps. What I hope you get from it, is the idea (sorting the OrderDetails Id, comparing it with actual "row" Id and counting how many items there are as a result of the "where" statement)  of how it works and can apply it to your own individual apps. Other things you should be aware of is the behavior within a multi-page query and the possibility that you might want to hard save these values .....

    If this is not the case, let me know, and I'll look at posting an example project. Time constraints / busy bee at the mo, though! 

    Thanks, Keith


    E tenebris lux. ±

    Wednesday, April 25, 2012 1:44 PM
  • Hello to everyone who's following this thread!

    i might be having easy fix for this, i am just not able to get it to work, maybe my logic is a bit off.

    What do you think about adding column "Row_Counter" to the TABLE, not shown by default.

    and than and QUERY PREPROCESS, when Query has been generated do something like this:

                Dim Counter As Integer = 1
    
                For Each CompTemp In query
                    CompTemp.Row_Counter = Counter
                    Counter = Counter + 1
                Next

    this would be really easy fix, i imagine? but it's not working for me, at PreProcess level, any ideas why?

    Thanx!

    Wednesday, April 25, 2012 6:05 PM
  • it's proved to be easy at screen level, on LOADED method for Query, but than i got "*" all over... for changed

    is it possible:

    1. either to do this at QUERY PREPROCESS level

    2. or at least to somehow SAVE changes for this table only? (not to make general SAVE, as user maybe was into doing something else?

    Thanx again!

    Wednesday, April 25, 2012 6:14 PM
  • real question is:

    is it possible to change data at QUERY PreProcess time?

    as it seems that this For Each / Next loop will easily add numbers at screen level,

    but not when used on query as "For Each CompTemp In query", is my logic off?

    Thanx!

    Wednesday, April 25, 2012 6:28 PM
  • If someone who has walked through Beth Massi's training videos could kindly add the above Computed Property and code and present the resultant screen. That would be nice!

    This idea was floated about a year ago and it would take me a while to hackdown my existing apps to share with the world!

    TIA, Keith


    E tenebris lux. ±

    Wednesday, April 25, 2012 6:44 PM
  • Keith, Thanx for your post.

    i will be looking at it ASAP, for some reason i wasnt able to start the video  for last couple of hours, not sure why...

    Wednesday, April 25, 2012 7:04 PM
  • Keith,

    i have reviewed this, but this looks like it will only work with ABSOLUTE values, i.m, not quite flexibility this numbering needs,

    i.m. it will number orders as they exist is TABLE.

    as in my case, they're for QUERY results, and this will fluctuate each time user changed Product/ProductColor/ProductSize,

    so these numbers are not matching exact number of ROWS IN TABLE, we need it to work in QUERY results.

    ------------

    it should work with something like

                Dim Counter As Integer = 1

               
    For Each CompTemp In query
                    CompTemp
    .Row_Counter = Counter
                    Counter
    = Counter + 1
               
    Next

    at QUERY PREPROCESS, but i am not sure how, just yet :)

    Thanks again!

    Wednesday, April 25, 2012 8:01 PM
  • Miroslav,

    I think you will find it derives it "soft" values from the Visual/Entity Collection after the query. The reason I pointed you in this area was that we are counting and indirectly numbering after the query/filter. In this case all the Order Details that relate to this Order and not line numbering all the Order Details in the database. I use it to "number" my line items in all of my Order/Invoice/CreditNote etc type documents and it works well, for me. Sorry, to hear it is of no use to your particular problem.

    Yes, I also enjoyed the frustration you are having with what appears to be a simple task.

    I do think it is an area where we should produce some guidelines and "How Do You"'s for all to criticise, consume, improve, adapt.

    I'm keen to see how you get on, Keith


    E tenebris lux. ±

    Thursday, April 26, 2012 8:38 AM
  • Thanx Keith!  :-)

    i'll keep you posted!  :)

    Thursday, April 26, 2012 9:40 AM
  • Miroslav,

    If this boils down to

    1. a Master - detail relation where detail records have to be renumbered;

    2. detail data can be from a sql server view

    Then I would use the sql server RANK() function

    e.g. In AdventureWorks

    SELECT a.Name AS SubCategoryName, b.Name As ProductName
    , RANK() OVER (PARTITION BY b.ProductSubcategoryID ORDER BY b.ProductID) AS ranknbr
    FROM Production.ProductSubcategory AS a
    INNER JOIN Production.Product AS b
     ON a.ProductSubcategoryID = b.ProductSubcategoryID

    This will return a recordset like this, where the 3rd column contains the detail sequence.

    Mountain Bikes Mountain-100 Silver, 38 1
    Mountain Bikes Mountain-100 Silver, 42 2
    Mountain Bikes Mountain-100 Silver, 44 3
    Mountain Bikes Mountain-100 Silver, 48 4
    Mountain Bikes Mountain-100 Black, 38 5
    ...
    Road Bikes Road-150 Red, 62 1
    Road Bikes Road-150 Red, 44 2
    Road Bikes Road-150 Red, 48 3
    Road Bikes Road-150 Red, 52 4
    Road Bikes Road-150 Red, 56 5
    Road Bikes Road-450 Red, 58 6
    ...
    Touring Bikes Touring-2000 Blue, 60 1
    Touring Bikes Touring-1000 Yellow, 46 2
    Touring Bikes Touring-1000 Yellow, 50 3
    Touring Bikes Touring-1000 Yellow, 54 4


    Jan D'Hondt - Database and .NET development

    Thursday, April 26, 2012 3:16 PM
  • Hi Miroslav,

    Have you gotten anywhere with this?  I too am facing the same problem where a seemingly simple request by the client and I find there is no simple way to resolve.  

    Jandho: The method you suggested is what I would have done if I were to generate the numbering for my reports in SSRS.  However, how can this be done for LS?  Can this be done with WCF RIA service?  How can it take into account the page number of the datagrid (i.e. can I programmically obtain the datagrid's page number and the number of Items displayed per page)?

    Best regards,

    CT

    PS: I have created the following post for future LS feature suggestions.  Do give it your votes if you would like to see this feature in the hopefully near future:- http://visualstudio.uservoice.com/forums/127959-visual-studio-lightswitch/suggestions/2866973-include-an-option-to-display-rownumber-in-datagrid

    Monday, May 21, 2012 9:25 AM
  • C.T.,

    nope... :-)     not yet

    i.m. the closest thing i could think of was making a separate column (not shown on default),

    and than on loaded method to do the numbering with For Each // Next, but than You also have to do the Save as with every change "*" will show for record change...

    anyway, i abandoned this function, for more pressing things, but it will have to be taken care of in next weeks...

    Monday, May 21, 2012 9:45 AM
  • CT,

    i gave 3 votes for your suggestion!

    it would be great to have this within DataGrid and just to turn it on/off, whenever it is needed.

    Monday, May 21, 2012 9:48 AM
  • Hi Miroslav,

    I have been toying with this problem for a bit.  I followed this suggestion which sort of displayed the row numbers, although there is some delay in the displaying of the numbers.  I believe any new rows will not have any row numbers, unless you specifically place them into the datagrid.

    I created a dummy screen data item "RowNumber" of type String and made it part of a data grid item.  Then I changed its "Label position" property to "Left-aligned".  Finally, I placed the following code in the data grid's collection Loaded event.

    Private Sub Customers_Loaded(succeeded As Boolean)
       Dim idx As Integer = (Customers.Details.PageNumber - 1) * Customers.Details.PageSize
       For Each cust As Customer In Customers
          idx += 1
          FindControlInCollection("RowNumber", cust).DisplayName = idx.ToString + "."
       Next
    End Sub

    Hope this works for you.  I think I'll be leaving it as it is unless someone else has a simpler idea.  

    Initially I contemplated using RIA but did not want to go there, fearing the need to change the data grids in the application to accommodate for row numbering.

    Best regards,

    CT


    • Marked as answer by Miroslav Marinković Tuesday, May 22, 2012 9:30 AM
    • Edited by cte00 Tuesday, May 22, 2012 10:43 AM Included tip by Miroslav
    Tuesday, May 22, 2012 7:59 AM
  • CT,

    this looks interesting, i will try this.

    since query has multiple parameters - numbering can be done only after presenting on screen and my problem with creating "*" for data change looks like very elegantly solved...

    i will be back here soon to "Mark As Answer" :)

    Tuesday, May 22, 2012 8:57 AM
  • Hey CT,

    i just implemented this, wokrs quite nice! :-)

    Thanx to You and Kivito!

    i (believe i) have made a minor cosmetic improvement!

    As You know, labels always have ":" at the end, so now it is "1:  2:  3:" and so on...

    but LightSwitch behaves great when label has "." at the end, as then it ommits ":"...   so, i have made chnage here:

    FindControlInCollection("RowNumber", cust).DisplayName = idx.ToString+"."

    i just added dot "." at the end..     it looks better to me like   1.  2.  3.  4.    than     1:  2:  3:  4:

    and a question:

    do you know how this can be arranged to go RIGHT ALIGNED, as for numbering this is better?

    normal setting obviously does not work, as it is overriden?

    THANKS!

    Tuesday, May 22, 2012 9:37 AM
  • No probs, and thanks for the "." tip.  Interesting to see that LS does not display a ":" when the labels end with a non-alphanumeric character. 

    Afraid I don't know how to "right-align" the labels on this one...

    Tuesday, May 22, 2012 10:21 AM