locked
Alternative to Computed Property RRS feed

  • Question

  • I have a VB LightSwitch project with 2 entities (PettyCashMaster and PettyCashDetail) attached from an external SQL Server datasource and have set up a one to many relationship from master to detail.

    I want to sum the value of 'PettyCashDetail.TotalAmount' into 'PettyCashMaster.TotalAmount'. I know this can be done using a computed property but I need to store the result in the database. I would also like to update the value of PettyCashMaster.TotalAmount without having to refresh the screen. Is this possible? If so, what's the best way to achieve this?

    • Changed type T2Mac Saturday, April 20, 2013 6:05 PM
    Friday, April 19, 2013 2:45 PM

Answers

  • The pros might give you a one line answer (I'm not claiming anywhere near a Pro yet).

    However, this post seems to be very similar to what you are wanting to do, or at least should get your started:

    change parent field based on change in child field on screen

    Good luck!


    Would someone please turn on the LIGHT?

    • Marked as answer by T2Mac Saturday, April 20, 2013 6:05 PM
    Friday, April 19, 2013 5:14 PM

All replies

  • The pros might give you a one line answer (I'm not claiming anywhere near a Pro yet).

    However, this post seems to be very similar to what you are wanting to do, or at least should get your started:

    change parent field based on change in child field on screen

    Good luck!


    Would someone please turn on the LIGHT?

    • Marked as answer by T2Mac Saturday, April 20, 2013 6:05 PM
    Friday, April 19, 2013 5:14 PM
  • Thanks for the pointer.

    First of all my entity is named PettyCashReceipt not PettyCashDetail as I previously stated.

    I tried the following code using Yann's example but unfortunately this only updates PettyCashMaster with values from the last record entered of PettyCashReceipt. 

    Private Sub PettyCashReceipts_SelectionChanged()
                Dim selectedItem = Me.PettyCashReceipts.SelectedItem
                If (selectedItem Is Nothing) Then Return
    
                Dispatchers.Main.BeginInvoke(
                    Sub()
                        RemoveHandler DirectCast(selectedItem, INotifyPropertyChanged).PropertyChanged _
                            , AddressOf PropertyChanged
                        AddHandler DirectCast(selectedItem, INotifyPropertyChanged).PropertyChanged _
                            , AddressOf PropertyChanged
                    End Sub)
            End Sub
    
            Private Sub PropertyChanged(ByVal sender As Object, ByVal e As PropertyChangedEventArgs)
                Select Case e.PropertyName
                    Case "NetAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashReceipts
                            total = d.NetAmount
                        Next
                        Me.PettyCashMaster.NetAmount = total
                    Case "VatAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashReceipts
                            total = d.VatAmount
                        Next
                        Me.PettyCashMaster.VatAmount = total
                    Case "TotalAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashReceipts
                            total = d.TotalAmount
                        Next
                        Me.PettyCashMaster.TotalAmount = total
                End Select
            End Sub

    Friday, April 19, 2013 7:05 PM
  • I think you might have it with a 'it must be midnight' change:

    Just change the code below (change "total = d.TotalAmount" to "total = total + d.TotalAmount"

                        For Each d In Me.PettyCashReceipts
                            total = total + d.TotalAmount
                        Next
                        Me.PettyCashMaster.TotalAmount = total
    

    I hope that is what it is, because it looks like you have it!

    Would someone please turn on the LIGHT?

    Friday, April 19, 2013 7:10 PM
  • You're absolutely right that sorted it, thank you.

    Now just one other little problem (well there would be wouldn't there?).

    If a detail record is deleted the master record doesn't recalculate. Any other ideas whilst you're on a roll?
    Friday, April 19, 2013 8:03 PM
  • With my limited experience, I'm not exactly sure if you would want to move your code into a separate sub, etc, but I think the method you are looking for is as follows:

            Private Sub PettyCashReceipts_Changed(e As Collections.Specialized.NotifyCollectionChangedEventArgs)
                If e.Action = Collections.Specialized.NotifyCollectionChangedAction.Remove Then
                    'put your code in here to iterate all totals
                End If
            End Sub
    

    I'm not sure how that would behave, but one of the options there might get you in the right direction.

    ** PROS ** any comment here would be good to make sure this is the best practice...


    Would someone please turn on the LIGHT?

    Friday, April 19, 2013 8:13 PM
  • Also, I would be tempted to add a trigger to the SQL database on the After Insert,Delete,Update, and do the same basic code.  In that way you can NEVER be out of sync.

    Would someone please turn on the LIGHT?

    Friday, April 19, 2013 8:19 PM
  • Not sure if this is what you meant but the recalculate after delete still doesn't work.

    I'll have another look at this in the morning as my brain's starting to ache and I need a glass (or two) of wine.

     Private Sub PettyCashDetails_SelectionChanged()
                Dim selectedItem = Me.PettyCashDetails.SelectedItem
                If (selectedItem Is Nothing) Then Return
    
                Dispatchers.Main.BeginInvoke(
                    Sub()
                        RemoveHandler DirectCast(selectedItem, INotifyPropertyChanged).PropertyChanged _
                            , AddressOf PropertyChanged
                        AddHandler DirectCast(selectedItem, INotifyPropertyChanged).PropertyChanged _
                            , AddressOf PropertyChanged
                    End Sub)
            End Sub
    
            Private Sub PropertyChanged(ByVal sender As Object, ByVal e As PropertyChangedEventArgs)
                Select Case e.PropertyName
                    Case "NetAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashDetails
                            total = total + d.NetAmount
                        Next
                        Me.PettyCashReceipts.SelectedItem.NetAmount = total
                    Case "VatAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashDetails
                            total = total + d.VatAmount
                        Next
                        Me.PettyCashReceipts.SelectedItem.VatAmount = total
                    Case "TotalAmount"
                        Dim total = 0
                        For Each d In Me.PettyCashDetails
                            total = total + d.TotalAmount
                        Next
                        Me.PettyCashReceipts.SelectedItem.TotalAmount = total
                End Select
            End Sub
    
            Private Sub PettyCashReceipts_Changed(e As Collections.Specialized.NotifyCollectionChangedEventArgs)
                If e.Action = Collections.Specialized.NotifyCollectionChangedAction.Remove Then
                    Dim ntotal = 0
                            For Each d In Me.PettyCashDetails
                        ntotal = ntotal + d.NetAmount
                            Next
                    Me.PettyCashReceipts.SelectedItem.NetAmount = ntotal
                    Dim vtotal = 0
                            For Each d In Me.PettyCashDetails
                        vtotal = vtotal + d.VatAmount
                            Next
                    Me.PettyCashReceipts.SelectedItem.VatAmount = vtotal
                    Dim total = 0
                            For Each d In Me.PettyCashDetails
                                total = total + d.TotalAmount
                            Next
                            Me.PettyCashReceipts.SelectedItem.TotalAmount = total
                End If
            End Sub
    Friday, April 19, 2013 8:44 PM
  • Looking at this with fresh eyes I see I had typed PettyCashReceipts_Changed when it should have been PettyCashDetails_Changed. All is now working as expected.

    Thanks again.

    Saturday, April 20, 2013 5:57 PM
  • Excellent!  Glad my limited experience helped!


    Would someone please turn on the LIGHT?

    Sunday, April 21, 2013 3:28 AM