Average of matching items in a single table

[bluearrow]

Hi,

I have a table for items. Items are defined by a Class, Type and Color combination. Items have a price.

For instance:

Item1: 1, 3, 4, 4 (Class, Type, Color, Price).

Item2: 2, 3, 6, 8

Item3: 2, 3, 6, 2

Item4: 1, 3, 4, 2

Item5: 2, 3, 6, 12

Item6: 2, 1, 1, 6

What I need to obtain is the average price for each Class, Type, Price combination, not in a report, but in a field within the table. The field should update every time a price change or a new combination is created.

In the sample above it would be:

1, 3, 4 = (4 + 2) / 2 = 3 (average price for item with combination 1, 3, 4)

2, 3, 6 = (8 + 2 + 12) / 3 = 7,34

2, 1, 1 = 6 / 1 = 6

I do not mind creating a new table or using a custom function, if that is the best route to go.

Note: I was trying to attach attach a file (tried FP7, xlsx, csv, htm) with a number of records and sample values, for your convenience, but for some reason I always get the message "You aren't permitted to upload this kind of file". If you know the reason and solution, I will upload the file.

Thanks in advance

[eos]

Uploads must be in .zip format.

First idea that comes to mind - “quick and dirty” - is creating a self-relationship with a compound key of Class, Type and Color (use a delimiter - like a pipe - so the IDs can

have any number of digits), or, a bit cleaner and more fp7, use a multi-predicate relationship with these three fields.

Have a look into the attached file.

For a “cleaner” solution with the same info stored only once, create a Combinations table where each record represents a unique combination of Class, Type and Color.

To calculate the pertinent information, establish a relationship like the one described above, but now from Combinations to Items.

You can then display the appropriate average (or other aggregate data) in the Items table.

You'd need a mechanism to ensure that the Combinations table is up-to-date, i.e. that for every Item record with a new unique combination, a new Combination record is created,

and if the last/only Items record of a given combination is deleted (should you allow deletion), that the matching Combinations record goes, too.

Normally I'd say use field triggers, but with FMPA 9, you have to find another way.

CombinationCalculation.fp7.zip

[bluearrow]

Thank you very much for the file. Your solution works fine. I will take into account your comments about a cleaner solution (in fact, I am using FMPA 11, not 9; I have modified that on my profile).

Thanks again