locked
How Do I Handle DB Calculated Fields In The UI ? RRS feed

  • Question

  • Can anyone offer some advice on this please...

    I am currently evaulating LightSwitch and have put together a simple customers / order headers / order lines test application.

    My data is currently being stored in an SQL database.

    The scenario is really easy in that I want to calculate the total price of each order line based on the quantity * the unit price. Of course in the DB the LineTotal field is a calculated one.

    Naturally when the user changes the line quantity or price, I would like to see the LineTotal update in the UI, however as the calculation is being done in the DB I have to wait until the data is saved before the LineTotal calculates.

    I can't seem to make the field calculated on the LS side, so what is the correct way of doing it ?

    Also, I can't seem to put any validation against these fields as the options are greyed-out in the entity designer property window. Again, what am I doing wrong ?

    Thanks,

    Nick

    Monday, June 27, 2011 11:35 AM

Answers

  • Hi Nick,

    You're not doing anything "wrong", there's just some things you can't do in LS, if you're using an external database.

    Because your calculation is being done in SQL Server, it's only natural that it can't occur until you do your "save". SQL Server just doesn't have the updated data to do the calculation. If you *need* the calculation to display in the client, before you save, you'll need to add a calculated property to your attached entity in LS's table designer, duplicate the calculation, & display that property instead of the server-calculated value. Yes, it's doubling up, but that's the only way I can think of that you're going to be able to display it *before* sending the updated values back to the server.

    Again, with the validation, you're not doing anything wrong, LS does not provide built-in validation on properties that come from an attached data source (I guess the assumption is that your validation is done in the database itself), which is why the options are greyed-out.

    There are a couple of ways of doing the validation in the screen (using INotifyPropertyChanged or LostFocus event handlers), but that's a bit of an advanced scenario, requiring you to code the event handlers yourself. I don't know how much programming experience you have, so it's difficult to know if this would be hard for you or not.

    So, in summary, what you're seeing is "normal" for LS, if you're using an attached data source. You *can* get around both situations. The first one is easy, the second one is a bit more work.


    Yann

    (plus ça change, plus c'est la même chose!)

    • Marked as answer by NickTaylor Monday, June 27, 2011 12:43 PM
    Monday, June 27, 2011 12:12 PM
  • Nick,

    The syntax above is a lambda expression. More details can be found on the MSDN help here.

    http://msdn.microsoft.com/en-us/library/bb397687.aspx

    William's code looks ok. Are you able to give any more details as to where the syntax fails for you?

    Tim

    • Marked as answer by NickTaylor Monday, June 27, 2011 4:48 PM
    Monday, June 27, 2011 4:17 PM

All replies

  • Hi Nick,

    You're not doing anything "wrong", there's just some things you can't do in LS, if you're using an external database.

    Because your calculation is being done in SQL Server, it's only natural that it can't occur until you do your "save". SQL Server just doesn't have the updated data to do the calculation. If you *need* the calculation to display in the client, before you save, you'll need to add a calculated property to your attached entity in LS's table designer, duplicate the calculation, & display that property instead of the server-calculated value. Yes, it's doubling up, but that's the only way I can think of that you're going to be able to display it *before* sending the updated values back to the server.

    Again, with the validation, you're not doing anything wrong, LS does not provide built-in validation on properties that come from an attached data source (I guess the assumption is that your validation is done in the database itself), which is why the options are greyed-out.

    There are a couple of ways of doing the validation in the screen (using INotifyPropertyChanged or LostFocus event handlers), but that's a bit of an advanced scenario, requiring you to code the event handlers yourself. I don't know how much programming experience you have, so it's difficult to know if this would be hard for you or not.

    So, in summary, what you're seeing is "normal" for LS, if you're using an attached data source. You *can* get around both situations. The first one is easy, the second one is a bit more work.


    Yann

    (plus ça change, plus c'est la même chose!)

    • Marked as answer by NickTaylor Monday, June 27, 2011 12:43 PM
    Monday, June 27, 2011 12:12 PM
  • Thanks again Yann,

    That makes sense, and I didn't realise that you can add your own properties to those already specified by the DB. I should be able to code the event handlers, but I can live without them for this exercise.

    At risk of taking this thread off on another tanget, I am looking at LS to speed up development time on an in-house business application. Currently the code is all WinForms, CSLA business objects, stored procedures and SQL. All in all this is quite a bit of work. LS does away ( sorry abstracts ) all this clutter and allows me to write stuff very quickly and easily. This we like :o)

    The question therefore is why wouldnt I want to write most of my business app in LS ?

    Obviously its a bit of a shift in skill base, but it would seem the right way to go ?

    Thanks,

    Nick

    Monday, June 27, 2011 12:43 PM
  • In your Orders table, Sum the detail lines. The calcs work in real time, so you don't need to save record first to see them change. The computed properties are object Properties, not db related. So they work on client and server side when ever you have an entity object.

        partial void OrderSubtotal_Compute(ref decimal result)
        {
          // Sum of the line item Subtotals.
          if (this.OrderDetail != null)
            result = this.OrderDetail.Sum(od => od.LineTotal);
        }

        partial void GrandTotal_Compute(ref decimal result)
        {
          // Total of order including tax and SH.
          result = this.OrderSubtotal + this.SalesTaxTotal + this.Shipping;
        }

    To validate a computed field in order parent when updating line items, a simple way is to validate a field in line items that triggers update of parent calculated results.  Qty updates subtotal, so I could validate qty and price fields and lookup at parent.  You could also change some field (such as LastchangedDate) in Orders to trigger a validation on save.

        partial void Quantity_Validate(EntityValidationResultsBuilder results)
        {
          if (this.Order.OrderSubtotal > 1)
            results.AddPropertyError("Order subtotal greater than 1");
        }

    Monday, June 27, 2011 12:51 PM
  • Hi William,

    Thanks for this. I am obviously somewhat slow off the mark today as I am having trouble with the syntax in your first code snippet.

    Would you be kind enough to clarify the syntax "(od => od.LineTotal)"

    Obviously the Sum function iterates the child order detail collection and totals the line total, however I'm not able to get the syntax right currently.

    Thanks,

    Nick

    Monday, June 27, 2011 3:45 PM
  • Nick,

    The syntax above is a lambda expression. More details can be found on the MSDN help here.

    http://msdn.microsoft.com/en-us/library/bb397687.aspx

    William's code looks ok. Are you able to give any more details as to where the syntax fails for you?

    Tim

    • Marked as answer by NickTaylor Monday, June 27, 2011 4:48 PM
    Monday, June 27, 2011 4:17 PM
  • Hi Tim,

    I havent used lambda expressions before, and hence didn't understand the syntax :o(

    I've gone through it again and it appears ok now. It would appear I have some more reading to do!

    On a separate note, would you happen to know why I can't seem display grid based data in a modal window picker.

    I want to show a list of part numbers together with descriptions etc. and all I seem to be able to do is organise them using controls such as the row layout.

    What is the correct way to display tables of information in a modal window picker or am I barking up the wrong tree ( again ) ?!

    Many thanks,

    Nick

    Monday, June 27, 2011 4:47 PM
  • I gave up on modal window picker until they fix a couple bugs with it.  I would use auto complete box for now if possible.
    Tuesday, June 28, 2011 3:40 AM
  • Hi William,

    So what is the correct way of handling a selection form where I have large quantities of data to choose from ?

    Can I use a modal window instead ? I'm pretty sure I've seen a thread somewhere on here that allows for parameterised queries etc. Can that be used to return a value to a calling form ?

    I have thousands of parts in my part database, and therefore need to provide a user friendly way of selecting them.

    Many thanks,

    Nick

    Tuesday, June 28, 2011 6:10 AM
  • Hi Nick,

    >> On a separate note, would you happen to know why I can't seem display grid based data in a modal window picker

    This behaviour is likely due to the relationships that you have set up. Imagine 2 tables for storing customers and orders with a one to many relationship between the 2.

    On a Customer a screen, you can create a grid to show the orders due to the nature of the relationship. However, you can't change this grid to a modal window picker.

    If you create an Order screen, a modal window picker could be used to select the customer because the screen is based on the other side of the relationship. 

    You may want to take a look at Andy Kung's ListBox Mover UI. Although the underlying data structure may be different to what you have, it might give you some inspiration as to the type of UI that's possible!

    http://blogs.msdn.com/b/lightswitch/archive/2010/12/16/how-to-create-a-many-to-many-relationship-andy-kung.aspx

    Tim

    Tuesday, June 28, 2011 10:53 AM