how to implement an Excel "Large" equivalent

[larosche]

I am converting an Excel spreadsheet to Filemaker. I am using the "large" function in Excel to get the most significant numbers from a selection.

The user interface was easy, but I am stumbling on a method to figure the 7 largest numbers from a selection of ten fields.

I am using a calculated values and can easily find the min and max by using

"Max(race1,race2,race3,race4,race5,race6)", but how to find the 2nd highest number?

Thanks for any help,

RAD DAD

[Ugo DI LUCA]

Hmm...

You could add a lot of calculations.

But you don't want to do this, do you ?

Check attachment if it can help.

max.fp5.zip

[ESpringer]

There may be elegant solutions out there... but here's what I'd probably try: farm the race values out into a related file, so that there are 10 records for each of the records in your main database file, linked via whatever the main record ID is (date, person, whatever). Set this INCLUSIVE relation to: (1) allow creation of related records and (2) sort (lowest first).

Set up an "omit tag" number field in the related file

Set up a filtered ID calc field in the related file which is *blank* if there's a 1 in "omit tag" field; otherwise it returns the main ID.

Set up a second FILTERED relation that relates your main ID only to the filtered ID key in the related file

(that is, the ID that shows up when omit tag isn't 1). Sort it lowest first also.

Now, you can have a portal to your race data, and for each value, there's a field for "omit tag". Since "allow creation" is on, your data entry can proceed just as if the portal rows were fields in the main db.

You could manually use this portal for the inclusive relation to omit-tag the three lowest values (conveniently at top).

Or, you could get a looping script to go to the related records, make sure they're sorted, go to first, tag omit, go to next, tag omit, go to next, tag omit, (now the lowest three are omit-tagged) Go back to MAIN file, go to its next record and do the same, etc.

(Perhaps you could set up a script to add the omit tag to the filtered-related record with the lowest value - which will always be the first one, so you just tell it to invoke "the" related record, and it will choose the first in the sort order. Maybe if you set up the script through the filtered relation, it could be easily looped three times. It might require some kind of refresh like zipping out to another field in the main db and back to "the related record" (haven't tried it) and loop three times, it will recognize on each loop that there is now one fewer related value, and it will be able to access the new "top" (lowest) one each time?)

A calc field that looks at the records in the *second* relation (the one filtered by omit-tag) can determine the average, or total, or median (etc.) of those highest 7 of 10 values.

[dbruggmann]

I'm not sure if I understood your setup correctly:

Do you have one FM file with six race fields (fields

[ESpringer]

If I understood, each record in larosche's one file had a mini-array of distinct non-repeated race fields (ten?).

The structure of FM doesn't lend itself to doing calculations such as finding the top 7 (or bottom 7) numeric values across ten different fields. If those fields are to be compared as similar kinds of data, they're more appropriately treated as records in a related file, much like line items in a purchase order...

What I myself don't know is whether, once the values are moved out into their own related file, there's an elegant way -- besides scripts like the one I imagine (which works by tagging some records for omission from a variant on the relational key) -- for FM to do calculations with "the highest 7 of 10 related values" or similar things.

[dbruggmann]

If there's no need for further calculations and the only thing you want is to show the seven highest/best/whatsoever results:

Create a portal based on a selfjoin (sorted as needed) with exact seven rows and no scroll bar!

[Ugo DI LUCA]

Hi guys,

I was editing my former post when the forum closed on friday...

Though I also believe in relationships.

This one works even if 2 fields have the same value, and is completed up to 10. Scripted loop.

Ranking.fp5.zip

[larosche]

Thanks everyone. I am looking over the suggestions. I have many applications that require sorting by a max to min number of fields within a record. I wish that Filemaker included this capability.

In this case I am trying to use the best 7 of 10 possible scores to calculate the year end score.

Currently I am trying to simplify my role as Points Steward for a racing organization. I have found that I can make little mistakes in Excel that affect our year end awards. I am hoping the conversion to Filemaker will reduce these errors.

RAD DAD

[larosche]

I have worked on the example that Ugo sent and am having problems. Several weird things are happening that I cannot explain. When I try to look at the "Ranking" button I get an error about "The selected group of objects include at least one previously identified button...."

I have modified the database to work with my caculated positions. If you use the script "rank test" on record 1 then ranking number 8 shows 2 which is in-correct. Record 2 works correctly and record 3 is in-correct after rank one.

Any ideas?

Thanks again,

RAD DAD

(how do I attach a file with the new user interface?? I attached the .fp5 but changed the name to .jpg

[Ugo DI LUCA]

Hi,

Delete the (-1) step in the first End Loop If step as this is causing the bug I think...

If not, post your file back zipped.

[larosche]

Not sure which line you want me to remove. I tried removing the line after the fist "loop", but it put the script in an endless loop.

Here is the zipped file.

SERA Year scores.zip

[Ugo DI LUCA]

Here's your file back.

I was talking about the step

End Loop If[g_dupesCounter =Patterncount(g_stack, g_MaxInText))-1

that needed to be converted to

End Loop If[g_dupesCounter =Patterncount(g_stack, g_MaxInText))

It should work now.

SERA Year scores.fp5.zip