Calculation problem

[Greg Hains]

Hi.

I have an issue that I cannot resolve and was wondering if somebody could please point me in the right direction.

I will give you the cut-down, but relevant version.

I have a table. It has two fields, "group", and "amount". For example:

Group Amount

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

Apple 15

Apple 21

Banana 4

Carrot 45

Carrot 22

Eggplant 6

I am trying to create a script that will summarise these and put them into a global text variable that looks like this

Apple 36

Banana 4

Carrot 67

Eggplant 6

The actual formatting of the text field I can do, but am having problems with the _logic_ in creating the text.

As it happens, I had the text group extraction working well, and as soon as I started with the numbers I stuffed it up and cant return to that point.

Its fairly simple logic I know, but I am making a mess of it. Is this is the best way to go about it, reading through each record making a text variable, or should I be using the database method?

Any ideas please?

Thanks in advance. Greg

[Matthew F]

How about the following approach.

1. Create a field called AmtSum which is a summary field that is set to the total of Amount.

2. Create a layout that has no body but just a sub-summary part is set to display when sorted by name.

3. Create a script that does:

a. Goes to this new layout

b. Loops over each record and sets gText = gText & ¶ & Name & " " & AmtSum

[Greg Hains]

Hi.

Thanks for this. I will give it a try right now.

Cheers,

Greg

[Matthew F]

My bad. I forgot that the script won't access sub-summary fields.

Take a look at this example, which uses a self-join relationship based on name (in addition to the summary field for number described above) ...

summarize.zip

[comment]

This is not the same thing as before. A self-join relationship will summarize ALL records in the category, not just the found set.

I don't see why a straightforward summary report wouldn't be sufficient, but if so desired, the report can be written out to a field using a technique known as Fast Summaries.

[Vaughan]

I made a custom function that may go part of the way to the solution:

http://www.briandunning.com/cf/1027

It summarises a list of values.

[Greg Hains]

I made a custom function that may go part of the way to the solution:

http://www.briandunning.com/cf/1027

It summarises a list of values.

Hi Vaughan,

That function works really well - thanks!

I have been working on this function (on and off admittedly) to do a subtotal of values in there. The example above counts the similar items, but in my case I need it to subtotal of a an extra field attached to that item.

eg. my list is: apple, 10

banana, 12

apple, 15

and the resulting output would give:

apple 25

banana 12

Im not fussed about delimiters or how it returns, just so long as it would subtotal each item "group"

I cannot work out how to calculate an extra value in that function. Any help would be hugely appreciated.

Cheers,

Greg