By Luiz Lima - 1/7/2010
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 
|
By Trent L. Taylor - 1/7/2010
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:
public double SummedValue
{
get
{
//-- At this point you would want to sum all of the children records. See below for more details.
}
}
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:
MyChildBO.CurrentDataTable.Compute("Sum(orderItemValue)", "ParentPk = 5");
|
By Luiz Lima - 1/7/2010
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 IfEnd Sub--------------------------------------------------------------------------------------------------- But I wrote it in form! 
|
By Greg McGuffey - 1/7/2010
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:
MyChildBO.CurrentDataTable.Compute("Sum(orderItemValue)", string.Format("ParentPk = {0}",this.OrderPK));
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.
MyChildBO.CurrentDataTable.Compute("Sum(orderItemValue)", "");
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.
|
By Trent L. Taylor - 1/7/2010
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. 
|
By Edhy Rijo - 1/7/2010
Greg McGuffey (01/07/2010)
Wow, didn't know about the compute thingy...that's cool.
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!
|
By Trent L. Taylor - 1/8/2010
No problem  Glad it came in handy!
|
By Luiz Lima - 1/8/2010
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
|
By Greg McGuffey - 1/8/2010
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!
|
By Trent L. Taylor - 1/9/2010
Thanks for the sample, Greg.
|
By Luiz Lima - 1/10/2010
Greg, tks a lot!!!It was really fantastic, my problem is solved!
I´ve implemented some Business Rules events and my fields works automatically. Trent, some comments: - I think the SF documentation would have more details about Advanced things, to solve my problem I read the "Dynamic Business Object Events" chapter from help, and with Greg explanations I´ve got it! I´m a SF Beginner.
- Worth doing the SF training for advanced stuffs? (Remeber that I´m not a advanced VB developer).
- Congratulations for SF development, until now I didn´t see nothing like that (I´ve tested a lot of RAD Tools).
- You and your team came from Softvelocity Clarion? Because SF it´s very close of Clarion concepts. See ya guys!
|
By Les Pinter - 1/10/2010
Oi Luiz,
Good guess, but StrataFrame evolved not from Clarion, but from FoxPro, which had so many data handling features built in that it made .NET look a little weak. Luckily, Trent decided to fix what was wrong with it, and StrataFrame is the result.
Les
|
By Luiz Lima - 1/11/2010
Les,1-) Is there some unleashed documentations in PDF format?
2-) What do you think about your courses for beginners programmers? In 2007/2008 I had a team specialized in C# and I didn´t write a single line of code, but in last year I decided to quit my employees and prepare myself, my partner and a new team with trainees.
This is was the best choice, because I know the whole development proccess and concepts and the new team have no vicious.
The Rule is: Learn right to get the best for RAD Framework!
I have 25 years of development experience, since from: Cobol and Clipper years, Delphi, Centura, Genexus, Clarion and the best one: SF!!!!! I hope the answers for 2 questions (if possible..) See ya
|
By Dustin Taylor - 1/11/2010
1) Les has some good articles on his site that would definately help you get your feet under you, and cover several things not in the standard SF documentation. Outside of that, no, we don't have any unreleased documentaiton in PDF format, but we will be releasing the next official point release soon which will have some additions to the documentation to cover some new features.2) We don't currently have the next training course on the calendar (we schedule them based on developer availability and user demand.) That said, when it does come around I think the course would be very helpfull for beginning programmers. The curriculum is written in a way that it is accessible to anyone with a basic knowledge of strataframe and programming concepts in general. Granted, some of the back-end, data layer concepts and the like may go deeper than you are interested in, but even that is good to hear if just for a basic understanding of how things fit together. Glad to hear things are going well for you
|
By Greg McGuffey - 1/11/2010
Glad that sample helped!
|
By Luiz Lima - 1/12/2010
Dustin,Tks for your explanations. Les, Oh my... I´ve read an example from your site about security, we´re working on that right now on our system.
It´s really amazing, congratulations!!! PS: Let´s to wait the new calendar for courses... I will pratice my "Brazilian Indian English" and take the journey!!!! See ya guys.
|
|