Hi,

I´d like to sum a field value from a Child BO into its parent.
Is there a method to do that automatically?

Example:

BoOrders
field: orderTotalProduct

BoOrdersItens
field: orderItemTotalProduct

While the user input orderItemTotalProduct, the field orderTotalProduct would receive the sum from child (in the EditState set as Adding or Editing).

Tks

So if I understand you correctly, you want to have the sum of all of the order items as a property exposed on the parent BO. is that correct? If so, just create a custom property on the parent BO that returns it from the child records. For example:







Next, where the comment is above, I would recommend trying a Compute off of the CurrentDataTable of the BO. You can actually specify an expression which will SUM all of the records on the BO. For example:

Trent,

You understood correctly but, if the BO would in Adding Editstate, how can I pass ParentPk?

MyChildBO.CurrentDataTable.Compute("Sum(orderItemValue)", "ParentPk = 5");

In your example ParentPk = 5, but my PK will be -1 or -2

I wrote this code bellow, what do you think?

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

Private Sub BoPedidoCompra_Itens1_CheckRulesOnCurrentRow(ByVal e As MicroFour.StrataFrame.Business.CheckRulesEventArgs) Handles
BoPedidoCompra_Itens1.CheckRulesOnCurrentRow

BoPedidoCompra1.pec_valtotal_item = 0
If BoPedidoCompra_Itens1.MoveFirst() Then
   Do
     If BoPedidoCompra_Itens1.itc_total_mercadorias <> Nothing Then
        BoPedidoCompra1.pec_valtotal_item = BoPedidoCompra1.pec_valtotal_item + 
                                                           BoPedidoCompra_Itens1.itc_total_mercadorias

     End If
     Loop While BoPedidoCompra_Itens1.MoveNext()
End If

End Sub

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

But I wrote it in form!
 

Wow, didn't know about the compute thingy...that's cool.



Luiz, to get the correct total, you have a couple of things to think about. First, how do you have your parent/child BOs setup to load. One way is to load all the parents and all the children, then filter the children to match the current parent. In that case, if I understand what Trent is suggesting, you'd do something like:







where OrderPK is the PK column of the orders BO/table.



This will actually work even before it is saved because StrataFrame assigns the temporary PK to both the new order and any new child items before it is saved, then updates the FK in the child items with the correct order PK during the save (if you've setup everything that way).



However, this can often be less than idea, as it could require a lot of data to be loaded into the client (e.g. the StrataFrameSamples OrderItems table has 200K+ rows, which would be way slower than loading just the typically number of order items, around 10 in this case). So another strategy is to only load order items that belong to the current order (by handling the orderBOs navigated event). If you load only the orders belonging to the current order, then I'd think you wouldn't need to provide a filter for the compute method.







The same holds true regarding PK/FK in the BOs in this scenario (if you've set it up correctly).



Some other options:



- in SummedValue property of the parent, just query the db. This could be bad if it is getting called a lot, so careful... This is nice if there are other ways the child records can get updated, as it will always return the correct current value.

- If you desired to persist the total in the parent table, you could setup the child BO to raise events when properties are changing and changed, then handle those events for the child quantity field to update the summed quantity column in the parent table. You'd need to setup parent relationship with the parent BO and set the ParentBusinessObject property on the form were the user is updating this. This has the opposite potential issue as the first suggestion, in that the parent total could be incorrect if you don't control all updates to the carefully. If you expect multiple users to be adding/editing items at the same time, this method could be bad.

Luiz,



Greg is correct that it should work. If you have the relationship setup between the parent and child, then the foreign key on the child will be automatically managed...thus, if you supply the primary key of the parent (instead of the "5" I had as an example) then it will still work. The beauty of the SF automatic management of foreign key fields...including new and unsaved records.

In fact, it is very cool, I just test it in one of my project and it is a much simple way to get this kind of calculation, instead of looping the BO.



Thanks Trent!

Edhy Rijo

No problem Glad it came in handy!

Trent and Greg,

Tks a lot, but I still have doubts... i´m starter (don´t forget! )

My Parent is: boPedidoCompra1, field to be summarized: pec_valtotal_item (it should be saved into table)
My Child is: boPedidoCompraItens1, total field: itc_total_mercadorias

How will I create a property on Parent boPedidoCompra1 if the pec_valtotal already already exist?
The code would be like below on boPedidoCompra1?

boPedidoCompraItens1.CurrentDataTable.Compute("Sum(itc_total_mercadorias)","");

 (and... where I put it in my code below generated by BOM?)

''' <summary>
''' Valor Total dos Itens
''' </summary>
''' <remarks></remarks>
<Browsable(False), _
BusinessFieldDisplayInEditor(), _
Description("Valor Total dos Itens"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public Property [pec_valtotal_item]() As System.Decimal
    Get
        Dim loValue As Object
         loValue = Me.CurrentRow.Item("pec_valtotal_item")
         If loValue Is DBNull.Value Then
            Return Nothing
         Else
           Return CType(loValue, System.Decimal)
         End If
     End Get
     Set(ByVal value As System.Decimal)
        Me.CurrentRow.Item("pec_valtotal_item") = value
    End Set
End Property

If you are saving it into the table, then the Compute method might not be the way to go. That is the way to go if you are going to calculate it when you need it.



If you are saving it to the table, then no custom property needed. It will be added when you map the BO (since there should be a column in the table to hold it). Thus, the saving, retrieving of the value from the table is taken care just like any other column in the table, via the BO.



However, the source of the data is different. Instead of a user filling it in, you need to manage the value of the parent total column, pec_valtotal_item, based on edits to the child items, from the itc_total_mercadorias field. I'm sure there are lots of ways to do this, but here are a few to start (these assume you want to persist the total to the table, as you indicated):



- in the child BO, handle some key events and keep the parent record updated.

- During the process of saving the child BO, call a sproc to keep parent updated.



The issue with the first is that you'd then have to code the UI to keep it fresh. I've worked up a sample that implements the first solution. It uses the StrataFrameSample database. Run the sql script in the SQL folder to update the Orders table to include a total column and initialize it with the correct data.



This sample uses BO property events (Changing and Changed) along with the handling BeforeUndo and AfterUndo events. The basic concept is that the child BO () updates its parent during edits, via these events.



Hope this helps!

Thanks for the sample, Greg.