Summarizing fields by unique values

[Jarvis]

I would like to have a database with three fields:

Item

Unit Cost

Total Cost of All Unique Items (by category)

The ITEM field is populated from a value list which gets its values from the Item Field.

(If we purchase an item that has never been purchased before, we add the new Item to the value list

If we purchase an item that is already in the database we just select it from the valuelist.)

I would like to be able to keep an eye on how much money we have spent to date on any particular category.

I would like the code for this question to keep itself updated as new items are added to

the value list.

I imagine this starts out as some kind of a perform-find unique records, then sum their value.

Is there a sum function that can total these fields as they are added on the fly?

Thanks for any guidance here.

Jarvis

[LiveOak]

Two options are: 1) to make Total Cost a summary field and display the total in a "Subsummary" layout part. The total by category will only be visible when the records are sorted by category and when the report is viewed in Preview Mode or printed. 2) You can create another table (file) in which each record is a category. This second table (file) would be related to the first by category. A calculated field in this second file would be defined as Sum(YourRelationship::Unit Cost). In a list view of records in this new file, this field will display a total by category.

-bd

[Jarvis]

bd,

Thanks for the direction. I was able to sum the individual fields nicely.

Since it's working so well now, I'd like to complicate it.

In order to derive the sum in the second table, I have to produce one record at a time with a corresponding value from the first table.

Is there a way to script the creation of all records in the second table?

What I would like is something that searches the first table, discovers that there are X number of unique values in the category field then automatically creates a record and posts those values in the second table.

I hope I explained that right.

I am trying to automate creating the list of unique values and summations.

Thanks for your forbearance,

Jarvis