locked
HTMLClient: Sum of products in child records RRS feed

  • Question

  • Hi All,

    Scene:

    I have a table called Jobs with a field called JobTotal. I have another table called Products which has fields - Description and Price. I have a third table to enable a multiple/multiple relationship called JobProductMappings with multiple:1 links to Products and multiple:0/1 to Jobs.

    Problem:

    I would like to display the total cost of the job of the products linked to the job. I've been having a few issues working this out so if anyone has any suggestions they would be welcomed :)

    Cheers,

    Adam



    Monday, March 9, 2015 7:26 PM

Answers

  • Hi,

    In LightSwitch, we can create a computed column and use the following code for the result:

    partial void OrderTotal_Compute(ref decimal? result)
    
            {
    
                decimal dOrderTotal = 0.0M;
    
                // Get OrderDetails that have products
    
                var colOrderDetails = from OrderDetails in this.FlowerShopOrderDetail
                                      where OrderDetails.FlowerShopProduct != null
                                      select OrderDetails;
    
    
                foreach (var order in colOrderDetails)
                {
                    dOrderTotal = dOrderTotal + (order.Quantity * order.FlowerShopProduct.Price);
                }
                result = dOrderTotal;
            }

    Besides,below resources help you:

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/185/Computed-Properties-With-the-LightSwitch-HTML-Client.aspx

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/43ee236a-dab8-4cd1-adb5-196db2b2f9d3/linq-sum-in-computed-property?forum=lightswitch

    Best Regards,

    qiminding
    Tuesday, March 10, 2015 3:00 AM
  • Computed properties in the LS HTML client have been discussed many times here on the forums, and there are several approaches.  The previous post mentioned probably the most helpful treatment on the subject found at Michael's website (although the code that is listed is not useful for the HTML client and can be ignored):

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/185/Computed-Properties-With-the-LightSwitch-HTML-Client.aspx

    HTML client computed properties that involve data across relationships and tables, such as yours, are typically more involved to code. Michael has shown how to manage the difficulties using WCF RIA services.  Using the Flower Shop project as a base, I'll point out a third way to do the same thing as a RIA class, or at least the essential part, namely retrieving an anonymous type as a projection at the server, using JayData.

    First, starting the the ComputedValuesHTMLClient2013 project, NuGet package manager is used to add JayData to the project.

    Next, using the included JaySvcUtil.exe program, the entity data model file is auto-generated. Both JayData.js and the generated model JS file are added to the default.htm script list.

    In the Main Screen, a global variable to hold the FlowerShop database is created and the JayData connection to the LightSwitch database is initialized:

    var FlowerShopDb = window.FlowerShopDb || {};
    
    myapp.Main.created = function(screen) {
        // Write code here.
    
        FlowerShopDb = new LightSwitchApplication.ApplicationData({
            name: 'oData',
            oDataServiceHost: '/ApplicationData.svc'
        });
    
        $.when(FlowerShopDb.onReady())
            .then(function() {
                console.log(FlowerShopDb);
            });
    };

    On the BrowseFlowerShopOrders Screen, a custom control named "Total Order Price" will be added to the Flower Shop Orders list, right above Order Date.  In the _render method, there is:

    myapp.BrowseFlowerShopOrders.TotalOrderPrice_render = function (element, contentItem) {
        // Write code here.
        element.innerHTML = '<div id="TotalOrderPriceDiv"></div>';
    };

    Now for JayData to make short shrift of this. In the Flower Shop Order (item) Columns Layout--the row template control, the following will go in the _postRender method:

    myapp.BrowseFlowerShopOrders.RowTemplate_postRender = function(element, contentItem) {
        // Write code here.
        FlowerShopDb.FlowerShopOrderDetails
            .filter("it.OrderDetail_Order === this.OrderId", { OrderId: contentItem.data.Id })
            .map(function(order) {
                return {
                    ProductPrice: order.FlowerShopProduct.Price,
                    Quantity: order.Quantity
                }
            })
            .toArray(function(orders) {
                var orderTotal = 0;
                orders.forEach(function(o) {
                    orderTotal += o.ProductPrice * o.Quantity;
                });
            $('#TotalOrderPriceDiv', element).text('$' + orderTotal);
        });
    
    };
    Briefly, the Flower Shop Order Id is used as the filter to pull up the corresponding FlowerShopOrderDetail (via EF's OrderDetail_Order navigation property). The anonymous projection pulls up the corresponding price and quantity of the Flower Order details (again across navigation properties at the server), and stores it in an array orders. Next, it iterates through the orders array, multiplying price and quantity and aggregating to a variable orderTotal.  Finally, once the server has returned all the values and calculations are complete, the result is displayed in the Total Order Price custom control field.




    Tuesday, March 17, 2015 5:46 AM

All replies

  • Hi,

    In LightSwitch, we can create a computed column and use the following code for the result:

    partial void OrderTotal_Compute(ref decimal? result)
    
            {
    
                decimal dOrderTotal = 0.0M;
    
                // Get OrderDetails that have products
    
                var colOrderDetails = from OrderDetails in this.FlowerShopOrderDetail
                                      where OrderDetails.FlowerShopProduct != null
                                      select OrderDetails;
    
    
                foreach (var order in colOrderDetails)
                {
                    dOrderTotal = dOrderTotal + (order.Quantity * order.FlowerShopProduct.Price);
                }
                result = dOrderTotal;
            }

    Besides,below resources help you:

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/185/Computed-Properties-With-the-LightSwitch-HTML-Client.aspx

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/43ee236a-dab8-4cd1-adb5-196db2b2f9d3/linq-sum-in-computed-property?forum=lightswitch

    Best Regards,

    qiminding
    Tuesday, March 10, 2015 3:00 AM
  • Computed properties in the LS HTML client have been discussed many times here on the forums, and there are several approaches.  The previous post mentioned probably the most helpful treatment on the subject found at Michael's website (although the code that is listed is not useful for the HTML client and can be ignored):

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/185/Computed-Properties-With-the-LightSwitch-HTML-Client.aspx

    HTML client computed properties that involve data across relationships and tables, such as yours, are typically more involved to code. Michael has shown how to manage the difficulties using WCF RIA services.  Using the Flower Shop project as a base, I'll point out a third way to do the same thing as a RIA class, or at least the essential part, namely retrieving an anonymous type as a projection at the server, using JayData.

    First, starting the the ComputedValuesHTMLClient2013 project, NuGet package manager is used to add JayData to the project.

    Next, using the included JaySvcUtil.exe program, the entity data model file is auto-generated. Both JayData.js and the generated model JS file are added to the default.htm script list.

    In the Main Screen, a global variable to hold the FlowerShop database is created and the JayData connection to the LightSwitch database is initialized:

    var FlowerShopDb = window.FlowerShopDb || {};
    
    myapp.Main.created = function(screen) {
        // Write code here.
    
        FlowerShopDb = new LightSwitchApplication.ApplicationData({
            name: 'oData',
            oDataServiceHost: '/ApplicationData.svc'
        });
    
        $.when(FlowerShopDb.onReady())
            .then(function() {
                console.log(FlowerShopDb);
            });
    };

    On the BrowseFlowerShopOrders Screen, a custom control named "Total Order Price" will be added to the Flower Shop Orders list, right above Order Date.  In the _render method, there is:

    myapp.BrowseFlowerShopOrders.TotalOrderPrice_render = function (element, contentItem) {
        // Write code here.
        element.innerHTML = '<div id="TotalOrderPriceDiv"></div>';
    };

    Now for JayData to make short shrift of this. In the Flower Shop Order (item) Columns Layout--the row template control, the following will go in the _postRender method:

    myapp.BrowseFlowerShopOrders.RowTemplate_postRender = function(element, contentItem) {
        // Write code here.
        FlowerShopDb.FlowerShopOrderDetails
            .filter("it.OrderDetail_Order === this.OrderId", { OrderId: contentItem.data.Id })
            .map(function(order) {
                return {
                    ProductPrice: order.FlowerShopProduct.Price,
                    Quantity: order.Quantity
                }
            })
            .toArray(function(orders) {
                var orderTotal = 0;
                orders.forEach(function(o) {
                    orderTotal += o.ProductPrice * o.Quantity;
                });
            $('#TotalOrderPriceDiv', element).text('$' + orderTotal);
        });
    
    };
    Briefly, the Flower Shop Order Id is used as the filter to pull up the corresponding FlowerShopOrderDetail (via EF's OrderDetail_Order navigation property). The anonymous projection pulls up the corresponding price and quantity of the Flower Order details (again across navigation properties at the server), and stores it in an array orders. Next, it iterates through the orders array, multiplying price and quantity and aggregating to a variable orderTotal.  Finally, once the server has returned all the values and calculations are complete, the result is displayed in the Total Order Price custom control field.




    Tuesday, March 17, 2015 5:46 AM
  • Thanks qiminding and LittleBobbyTables. I appreciate the time you both took to answer my question. Based on qiminding's original suggestion I was able to solve this challenge I was having.The only "quirk" I still have with this is that when I update the child collection it results in the total returning as NaN instead of the value. When I save the screen and reopen everything displays fine. Not sure why it's doing this but I haven't investigated further yet.

    The second suggestion looks very useful and I've bookmarked this for investigation in the future.

    Saturday, March 21, 2015 2:10 PM