I've got a Table "Company" with customer account information. It has a
related table "Person" with information about individuals. Each record in
Person table has a ranking in points and percentage.

I want to find the top X% people in a company based on the points or
percentage ranking. Here's an example:

Company X has the following related Person records:
Name Rank
1. Bob 75%
2. Sally 64%
3. Frieda 54%
4. George 43%

Company Y has the following related Person records:
Name Rank
1. Chris 35%
2. Sue 29%
3. Kathy 24%
4. Fred 22%
5. Joe 19%
6. Pat 14%
7. Jack 10%
8. Terry 8%
9. Harold 5%
10. Eric 1%

Now, I want to pick out the top 50% (or X%) of people in each company based
on the value in the "Rank" field. In the examples shown, if I set a 50% Rank
threshold, I expect to select the top two from Company X and the top 5 from
Company Y.

Obviously, I can't set a simple percentage threshold for the Rank because
the range of rankings varies by company as shown. If I set a Rank threshold
of 50%, I would capture 3 people in Company X, but zero in Company Y.

Also, I don't want to get just the top 2 people in each company because the
number of people vary in each company.

Can this be done in FileMaker?

Thanks!
Steve

Yes. Look into the List() function in FileMaker 8.5. You'll be able to get a
return-delimited list of the matching items -- sorted according to the
criteria specified in the relationship -- and then analyze that list to
return however many number of lines you want.

"Steve Drenker" <sdrenker (AT) OMITTHISpacbell (DOT) net> wrote

Thanks, Bill. I am still using FMP Advanced, 8.0v3. Do I need to upgrade to
8.5 to get this function? Is there a way to do it in 8.0?

SD


in article UcudnX8Bz_p8DM7YnZ2dnUVZ_vGdnZ2d (AT) comcast (DOT) com, Bill Marriott at
wjm (AT) wjm (DOT) org wrote on 11/9/06 12:38 PM:

List() was new to 8.5, There are ways to do it in 8, but List() is nicer.
Previously, you'd have to create a value list and then use the
ValueListItems() function, or create a recursive custom function.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"Steve Drenker" <sdrenker (AT) TAKEOUTpacbell (DOT) net> wrote