Need related value with highest count

[Glorifindal]

Hi there,

I have been trying different methods to achieve the following - and none are correct as far as FMP 11 goes. Perhaps someone can help me ?

I need to be able to tell which values in a relationship have the highest count.

Parent record = Owner

Child record = Shop

Child record = Category

Child record = Items

If in the related items the carrots have sold the most, I need the Parent Owner to know this.

Example data:

Apples

Apples

Carrots

Carrots

Carrots

Carrots

Carrots

Carrots

Dried Fruit

Dried Fruit

The value I want to see is Carrots. The parent record has to be able to know this.

The ER is as given above - i.e. there are 2 join tables between Owner and Items

Any help greatly appreciated.

Many thanks in advance

Glorifindal

[comment]

This part is not clear:

The ER is as given above - i.e. there are 2 join tables between Owner and Items

[Glorifindal]

Sorry -

The items table is a great great grandchild of the Owner table - not just a child

Owner - shop - category - Item

Two other tables between Owner and Item

Hope that is clearer

[comment]

Maybe... Is there a table of item types - where Apples, Carrots and Dried Fruit would be unique records?

[Glorifindal]

Actually no - item is part of the line item table

[bruceR]

Are you really still stuck in FileMaker 8 or are you using something more recent?

[comment]

I'd probably do this with a custom function, but here's another way using the so-called Ugo method. I've only done grandchildren, but it's the same thing for any generation.

Of course, this won't work if a store clerk misspells "Carrots"; that's something that doesn't seem right with the system you describe.

MostFrequentGrandchildU.zip

[Glorifindal]

Hi guys,

sorry, I fell asleep (up in Scotland)

@BruceR - actually, FMP 11 - need to update - sorry

@Comment - nice one - I'll have a look and get back.

Many thanks

Glorifindal

Edited August 26, 2010 by Guest

[Lee Smith]

FMP 11 - need to update - sorry

Please take care of this right away.

Go to the Menu above, Click on "MY PROFILE" >> "Control Panel" >> "FileMaker Questions"

TIA

Lee

[Glorifindal]

Thank you very much - works fine

I would also prefer a custom function - but this will do for the moment

kindest Regards

Glorifindal