Sum for group in memory

[Former Member]

Hello everybody:

I need some help, I have a tabular datawindow and it´s a data entry, it has a money field and I have to group for 3 fields and sum the money field,

the problem is the data is not in database for doing a sum group by, by the way if the sum is less than 0 for any grouping I can´t save the data. Can I sum in memory  or something like that.

I will appreciate any help

Regards

SandraE

[Former Member]

If you want to do a group sum and validations based on the sum, here's some guides for you:

First step is you create the group and specify the sort order accordingly. The group can be based on one column or on multiple columns as one group.

Second is you create two sum compute fields. Both have the same expressions/formula ( e.g. sum(col1 for group 1) ). Name one field as group_sum1 and the other one detail_sum1. Place the group_sum1 in the Trailer of the Group band and the detail_sum1(you can hide this field) in the Detail band.

Third, you do a Find (using the DW Find method) for detail_sum1 which is less than 0 or whatever validations you want to make. You will need to access the detail_sum1 (NOT the group_sum1) field to validate the group sum.

Note: There is also a FindGroupChange DW method that you may find useful.

HTH,

Neil

[Former Member]

Hi Sandra;

   The DataWindow has a  Group feature of course and a Sum () method support. This would create a computed column. I would put the summed column (field) in the DW's summary band. You can make it non-visible if you like. To access computed columns in the header, footer or summary  band, just use the standard GetItemXxxx() method -  always specifying Row #1.

  Using the DW's internal Sum() method is much faster than performing a Sum within a loop plus, the pain of coding the loop in the first place.

Tip: combine an IF() method within a Sum() to conditionally count/Sum anything. That way, you could have 3 group calculations for example using three computed Sum(If)) columns.   😉

HTH

Regards ... Chris

[Former Member]

If all your required data is in the datawindow the simplest is create a computed field in a group header or trailer.  You should do a sort() and GroupCalc() before saving to ensure the sum is calculated correctly as part of your validation prior to Update(). Note that the sort() will cause the datawindow to scroll to first row.

[Former Member]

Hi Lars

Thanks for answering.

There is not a group in dw the idea is in a messagebox show the error to users, I need to validate every result (sum) for eachgrouping so I created a group with computed field but I couldn´t get the value for each result and compared if it is less than 0.

Regards

SandraE

[Former Member]

You can cycle through in a for next loop and do a getitemdecimal of the computed item. If its zero then

get appropriate info for the messagebox.

long ll_row

decimal ldec_sum

for ll_row = 1 to dw_1.RowCount()

     ldec_sum = dw_1.GetItemDecimal ( ll_row , 'c_sum' )

     if ldec....

[ricardojasso]

Sandra,

Be sure to include "for group 1" inside the sum function of the calculated field in the dw:

Field: c_money_sum

Expression: sum(money_field for group 1)

You can get the sum value with the GetItemNumber function:

decimal ldec_sum

ldec_sum = dw_1.GetItemNumber(ll_i, 'c_money_sum')

The thing is the GetItemNumber will give you the group sum associated with each particular row. So it will repeat itself until a row from a new group is reached. AFAIK you cannot get the group value for a specific group.

Let's say your data is grouped in three rows each. You'll get the same value for the first three rows and then a new value for the next three rows and so on. So you need to know when a row changes group to get the new group value and validate it. In other words, you need to use a loop and read the group value in each row to know when a group has changed.

Regards,

Ricardo