locked
Query based on datagrid selected item. Reset it? RRS feed

  • Question

  • I have a search query on my input form that search's the db based on the user input in the datagrid above. The parameter for the query is setup like so:

    JobDetails.SelectedItem.DrawingNumber - JobDetails being the input datagrid.

    This on the face of it works fine, when the user goes to that field and types in a drawing number, the search query automatically goes to the database and shows records that match. However, I've noticed a few anomalies. 

    1. When the db should return no records, there is a row in the search datagrid that mirror's what the user typed in. Obviously there is no data for the record, but why does it show what the user typed there? See image...

    Screen shot

    2. When you move to a new row in the input datagrid, and type in a new drawing number, all drawing numbers are still in the search query datagrid, not just the selected item as per the parameter set! See screenshot:

    screenshot

    I must have something set wrong somewhere but can't seem to figure out where.

    Any help would be appreciated.

    Thanks,

    Paul.


    • Edited by Paul Pitchford Friday, December 14, 2012 9:32 AM updated description
    Friday, December 14, 2012 9:31 AM

Answers

  • Hmm - could you create an identical SQL view and bring that into the project?  Then you could add that to the screen and not have this issue since the data won't be reflected in that view until the records are saved?
    • Marked as answer by Paul Pitchford Wednesday, January 23, 2013 9:46 PM
    Monday, January 21, 2013 11:46 PM
  • Hi Paul,

    Sorry for the delay. I spoke with the devs on this and unfortunately there isn't an easy way to support what you want to do if the grids are on the same screen.

    Here's what's going on. The filtering that determines the result of a query is happening on the server side. When you add a new item on the client side, the server does not know anything about it before you save. So the client has to decide whether to include newly created items in any query results, and if so, what query results, or other screen collections, should include the newly created item. So we decided it would be best to display the new rows. This is a good default, because in most simple cases you want them to be visible (e.g. single grid screen where you edit/delete/add stuff). Things get a bit out of whack if you have multiple screen collections with the same entity type—they all get the newly created item as you observed.

    So you have a couple options. One is to create a separate screen for adding data, and once the newly created item is added and saved, refresh the “master” screen to make the new data appear there. Another option would be to create database views, bring those into your data model, and use those since they would look like separate entities to LightSwitch.

    This whole scenario is one of the reasons why the HTML client is much more prescriptive about screens for adding/editing data vs. screens for browsing data and why it has the concept of a save boundary. In the Silverlight client the save boundary is the entire screen.

    I hope this clears things up.
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Wednesday, January 23, 2013 9:00 PM
  • Hi Paul,

    I think the better way to go about it is to start with a Search Screen and then you can add commands to the results grid for Adding/Editing/Deleting data. Just keep in mind that LightSwitch will not blindly discard pending changes on the screen when a user re-executes the query.

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Monday, December 17, 2012 11:32 PM

All replies

  • Hi Paul,

    How did you define the parameters on the query? Did you customize the search screen with your own code? I suspect the first issue is because the user hasn't saved the row in the grid before executing the search again. LightSwitch will not discard pending changes in this case. You can change this behavior by making the grid read-only or explicitly calling a Refresh (and thereby discarding pending changes)

    HTH,

    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Saturday, December 15, 2012 1:59 AM
  • Hi Beth,

    Thanks for taking the time to look at my post!

    Here is my query. Note, there is no code behind. It's box standard as you see:

    I can only add two images so will type what is in the query:

    DrawingNumberHistoryByIDDesc:

    Filter: Where DrawingNumber = DrawingNumber

    Sort: Id Descending

    Parameters: DrawingNumber of type String

    This is a "create" screen, not a search screen but the query has "Show Add-new Row" unchecked:

    Screen shot 2

    Finally, the datagrid for the row's the query produces has "Use Read-only Controls" checked:

    Screen shot 3

    Does this make any sense to you?

    Thanks,

    Paul.

    Saturday, December 15, 2012 11:15 AM
  • Hi Paul,

    I think that explains it. Because you're using a create screen that's causing #1 above. #2 is by design. You should be using a Search Screen template for this.

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Monday, December 17, 2012 5:45 PM
  • I think that explains it. Because you're using a create screen that's causing #1 above. #2 is by design. You should be using a Search Screen template for this.

    Hi Beth,

    That's the point though. This is an input screen but my client is wanting to see drawing number history on the same screen without having to switch screens for the information. It sort of has to be an input screen really. I guess I'll have to figure another way of doing things.

    As it stands, I purchased Derek Spurlock's Contect Menu extension (it's is great btw) which allows them to right click the row and open the search in a proper search screen but I was hoping to find a feasible way of getting the data on the same screen.

    Thanks,

    Paul

    Monday, December 17, 2012 6:10 PM
  • Hi Paul,

    I think the better way to go about it is to start with a Search Screen and then you can add commands to the results grid for Adding/Editing/Deleting data. Just keep in mind that LightSwitch will not blindly discard pending changes on the screen when a user re-executes the query.

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Monday, December 17, 2012 11:32 PM
  • Hi Beth,

    I've been having a fresh look at this today and tried to follow your recommendations. This is what I did:

    1. Added a search screen and assigned the query I ultimately want to add to my input screen.
    2. Added a local property for the job I'm creating.
    3. Converted that property to a row layout.
    4. Added the job details from the job property by pressing "Add Job Details" from within the job property.
    5. Hooked the parameter of the search query to the selected item of the details grid.
    6. In the InitializeDataWorkspace method for the screen added Me.JobProperty = New Job().
    7. Ran the application.

    Unfortunately we're getting the same as before. I've checked that the datagrid containing the query has the "Show Add-new Row" check-box un-ticked. Also as before, if you have more than 1 row in the job details grid, the search grid shows all items from the details grid when searching, along with the search results for the selected item. It does not show search results for all items in the details grid though, only the selected item as is really expected.

    You can see from the screen shot that the query data grid shows them as new rows despite having the show new row un-ticked.

    Can you help any further?

    Thanks Beth,

    Paul.Screen shot

    Tuesday, January 15, 2013 9:49 AM
  • Hi Paul,

    The last picture really helps. Correct me if I'm wrong but both of the grids are based on the same underlying entity, correct? If this is the case, then that's why you are seeing the updates to the data -- because the underlying entity collection is the same.

    I don't think there is a way around this. Just thinking off the top of my head, you may try digging into the DataGrid (using the FindControl method) and then trying to set the binding manually with a binding mode of "OneTime" to see if that works.

    Sorry for my confusion!

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Wednesday, January 16, 2013 11:05 PM
  • Can't the bottom grid simply have its query changed to where it ignores any item with a Job ID of "0"?
    Sunday, January 20, 2013 6:16 PM
  • Can't the bottom grid simply have its query changed to where it ignores any item with a Job ID of "0"?
    Thanks for the thought but that didn't work. I guess it's something to do with the way it's bound as per Beth's post above. Thanks for taking the time to read though!
    Monday, January 21, 2013 9:40 AM
  • Hi Paul,

    The last picture really helps. Correct me if I'm wrong but both of the grids are based on the same underlying entity, correct? If this is the case, then that's why you are seeing the updates to the data -- because the underlying entity collection is the same.

    I don't think there is a way around this. Just thinking off the top of my head, you may try digging into the DataGrid (using the FindControl method) and then trying to set the binding manually with a binding mode of "OneTime" to see if that works.

    Sorry for my confusion!

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Beth,

    I'm aware of the FindControl method but how would I go about setting the binding mode to OneTime? I've searched the internet and all the examples appear to be custom controls and this can be set in XAML and not in code. Or is the custom control the way to go?

    Thanks,

    Paul.

    Monday, January 21, 2013 9:42 AM
  • Hmm - could you create an identical SQL view and bring that into the project?  Then you could add that to the screen and not have this issue since the data won't be reflected in that view until the records are saved?
    • Marked as answer by Paul Pitchford Wednesday, January 23, 2013 9:46 PM
    Monday, January 21, 2013 11:46 PM
  • Hi Paul,

    Sorry for the delay. I spoke with the devs on this and unfortunately there isn't an easy way to support what you want to do if the grids are on the same screen.

    Here's what's going on. The filtering that determines the result of a query is happening on the server side. When you add a new item on the client side, the server does not know anything about it before you save. So the client has to decide whether to include newly created items in any query results, and if so, what query results, or other screen collections, should include the newly created item. So we decided it would be best to display the new rows. This is a good default, because in most simple cases you want them to be visible (e.g. single grid screen where you edit/delete/add stuff). Things get a bit out of whack if you have multiple screen collections with the same entity type—they all get the newly created item as you observed.

    So you have a couple options. One is to create a separate screen for adding data, and once the newly created item is added and saved, refresh the “master” screen to make the new data appear there. Another option would be to create database views, bring those into your data model, and use those since they would look like separate entities to LightSwitch.

    This whole scenario is one of the reasons why the HTML client is much more prescriptive about screens for adding/editing data vs. screens for browsing data and why it has the concept of a save boundary. In the Silverlight client the save boundary is the entire screen.

    I hope this clears things up.
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Wednesday, January 23, 2013 9:00 PM
  • @ExcerciseIce

    Thank you for your alternative way of looking at things. Setting up a SQL view and creating a parameterized query on that view gives me what I need. I'll have to deploy it into the live environment to see how it performs but from testing it looks usable.

    @Beth

    Thanks for getting back to me. Please don't worry about any time period in getting back to me. The fact you (and others) have gotten so involved in my query is excellent, thank you.

    Your post makes perfect sense and as ExcerciseIce above has pointed out, you can create a view to do what I need. I would have replied to him/her sooner but I only got around to the work this evening.

    By the way, it's just dawned on me that I haven't actually explained why I want to achieve this "split screen" view for an input screen... My client provides a metal finishing service to their customers so as such do not use part numbers themselves.

    However, their customers order under their own part number or reference which my client keeps record of, 1. So that they know what process to use, and 2. So that they can recall historical pricing information. The split screen works well as they can now enter the customers part number or reference in the detail entry datagrid and it instantly, at a glance, show's them all the information they require.

    Thanks for you both for your help, I wish you knew the pressure from the client to implement this! :)

    Kind Regards,

    Paul.


    • Edited by Paul Pitchford Wednesday, January 23, 2013 10:15 PM Added information
    Wednesday, January 23, 2013 10:00 PM