The attached file has a custom function that returns the median value(s) of a return delimited list.

Since I can't figure out how to get all the related values in a portal into one calculated field...(ValueListItems only returns unique values) I tried to use the primary keys of the related table to make the value list instead...sorted by the values, not the keys. I can then use the keys corresponding to the median values in relationships to return the values. This file works, but it seems like kind of a hack, and there are field refresh issues due to the way I did it.

Can anyone see a way to get all of the values in a one-to-many relationship into a single field, sorted and delimited so that it can be operated on by a custom function? Or some slicker way to write a custom function that will return the median of related values?

median.fp7.zip

The equation for median is

m = b + d/f * c

where m = median, b = lower boundary of the class containing the median,

c = length of class containing the median, f = frequency of the class containing the median, and d = n/2 minus the cumulative frequency at the lower boundary b of the class containing the median, and n = the number of observations. (This is from an old college statistics book.) So it's just a matter of defining a calculation field which takes b, d, f, and c.

It's not the equation for obtaining the median that's the problem, it's getting all of the related values (including duplicate ones) into one delimited field so it can be passed to a calculation. You can't just pass the related field like you can with the native FM aggregate functions. Which brings up the point of why there is no native FM function to do this....

It's easy to do this using a script... I'm just trying to define a calculation instead of a script.

OK, then use The Shadow's custom matrix functions. You will need several intermediate calculation fields which will feed into the final one.

Perform applescript:

copy cell "relation::Datafield" of current record to temp

-- this gets the entire "column" of related data

-- in list format: {1,2,3.1,4,1,5}

set applescript's text item delimiters to return

copy temp as text to cell "Whatever" of current record

But Smile has great statistical functions that you could call on also:

statlist : returns as a record the min, max, min index, max index, mean, sum, standard deviation, variance.

statlist a list of real -- ... or an array of real

Result : record

So in your case the script would be:

copy cell "relation::Datafield" of current record to temp

copy item 5 of (stalist temp) to cell "Mean" of current record

Thanks for the cool Applescript method to get the related data out of FM.... unfortunately most of the machines on the network are on windows, and I don't really want to use VB/Active X given my lack of experience with it.

Here's a method I picked up and modified from another discussion of a similar topic.

In the data table, create a concatentation field = dataField & " " & serialField returning data type number. Create a value list of the values showing (and sorted by) the new calc field. This way ValueListItems will show all of the values since they are sorted on a field guaranteed to be unique... and they will be sorted in the proper order. Now my custom function can return the median value of this return delimited list.

Of course it wouldn't have to be a custom function, but it makes it easy to reuse anywhere in the file.

Here is the modified file...

median.fp7.zip

Can't get your function to work on FM Dev 7.03 in Mac OSX.

Any suggestions?