PDA

View Full Version : Calculated Field Expression using Grouping in Field Rules


ABC123

gazzat
09-23-2007, 12:18 AM
I'm very new to ALpha Five and am probably asking a stupid question but:

I have a field in a table that requires the value to be calculated from a group of other fields in the same table.

In excel the formula would be Cell C8 =sum(C1:C20). Is it possible to do this using the Field Rules and Expression Editor? If not is there a recommended way of achieving this result? In ALPHA I wish to have the Field 'AdjustedMarketValue' = 100 divided by total(MarketValue) for a group of Day, Venue, Section.

Any help is appreciated and will get me started down the right road.

Regards
GazzaT

Tim Kiebert
09-23-2007, 04:40 AM
The two functions I would suggest to get a sum of field values at the table level are DbSum() if you have an index defined that would group your records or TableSum() if you don't. DbSum() is faster because it uses an index but TableSum() is a bit more flexible because it takes a filter.

To achieve the value you are after for each record in the table you would enter something like this in the calculation for theAdjustedMarketValue field. Make the appropriate changes to match your field names

100/tablesum("YourTableName","Day+Venue+Section="+s_quote(Day+Venue+Section),"Marketvalue")
The expression may have to be tweaked a bit if your fields are not all character.


However, even though this expression may return the correct value in the first instance I have a feeling it is not going to behave the way you think it will behave. A calculated field is a stored value. Yes it is arrived at through calculation but once arrived at it is stored. It only gets updated when something in its own record changes. So in your case you could have four records that fit your grouping with marketvalues of 100 each. If you now add the calculation to the AdjustedMarketValue field the field will be empty unless you apply a recalculation to the table. When doing so you would get .25 in each record (100/400). If you add another record that matches the same filter with a value of 100 then the field AdjustedMarketValue would still only result in .25 (100/400) because there are only four saved records to sum up. If you now edit the newly entered record it will update to .2 (100/500). However the four previously existing records will still hold the value of .25. They won't get updated until they are edited or a recalculation applied.

Do you really need to have this value stored in the table?

Through the use of an unstored calculation you can have access to this value pretty much anywhere you need to see it. Such as on a form or in a report. Unstored calculations can be defined in a number of places. In a form, in a report, letter or label. They can also be defined at the table level (when editing the table structure use the xy buton on the toolbar) so that where ever the table is used the calculation will automatically be available. The actual expression will essentually be the same.

gazzat
09-24-2007, 03:28 AM
Many thanks for that well versed explanation Tim,

It is appreciated. I think you have set me off in the right direction too. The 'unstored calculation' maybe exactly what I required to complete this excercise. I am going to go back and doing a redesign of my approach.

Once again.

Many thanks.

and. good luck Saturday if you are an AFL follower. :-)

Cheers
GazzaT

Tim Kiebert
09-24-2007, 03:41 AM
Many thanks.

and. good luck Saturday if you are an AFL follower. :-)

Cheers
GazzaTYou're welcome and thanks. GO the CATS!!!! I am not a hard core fan but you can't help but get caught up in the excitement. especially as its been a while for the Cats. As luck would have it I actually topped the footy tipping at work. Yay me.

Do I detect an aussie accent?

And welcome to Alpha and the forum.

dptroutt
12-02-2007, 05:15 PM
Do you really need this calculation at the table level?
Generally, you would create this at the Form or Report level by a simple calculated field on the Form or Report designer.

If you need it at the table level, you can create an expression for that field by Design/Edit Field Rules.

On re-reading, I see I've recapitulated the last part of Tim's answer. Oh, well.